compare table by row and per row column to another table

Question

I have two tables with the same structure. (refer below)

records = id (int, auto increment), name (varchar 250), age (int), address (varchar 250)
backup = id (int), name (varchar 250), age (int), address (varchar 250)

both table contents 3 records. Now, I want to compare those two tables per row by per column of the row and if there's a row that didnt match to the corresponding row on the compared table then produce and error.

table "records" content:

row 1 : column id = 1, column name = name1, column age = 12, column address = USA
row 2 : column id = 2, column name = name2, column age = 17, column address = USA
row 3 : column id = 3, column name = name3, column age = 32, column address = USA

table "backup" content:

row 1 : column id = 1, column name = name1change, column age = 12, column address = USA
row 2 : column id = 2, column name = name2, column age = 17, column address = USA
row 3 : column id = 3, column name = name3, column age = 32, column address = USA

as you can see from the above table contents, in the table "backup row 1, column name" there is a changes, name1 (records table) is changed to name1change (backup table). So when the row 1 of table records is compare to row 1 of table backup and found a difference, it will then get the row number and column name followed by a text of error (refer below)

echo $errorRow . "with" . $errorColumnOfTheErrorRow . "didn't match, there's is changes";

and I want to loop until the end of records with the same process (compare). How to make it? any ideas, help, suggestions and recommendation would be gladly appreciated. Thank you!

so far, what i tried is (refer below)

$sql = "SELECT DISTINCT * FROM (SELECT a.* FROM records a UNION SELECT b.* FROM backup b ) c";
$result = mysqli_query($this->db,$sql);
$sql = "SELECT COUNT(*), id FROM (SELECT DISTINCT * FROM (SELECT a.* FROM records a UNION SELECT b.* FROM backup b ) c ) d GROUP BY id;";
$result = mysqli_query($this->db,$sql);

echo $result;

as above query, im trying to display the unmatched row but sadly, not working.

What have you tried so far? Do you have any code attempt? Note- you could use a JOIN in MySQL so that you only return the rows that don't match -> SELECT * FROM records r LEFT JOIN backup b ON r.id=b.id WHERE r.name != b.name OR r.address != b.address — Sean, Mar 27 '15 at 03:21

Norbert · Answer 1 · 2015-03-27T03:39:15.333

0

You can do this is SQL in a nice way: First step: put all the data together and keep only the distinct (=not the same) rows:

SELECT DISTINCT *
FROM (
SELECT a.*
FROM records a
UNION
SELECT b.*
FROM backup b ) c

Second step: Count all records where ID occurs more then 1 time:

SELECT COUNT(*), id 
FROM (
SELECT DISTINCT *
FROM (
SELECT a.*
FROM records a
UNION
SELECT b.*
FROM backup b ) c ) d
GROUP BY id
HAVING COUNT(*)>1;

This will give you the exact ids where the rows are not the same.

Third step: Get you the rows with the changed data:

SELECT * FROM (
SELECT "current data" AS description,f.* FROM 
records f
INNER JOIN (
SELECT id 
    FROM (
    SELECT DISTINCT *
    FROM (
    SELECT a.*
    FROM records a
    UNION
    SELECT b.*
    FROM backup b ) c ) d
    GROUP BY id
    HAVING COUNT(*)>1 ) e ON f.id=e.id
UNION 
SELECT "backup data" AS description, g.* FROM 
records g
INNER JOIN (
SELECT id 
    FROM (
    SELECT DISTINCT *
    FROM (
    SELECT a.*
    FROM records a
    UNION
    SELECT b.*
    FROM backup b ) c ) d
    GROUP BY id
    HAVING COUNT(*)>1 ) h ON g.id=h.id;
) i ORDER BY id ASC, description ASC;

It is not pretty, but it works :)

edited Mar 27 '15 at 03:39

answered Mar 27 '15 at 03:23

Norbert

6,026
3
17
40

Hi, thanks for the answer. how to display the rows that are not the same? – Juliver Galleto Mar 27 '15 at 03:33
thank you for the answer. I got an error in this line "SELECT "current data" AS description,f.* FROM " saying "syntax error, unexpected identifier: 'current'" any ideas how to fix this? – Juliver Galleto Mar 27 '15 at 03:57
how to integrate this in php? – Juliver Galleto Mar 27 '15 at 04:10
Sorry, you will have to name all your columns in the SELECT "current data" as description, {your column names}. Same for the SELECT "backup data" AS description, {your column names}. For integration in php you are on the right path already, except that you will get multiple results. There are loads of examples about that. For example http://stackoverflow.com/questions/1756743/how-to-loop-through-a-mysql-result-set – Norbert Mar 27 '15 at 04:10

compare table by row and per row column to another table

1 Answers1