PHP SQL - Saving versions of changed records

Author

Saving versions of changed records

Kenoli

2008-02-27, 7:04 pm

I am using a mysql database to store data that is inserted into a web
page. I have an admin site that lets someone edit the data in that
table thus changing the web site. I would like to save each changed
record somehow so it is possible to "restore" a previous version of
the data if desired.

The approach I am considering is to copy a row that is to be modified
into a second table with the same fields plus an additional field
containing a date tag that can be used to retrieve a given version. A
previous version could be called up using an ID link associating it
with a record in the original database and allowing the user to select
a particular historical version based on the date tag. I'm sure I
could create a php/mysql script that would do this, though I am hoping
that there may be an sql command or existing php/mysql script or class
that would let me copy the entire row into the "history" table, adding
a date tag to the new record. This would save me a lot of code
writing.

It seems like something so commonly needed that this wheel must have
already been invented.

--Kenoli

J.O. Aho

2008-02-27, 7:04 pm

Kenoli wrote:

> The approach I am considering is to copy a row that is to be modified
> into a second table with the same fields plus an additional field
> containing a date tag that can be used to retrieve a given version. A
> previous version could be called up using an ID link associating it
> with a record in the original database and allowing the user to select
> a particular historical version based on the date tag. I'm sure I
> could create a php/mysql script that would do this, though I am hoping
> that there may be an sql command or existing php/mysql script or class
> that would let me copy the entire row into the "history" table, adding
> a date tag to the new record. This would save me a lot of code
> writing.

No you don't have a such, but you could look at triggers in mysql.

--

//Aho

Kenoli

2008-02-28, 7:41 pm

For those interested, I ended up creating some code to do what I
wanted. It seems to work fine. The script included in this email
goes as far as creating the query to insert the backup record in the
history table. I'd be interested in feedback as it got me into some
areas, particularly working with objects that I have not worked with
before. It assumes there are two tables, the primary table and a
history table. The difference is that the history table has an "h"
prefix added and has an additional date field as well as its own
primary key. Otherwise the tables have the same fields. The script
gets the field names from each table and creates a query which
transfers the data from the primary table to the history table adding
a timestamp. I left the comments I wrote to myself. I can never
remember these things when I go back to them.

The obvious issue here is that if someone changes the primary table it
won't work. I guess one could write more script that checked for
changes in the primary table and added or deleted fields in the
history table so it corresponded to the primary table. It could even
check to see if the table exists and create the table if it doesn't.

Thanks,

--Kenoli

//:::::::::::::::::::: Function to backup content
databases. ::::::::::::::::::://
//
// $pid is the id of the record in the original table to be
backed up.
// $table is the name of the original table.
//
// The assumption is that the history table will have the same
name
// as orignal table with an 'h' added as a prefix as well as the
same
// fields in the same order with an additional autoincrementing
key and
// timestamp field.
//
// ::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::://

function backupData($table, $pid) {

require_once ('../includes/mysql_connect.php');

$htable ="h$table";

$tquery = "SELECT * FROM $htable";
$tresult = mysql_query($tquery);

//::::::: The following leaves out the primary key field which is
field "0":::::://

$i = 1;
while ($i < mysql_num_fields($tresult)) {

$meta = mysql_fetch_field($tresult, $i);

$fieldnames[] = $meta->name;

$i++;

}

//:::::::::::::::::::::::::::
IMPORTANT!!! ::::::::::::::::::::::::::://
// The key field for source table is obtained from the target
table //
// for efficiency sake. The second column of the target table
must //
// have the same name as the key of the source table for this to
work.//
// Otherwise, this value must be obtained via some code like
that //
// above aimed at the source
table. :::::::::::::::::::::::::::::::::://

$id = $fieldnames[0]; / /Primary field name of the table to be
backed up for use in the query

$hquery = "INSERT INTO $htable (";

foreach ($fieldnames as $key => $value) {

$hquery .= $value . ', ';

}

$hquery = rtrim($hquery, ", ");

$pquery = "SELECT * FROM $table WHERE $id = '$pid'";
$presult = mysql_query($pquery);

$hquery .= ") VALUES ('NOW()'";

$prow = mysql_fetch_array($presult);

$i = 0;

while ($i < mysql_num_fields($presult)) {

$hquery .= ", '{$prow[$i]}'";

$i++;

}

$hquery .= ")";

//echo $hquery;

}

Tom

2008-03-04, 7:07 pm

On Thu, 28 Feb 2008 08:30:32 -0800 (PST), Kenoli wrote...
>
>For those interested, I ended up creating some code to do what I
>wanted. It seems to work fine. The script included in this email
>goes as far as creating the query to insert the backup record in the
>history table. I'd be interested in feedback as it got me into some
>areas, particularly working with objects that I have not worked with
>before. It assumes there are two tables, the primary table and a
>history table. The difference is that the history table has an "h"
>prefix added and has an additional date field as well as its own
>primary key. Otherwise the tables have the same fields. The script
>gets the field names from each table and creates a query which
>transfers the data from the primary table to the history table adding
>a timestamp. I left the comments I wrote to myself. I can never
>remember these things when I go back to them.
>
>The obvious issue here is that if someone changes the primary table it
>won't work. I guess one could write more script that checked for
>changes in the primary table and added or deleted fields in the
>history table so it corresponded to the primary table. It could even
>check to see if the table exists and create the table if it doesn't.
>
>Thanks,
>
>--Kenoli

