On Wed, Sep 08, 2004 at 07:47:34PM -0500, NIPP, SCOTT V (SBCSI) wrote:
> I have created a database that has a table for the active data,
> and a table that basically captures the changes to that table. The
> second table has all of the same fields as the first with one extra and
> that being to capture a date of the change. I know that I can write a
> piece of code to compare the incoming data to the existing data and then
> update accordingly. This seems like it would be an extremely common
> thing to do though. I was wondering if someone might know of a module
> to do this? Thanks in advance.
No module that I know of, but then it is fairly simple if you're
not changing the key fields.
Issue an update statement like this
UPDATE table
SET foo=?, bar=?
WHERE key=?
AND NOT (foo=? AND bar=?)
then do
$rows = $sth->execute($new_foo, $new_bar, $key, $old_foo, $old_bar);
then if $rows >= 1 then do an insert into your "second table".
If you're using mysql you can simplify this by setting the mysql_client_found_rows
connect option to false, then you don't need the "AND NOT (foo=? AND bar=?)" part.
Tim.
