Duplicating MySQL Table Rows

duplicating table rows

[Tourex]

I'm not sure whether this should be posted here on in databases, but it's a question about inserting records in a MySQL table using PHP.

I have a large table with some 50+ fields with an auto-increment primary key field. When a record is edited by a user, we need to duplicate the original record, and update the fields changed by the user.

My question is, what is the easiest way to capture and duplicate the original table row? Yes, I could read the record in and then loop through the $row[] array using this as the source of all fields (except the primary key), but it strikes me that there must be a simpler method.

Any tips would be much appreciated.

[whoisgregg]

I think this is one of the uses of the INSERT … SELECT syntax [dev.mysql.com]:

 INSERT INTO tbl_temp2 (fld_id)
 SELECT tbl_temp1.fld_order_id
 FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Keep in mind the notes from the manual page, particularly:

To avoid ambigious column reference problems when the SELECT and the INSERT refer to the same table, provide a unique alias for each table used in the SELECT part, and qualify column names in that part with the appropriate alias.

[coopster]

You will have to use a SELECT statement to get current values prior to update. UPDATE will return the number of rows updated, not the before/after values. And actually it would be mysql_affected_rows [php.net] that would return that type of value or information, not the query itself.

Why do you need to get and store the values? As a tracking system of sorts? If so, there is always the binary log and it's related utility to dig in and locate operations.