Jump to content


Photo

Copy a record


  • Please log in to reply
3 replies to this topic

#1 Thierry

Thierry
  • Members
  • PipPipPip
  • Advanced Member
  • 85 posts
  • LocationNetherlands

Posted 18 August 2006 - 10:22 AM

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'"?
Have you tried....a reset?

#2 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 18 August 2006 - 10:33 AM

You would have to select a record then insert a new record with all the fields except the unique id.
follow me on twitter @PHPsycho

#3 Thierry

Thierry
  • Members
  • PipPipPip
  • Advanced Member
  • 85 posts
  • LocationNetherlands

Posted 18 August 2006 - 10:35 AM

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.
Have you tried....a reset?

#4 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 18 August 2006 - 11:10 AM

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

Adapted the example in the manual for your needs

<?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);

?>

Try that....
follow me on twitter @PHPsycho




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users