backup of database record before update

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: backup of database record before update

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: backup of database record before update

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
>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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Re: backup of database record before update

Neil,

Have you had success with triggers. I couldn't get one to work and then saw
discussion on the board here that MYSQL triggers were not so reliable and
still somewhat problematic so I archive through the application.

Just curious.



-----Original Message-----
From: Neil Smith [MVP, Digital media] [mailto:php@comatose.freeserve.co.uk]
Sent: Tuesday, October 30, 2007 3:16 PM
To: php-db@lists.php.net
Cc: Ronald Wiplinger
Subject: [PHP-DB] Re: backup of database record before update

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
>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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: backup of database record before update

Stephen Sunderlin wrote:
> Neil,
>
> Have you had success with triggers. I couldn't get one to work and then saw
> discussion on the board here that MYSQL triggers were not so reliable and
> still somewhat problematic so I archive through the application.
>
> Just curious.
>

i have used a couple simple triggers on a 5.0.20nt box.

one thing i read recently is triggers and stored procedures do use more
system resources though...

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: backup of database record before update

"Leo G. Divinagracia III" wrote:
> Stephen Sunderlin wrote:
>> Neil,
>>
>> Have you had success with triggers. I couldn't get one to work and
>> then saw discussion on the board here that MYSQL triggers were not
>> so reliable and still somewhat problematic so I archive through the
>> application. Just curious.
>>
>
> i have used a couple simple triggers on a 5.0.20nt box.
>
> one thing i read recently is triggers and stored procedures do use
> more system resources though...

Why depend on different MySql infrastructures, while you can easily
implement a perfect fit for your needs in a PHP routine? ...


--
Kind regards,
hochprior

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: backup of database record before update

At 09:46 01/11/2007, you wrote:
>Message-ID: <44.D0.03551.E40A9274@pb1.pair.com>
>Date: Thu, 1 Nov 2007 10:45:57 +0100
>
>"Leo G. Divinagracia III" wrote:
>
>>Stephen Sunderlin wrote:
>>>Neil,
>>>
>>>Have you had success with triggers. I couldn't get one to work and
>>>then saw discussion ...
>...snip...
>>... on a 5.0.20nt box.
>>
>>one thing i read recently is triggers and stored procedures do use
>>more system resources though...
>
>Why depend on different MySql infrastructures, while you can easily
>implement a perfect fit for your needs in a PHP routine?


Because it's the job of the database to implement data access,
referential integrity and storage.
It's the job of the PHP code to manage business and application
logic, and some data sanitisation.
Finally, it's the job of the templating engine / output layer to
manage actual display of content.

I tend to try to move as much as possible to the Database, because
it's often running on a separate machine or cluster.

That frees up resources on the web servers to get and send data as
quickly as possible, rather than have it spending all day
constructing SQL strings and managing stuff the great engineers at
MySQL have already invented to make my life simpler ;-)

TBH it *oughtta* be much faster than having PHP do that, since (a)
you're saving the roundtrip of the query to the DB from the web
server and (b) the trigger query will probably end up quite rapidly
in MySQL query cache on the DB in most cases.

Cheers - Neil

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: backup of database record before update

"Neil Smith [MVP, Digital media]" wrote:
> At 09:46 01/11/2007, you wrote:
>> Message-ID: <44.D0.03551.E40A9274@pb1.pair.com>
>> Date: Thu, 1 Nov 2007 10:45:57 +0100
>>
>> "Leo G. Divinagracia III" wrote:
>>
>>> Stephen Sunderlin wrote:
>>>> Neil,
>>>>
>>>> Have you had success with triggers. I couldn't get one to work and
>>>> then saw discussion ...
>> ...snip...
>>> ... on a 5.0.20nt box.
>>>
>>> one thing i read recently is triggers and stored procedures do use
>>> more system resources though...
>>
>> Why depend on different MySql infrastructures, while you can easily
>> implement a perfect fit for your needs in a PHP routine?
>
>
> Because it's the job of the database to implement data access,
> referential integrity and storage.
> It's the job of the PHP code to manage business and application
> logic, and some data sanitisation.
> Finally, it's the job of the templating engine / output layer to
> manage actual display of content.
>
> I tend to try to move as much as possible to the Database, because
> it's often running on a separate machine or cluster.
>
> That frees up resources on the web servers to get and send data as
> quickly as possible, rather than have it spending all day
> constructing SQL strings and managing stuff the great engineers at
> MySQL have already invented to make my life simpler ;-)
>
> TBH it *oughtta* be much faster than having PHP do that, since (a)
> you're saving the roundtrip of the query to the DB from the web
> server and (b) the trigger query will probably end up quite rapidly
> in MySQL query cache on the DB in most cases.

And still you would have to rely on one more infrastructure.
Just do it the way you like, and I´ll do it my way... ;-)

--
Kind regards,
hochprior

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php