Insert all rows from one table to another + time stamp at the end

I'm creating a change log db that's the exact same as my active db except it has a changedate DATE field at the end.

The db is basically one primary key id, and about 50 other columns of various data types. The script I have in php is it tries to insert new ids and if it gets the error message for duplicate primary key, then it should get that row, insert it into my backup db with a curdate() call as the final dat开发者_如何学Ce value, delete the entry from my first db, then insert the new entry.

I have all the other parts of the script finished except the part where I have to insert everything from the first table + an extra column for curdate(). Or if there's a better solution to my problem of inserting into a backup database when a duplicate primary key comes in when there's a fairly high amount of rows please share that.

You could do an INSERT INTO SELECT:

INSERT INTO `backupTable` SELECT *, NOW() FROM `originalTable` WHERE id = '$id';

You have to specify the ID for the entry you wish to copy to your backup db. You have also to be sure, that the IDis not already in your backup table. You can use REPLACE INTO to workaround this case.

REPLACE INTO `backupTable` SELECT *, NOW() FROM `originalTable` WHERE id = '$id';

basicly, you can create a TIMESTAMP column with CURRENT_TIMESTAMP as default value. when you insert a row to that table, the current date/time will be automaticly inserted. is that what you were looking for ?

BTW: i would recommend to kill the problem at it source and make sure a duplicate primary key will not be inserted to the datatable.. to do that, you can use the SELECT LAST_INSERT_ID();