The REAL Auto Increment

August 13, 2009 | PHP, Time Savers, Tutorials, Wordpress | 0 comments

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.

A Donate Button!

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

CommentLuv badge

Stay in touch!

Page Builder Cloud

Page Builder Cloud

A truly universal template library for WordPress Page Builders.

Page Builder Recommendation

Elementor Banner

We are BIG fans of the Elementor page builder. Give it a look!

About this site and Sean Barton

Picture of Sean
Sean Barton is a Freelance PHP Website Developer in Crewe, Cheshire. He is a Wordpress and CMS/Framework specialist and Co-Founder of Page Builder Cloud.
This site was set up in 2008 as a tutorial and scripting resource for the PHP language and Wordpress.
Find out more about Sean on the About Me page or use the Hire Me page to get in touch. For more information about Sean's work take a look at the Portfolio

Our Services

  • Wordpress plugin/theme development
  • Divi specialist
  • Ecommerce (Woocommerce, WPSC, Shopify, Magento)
  • PSD to Wordpress theme conversion (Responsive)
  • Website design work (Banners, Logos, Full Site, etc)
  • Website analysis (security, usability, SEO)
  • API Integrations (InfusionSoft, SalesForce, Ontraport, Customer Thermometer, etc..)
  • Wordpress consultancy & expert advice
  • Crisis support
  • Website hosting

The main services offered are Wordpress based although we do a great deal of technical programming for bespoke systems. From troubleshooting, extending frameworks, finding bugs to writing them from scratch.

Find out more by looking through our past projects or get a quote.

Be the first to hear about new products/updates!

This is a mailing list for those people interested in being told when we release a new product (Divi plugin or Theme).

We shall also use this list to let you know about product updates and releases.

You have Successfully Subscribed!