0

I am trying to understand the database design for an e-commerce site. I am having trouble understanding on what to do in the following situation. Say the user creates an account for the first time, and makes an order. I can write php code which will add the user_id (primary-key), first name & last name. But what if I want to add the user_id in the orders table (user_id in the orders tables is a foreign key). How do I get the value, as the user_id in the customers table is auto incrementing?

raju bhai
  • 21
  • 5
  • This seems like a repost of http://stackoverflow.com/questions/9477502/get-the-last-inserted-row-id-with-sql-statement – dvntehn00bz Feb 08 '14 at 06:53
  • please refer http://stackoverflow.com/questions/1685860/how-do-i-get-the-last-inserted-id-of-a-mysql-table-in-php – shola Feb 08 '14 at 06:56
  • Thanks for the responses guys. However, I am little unclear here because I could use the max method, but what if someone other customer registered at the same time? Wouldn't it give me the wrong id (which is already used)? Is using transactions the best way to go in this situation? – raju bhai Feb 08 '14 at 07:04

2 Answers2

0

Are you using two tables one for storing user details and other for placing orders ?(which is better) If so my recommendation would be to use username (not first name or last name) as primary key and store the username as SESSION variable for each login and for each order add the username in order table along with orders details

Jithu R Jacob
  • 368
  • 2
  • 17
0

There are couple of ways to do that, you can access the newly generated user_id by LAST_INSERT_ID() and use it in subsequent transaction, or you can store it in session and use it later when inserting in Orders table.

Vinay Pandey
  • 8,589
  • 9
  • 36
  • 54