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);


...WHERE name not in (Select name from table2)

mysqli_query($databaseConnection, $sql)

3 Answers
3

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


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);

$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

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

mysqli_query($databaseConnection, $sql)

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.