How do you compare 2 array variables

[Hobbyist_PHPer]

So what I'm wanting to do is log changes made by users, to the database... I was thinking on the lines of first doing a query of the database row that is going to be affected, prior to it being affected, putting that row result in an array variable, then allowing the update to happen and re-querying the database row and putting the results in a separate array, to then compare to the original database row array variable...

 

So I guess, (A): Does this make sense to do this, this way? and (B): How do you compare 2 identical arrays, then extract the value from both where the particular element does not match, e.g. has been updated?

[Pikachu2000]

By running array_diff in both directions, you should be able to get the results you're after. This should serve as an example. You can paste it in and run it as it is to see what it does.

 

$arr1 = array('name' => 'Bob', 'email' => '[email protected]', 'city' => 'Dumpsville', 'state' => 'WI'); // Before DB update
$arr2 = array('name' => 'Bobby', 'email' => '[email protected]', 'city' => 'Dumpsville', 'state' => 'WI'); // After DB update (or form submission, whatever)
$before = array_diff($arr1, $arr2);
$after = array_diff($arr2, $arr1);
echo "<table border=\"3\" cellpadding=\"5\">\n<tr>\n<th>Field</th><th>Before</th><th>After</th>\n<tr>\n";
foreach($before as $k => $v) {
echo "<tr>\n<td>$k</td><td>$before[$k]</td><td>$after[$k]</td>\n</tr>\n";
}
echo "</table>";

[Hobbyist_PHPer]

By running array_diff in both directions, you should be able to get the results you're after. This should serve as an example. You can paste it in and run it as it is to see what it does.

 

$arr1 = array('name' => 'Bob', 'email' => '[email protected]', 'city' => 'Dumpsville', 'state' => 'WI'); // Before DB update
$arr2 = array('name' => 'Bobby', 'email' => '[email protected]', 'city' => 'Dumpsville', 'state' => 'WI'); // After DB update (or form submission, whatever)
$before = array_diff($arr1, $arr2);
$after = array_diff($arr2, $arr1);
echo "<table border=\"3\" cellpadding=\"5\">\n<tr>\n<th>Field</th><th>Before</th><th>After</th>\n<tr>\n";
foreach($before as $k => $v) {
echo "<tr>\n<td>$k</td><td>$before[$k]</td><td>$after[$k]</td>\n</tr>\n";
}
echo "</table>";

 

That's kind of really great, that's exactly what I was thinking, just didn't know the logic... the only problem is, that I want to then insert into a log table what changes actually took place... So instead of showing the entires tables in comparison, to enter into a log table, this field value was changed to this value... accumulate those into a string, then enter them into a text field in the log table... so the log can then be looked at to see what was changed, by who, what date/time, etc. etc.

 

I'll work on trying to figure this out, I'm sure inside the foreach statement I can then compare the 2 different array variables some how and then if they're not a match, append them to a string variable...

 

I'll let you know if I can figure it out or not...

[Pikachu2000]

If you run the code, you'll see it only shows the values that have actually changed. Any elements that are the same are not shown at all, so it should be pretty easy to adapt it.

[Hobbyist_PHPer]

If you run the code, you'll see it only shows the values that have actually changed. Any elements that are the same are not shown at all, so it should be pretty easy to adapt it.

 

Actually it showed me all the fields and their values... I attached a screenshot, notice that all I changed was I put an "r" on the end of the person's last name...

 

Edit: Here's my code...

$query = mysql_query("SELECT * FROM Clients WHERE ClientID = '$_GET[clientid]'");
$before = mysql_fetch_assoc($query);
//                        echo '<pre>';
//                        print_r($before);
//                        echo '</pre>';
$theStrippedBusinessPhoneNo = stripPhone($_POST['ClientBusinessNum']);
$theStrippedHomePhoneNo = stripPhone($_POST['ClientHomeNum']);
$theStrippedMobilePhoneNo = stripPhone($_POST['ClientMobileNum']);
$theStrippedFaxPhoneNo = stripPhone($_POST['ClientFaxNum']);
$theStrippedSSN = stripSSN($_POST['ClientSSN']);
$ClientDOBComplete = $_POST['ClientDOBYear'].'-'.$_POST['ClientDOBMonth'].'-'.$_POST['ClientDOBDay'];
$query = mysql_query("UPDATE Clients SET ClientFirstName = '$_POST[ClientFirstName]', ClientMiddleName = '$_POST[ClientMiddleName]', ClientLastName = '$_POST[ClientLastName]', ClientMaidenName = '$_POST[ClientMaidenName]', ClientBusinessNum = '$theStrippedBusinessPhoneNo', ClientBusinessExt = '$_POST[ClientBusinessExt]', ClientHomeNum = '$theStrippedHomePhoneNo', ClientMobileNum = '$theStrippedMobilePhoneNo', ClientFaxNum = '$theStrippedFaxPhoneNo', ClientEmail = '$_POST[ClientEmail]', ClientAddress = '$_POST[ClientAddress]', ClientCity = '$_POST[ClientCity]', ClientState = '$_POST[ClientState]', ClientZipCode = '$_POST[ClientZipCode]', ClientSSN = '$theStrippedSSN', ClientDOB = '$ClientDOBComplete', ClientTaxIDNum = '$_POST[ClientTaxIDNum]', ClientEmployer = '$_POST[ClientEmployer]', ClientOccupation = '$_POST[ClientOccupation]', ClientNotes = '$_POST[ClientNotes]' WHERE ClientID = '$_GET[clientid]'");
$results = mysql_query($query);
$query = mysql_query("SELECT * FROM Clients WHERE ClientID = '$_GET[clientid]'");
$after = mysql_fetch_assoc($query);
//                        echo '<pre>';
//                        print_r($after);
//                        echo '</pre>';
echo "<table border=\"3\" cellpadding=\"5\">\n<tr>\n<th>Field</th><th>Before</th><th>After</th>\n<tr>\n";
foreach($before as $k => $v) {
    echo "<tr>\n<td>$k</td><td>$before[$k]</td><td>$after[$k]</td>\n</tr>\n";
}
echo "</table>";

 

