Copy MySQL Record

[br0ken]

I'm trying to copy a row from a table and insert it into thee same table.

 

The code given on the internet is the following:

 

INSERT INTO table SELECT * FROM table

 

(I've changed the word table to match my table name)

 

I get an error because this query doesn't increment the records primary key and therefore will create a duplicate entry.

 

Any ideas will be appreciated

thanks!

[djpic]

I would just take it, put it into an array then re-insert it into the new table:

 

<?php
  $conn = (database connection info here);

  $sql = "SELECT * FROM `table` WHERE `id`='$id'";
  $result = mysql_query($sql, $conn);
  $data = mysql_fech_array($result);

  $sql = "INSERT INTO `table` (`id`,`col1`,`col2`,`col3`) VALUES (NULL, '$data['col1']','$data['col2']','$data['col3']')";
  mysql_query($sql, $conn);
?>

[matto]

You may need to be more specific.

 

example:

 

insert into customers (first_name, family_name) select first_name, family_name from customers where family_name = 'Smith';

 

 

[br0ken]

I was hoping to not have to be specific as I wanted the script to work even after new columns are added.

 

Anyway, this is the code I've came up with incase anybody else encouters this similar problem

 

function mysql_copy_record($table, $id)
{
$db	= mysql_connect("localhost", "user", "pass");
	mysql_select_db("dbname");

$rs	= mysql_query("SELECT * FROM $table WHERE id = $id LIMIT 1");
$i	= 1;
$f = "";
$v = "";

if (mysql_affected_rows > 0)
{
while(@mysql_field_name($rs, $i))
{
	$f .= mysql_field_name($rs, $i).", ";
	$val	= mysqk_result($rs, 0, $i);

	switch(mysql_field_type($rs, $i))
	{
		case "int":
		case "unknown":	
					if (strlen($val) < 1)	$v .= number_format($val, 0).", ";
					else					$v .= $val.", ";
		break;
		default:		$v .= "'".inputHTML(mysql_result($rs, 0, $i), 0)."', ";
		break;
	}

	$i++;
}

$query	= "INSERT INTO $table (".substr($f, 0, -2).") VALUES (".substr($v, 0, -2).")";

$rs		= mysql_query($query);

return mysql_insert_id($rs);
}