Not sure if there is an easy solution for that. I routinely backup MySQL
databases once a day, so I can restore from those files if needed. Depending how
you organize your tables and how you build your queries you may be able to use
one table, and save the headache of copying rows from one to another.

You might be able to alter your primary table and add a date column. That way
you have your data in one place and the row with the newest date would be the
most recent modified version. If you need to restore a prior version, maybe
setup an extra table to move the unwanted ones. Not sure if it would save you
any extra work.

Tom
--
NewsGuy Leap Year Savings!
Free Month with 3, 6 or 12 Month Unlimited Accounts
http://newsguy.com/overview.htm

>
>//:::::::::::::::::::: Function to backup content
>databases. ::::::::::::::::::://
>//
>// $pid is the id of the record in the original table to be
>backed up.
>// $table is the name of the original table.
>//
>// The assumption is that the history table will have the same
>name
>// as orignal table with an 'h' added as a prefix as well as the
>same
>// fields in the same order with an additional autoincrementing
>key and
>// timestamp field.
>//
>// ::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::://
>
>function backupData($table, $pid) {
>
> require_once ('../includes/mysql_connect.php');
>
> $htable ="h$table";
>
> $tquery = "SELECT * FROM $htable";
> $tresult = mysql_query($tquery);
>
>//::::::: The following leaves out the primary key field which is
>field "0":::::://
>
> $i = 1;
> while ($i < mysql_num_fields($tresult)) {
>
> $meta = mysql_fetch_field($tresult, $i);
>
> $fieldnames[] = $meta->name;
>
> $i++;
>
> }
>
> //:::::::::::::::::::::::::::
>IMPORTANT!!! ::::::::::::::::::::::::::://
> // The key field for source table is obtained from the target
>table //
> // for efficiency sake. The second column of the target table
>must //
> // have the same name as the key of the source table for this to
>work.//
> // Otherwise, this value must be obtained via some code like
>that //
> // above aimed at the source
>table. :::::::::::::::::::::::::::::::::://
>
> $id = $fieldnames[0]; / /Primary field name of the table to be
>backed up for use in the query
>
> $hquery = "INSERT INTO $htable (";
>
> foreach ($fieldnames as $key => $value) {
>
> $hquery .= $value . ', ';
>
> }
>
> $hquery = rtrim($hquery, ", ");
>
> $pquery = "SELECT * FROM $table WHERE $id = '$pid'";
> $presult = mysql_query($pquery);
>
> $hquery .= ") VALUES ('NOW()'";
>
> $prow = mysql_fetch_array($presult);
>
> $i = 0;
>
> while ($i < mysql_num_fields($presult)) {
>
> $hquery .= ", '{$prow[$i]}'";
>
> $i++;
>
> }
>
> $hquery .= ")";
>
> //echo $hquery;
>
> }
>
>

Jeff Rose

2008-03-05, 4:05 am

Tom wrote:

> Not sure if there is an easy solution for that. I routinely backup MySQL
> databases once a day, so I can restore from those files if needed. Depending how
> you organize your tables and how you build your queries you may be able to use
> one table, and save the headache of copying rows from one to another.
>
> You might be able to alter your primary table and add a date column. That way
> you have your data in one place and the row with the newest date would be the
> most recent modified version. If you need to restore a prior version, maybe
> setup an extra table to move the unwanted ones. Not sure if it would save you
> any extra work.
>
> Tom

That's what I was going to suggest. Keep ALL entries and select the 1
most recent record to display. If you need the ability to edit one and
save it before going live, a status flag would work.

Kenoli

2008-03-05, 7:07 pm

Thanks to all. So far the script I wrote, which backs up records each
time they are modified to a second identical table with a datetime
stamp added, has worked superbly. I will see how many records I end
up with. I may add something like many wikis have where the person
changing the data can indicate whether it is a major or trivial edit,
with only the major edits actually getting backed up. I can also
backup the backup table now and then for archival saves and purge the
history table.

--Kenoli

2008-03-05, 7:07 pm

On Feb 27, 11:53 am, Kenoli <kenol...@gmail.com> wrote:
>
> I am using a mysql database to store data that is inserted into a web
> page. I have an admin site that lets someone edit the data in that
> table thus changing the web site. I would like to save each changed
> record somehow so it is possible to "restore" a previous version of
> the data if desired.
>
> The approach I am considering is to copy a row that is to be modified
> into a second table with the same fields plus an additional field
> containing a date tag that can be used to retrieve a given version.

Bad idea. Moving data between tables is a high-overhead operation.
Consider simply adding a status field to your existing table. The
most recent version could have status=1 (meaning, active); all
previous versions, status=0 (inactive).

Cheers,
NC

Sponsored Links