easiest way to compare 2 rows from different tables

[seany123]

Hello,

What would be the easiest way to compare rows from different databases?

I have 2 tables 1 is live and the other is for newly inserted data which needs to be compared against.

firstly the unique_id must match, if not then comparing the rest of the columns is irrelevant.

 

The structure is the same for both tables, something like this:

id

unique_id

val1

val2

val3

 

so i thought i could do something like this

<?php
$q = mysqli_query($conn, "SELECT * FROM live_table LIMIT 10");
while ($r = mysqli_fetch_array($q)) {
    $uniqueId    = $r['unique_id'];
    $val1        = $r['val1'];
    $val2        = $r['val2'];
    $check_query = mysqli_query($conn, "SELECT * FROM unverified_table WHERE unique_id='$uniqueId' AND val1='$val1' AND val2='$val2'");
    
    //from here i would check the num rows and get the result etc...
}

however i have around 20+ columns which would need to be checked to see if they match.

also i would preferable want to be able to print out which columns matched and which didn't depending on what i need.

 

the other idea would be to firstly check for a row with the same unique_id, return it back as an array and then loop through each individual element to check if they match... EG.

<?php
$q = mysqli_query($conn, "SELECT * FROM live_table LIMIT 10");

while ($r = mysqli_fetch_array($q)) {
    
    $uniqueId    = $r['unique_id'];
    $check_query = mysqli_query($conn, "SELECT * FROM unverified_table WHERE unique_id='$uniqueId'");
    while ($r2 = mysqli_fetch_array($check_query)) {
        if ($r[1] != $r2[1]) {
            //etc...
        }
    }
}

is there a better way to do this?

 

thanks

 

sean

Edited February 8, 2017 by seany123

[benanamen]

It sounds a lot like you have a bad database design. Can you post your DB schema or p.m. it?

[NigelRel3]

A simple way to get a list of the records which are different would be something like (changing names as appropriate)...

select * from events where (end, id, start, title ) not in ( select end, id, start, title from events1 )

You can list all of the columns in the first set of brackets and list the same columns from the second table.  The query should only return where the row has different values or there isn't a matching row.

If in your case you use the new data as your driving table (so events would be unverified_data), it will check any unverified records against the existing data.