How to insert the last id from a table into another table in MySQL?

Question

I want to take the id of the most recent item in a database, increment it by one and insert that new id in to another table.

I tried:

$select = mysql_query("SELECT id FROM tableName ORDER BY id DESC");

while ($return = mysql_fetch_assoc($select)) {

   $id = $return['id'];
   $newId = $id++;

}

mysql_query("INSERT INTO anotherTable (someColumn) VALUES ('$newId')");

But it didn't work. How can this be done?

Answer 1

You should not do this. Instead insert a new record into tableName then use mysql_insert_id() to capture the new ID and then insert it into anotherTable. This way you avoid race conditions that could happen if you did it the other way around.

Answer 2

Why are you using a while statement? If you only want the most recent id, just take the while out..

$select = mysql_query("SELECT id FROM tableName ORDER BY id DESC");

$return = mysql_fetch_assoc($select);

$id = $return['id'];
$newId = $id + 1;

mysql_query("INSERT INTO anotherTable (someColumn) VALUES ('$newId')");

Answer 3

You can try this:

$select = mysql_query("SELECT id FROM tableName ORDER BY id DESC");

if(! $select)
    die("Query Failed");

$return = mysql_fetch_assoc($select);
$id = $return['id'];
$newId = ++ $id; // post increment does not work...use preincrement.
mysql_query("INSERT INTO anotherTable (someColumn) VALUES ('$newId')");

Answer 4

Use mysql_insert_id() to get the last inserted ID