[Resolved] Storing a log of all changes to database

[markdw]

I want to store a log of all changes made to records in my db.

When the form is submitted, what I think I will do is select from the database, then compare the currently saved value with the new value, if they are different then insert a record into the Log table with both old and new values....

if ($row["name"] <> $name)
{
logToDB($User, $PHPSESSID, $ID, $row["name"], $name);
}

Just wondering if anyone had any thoughts on this, or any suggestions on how to improve it.

Many thanks, Mark

[LordShryku]

I was asked to do something like this also, and just made a transactional function, and included it on the pages I was tracking. Then all that did was insert the transaction into another table when something happened. Real basic stuff. What type of transaction (Update, Insert, Delete, etc), who did it, old value, new value.

[markdw]

I have it working, it stores all the info I want but it takes a lot of code...

if ($row["Prop_Strap"] <> $Strap)
{
logToDB($User, $PHPSESSID, "Prop", "Prop_Strap", $ID, $row["Prop_Strap"], $Strap);
}
if ($row["Prop_Description"] <> $Description)
{
logToDB($User, $PHPSESSID, "Prop", "Prop_Description", $ID, $row["Prop_Description"], $Description);
}
if ($row["Prop_Summary"] <> $Summary)
{
logToDB($User, $PHPSESSID, "Prop", "Prop_Summary", $ID, $row["Prop_Summary"], $Summary);
}
if ($row["Prop_Keywords"] <> $Keywords)
{
logToDB($User, $PHPSESSID, "Prop", "Prop_Keywords", $ID, $row["Prop_Keywords"], $Keywords);
}

this is fine if there are only a few fields, but on form where i have 30+ fields it is going to be a pain to code. I am sure someon out there will have a more gracefull way of doing this??

TIA - Mark

[rinjani]

Maybe an easier way than what you are doing is to start by making two arrays,

e.g.

while ($row = mysql_fetch_array($result)) {
$my_current_data[] = $row

}

$my_new_data = array($strap, $description, etc. etc.);

Make sure you have both arrays in the same order and same size.

for ($i=0; $i< count($my_current_data); $i++) {
if ($my_current_array[$i] <> $my_new_data[$i] {

logToDB($User, $PHPSESSID, "Prop", "Prop_Keywords", $ID, $my_current_data[$i], $my_new_data[$i]);

}

}

Something like that makes it a few lines of code that applies to any size of table without having to know it ahead of time. Just make sure that your arrays are ordered correctly.

Code hasn't been tested but hope it gives you an idea or two.

HTH

rinjani

[markdw]

Super, I had a feeling arrays might help.
Many thanks again!