1

Im new to PHP and I was wondering how I can overcome this seemingly simple problem:

I have a database with several tables. Of them 1 table is called "order_header". Order header has a field called "orderID" which is the primaryKey and is auto-incremented. OrderID is used in other tables in the database (food_table, drinks_table, merchant_info, customer_info, etc)and is unique to a particular order.

Now I insert data into the order_header using the usual INSERT statement and the order_header generates a new orderID. But now I need to retrieve the orderID I just created and use it to insert data into other tables of that database.

The question is how can I do both inserting data and retrieving the resulting orderID in one atomic method? I cannot use the mySQL query to get the last orderID because what if another thread has inserted an entry in orderID in the meanwhile.

In Java I guess one could use locks and the word @synchronized, but how would one do this in PHP?

banditKing
  • 9,405
  • 28
  • 100
  • 157
  • 1
    possible duplicate: http://stackoverflow.com/questions/621369/sql-insert-and-catch-the-id-auto-increment-value – Nick B Aug 08 '11 at 00:28

1 Answers1

5

Use mysql_insert_id straight after the query. It doesn't run another query to find the last ID

bcoughlan
  • 25,987
  • 18
  • 90
  • 141
  • Thanks for your answer- quick question------but is that thread-safe and atomic? What is another thread inserts a record right after and then this statement executes, then don't I get the incorrect ID? – banditKing Aug 08 '11 at 00:36
  • Yes - it returns the result for the last query on the **current connection**, since you call mysql_connect on each request, each "thread" has a different connection. – bcoughlan Aug 08 '11 at 00:49