How to get the next auto-increment value?

Germaris · #1 (**permalink**) 02-26-08, 15:27

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

healdem · #2 (**permalink**) 02-26-08, 17:12

you can't
what you can do is get the last value using the appropriate php function, if you are using MySQL its mysql_insert_id

you can guess it will almost certainly be that +1, however in a multi user system, short of attempting to secure a table lock you cannot guarantee what the next autonumber ID will be. In any event an autonumber column should have no meaning or value outside the system itself.

just out of curiosity: why would you want to know what the next ID is going to be?

Germaris · #3 (**permalink**) 02-26-08, 19:03

Thanks for your reply and for the logic of the ways you explored.
You are right.
Except for the first paragraph because you are 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)

Quote:

Originally Posted by healdem

you can't

This is not true.

Code:

<?php 
$res = mysql_query("SHOW TABLE STATUS LIKE '$tablename'");  
if ($res && mysql_num_rows($res)) {  
    $row = mysql_fetch_assoc($res);  
    $next_autoid = $row["Auto_increment"];  
} else { 
    die("error!"); 
} 
echo "next auto_increment value for `tablename` is ", $next_autoid; 
?>

Quote:

Originally Posted by healdem

what you can do is get the last value using the appropriate php function, if you are using MySQL it's mysql_insert_id

It will be a good solution and I think I’ll use it...

Quote:

Originally Posted by healdem

you can guess it will almost certainly be that +1, however in a multi user system, short of attempting to secure a table lock you cannot guarantee what the next autonumber ID will be. In any event an autonumber column should have no meaning or value outside the system itself.

That’s really true!

Quote:

Originally Posted by healdem

just out of curiosity: why would you want to know what the next ID is going to be?

See the beginning of this post...

Your post makes sense and, for sure, I’ll use the solution mentioned in the second quote.

Thanks again for the time you dedicated to help me!

Best regards,
Gerry

healdem · #4 (**permalink**) 02-27-08, 09:50

In which case inserting a record becomes a two stage process. first off write as record, I;d suggest the barest minimum of information, retain that ID, accept user input, validate then update the record.

However this can leads to problems by nature of your design. Unless your system allows order numbers to non contiguous (and I'd check with the user or the users auditors) then coercing an autonumber value to be someghtign like an ordernumber is a bad idea.

Why? you never ever know what may happen to your data, a system generated autonumber ID may skip a few records (as rows get deleted, inserts are made but fail for whatever reason, some servers allow the gap to be altered, most allow the number to be reset. In short its not a good idea to use autonumber for things like receipt no, order no, where the number has a significance outside the system itself. Autonumber columns are there purely for the db engine to have a quick simple method of making a row unique, nothing more, they should not be used for any other purpose outside the system itself.

If you want on order number then there are several alternative methods
either retrieve the current highest order number in the system, requires a table lock, write your record, release the table lock

OR the better solution in my books, store the last used order number in a config table somewhere, theres often a grubby little config tabel in a system containing the data that couldn't be normalised, or is disparate settings and the like.. store the order number there, get a record lock, get the order number, add one, write the new order number and update the last used order number and finally release the record lock.

Another less elegant solution could be to create a skeleton order record, with no data in it, query the db for the first available skeleton record and update that record, writing another skeleton record to replace the one just used. a nasty nasty solution but it may work, again you will need to be carefull with record locks to make sure you don't have problems with mulitple users trying to grab the same skeleton record at the same time

Assuming you are using PHP then I'm pretty certain you need to be using INNODB tables rather than MyISAM, as MyISAM tables do not support transaction.

dbmab · #5 (**permalink**) 02-28-08, 13:38

I know you have gone the route of inserting an empty record to "fix" the id/order number, but you are apparently making an electronic version of a paper order form, where each form has a pre-printed number on it.

The short answer - you don't need to display the order number until the order has been placed. There is no real world need to be displaying the number on the entry form (look at the extra code to do this and the problems it creates - like if they don't complete the order, you now have an incomplete row in the database that should be removed at some point, meaning even more code to write and manage.)

aschk · #6 (**permalink**) 02-29-08, 06:11

I agree with dbmab, you don't display an order number UNTIL the order is confirmed. Basically they could NOT click confirm and just leave the purchase unfinished and now you have a record in your DB which is blank, and meaningless.

r937 · #7 (**permalink**) 02-29-08, 09:47

Quote:

Originally Posted by healdem

In which case inserting a record becomes a two stage process. first off write as record, I;d suggest the barest minimum of information, retain that ID, accept user input, validate then update the record..

and now we see where he got the idea for

Code:

INSERT INTO tbl_name VALUES ()

the tears will be all your fault!!!