Entry

How do I copy a record to another record while only incrementing the id # with php and mysql?

Feb 16th, 2008 02:49
dman, Jim Epler, Martijn Sandbergen, Eric Thomas, http://sturly.com

Hi,
I was searching for the very same myself, but found nothing that could
do such a thing in standard MySQL syntax. So, for what it worth, I
created a function that can. It requires an open database connection, a
table name, the name of the index field and the record_id located in
that index field.
Hope it does the trick.
// function to create a duplicate record in a table with 1
auto_increment id.
function mysql_duplicate_record($table, $id_field, $id)
{
	// load original record into array
	$query = 'SELECT * FROM ' . $table . ' WHERE ' . $id_field . ' = ' .
$id . ' LIMIT 1;';
	$r = mysql_query( $query ) or die('Error, query failed. ' . mysql_error());
	$ar = mysql_fetch_array( $r, MYSQL_ASSOC );
	// insert new record and get new auto_increment id
	mysql_query ('LOCK TABLES ' . $table . ' WRITE;') or die('Error, query
failed. ' . mysql_error());
	mysql_query ('INSERT INTO ' . $table . ' ( `' . $id_field . '` ) VALUES
( NULL );') or die('Error, query failed. ' . mysql_error());
	$id = mysql_insert_id();
	mysql_query ('UNLOCK TABLES;') or die('Error, query failed. ' .
mysql_error());
	// update new record with values from previous record
	$query = 'UPDATE ' . $table . ' SET ';
	while ($value = current($ar))
	{
		if (key($ar) != $id_field)
		{
			$query .= '`'.key($ar).'` = "'.$value.'", ';
		}
		next($ar);
	} 
	$query = substr($query,0,strlen($query)-2).' ';
	$query .= 'WHERE ' . $id_field . ' = "' . $id . '" LIMIT 1;';
	mysql_query($query) or die('Error, query failed. ' . mysql_error());
	// return the new id
	return $id;
}
///
Just wanted to add that the above function to duplicate a record in
MySQL works great! Thank you!!