PHP MySQL Copy a row within the same table... with a Primary and Unique key

Alternatively, if you don't want to write all the columns explicitly (and don't want to start creating/dropping tables), you can just get the columns of the table and build the query automagically:

//get the columns
$cols=array();
$result = mysql_query("SHOW COLUMNS FROM [table]"); 
 while ($r=mysql_fetch_assoc($result)) {
  if (!in_array($r["Field"],array("[unique key]"))) {//add other columns here to want to exclude from the insert
   $cols[]= $r["Field"];
  } //if
}//while

//build and do the insert       
$result = mysql_query("SELECT * FROM [table] WHERE [queries against want to duplicate]");
  while($r=mysql_fetch_array($result)) {
    $insertSQL = "INSERT INTO [table] (".implode(", ",$cols).") VALUES (";
    $count=count($cols);
    foreach($cols as $counter=>$col) {
      $insertSQL .= "'".$r[$col]."'";
  if ($counter<$count-1) {$insertSQL .= ", ";}//dont want a , on the last one
    }//foreach
  $insertSQL .= ")";

  mysql_query($insertSQL);//execute the query
  }//while

Note that this uses the depreciated code of MySQL and it should be MySQLi. I'm sure it could also be improved, but it's what I'm using and it works very well.

For tables with many columns, I use a (yes, slow) method similar to Phius idea.
I put it here just for completeness.

Let's assume, table 'tbl' has an 'id' defined like

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

Then you can clone/copy a row by following these steps:

1. create a tmp table

CREATE TEMPORARY TABLE tbl_tmp LIKE tbl;

1. Insert one or more entries you want to clone / copy

INSERT INTO tbl_tmp SELECT * FROM tbl WHERE ...;

1. remove the AUTOINCREMENT tag from 'id'

ALTER TABLE tbl_tmp MODIFY id INT;

1. drop the primary index

ALTER TABLE tbl_tmp DROP PRIMARY KEY;

1. update your unique indices and set 'id' to 0 (0 needed for step 6. to work)

UPDATE tbl_tmp SET unique_value=?,id=0;

1. copy your modified rows into 'tbl' with 'id' being autogenerated.

INSERT INTO tbl SELECT * FROM tbl_tmp;

1. cleanup (or just close the DB connection)

DROP TABLE tbl_tmp;

If you also need clone/copy some dependant data in other tables, do the above for each row. After step 6 you can get the last inserted key and use this to clone/copy the dependant rows within other tables using the same procedure.

I had to do something similar recently so I thought I post my solution for any size table, example included. It just take a configuration array which can be adjusted to practically any size table.

$copy_table_row = array(
    'table'=>'purchase_orders',     //table name
    'primary'=>'purchaseOrderID',   //primary key (or whatever column you're lookin up with index)
    'index'=>4084,                  //primary key index number
    'fields' => array(
        'siteID',             //copy colunm
        ['supplierID'=>21],   //overwrite this column to arbirary value by wrapping it in an array
        'status',             //copy colunm
        ['notes'=>'copied'],  //changes to "copied"
        'dateCreated',        //copy colunm
        'approved',           //copy colunm
    ),
);
echo copy_table_row($copy_table_row);



function copy_table_row($cfg){
    $d=[];
    foreach($cfg['fields'] as $i => $f){
        if(is_array($f)){
            $d['insert'][$i] = "`".current(array_keys($f))."`";
            $d['select'][$i] = "'".current($f)."'";
        }else{
            $d['insert'][$i] = "`".$f."`";
            $d['select'][$i] = "`".$f."`";
        }
    }
    $sql = "INSERT INTO `".$cfg['table']."` (".implode(', ',$d['insert']).")
        SELECT ".implode(',',$d['select'])."
        FROM `".$cfg['table']."`
        WHERE `".$cfg['primary']."` = '".$cfg['index']."';";
    return $sql;
}

This will output something like:

INSERT INTO `purchase_orders` (`siteID`, `supplierID`, `status`, `notes`, `dateCreated`, `approved`)
SELECT `siteID`,'21',`status`,'copied',`dateCreated`,`approved`
FROM `purchase_orders`
WHERE `purchaseOrderID` = '4084';

Simplest just make duplicate value of the record

INSERT INTO items (name,unit) SELECT name, unit FROM items WHERE id = '9198' 

Or With make duplicate the value of the record with add new/change value of some columns value like 'yes' or 'no'

INSERT INTO items (name,unit,is_variation) SELECT name, unit,'Yes' FROM items WHERE id = '9198' 

I use this one... it drops the primary key column on temp_tbl so there is no problem with duplicate IDs

CREATE TEMPORARY TABLE temp_tbl SELECT * FROM table_to_clone;
ALTER TABLE temp_tbl DROP COLUMN id;
INSERT INTO table_to_clone SELECT NULL, temp_tbl.* FROM temp_tbl;
DROP TABLE temp_tbl;