[attachment deleted by admin]

[Psycho]

Referring to:

 

I was thinking on the lines of first doing a query of the database row that is going to be affected, prior to it being affected, putting that row result in an array variable, then allowing the update to happen and re-querying the database row and putting the results in a separate array, to then compare to the original database row array variable...

 

I would add that there is no need to query the record again after the update has occurred. You already have the updated values to be used for the UPDATE query. Just compare those values to the current values from the first query.

 

In this case I think it would be easier to just iterate through each field instead of using array_diff() since that function doesn't give you a comparison of what changed.

 

Here is a very rough example

$record_id = (int) $_POST['id'];
$field1Val = mysql_real_escape_string($_POST['field1']);
$field2Val = mysql_real_escape_string($_POST['field2']);
$field3Val = mysql_real_escape_string($_POST['field3']);
//Create array of new values using same key names as in DB fields
$new_record = array('field1'=>$_POST['field1'], 'field2'=>$_POST['field2'], 'field3'=>$_POST['field3']);

//Get current record vlaues
$query = "SELECT field1, field2, field3 FROM table WHERE id = $record_id";
$result = mysql_query($query);
$old_record = mysql_fetch_assoc($result);

$log_text = '';
foreach($new_record as $key => $new_value)
{
    if($old_record[$key]!=$new_value)
    {
        $log_text .= "$key was changed from {$old_record[$key]} to $new_value,";
    }
}

//Run query to UPDATE record
$query = "UPDATE table
          SET field1=$field1Val, field2=$field2Val, =$field3Val
          WHERE id = $record_id";
$result = mysql_query($query);

//Insert log entry if there were changes
if($log_text != '')
{
    $query = "INSERT into logs
                  (user_id, date, text)
              VALUES
                  ($userID, NOW(), '$log_text'";
    $result = mysql_query($query);
}

[PaulRyan]

Your code should be like this...

$query = mysql_query("SELECT * FROM Clients WHERE ClientID = '$_GET[clientid]'");
$before = mysql_fetch_assoc($query);
//                        echo '<pre>';
//                        print_r($before);
//                        echo '</pre>';
$theStrippedBusinessPhoneNo = stripPhone($_POST['ClientBusinessNum']);
$theStrippedHomePhoneNo = stripPhone($_POST['ClientHomeNum']);
$theStrippedMobilePhoneNo = stripPhone($_POST['ClientMobileNum']);
$theStrippedFaxPhoneNo = stripPhone($_POST['ClientFaxNum']);
$theStrippedSSN = stripSSN($_POST['ClientSSN']);
$ClientDOBComplete = $_POST['ClientDOBYear'].'-'.$_POST['ClientDOBMonth'].'-'.$_POST['ClientDOBDay'];
$query = mysql_query("UPDATE Clients SET ClientFirstName = '$_POST[ClientFirstName]', ClientMiddleName = '$_POST[ClientMiddleName]', ClientLastName = '$_POST[ClientLastName]', ClientMaidenName = '$_POST[ClientMaidenName]', ClientBusinessNum = '$theStrippedBusinessPhoneNo', ClientBusinessExt = '$_POST[ClientBusinessExt]', ClientHomeNum = '$theStrippedHomePhoneNo', ClientMobileNum = '$theStrippedMobilePhoneNo', ClientFaxNum = '$theStrippedFaxPhoneNo', ClientEmail = '$_POST[ClientEmail]', ClientAddress = '$_POST[ClientAddress]', ClientCity = '$_POST[ClientCity]', ClientState = '$_POST[ClientState]', ClientZipCode = '$_POST[ClientZipCode]', ClientSSN = '$theStrippedSSN', ClientDOB = '$ClientDOBComplete', ClientTaxIDNum = '$_POST[ClientTaxIDNum]', ClientEmployer = '$_POST[ClientEmployer]', ClientOccupation = '$_POST[ClientOccupation]', ClientNotes = '$_POST[ClientNotes]' WHERE ClientID = '$_GET[clientid]'");
$results = mysql_query($query);
$query = mysql_query("SELECT * FROM Clients WHERE ClientID = '$_GET[clientid]'");
$after = mysql_fetch_assoc($query);
//                        echo '<pre>';
//                        print_r($after);
//                        echo '</pre>';
/*#### - Missed This - #####*/
$before = array_diff($before,$after);
$after = array_diff($after, $after);

echo "<table border=\"3\" cellpadding=\"5\">\n<tr>\n<th>Field</th><th>Before</th><th>After</th>\n<tr>\n";
foreach($before as $k => $v) {
    echo "<tr>\n<td>$k</td><td>$before[$k]</td><td>$after[$k]</td>\n</tr>\n";
}
echo "</table>";

 

Tell me how it goes bud.

 

Regards, PaulRyan.

[Hobbyist_PHPer]

Tell me how it goes bud.

 

Regards, PaulRyan.

 

Works great when I followed the directions... LOL... my eyes are going googly, I've been looking at code for days now... sorry to Pikachu2000 and thanks to PaulRyan for pointing out my mistake...

 

mjdamato, I'm going to look into your solution as well... Thank you

[PaulRyan]

Not a problem

 

I would also personally use mjdamato's method, it will use alot less resources than having to query the database twice.

 

Regards, PaulRyan.