I came across an interesting problem today. Not something that most people will realise or care about but something which seemed to absorb a good hour of my life searching for a solution.

Here goes..

MySQL tables, like any other, have an auto increment value to determine the next in line when inserting new data. How do we preempt this data though? How do we get the next insert ID before we insert the data? I can almost hear you now reeling off the following code:

SELECT MAX(id)+1 FROM table1;

You are wrong… What happens if you have 100 rows in ‘table1′.. the next insert is 101 right? wrong. We don’t have enough data to make anything other than an educated (and usually correct) guess.

Let me explain… What happens if you inserted a row by accident. This is easily done when the programmer doesn’t use single use tokens on insert queries. You then have to delete that row. What then would the next insert value be? 102 is the answer.

There is a MySQL command to allow you to get a list of table information within which is the Auto Increment value as follows:

SHOW TABLE STATUS LIKE 'table1';

There are two downsides to using this method..

1. Your host might not allow you to use SHOW on your database meaning it would either result in an access denied or no recordset.
2. The data is not sortable or filterable. This means you need to use some PHP logic to get the actual Auto Increment value from the recordset.

It isn’t that bad actually getting the data out of the recordset using PHP. See the following example using WordPress database calls:

$sql = 'SHOW TABLE STATUS LIKE "wp_nmv_version"';
$status = $wpdb->get_row($sql);
echo $status->Auto_increment;

So if knowing the correct Auto Increment is vital to your system bare this article in mind before proceeding.

If anyone has any idea how to tidy this process up a bit to remove the PHP element from the filtering process then I would be glad to listen.