php比较两个sql表并寻找差异

Question

I'm working with two tables :

• criter_live which is automatically updated through AJAX
• worksheets which is manually updated by a user

So, my issue is, I want to know the differences between the automatically updated table (criter_live) and the manual updated table (worksheets). entry_number and left_number are the same in both table, but machine_id, entry_date & left_date could be different, so I want a query to know when there is a difference on worksheets from criter_live. Although, a row cannot be in criter_live but be in worksheets and conversely. In this case we will make a new record or we'll remove a record from the database.

For example, I'm checking on criter_live & worksheets for entry_number ABC, BUT worksheets does not contains the latest left_date value (criter_live contains the latest value) => print smth to my current user.

I was using this query (for machine_id, for left_date, for entry_date):

SELECT train_id FROM criter_live WHERE entry_date > $currentdate_today_midnight AND mac_id NOT IN (SELECT train_id FROM worksheets)

but it doesn't work as I want... In some cases it doesn't not return the result as I want, I think there is an issue but where... In fact, I can have several machine_id the same day but however not have the same entry_number or left_number... I should mention that in both the tables the fields entry_number & left_number contains the same values (except missing line which is not in one of the bases obviously ...).

In concrete cases, if you do not understand: - Checking criter_live and worksheets: left_date for a certain entry_number is different in worksheets from the ref db criter_live (applying the changes on worksheets)

• Checking criter_live and worksheets: entry_date for a certain entry_number is different in worksheets from the ref db criter_live (applying the changes on worksheets)

• Checking criter_live and worksheets: a new entry_number appears in criter_live which does not appear in worksheets : create new row in worksheets.

• Checking criter_live and worksheets: a entry_number no longer appears in criter_live but exists in worksheets(deletion of the record in worksheets)

Thank you

Db scheme:

+--------------------------------------------------------------------------------------+
|                               criter_live & worksheets                               |
+--------------------------------------------------------------------------------------+
| id | machine_id | entry_number | machine_type | entry_date | left_date | left_number |
+----+------------+--------------+--------------+------------+-----------+-------------+
| 1  | 76801      | R88901       | -Z           | timestamp  | timestamp | S79980      |
+----+------------+--------------+--------------+------------+-----------+-------------+
| 2  | 82501      | R89874       | -X           | timestamp  | timestamp | S98780      |

+--------------------------------------------------------------------------------------+

Answer 1

There are a lot of cases the question does not address, for example, what happens if there are two records, one in criter_live and the other in worksheets, for which entry_number match but left_number doesn't? Anyhow, I feel the solution to this is not in an over-complicated SQL query but in a simple PHP script (I'm assuming using PHP is OK since you included the php tag in your question).

So, the following is a pseudocode of how the PHP would go. Not intended to be readily-runnable PHP, just a skeleton:

function records_match (record_from_criter_live, record_from_worksheets)
{
    // The code below this function assumes the following two lines
    if (record_from_criter_live['entry_number'] != record_from_worksheets['entry_number'])
        return false;

    // That said, you can implement here any further criteria
    // you wish to add to decide if two records
    // (one record from table criter_live and the other from worksheets)
    // are "the same" or not
    // Return true if they match
    // Return false if they don't
}

// The following two lines are intended to be
// PHP code for establishing the connection to the database
// and setting up everything.
// Note that it is assumed that ORDER BY entry_number
// will always give all matching record pairs
// in the correct order.
// That's why the function above must always return false
// for any two records with different entry_number
query1 = SELECT * FROM criter_live ORDER BY entry_number
query2 = SELECT * FROM worksheets ORDER BY entry_number

// Again, these two lines represent
// PHP code for getting a record from each query
// It is assumed that record1 and record2 get a special value
// when trying to get a record past the last one
// The condition in the while loop checks for this
record1 = first record from query1
record2 = first record from query2

while ((record1 not past end-of-table) OR (record2 not past end-of-table))
{
    // variable next means:
    // $next = 1 -> record1 is orphan, no matching record2 exists
    // $next = 2 -> record2 is orphan, no matching record1 exists
    // $next = 3 -> record1 and record2 match

    if (record1 is past end-of-table)
        $next = 2
    else if (record2 is past end-of-table)
        $next = 1
    else if (records_match (record1, record2)) // Notice this is a call to function above
        $next = 3
    else if (record1[entry_number] < record2[entry_number])
        $next = 1;
    else
        $next = 2;

    // Now process the result
    if ($next == 1)
    {
        // Add record1 to list of criter_live
        // with no matching worksheets
        do_whatever_with (record1)

        // Then move forward
        record1 = next record from query1
    }
    else if ($next == 2)
    {
        // Add record2 to list of worksheets
        // with no matching criter_live
        do_whatever_with (record2)

        // Then move forward
        record2 = next record from query2
    }
    else
    {
        // Add (record1, record2) to list of matching (criter_live, worksheets)
        // I suppose this means just ignore both record1 and record2
        // i.e. I suppose the following line is innecesary
        do_whatever_with (record1, record2)

        // Then move forward
        record1 = next record from query1
        record2 = next record from query2
    }
}

close query1 and query2 as necessary