CODEWITHSUNDEEP
phphtmlmysqldatabase
JF058
JF058

Reputation: 33

How to compare two tables and return mismatches with PHP

I have 2 tables below with both a list of names.

table1

id  name            joined_on
1   Daniel Smith    2018-07-01 00:00:00
2   James Owen      2018-07-03 00:00:00
3   Dave John       2018-04-04 00:00:00
4   Dean Davidson   2018-02-01 00:00:00
5   James Saunders  2018-07-04 01:05:02
6   Earl Smith      2018-07-04 01:05:19
7   Faud Earl       2018-07-04 01:07:46
8   Casper James    2018-05-01 00:00:00

table2

id  name            joined_on
1   Daniel Smith    2018-07-04 00:00:00
2   James Owen      2018-07-04 01:04:03
3   Dale Davidson   2018-02-02 00:00:00
4   Faud Earl       2018-05-15 00:00:00
5   Casper James    2018-05-26 00:00:00
6   Dave John       2018-07-04 01:05:10

How do I compare all names of table1 with all the names of table2 and return all mismatches. I want to achieve that it will return all names from table1 that are not in table2.

I need this for a school assignment but I just don't know where to start. I'd appreciate if somebody could help.

Edit:

Now I got this, I tried printing the result out on different ways but it doesn't return the names, it only returns "NULL".

$sql = "SELECT name from Players_christmas where name not in (select name from Players_halloween";
$assoc = mysqli_fetch_assoc($sql);
var_dump($assoc);

Upvotes: 0

Views: 55

Answers (3)

Jeff
Jeff

Reputation: 6953

As you already have in your description "all names from table1 that are not in table2" you can do:

SELECT `name` from `table1` 
WHERE `name` not in (SELECT `name` from `table2`)

here's a fiddle: http://sqlfiddle.com/#!9/e87c78/1

Upvotes: 0

Zac
Zac

Reputation: 2081

I'm thinking you could query to get all data from each table and for each row, store as an associative array or just a regular array into an overall array as shown below.

$sql = "SELECT * FROM your_table1";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
                // output data of each row
                while($row = $result->fetch_assoc()) {
                    $table1_rows[$i] = $row;
                    $i++;
                }

then once you have both $table1_rows and $table2_rows, you can use this difference between arrays function ( array_diff_assoc for associative arrays, array_diff for a standard array )

$array_of_different_indexes = array_diff($table1_rows,$table2_rows);

the array_diff function is really handy, here's a link for it https://secure.php.net/manual/en/function.array-diff.php

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You can do directly in SQL

You could use left join and check for null values 

  select name from table1 
  left join table2 on table1.name = table2.name 
  where t2.name is null

Upvotes: 1

Related Questions