Question about auto-increment numbers

[degroat]

Here's what I'm trying to do...

1. Insert info into a table that has an ID# which increments automatically.
2. Get that ID# that was generated.

Obviously, I can just run a select where I grab the highest ID#, but I'm concerned that there could be conflictions...

I.E.

Person 1 accesses page that inserts.
Person 2 accesses page that inserts.
Person 1 grabs highest ID
Person 2 grabs highest ID

Now, but people have the same ID. Given how fast these pages run, the chances of this happening are pretty rare, but it's a possibility and I don't want to have to deal with it if/when it does happen.

So, is there another way to do this?

 

[sleipnir214]

MySQL provides a builtin function last_insert_id(), which returns the last auto_increment value generated.

MySQL maintains the values on a per-connection basis, so it knows which auto_increment value you generated and can keep it separate from auto_increment values other concurrent users have generated. For more information:

http://www.mysql.com/doc/en/Miscellaneous_functions.html

Just perform the query:
SELECT last_insert_id()



Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[miahmiah900]

i would suggest NOT using a SELECT query to get the highest ID. an AUTO_INCREMENT field is faster, and the ID is generated when the row is actually inserted, and not before.

 

[degroat]

I'm sorry, but I'm not quite getting this function.

Can you provide me an example of how this would be used in php code with table name 'cart' and the auto incremented field of 'cart_num'?

 

[sleipnir214]

degroat:
There isn't much to understand.

First you insert your data, making sure to let MySQL set the auto_increment column.

Then you run the query "SELECT last_insert_id()".


PHP has a function to perform that query in a single function call: mysql_insert_id() (

http://php.he.net/manual/en/function.mysql-insert-id.php).

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[degroat]

Another question somewhat related to this... Here's what I'm trying to do:

1. Determine the next auto incremented order number
2. Process a transaction
3. Insert order using

So, basically what I need is a way to lock the next auto incremented number while the order is being processed and until the insert is complete. Is there a function that does this in PHP?

 

[sleipnir214]

There is not.

Guessing the next auto_increment number is non-deterministic. Between the time you guess the number and the time you actually insert a record, another user could have used up the number you guessed.

What I recommend you do is enter an empty record in the table, then fetch the auto_increment value used for that record.

Then if you actually store the transaction, you update that record.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[degroat]

I figured that I may have to do that... I guess it isn't as big of deal as I thought. At first I didn't want to do that because I didn't want a bunch of empty rows in the DB, but I can delete them right away if the order does not go through (duh!).

 

[sleipnir214]

Yes.

Some folk also have a problem with the fact that all auto_increment numbers will not be used.

One thing I recommend is making your auto_increment column unsigned. It gives you a lot more numbers to play with.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!