Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    New Coder
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to get the Next Auto Increment number in Mysql

    Dear Friends.

    I want to get the How to get the Next Auto Increment number in Mysql using PHP code ?

    can i get the Next Auto Increment number in Mysql Usiing PHP code??

    Pl help me.

    Thanks
    Casper.

  • #2
    New to the CF scene
    Join Date
    Aug 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Im not quite sure what you mean...From what i understand i think you mean a counter? You could add a counter to your query and put it in a while loop so it auto increments after every load...

    Here is an example:
    PHP Code:
    $counter=0;

    while(
    $counter =< 50)
    {
         
    $query "SELECT * FROM tablename WHERE id='$counter';
         $result = blah_blah_blah;


  • #3
    Regular Coder
    Join Date
    May 2006
    Location
    Wales
    Posts
    820
    Thanks
    1
    Thanked 82 Times in 79 Posts
    Assuming id is the auto_increment column:
    PHP Code:
    $query mysql_query("SELECT MAX(id) FROM `table`");
    $results mysql_fetch_array($query);
    $cur_auto_id $results['MAX(id)'] + 1

  • #4
    New Coder
    Join Date
    Aug 2006
    Location
    Netherlands
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nono!

    Nonono! You can ONLY get the next_id by using it after an insert or update statement.

    The MAX() will not work, it will only give you the highest number used, and that plus 1 is not alway the next assigned number. Example:
    You have a table with auto_increment column 'id' and values:
    1
    2
    3

    The MAX(id) will give you 3.
    Now you delete 3. The MAX(id) value will give you 2.
    After inserting a new row, the table will be:
    1
    3
    4
    and the MAX(id) will return 4.

    The only way to get the the last insert id is after you have issued an UPDATE or SELECT statement, and you get it either via a LAST_INSERT_ID() or mysql_insert_id().

    Have a look at the MySql documentation:
    http://dev.mysql.com/doc/refman/4.1/...unique-id.html
    http://dev.mysql.com/doc/refman/4.1/...insert-id.html


    Ronald
    Last edited by ronverdonk; 08-25-2006 at 12:59 PM.
    RTFM is an almost extinct art form, it should be subsidized.

  • #5
    Regular Coder
    Join Date
    Mar 2006
    Location
    Connecticut, USA
    Posts
    400
    Thanks
    1
    Thanked 0 Times in 0 Posts

  • #6
    New Coder
    Join Date
    Sep 2006
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You could try SELECT * FROM 'table' ORDER BY 'id' DESC LIMIT 1 to get the mose recently added row, get the id number from that and add 1 to it, but if someone else happens to be running the script at the same time and inserts a new row in the window between you getting the number of the last record and the time you actually use the number then the number you got will be wrong.

    The only reliable way of doing it would be to INSERT a dummy record into the table, get the ID of it with LAST_INSERT_ID() or mysql_insert_id() and then UPDATE the newly created row with the actual data you want to store.

  • #7
    New Coder
    Join Date
    Aug 2006
    Location
    Netherlands
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by PassiveSmoking View Post
    You could try SELECT * FROM 'table' ORDER BY 'id' DESC LIMIT 1 to get the mose recently added row, get the id number from that and add 1 to it, but if someone else happens to be running the script at the same time and inserts a new row in the window between you getting the number of the last record and the time you actually use the number then the number you got will be wrong.

    The only reliable way of doing it would be to INSERT a dummy record into the table, get the ID of it with LAST_INSERT_ID() or mysql_insert_id() and then UPDATE the newly created row with the actual data you want to store.
    Is this contribution different from the one added at August 25?? Or do I miss something here?
    And you should definitely NOT do the first thing in this quote. Read my entry again and you know why not.

    Ronald
    RTFM is an almost extinct art form, it should be subsidized.

  • #8
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    just to reiterate what has been said previously - the correct answer is here:
    http://blog.jamiedoris.com/geek/560/

    PHP Code:
    <?
    $tablename         
    "tablename";
    $next_increment     0;
    $qShowStatus         "SHOW TABLE STATUS LIKE '$tablename'";
    $qShowStatusResult     mysql_query($qShowStatus) or die ( "Query failed: " mysql_error() . "<br/>" $qShowStatus );

    $row mysql_fetch_assoc($qShowStatusResult);
    $next_increment $row['Auto_increment'];

    echo 
    "next increment number: [$next_increment]";
    ?>

  • #9
    New Coder
    Join Date
    Aug 2006
    Location
    Netherlands
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by NancyJ View Post
    just to reiterate what has been said previously - the correct answer is here:
    http://blog.jamiedoris.com/geek/560/

    PHP Code:
    <?
    $tablename         
    "tablename";
    $next_increment     0;
    $qShowStatus         "SHOW TABLE STATUS LIKE '$tablename'";
    $qShowStatusResult     mysql_query($qShowStatus) or die ( "Query failed: " mysql_error() . "<br/>" $qShowStatus );

    $row mysql_fetch_assoc($qShowStatusResult);
    $next_increment $row['Auto_increment'];

    echo 
    "next increment number: [$next_increment]";
    ?>
    Thanks NancyJ! I didn't know this. Anyway, using MAX() is wrong.

    Ronald
    RTFM is an almost extinct art form, it should be subsidized.

  • #10
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Read this article on using last_insert_id in mysql. You don't need to use mysql_insert_id because the use of last_insert_id is already used in your insert in the next table.

    You don't need to know the value of the auto increment before you use it. THe only reason you should need it is to insert data into a secondary table and that information is available after you do the insert.

  • #11
    New Coder
    Join Date
    Aug 2006
    Location
    Netherlands
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    Read this article on using last_insert_id in mysql. You don't need to use mysql_insert_id because the use of last_insert_id is already used in your insert in the next table.

    You don't need to know the value of the auto increment before you use it. THe only reason you should need it is to insert data into a secondary table and that information is available after you do the insert.
    Well I DO want to know it beforehand, because I create a unique token based on that number and I want to know it before I insert the row into the database. (don't tell me there are other ways of creating that token, because I already know that).
    Btw: the link to the article is not much use, it is a broken link.

    Ronald
    Last edited by ronverdonk; 09-23-2006 at 03:23 PM.
    RTFM is an almost extinct art form, it should be subsidized.

  • #12
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    You can't know it beforehand. If you have two (or 2000) people all at approximately the same place in the processing at the same time then the same current value will be returned for all of them but only one of them will actually get that number.

    To do it properly you insert the record first before creating your unique token as otherwise you can't guarantee that the token actually is unique. You can always delete the record again if it turns out that it wasn't needed after all and you can always update the record after creating it if you want to store the token in it.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #13
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    the only reason you should be using an auto increment column in your table is because there are no suitable natural keys (columns or combinations of columns) to create as your primary key. in place of this you create a surrogate key.

    if it is necssary for you to create an artificial key it helps that it is an auto_increment key specifically so you can take advantage of getting that value uniquely from other users and installing it as the foreign key in the next table.

    you should not rely on the auto increment key for any other reason, you thus do not have to know the value before you use it, you are using it as the foreign key in another table.

    if you are trying to create a unique token based on the value of the auto increment key you should not be doing so.

    sorry but that isn't standard database practice, the use of the auto increment column as described above is.

  • #14
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by NancyJ View Post
    just to reiterate what has been said previously - the correct answer is here:
    http://blog.jamiedoris.com/geek/560/
    I'd say it is AN answer, just not the correct one. How are you guaranteed that value won't be used by another user before you use it?

  • #15
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    The problem with that article is the author admits it can sometimes fail, but he doesn't care if it occasionally fails for his particular application. But then others reading the article will try to use it to assign foreign keys in other tables, which of course will have disasterous results on those times it goes wrong.

    I'd never use that technique personally.


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

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