How to Copy a MySQL Record

[jcarver]

Hi,

I'm looking for an efficient SQL statement for copying a record.

Basically, I have a very complicated table that may change in the future, but I don't want to have to change this function every time it is. I want to feed it the id of a database record, and have one just like it be created.

So start as:

<U>ID, colA, colB, colC, colD</U>
1 , qwer, tyui, opas, dfghj
2 , zxcv, srdf, fghj, poik

run a function like
makeDuplicate (ID);

that calls a MySQL query THIS IS WHERE I NEED HELP

that makes the table look like:

<U>ID, colA, colB, colC, colD</U>
1 , qwer, tyui, opas, dfghj
2 , zxcv, srdf, fghj, poik
3 , qwer, tyui, opas, dfghj

Thanks for any help, in advance!
Jason

[Anon]

Hi,
there is no way in SQL.
Write a insert/select without-id - Query in SQL .

It's easier than writing a screen full of PHP-code with doing the same.

regards
mb

[Anon]

Well, heres screen full of PHP-code :-)

This is probably a dirty way of doing this but hey, it works!...

heres the code:

<?php
function makeDuplicate($id)
{
$table="yourtable"; // you could of course put $table in the function variable to make this function more flexible

$query="SELECT * from ".$table." where id='".$id."'";
$query_result_handle=mysql_query($query);
$row=mysql_fetch_row($query_result_handle);

$result = mysql_list_fields("yourdb", $table);
$nr = mysql_num_fields($result);
$insert="INSERT into ".$table." (";

//put field names in the $insert-string
for ($count = 1; $count < $nr; $count++)
{
$field[$count]=mysql_field_name($result, $count);
$insert = $insert.$field[$count].",";
}

$insert = substr("$insert", 0, -1); //get that "," from the end
$insert = $insert.") VALUES (";

//put values in the $insert-string
for ($count = 1; $count < $nr; $count++)
{
$insert = $insert."'".$row[$count]."',";
}

$insert = substr("$insert", 0, -1); //get that "," from the end
$insert = $insert.")";

// And finally Insert
mysql_query($insert);

}
?>

[mattr]

CREATE TABLE #temp( colA, colB, colC, colD );

INSERT INTO #temp ( SELECT colA, colB... FROM yourTable WHERE id = 123 );

INSERT INTO yourTable ( SELECT * FROM #temp );

DROP TABLE #temp;