Retrieve The Auto Increment Value Of A MySQL Table
I’m beefing up the “Tweet this” option in an event calendar CMS I’m working on for a friend, and I needed a quick way to determine the next Auto_increment value of my events table to provide a link to the event on the website. The query for this is totally easy:
SHOW TABLE STATUS LIKE 'events';This query returns all kinds of useful information about the given table, including the current Auto_increment value. Learn more about how I applied my newly learned trick inside!
I’m in the process of building a website for my buddy Andy Lentz who is taking the big plunge and doing music full time! One of the key features of his site is an event calendar that tells visitors when, where and with whom he is playing next. I’ve done a few working man’s band sites over the years and I decided to upgrade my usual “Post to Twitter” option in the CMS.
My usual M.O. automatically posts a tweet with the following text to twitter when you add a new event and select the “Post to Twitter” checkbox:
Just confirmed @[user name] at [venue name] on [event date]. More info: [http://event/detail/url]
When updating an event, the following text is used:
New details on the @[user name] show at [venue name] on [event date]. More info: [http://event/detail/url]
This is a quick and easy way to update your followers about event info, but it would be nice to have a little more control over the tweet text to add other user names, other info, or simply to keep the tweet below the 140 character maximum.
My beefed up “Post to Twitter” widget (or tweeter as I have bedubbed it) consists of a texrarea populated with the relevant tweet text under the checkbox when it is clicked. This gives you the opportunity to modify the tweet text before posting the form data.
The one caveat is that when posting a new event, the form doesn’t know what the event ID is going to be for the link to the event on the website because it hasn’t been generated yet. One’s first assumption might be to select the id value of the most recent entry and increment it by 1, but this can be sketchy. If the most recent event(s) was/were deleted, you would end up with the wrong value!
The best (albeit not perfect) solution is to find the current Auto_increment value of the table. This is the number that will be assigned to the id of the next inserted record of this table. This is the query that retrieves it:
SHOW TABLE STATUS LIKE 'events';This query returns various bits of useful information about the table in question. When using this in practice, you’ll want the value of the Auto_increment column. For example, in PHP you might do something like this:
$result = mysql_query("SHOW TABLE STATUS LIKE 'events'");
$values = mysql_fetch_array($result);
$auto_increment_value = $values['Auto_increment'];Obviously this solution isn’t perfect. If someone were to publish a new event between the time you load this screen and submit the form, your link would point to their new event instead. This isn’t a concern for a site like Andy’s that will be updated by a single user. If this were an issue, using a placeholder variable in the URL and then replacing the value with the actual ID once the form was submitted would be a better solution. But that is a topic for another article.