[PHP+MySQL] How to get the next auto-increment value

Discussion in 'Tutorials' started by farscapeone, Feb 5, 2010.

  1. farscapeone

    farscapeone Community Advocate Community Support

    If you ever wondered how to get the next auto increment value form MySQL table then this small tutorial is for you:

    PHP:
    // You have to connect to MySQL and select a database before you can do this
    $table_name "myTable";
    $query mysql_query("SHOW TABLE STATUS WHERE name='$table_name'");
    $row mysql_fetch_array($query);
    $next_inc_value $row["AUTO_INCREMENT"];
    You can find more info about "SHOW TABLE STATUS" MySQL command at:
    http://jayapal.instablogs.com/entry/mysql-show-table-status-syntax/
    Last edited: Feb 5, 2010
  2. ChatIndia

    ChatIndia Community Advocate Community Support

    Thanks for this tutorial.
  3. scc123

    scc123 New Member

    Eh thats handy, all this time I've been using:
    PHP:
    $get=mysql_query("SELECT * FROM table");
    $got=mysql_fetch_array($get);
    $curr_id=$got['ID'];
    $next_id=$curr_id+1;
    Which takes an extra line 0.o
  4. farscapeone

    farscapeone Community Advocate Community Support

    The code you are using is not exactly getting the next auto-increment value. It's just getting the biggest ID and increases it by 1. You can do that even more simple like this:

    PHP:
    $get=mysql_query("SELECT MAX(id) FROM table");
    $got mysql_fetch_array($get);
    $next_id $got['MAX(id)'] + 1;
    But that won't work. Auto-increment is assigning a unique value every time, even if you deleted some rows. The point is, if you delete the highest value, next time you add something it will take that value, and that's not unique is it? For example, you have an id values like this:

    1
    2
    3

    You then delete 3 so you get

    1
    2

    Now if you use your code you'll get 3 as the next id value and you already had that value before. So it's not unique.
    If you use my code you'll get 4 cos that's the next unique value that MySQL uses for it's auto-increment option. Four is unique because you NEVER had that id value before in your table.
    Last edited: Feb 15, 2010
  5. scc123

    scc123 New Member

    Eh true but thats what I used =/
    Atleast now I have something that works now :D
    Thanks.
  6. dragonvirus23

    dragonvirus23 New Member

    thank you so much
  7. horion

    horion New Member

Share This Page