Consider the following table row:
ID | First Name | Last Name | Email | Age
____________________________________________________
1 | John | Smith | john@smith.com | 23
2 | Mohammad | Naji | me@naji.com | 26
When an update occurs, eg. an email of an account is changed, how should I detect the change was that?
I should bold the changes for website admins.
Current database schema doesn't support it because I don't store previous revisions of the row.
Please advise me with the least cost solution for me now.
You can create a function in php and use it to update the data:
function update_row($new_row, $id)
Parameters:
Function works like this:
Select current row with id = $id into $old_row
Compare old and new rows and get the columns updated:
$columns_updated = array();
foreach($new_row as $key => $value){
if($new_row[$key] != $value)
{
array_push($key);
}
}
update row where id=$id to $new_row
return $columns_updated
You'll be unable to track changes unless you make some sort of change to the schema. At the very least you'll need a table (or tables) that do that for you.
You can either a) explicitly track changes as updates are made by modifying the code that makes them. These could be in many places, so this is likely to be time consuming. b) Track the changes by implementing a mySQL trigger on the database that automatically copies the old version to your new tables each time a row is updated.
In either case, you'll need to query both the current table and the changes table to check for changes you need to highlight.
You'll also need to determine at what point a change no longer needs to be highlighted. Simply deleting the old row from your changes table will remove the change, but you'll need to decide when that should be done. You could use a MySQL event to cull the changes on a regular basis, or you could tie this maintenance to some other trigger or action in your system.
Implementation details will need to be decided based on your system and expectations.
Using Triggers and Events has the advantage that changes can be confined to the database, except where the changes need to be highlighted.