mysql: most DRY row copy

[eluser]gungbao[/eluser]
imagine a database table with an auto_incremented primary "id" and lets say 1000 other columns, which are not auto_incremented.

what would be the most efficient and most DRY method to clone a row of such a table.

$query = $this->db->get_where('mytable', array('id' => $id));
$data = $query->row();
$data->id = ''; // perhaps more clean code to say $data->id =NULL;
$db->insert= $this->db->insert('mytable', $data);

will do the job.

But that causes two querys. I want to to it in ONE query.

Code:

$SQL = "INSERT INTO mytable SELECT * FROM mytable WHERE id=$id";
$this->db->query( $SQL );

Okay, that won't work because of the uniqueness of the primary key. I could manually type all column names from 1 to 999 but leave out column 0 or could retrieve tables meta data and even cache it, but I am keen to know if there is a maximum performant sequel.

Example:

Code:

$SQL = "INSERT INTO mytable SELECT col_1, col_2, ... , col_999 FROM mytable WHERE id=$id";
$this->db->query( $SQL );

This would work but is it DRY? No, for sure.
Any Guru out there to find a cool solution???

Greetings,
Christoph

[eluser]Sean Murphy[/eluser]
Here's an idea: make a copy of mytable and make the id column null for all rows. Then just do your INSERT/SELECT.

Code:

$SQL = “INSER INTO mytable SELECT * FROM mytable_copy WHERE other_col=$other_col”;
$this->db->query( $SQL );

Of course that wouldn't work in very many situations.

Your first solution seems to be the best, but to cut down on queries you could select all or chunks of rows from the first table instead of one at a time.

[eluser]thocutt1020[/eluser]
This uses more queries but it works - I throw this into my model and boom I can copy records no problem. Adapted from a quick n dirty function I found here: PHP MySQL Copy Record Function

Code:

function copy_record($id,$table='products', $id_field='product_id') {
  // load the original record into an array
  $this->db->select('*');
  $this->db->from($table);
  $this->db->where($id_field,$id);
  $query = $this->db->get();
  $original_record = $query->row();
    
  // insert the new record and get the new auto_increment id
  $sql_data = array('name' => 'Copy in Progress');
  $this->db->insert($table, $sql_data);
  $newid = $this->db->insert_id();
  
    
  // generate the query to update the new record with the previous values
  $query = "UPDATE ".$table." SET ";
  foreach ($original_record as $key => $value) {
         if ($key != $id_field) {
             $query .= '`'.$key.'` = "'.str_replace('"','\"',$value).'", ';
         }
  }
  $query = rtrim($query, ', '); # lop off the extra trailing comma
  $query .= " WHERE {$id_field}={$newid}";
  $this->db->query($query);
    
  // return the new id
  return $newid;
}

[eluser]gungbao[/eluser]
My Question (for some good reasons) was: Howto do it in MySQL only!
I guess the solution could be found in the expression of in "INSERT ... ON DUPLLICATE KEY expression;" perhaps combined with LAST_INSERT_ID()?

Still wondering how mysql does not easily support such a common problem more flexible, there must be a solution.

Cheers
Christoph

[eluser]jmadsen[/eluser]
Best I've been able to manage so far is to run this:

Code:

SELECT
CONCAT( 'INSERT INTO `category` ',
(SELECT REPLACE( GROUP_CONCAT( COLUMN_NAME ) , 'category_id,', '' )
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'category'
AND TABLE_SCHEMA = 'test' ) ,
' SELECT ',
(SELECT REPLACE( GROUP_CONCAT( COLUMN_NAME ) , 'category_id,', '' )
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'category'
AND TABLE_SCHEMA = 'test'
),
' FROM `category` WHERE `category_id` = 1 ')

inside a stored procedure, then execute that.

my testing was

database: test
table: category
primary key: category_id

You can cheat a little and use "AND EXTRA <> 'auto_increment' " in your where clauses instead of the REPLACE, assuming that is correct for your table.

Toying with some other ideas, but I think in the end you'll need a stored procedure to do this