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

[farscapeone]

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

// 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/

 

[ChatIndia]

Thanks for this tutorial.

 

[scc123]

Eh thats handy, all this time I've been using:

$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

 

[farscapeone]

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:

$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.

 

[scc123]

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

 

[dragonvirus23]

thank you so much

 

[horion]

gracias