Copy a record

[Thierry]

I was wondering if there was a way in which PHP/SQL will copy the selected record, simply copy & pasting all its contents into a new record, with only 1 field being changed.
So if I had ten records, and I hit the copy button, then record 11 should be made.

Right now, I do this by using a select query to select all the data, then getting all the quotes and stuff right (again) and then an insert query.
Anyway in which it goes a lot faster then that? Something like "COPY * FROM table WHERE id = 'id'"?

[ToonMariner]

You would have to select a record then insert a new record with all the fields except the unique id.

[Thierry]

Thats currently what I am doing.
I select the record I want, get all the data, and put all that in another query to insert it.
But with tables that have a lot of fields(not to mentions quotes) this can take a long time.
I was wondering if there was simply some kind SQL statement in which it simply adds a new record with the data of the selected record.

[ToonMariner]

IF you use the field `id` as the unique or primary key then do this.

Adapted the example in the manual for your needs

[code]
<?php
$qry = "SELECT * FROM `atable` WHERE `id` = $id";
$qry = mysql_query($qry);

// generate field names..
$fields = "(";
$i = 0;
while ($i < mysql_num_fields($qry)) {
echo "Information for column $i:<br />\n";
$meta = mysql_fetch_field($qry, $i);
if (!$meta) {
  echo "No information available<br />\n";
}
if ($meta->primary_key == 1)
{
  $pripos = $i;
}
$fields .= strcomp($fields,'(') == 0 ? "`" . $meta->name . "`" : ", `" . $meta->name . "`";

$i++;
}
$fields .= ")";

That is your string...

Now the values...

$row = mysql_fetch_assoc($qry);
$vals = "(";
$i = 0;
foreach($row as $key => $val)
{
if ($i == $pripos)
{
  $vals .= strcomp($vals,'(') == 0 ? "NULL" : ", NULL" ;
}
else
{
  if (is_null($vals))
  {
   $vals .= strcomp($vals,'(') == 0 ? "NULL" : ", NULL" ;
  }
  else
  {
  $vals .= strcomp($vals,'(') == 0 ? "'" . $vals . "'" : ", '" . $vals ."'" ;
  }
}
}
$vals .= ")"

$qry = "INSERT INTO `atable` " . $fields . " VALUES " . $vals . "";
$qry = mysql_query($qry);

?>
[/code]

Try that....