PHP DB - backup of database record before update

Author

backup of database record before update

Ronald Wiplinger

2007-10-28, 3:59 am

I need some hints how to do that. Maybe there is a template or an easy
function available.

I have a MySQL database with some tables. Everytime a table record
changes, I want also put the old record to a history database.
The history table and the original only differs that the key of the
original will be just a field in the backup, while the new key is now
the UNIX time stamp.

How can I do that "easy"?

bye

Ronald

hochprior

2007-10-28, 6:59 pm

Ronald Wiplinger wrote:
> I need some hints how to do that. Maybe there is a template or an easy
> function available.
>
> I have a MySQL database with some tables. Everytime a table record
> changes, I want also put the old record to a history database.
> The history table and the original only differs that the key of the
> original will be just a field in the backup, while the new key is now
> the UNIX time stamp.
>
> How can I do that "easy"?

Just write yourself a little update function, and add a insert call before
the actual update.
Here is a little update function that I use ($columns and $values get set in
a loop before):

function mysqlUpdate($table, $columns, $values, $criteria){
$sql = 'UPDATE '.$table.' SET ';
for($i = 0; $i < count($columns); $i++){
$sql .= $columns[$i].' = "'.$values[$i].'"';
if($i < count($columns) - 1){
$sql .= ', ';
}
}
$sql .= ' WHERE '.$criteria;
#debug($sql);
return mysql_query($sql);
}

Since you pass the $criteria (e.g. 'id = "23"') anyways, you can easily call
the record first, and copy it to another table.

--
Kind regards,
hochprior

Neil Smith [MVP, Digital media]

2007-10-30, 6:59 pm

At 06:57 28/10/2007, you wrote:
>Message-ID: <4724328C.90608@elmit.com>
>Date: Sun, 28 Oct 2007 14:56:12 +0800
>From: Ronald Wiplinger <ronald@elmit.com>
>I need some hints how to do that. Maybe there is a template or an
>easy function available.
>
>I have a MySQL database with some tables. Everytime a table record
>changes, I want also put the old record to a history database.
>The history table and the original only differs that the key of the
>original will be just a field in the backup, while the new key is
>now the UNIX time stamp.
>
>How can I do that "easy"?

"Easy" is to set a MySQL5 trigger which does the Insert on update :
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

eg (from the manual page) :

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|

HTHCheers - Neil

Sponsored Links