Description of problem:

I want to add values to a log that will show me which fields a user has changed on a form (during an update). This would be something like an audit trail for a database, but not so complex.

What I've thought up is a function that allows you to pass the entire array of posted values (new values) (values from the form's textboxes). This function will then reach into the database and create an array of values that already exist (old values).

Now we need to see which values the user has actually changed. What I have come up with in my code below partially works, and this is where I'm asking for help.

When the field is blank or something is entered into it, the code works fine. When you remove text, and leave the field blank, it doesn't show up.

Also, with radio buttons, since they are arrays, they are also always showing up as differences - and therefore being logged incorrectly to the log. I think I'm on the right track to fixing this, but I've commented that part out so I can just view some debug text.

Please bear with me as I have only been working with PHP for a few weeks. I am constantly trying to learn how to do things better and easier. What I have tried to do is use the array_diff function to try and make it easier for me to not do the for...each loops, although, if I should be using that, go ahead and let me know my logic is stupid, and I'll try something else. 🙂

Thanks in advance for your time.

Code:

/**
* Add new entry into the model log
* 
* @access public
* @param array $PostedValues
* @return void
*/
function Add_1( $PostedValues ) 
{
	$ModelID = $PostedValues['modelid'];
	foreach ($PostedValues as $key => $value)
	{
		if($key!='submit') 
		{
			${$key} = $value;
			if($value)
			{
				if(!is_array($value))
				{
					$sqlvar .= addslashes($key).",";
					//echo "Key: " . addslashes($key) . "<br />";
					//echo "Value: " . addslashes($value) . "<br />";
				} else {
					$sqlvar .= addslashes($key).",";
				}
			}
		}
	}

$sql = "SELECT ".substr($sqlvar, 0, -1)." FROM model WHERE modelid = " . $ModelID;
//echo "<br /><br /><br /><br /><br /><b>Select Model SQL:</b> " . $sql . "<br />";

$query_results = mysql_query($sql);
$results = mysql_fetch_array($query_results);

echo "<b>Result Count:</b> " . count($results) . "<br />";
echo "<b>Posted Count:</b> " . count($PostedValues) . "<br />";

echo "<b>Differences:</b>";
$UpdatedValues = array_diff($PostedValues,$results);
//$bUpdatedValues = array_diff($results,$PostedValues);
print_r($UpdatedValues);
//print_r($bUpdatedValues);
echo "<br />";	

foreach ($UpdatedValues as $key => $value)
{
	if($key!='submit') 
	{
		${$key} = $value;
		if($value)
		{
			if(!is_array($value))
			{
				$sql = "INSERT INTO modellog (modelid,userid,field_updated,old_value,new_value,date_updated)
						VALUES ($ModelID,".$_SESSION['userid'].",'".addslashes($key)."','".$results[addslashes($key)]."','".$PostedValues[addslashes($key)]."',now())";
				//$junk = mysql_query($sql);
				echo "<br /><b>ModelLog Add SQL:</b> " . $sql . "<br />";
				//$sqlvar .= addslashes($key).",";
				//echo "Key: " . addslashes($key) . "<br />";
				//echo "Value: " . addslashes($value) . "<br />";
			} /*else {
				$ArrayUpdatedValues = array_diff_assoc($PostedValues,$value);
				if($ArrayUpdatedValues && !is_array($ArrayUpdatedValues))
				{
					echo "<br /><b>Array:</b> ";
					print_r($ArrayUpdatedValues);
					echo "<br />";
					$sql = "INSERT INTO modellog (modelid,userid,field_updated,old_value,new_value,date_updated)
							VALUES ($ModelID,".$_SESSION['userid'].",'".addslashes($key)."','".$results[addslashes($key)]."','".implode(",", $PostedValues[addslashes($key)])."',now())";
					//$junk = mysql_query($sql);
					echo "<b>ModelLog Add SQL:</b> " . $sql . "<br />";
				}
			}*/
		}
	}
}
}

    People tell me I code with the finesse of a sledgehammer, but here's what I would do:

    I'd create a second table EXACTLY like the original ... same fieldnames, etc.

    I'd then copy the INSERT code of the original table EXACTLY, only changing the tablename...that would pretty easy to do.

    Then I'd insert the new submitted values into the duplicate table.

    Then I'd compare the values of the original record to the values of duplicate table's record.

    That compare would be pretty easy to do, as the returned values from records in both tables would be set up with exactly the same structure.

    No finesse, but easy to code.

      Very nice suggestion, and it does simplify my problem. I could probably then just do the sql query before and after the update, place those into arrays and then do the array_diff thingy? I'll give it a try.

      Thanks for your idea.

        I think I could get this...now just need to figure out how you would compare values in an array? Using array_diff gives up nothing, it's used to compare the arrays themselves. I need to compare the values within the arrays.

        Any suggestions?

          $count=count($firstarray);
          $i=0;
          
          while($i<$count){
           if($firstarray[$i]<>$secondarrray[$i]){echo "element $i is different<BR>";}
           $i++;
          }

            This almost works, but I need to know which array field value by assoc has been changed. Here's something I've been trying to get to work, and it does partially:

            foreach( $BeforeArray as $FieldName => $FieldValue )
            		{
            			if( !in_array( $FieldValue, $AfterArray ) )
            			{
            				echo "<p><b>Fieldname: </b> " . $FieldName . " <b>OldValue:</b> [" . $FieldValue . "] <b>NewValue:</b> [". $AfterArray[$FieldName]."]</p>";
            			}
            		}
            

            This doesn't work when you have no value, then enter a value. I know why, just don't know how to fix. I will keep working on it though. Thanks for your help.

              It's been a long day. This seems to work with a little bit of my testing:

              foreach( $BeforeArray as $FieldName => $FieldValue )
              {
              	if( $BeforeArray[$FieldName] != $AfterArray[$FieldName] )
              	{
              		echo "<p><b>Fieldname: </b> " . $FieldName . " <b>OldValue:</b> [" . $FieldValue . "] <b>NewValue:</b> [". 
                                              $AfterArray[$FieldName]."]</p>";
              	}
              }
              

                I think I have it, thanks for your help nemonoman!

                In my model class (sans comments):

                $SelectSql = "SELECT * FROM model WHERE modelid = " . $modelid;
                
                
                	$BeforeArray = MySqlHelper::GetRow( $SelectSql );  // Get values before update
                
                	$sql = "UPDATE model SET " . $sqlvar . " WHERE modelid=" . $modelid . "";
                	$results = MySqlHelper::ExecuteNonQuery( $sql ); // Update the model
                
                	$AfterArray = MySqlHelper::GetRow( $SelectSql );  // Get the values after the update
                
                ModelLog::Add_2( $BeforeArray, $AfterArray );  // Add changes to model log
                

                Which over in good ol ModelLog:

                /**
                	 * Add changed values into model log
                	 * 
                	 * @access public
                	 * @param array $BeforeArray
                	 * @param array $AfterArray
                	 * @return void */
                	 function Add_2( $BeforeArray, $AfterArray )
                	 {
                	 	foreach( $BeforeArray as $FieldName => $FieldValue )
                		{
                			if( $BeforeArray[$FieldName] != $AfterArray[$FieldName] )
                			{			//		 UserID		  Field Updated	  Old Value			New Value			modelid
                 				$this->Add_5( $_SESSION['userid'],$FieldName,$FieldValue,$AfterArray[$FieldName], $BeforeArray['modelid'] );
                	 		}
                		}
                	 }
                

                Thanks again.

                  Write a Reply...