copy table with autoincrement

[hygum]

I have tried a lot to copy a table with php, but I can't:

I tried first:
CREATE TABLE copy SELECT * FROM table

but it doesn't copy autoincrement or primary key. Deleting the autoincrement column, and then assigning it, does however makes an auto_increment column, but the values doesn't correspond with the original, because some posts where deleted there, and then the auto_increment values are higher.

Then I tried to assign those two values later, by first truncating.
CREATE TABLE copy SELECT FROM table";
$sql2="TRUNCATE TABLE copy";
$sql3="INSERT INTO copy SELECT FROM table";
$sql4="ALTER TABLE copy ADD PRIMARY KEY (id)";
$sql5="ALTER TABLE copy CHANGE id id INT( 11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT";
$sql6="ALTER TABLE copy AUTO_INCREMENT=$maxid

but that doesn't work either. Isn't there a simple way to copy a table including the primary key and the autoincrement with php?

I use mysql 4.0.25

[nemonoman]

I suggest you create the table using the

SHOW CREATE TABLE

command
//get the row
$myCreateRow=mysql_fetch_array(mysql_query('SHOW CREATE TABLE tableToCopy'));
//Change the table name:
$newcreate=(' tableToCopy', 'NewTableName', $myCreateRow[0]);

//make the table
$result=mysql_query($newcreate);

//ALTER TABLE to the autoincrement value

//get the autoincrement value:
$myStatusRow=mysql_fetch_array(mysql_query('SHOW TABLE STATUS LIKE 'tableToCopy'));
$myAutoInc=$myStatusRow['Auto_increment'];
//set the autoincrement
mysql_query("ALTER TABLE NewTableName AUTO_INCREMENT =$myAutoInc");