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

    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

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

      }
      ?>

        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;

          Write a Reply...