18 November, 2010

Get changed field names from database using PHP & MySQL

Yesterday, i had a problem to get changed field value in mysql from database. MY problem was,lets say i have a field "user_name" in users table and have value "Sudhir" in it, after some updates the "user_name" field's value changed to "John", now i had to find such fields whose values has changed.
After some testing with the codes, i was able to get the field name and its changed value from database. So here's the code snippet:

//define an array of field names and values
$uniqId = "1"; // this can be the user id in database, whose values are changed
//array of field names and changed values, i.e. these fields contain values other than listed in this array
$fieldsArr = array(
"first_name" => $firstname,
"last_name" => $lastname,
"location" => $location,
"ip" => $ip
);
//call function to get list of changed fields in database
$changedFieldValueArr = getChangedFieldValue($uniqId, "user_details", $fieldsArr);

This is the function part. Connect to database, select database first.

function getChangedFieldValue($uniqId, $tableName, $fieldsArr) {
$changedValuesArr = array();
$sql = "SELECT id FROM ".$tableName;
$sql .=" WHERE ";
foreach($fieldsArr as $fieldName => $fieldValue) {
$subSql =$fieldName ." = "."'".$fieldValue."'";
$finalSql = $sql.$subSql;
$res = mysql_query($finalSql, $this->connection);
if(mysql_num_rows($res) < 1) {
$changedValuesArr[] = array("fieldname" => "'".$fieldName."'" , "changedFieldValue" => "'".$fieldValue."'");
}
unset($res);
unset($finalSql);
}
//resulting field names for changed values are added to array
return $changedValuesArr;
}

And thats it. I hope this can be of some help to someone like me. The code can be customized to a greater extent depending on the need.

No comments: