mysql使用第一个表中的auto_increment值作为第二个中的外键插入到两个表中

Question

I want to insert into the cart table

**orderId** | cartId | cartDate | cartStatus
____________________________________________
1           | 1      | 20120102 | complete
2           | 2      | 20120102 | complete
3           | 3      | 20120102 | complete
4           | 4      | 20120102 | complete

using the auto increment value orderId from the order table

**orderId** | orderStatus | secret   | sauce
____________________________________________
1           | 7          | 020200202 | bbq
2           | 6          | 020200202 | bbq
3           | 6          | 020200202 | t
4           | 4          | 020200202 | m


INSERT INTO ordertable VALUES(null,7,020200202,bbq)

but then using the orderId (which will now be 5)

INSERT INTO carttable VALUES(orderId,20120102,complete)

However, this insert must be done as the same query. If I use mysql_last_id (php) there is an opportunity for someone else to insert into the database before my cart insert is executed. Or the connection might timeout. The database is MyISAM (and I can not change this, 3rd party solution).

Thank you, J

Answer 1

I think your concern about using mysql_last_id is unfounded - it will return the last id for the current connection, not the last id globally across all connections.

So unless you have multiple threads sharing the same database connection or you perform another identity insert on the same connection before calling mysql_last_id, you should have nothing to worry about.

ETA: You could do this by sending multiple queries at once, like this:

INSERT INTO ordertable VALUES(null,7,020200202,bbq);
INSERT INTO carttable VALUES(LAST_INSERT_ID(),20120102,complete);

But if you are using mysql_query it usually won't let you send multiple queries in the same call (mostly as a security measure to try to prevent SQL injection).