How to duplicate a record in MySQL using PHP
May 7th, 2007 | Comments (15)
If you've ever needed to duplicate a MySQL record with a unique ID field, here is a quick-n-dirty PHP function:
function DuplicateMySQLRecord ($table, $id_field, $id) {
// load the original record into an array
$result = mysql_query("SELECT * FROM {$table} WHERE {$id_field}={$id}");
$original_record = mysql_fetch_assoc($result);
// insert the new record and get the new auto_increment id
mysql_query("INSERT INTO {$table} (`{$id_field}`) VALUES (NULL)");
$newid = mysql_insert_id();
// generate the query to update the new record with the previous values
$query = "UPDATE {$table} SET ";
foreach ($original_record as $key => $value) {
if ($key != $id_field) {
$query .= '`'.$key.'` = "'.str_replace('"','\"',$value).'", ';
}
}
$query = substr($query,0,strlen($query)-2); # lop off the extra trailing comma
$query .= " WHERE {$id_field}={$newid}";
mysql_query($query);
// return the new id
return $newid;
}
function DuplicateMySQLRecord ($table, $id_field, $id) {
// load the original record into an array
$result = mysql_query("SELECT * FROM {$table} WHERE {$id_field}={$id}");
$original_record = mysql_fetch_assoc($result);
// insert the new record and get the new auto_increment id
mysql_query("INSERT INTO {$table} (`{$id_field}`) VALUES (NULL)");
$newid = mysql_insert_id();
// generate the query to update the new record with the previous values
$query = "UPDATE {$table} SET ";
foreach ($original_record as $key => $value) {
if ($key != $id_field) {
$query .= '`'.$key.'` = "'.str_replace('"','\"',$value).'", ';
}
}
$query = substr($query,0,strlen($query)-2); # lop off the extra trailing comma
$query .= " WHERE {$id_field}={$newid}";
mysql_query($query);
// return the new id
return $newid;
}
thks so much !
Here's a less dirty version, using the "INSERT INTO table1 (SELECT * FROM table2 WHERE condition)" syntax, which is far much simpler.
The trick is to get the column names and then set the ID field to NULL.
2 queries instead of 3.
Please note that I adapted your function very quickly, and there may be 1 or 2 errors in it, but you see the principle.
Also note that you SHOULD add error testing after each mysql_query() command.
function DuplicateMySQLRecord ($table, $key_field, $key_value) {
// Get the names of all fields/columns in the table
$query = 'SHOW COLUMNS FROM ' . $table . ';';
$results = mysql_query($query);
// Generate the duplication query with those fields except the key
$query = 'INSERT INTO ' . $table . ' (SELECT ';
while ($row = mysql_fetch_array($results)) {
if ($row[0] == $key_field) {
$query .= 'NULL, ';
} else {
$query .= $row[0] . ', ';
} // END IF
} // END WHILE
$query = substr($query, 0, strlen($query) - 2);
$query .= ' FROM ' . $table . ' WHERE ' . $key_field . ' = "' . $key_value . '")';
mysql_query($query);
// Return the new id
$new_id = mysql_insert_id();
return $new_id;
}
Just a few tweaks and it works great in my application.
Thanks a lot!
... // Generate the duplication query with those fields except the key
$query = 'INSERT INTO ' . $table . ' (SELECT ';
$i = 0;
while ($row = mysql_fetch_array($results)) {
$query .= ($i++ > 0 ? ' ,' : '');
...then remove the commas from the strings in the $query building statements that follow...