Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57

    Unanswered: How to get the next auto-increment value?

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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?

  3. #3
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    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 Ill 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.
    Thats 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, Ill use the solution mentioned in the second quote.

    Thanks again for the time you dedicated to help me!

    Best regards,
    Gerry

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.

  5. #5
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    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.)

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •