How to get the next auto-increment value ?

[Germaris]

Hi there!

 

How should I write a query to get the next auto-increment value of a table?

 

Many thanks in advance for any help.

 

Best regards,

Gerry

[php_dave]

Why not just get the last one and add 1 to it?

 

SELECT id from my_table order by id DESC LIMIT 1

 

will return the last id in the table

 

Dave

 

[PFMaBiSmAd]

Due to multiple visitor concurrency, you cannot get the last value and add one and then use the value, because each concurrent visitor can get the same value and there would be a collision.

 

If you have just executed an INSERT query in the same script you can use the mysql_insert_id() function to get the id of that insert - http://www.php.net/manual/en/function.mysql-insert-id.php

 

If you want to get the next id and use it for some purpose before you have all the data necessary, you need to INSERT a row to "reserve" the id and then update the row when all the information is known.

 

It is normally not necessary to get and use the next id. If you rethink what you are doing, you might find that you don't really need to know what the next id is, until you have actually inserted the data in to the database and the actual id has been assigned.

[php_dave]

PFM - you are correct of course! Apologies - shows my inexperience!

[Germaris]

If you want to get the next id and use it for some purpose before you have all the data necessary, you need to INSERT a row to "reserve" the id and then update the row when all the information is known.

 

Thanks for your reply and for the logic of the ways you explored.

 

You are right.

Except for the last paragraph because you were not aware of my needs (I need to get the next auto_increment value because I need to display this value as it will be also the number of the order/bill BEFORE the customer click to confirm its purchase)

 

All of your post makes sense and, for sure, I’ll use the solution mentioned in the quote.

 

Thanks again!

 

Best regards,

Gerry

[PFMaBiSmAd]

Insert a record with a "pending" value that gets updated when the order is placed/complete. Its the only way to insure the code will work under all conditions. Otherwise you will end up with two orders with the same number by different concurrent visitors.

[Germaris]

Insert a record with a "pending" value that gets updated when the order is placed/complete. Its the only way to insure the code will work under all conditions. Otherwise you will end up with two orders with the same number by different concurrent visitors.

 

How do you do this? (I'm talking about the "pending" value...)

[luca200]

He means you must insert the row with a value showing that it's not completed

[PFMaBiSmAd]

Either insert a row that has empty/null values for all the unknown fields or use an existing column and put a value in it that would never occur naturally or add a column to indicate the status of the order, such as - pending, ordered, paid, shipped, complete... A lot of ecommerce scripts would already have a "status" column that could be used.