Getting MySQL Auto Increment
Tue Jan 12 14:52:00 2010
If you have ever written a program that connects to a database using PHP, then there may have been times when you wanted to find the next available Auto Increment value in an MySQL database. The code below will allow you to do just that and it's very simple to use.
$row = mysql_fetch_assoc(mysql_query("SHOW TABLE STATUS LIKE 'votes'"));
echo $increment = $row['Auto_increment'];
Here's how it works. On the first line, we query the database and store the result in the variable $row. You could write this so that you store the query string in a variable and then pass that variable into the mysql_fetch_assoc() function, which is probably more usual. However, I was looking for the minimum amount of code possible for a project that I'm currently working on, so that's the way I wrote it.
On the second line, we store the the value of 'Auto_increment' in the variable $increment and echo it back to see the results. Depending on the database you connect to, you will obviously get different results.
Information about the SHOW TABLE STATUS syntax used and more can be found here at the online SQL 5.1 reference where 'Auto_increment is also listed.
Tweet
