MySql copying a whole row into the same table

lomokev

is there a quick and easy way to copy a whole row in a table and copy it the same table? obviously i want the ID to change to the next auto_increment value.

bough

you can select the row you wish to insert create a new sql query and insert that

$sql = "select * from database"; //or select specific fields
$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {
$insertArr[0] = $row[1];
$inserArrt[1] = $row[2];//build up array of fields selected
}

$insertString = implode(",", $insertArr);
$sql = insert into database values (NULL,$insertArr);

$results = mysql_query($sql);

is a rough option

Roger_Ramjet

If your version of mysql is 4.0.14. or higher then you can do it in one query

$sql = "INSERT INTO table(col1,col2,col3,col4,col5) SELECT col1,col2,col3,col4,col5 FROM table WHERE id=$id";

Note that I am not selecting the id column and so have to list all the other columns. You could also just select * and use ON DUPLICATE KEY UPDATE instead.