lastInsertId() returns 0

Question

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...)

Answer 1

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.

Answer 2

Check that the row is actually being inserted. If it's not being inserted, then it follows that there will be no new Id. This was what I discovered was happening and it turned out that I hadn't granted Insert rights for my user on the relevant table.