CODEWITHSUNDEEP
phpmysqlarrayscomparisondifference
mayersdesign
mayersdesign

Reputation: 5310

Finding the differences between two rows of data, key by key

I have two rows of data - always just two rows, but there could be a maximum of around forty columns. The column names are different on a case by case basis, but here is a representative example:

id | height | width | colour | in_stock | featured | on_sale
------------------------------------------------------------
1  |  30    |  20   |  black | yes      | no       |  yes
2  |  30    |  25   |  red   | yes      | yes      |  no

I want to get all of the differences between those two rows into an array so that I can log what was changed from row 1 to row 2.

I thought it array_diff() would do the job!

So I cheerfully chucked array_diff() at it thus:

//Simplified queries for the example

$sql1 = "SELECT * FROM table WHERE id = 1";
$rs1     = $conn->Execute($sql1);
$rs1     = $rs1->fields;

$sql2  = "SELECT * FROM table WHERE id = 2";
$rs2      = $conn->Execute($sql2);
$rs2      = $rs2->fields;

//Build first array
foreach($rs1 as $key => $value){
  $data1[$key] = $value;
}

//Build second array
foreach($rs2 as $key => $value){
  $data2[$key] = $value;
}

//Find the differences
$theDifferences = array_diff($data1, $data2);

//Loop through the differences logging the changes
foreach($theDifferences as $field => $value){
 echo "Change found for ".$field."!";
}

Why that doesn't work.

This "looked like" it was working. Since many columns contain long strings, colour names, dates etc, so when one changed it was duly pushed into the differences array. The problem was (of course) that the multiple "yes" or "no" columns did not behave as I had expected. Thus the result of the code above, for the table example is:

colour, width

It is not "seeing" the featured or on_sale columns as changed because the data1 array AND the data2 array both contain no's and yes's.

I suppose I need to compare on a key by key basis? Something like the opposite of array_diff_key()? But here I am stuck.

I also considered if this could be done solely with the SQL query, which would I suppose be more efficient, but that is way beyond my SQL ability.

Thanks in advance.

Upvotes: 0

Views: 88

Answers (1)

Nate Reynolds
Nate Reynolds

Reputation: 137

I think you're very nearly there. Maybe something like this after your queries:

$theDifferences = array();
foreach($rs1 as $key => $value){
   if ($rs2[$key] != $value){
      $theDifferences[$key] = $value;
   }
}

As for SQL, you can use an EXCEPT to get a list of rows which are different between two queries, but you'd still have to loop through the keys and look for nulls - which doesn't save you a whole lot.

Upvotes: 1

Related Questions