INSERT order and getting row ID back
I've been over to the MySQL site just to confirm this, but any voice of experience would be nice here...
So I'm reaching the point that when an order is placed I do two things:
1. Insert a row into table 'orders'
2. Insert shopping cart rows for that order, into the 'ordered_items' table
The problem I have is, table 'orders' uses an auto increment value for each row's key field, so I need some way to insert the row and retrieve that newly generated key field value.
MySQL provides this:
INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
Does this seem OK ?
In theory I don't *need* to use an auto increment field. For example I could use the user's unique_session_ref and concatenate a datetime onto it ? I wasn't sure if such a VARCHAR type field for the key would impact performance.
Let me know what you think ?
AdChoices