[SOLVED] lastInsertId() returns 0

lastInsertId() returns 0

I have looked through the similar questions here but have not been able to solve my problem…all help is greatly appreciated!

Basically, I have the following SQL code in my PHP:

$query = 'CREATE TEMPORARY TABLE tmp SELECT * from orders WHERE id='.$old_order_id.';
    ALTER TABLE tmp drop id; # drop autoincrement field
    UPDATE tmp SET groupID=null, timeOrdered=CURRENT_TIMESTAMP, totalOrderStatus=0; 
    INSERT INTO orders SELECT 0,tmp.* FROM tmp;
    DROP TABLE tmp;';
$db->exec($query);
$new_order_id = $db->lastInsertId();

Now, I would have thought that lastInsertId() would successfully return the id of the row that was last inserted into 'orders'. Instead, I get a zero--but I don't know whether this is because it is taking the last inserted ID of table 'tmp', or if it's because of some other mistake I'm making. (I don't see why it would take the id from tmp, since that's not the last inserted id really...)

Solution

It looks like you are just trying to copy an existing row into your table with some data modified. Why don't you simplify this and do this like:

INSERT INTO orders (
    /* fields you are changing */
    groupID,
    timeOrdered,
    totalOrderStatus,
    /* other fields from select below that are not changing */
    field1,
    field2,
    ...
)
SELECT
    /* values for fields you are changing */
    null,
    CURRENT_TIMESTAMP,
    0,
    /* other fields from order table that are to remain unchanged */
    field1,
    field2,
    ...
FROM orders
WHERE id = ?

Just don't INSERT a value for whatever you primary key field is (assuming it is autoincrement), then your insert id will work fine.