How to get the Next Auto Increment number in Mysql

[Ultragames]

I think my last post there says it all. There are a few (only one decent) way to do this given through this thread, but the problem is weather you should or shouldn't do it at all. And in this case, you shouldn't.

[bustamelon]

Originally Posted by Fumigator

the author admits it can sometimes fail, but he doesn't care if it occasionally fails for his particular application

Hey y'all. I'm the author of the "solution" in the link, http://blog.jamiedoris.com/geek/560/. I found this thread in my referral logs.

To clarify for those that didn't read it, I **used to** use this method in my own personal CMS backend thingamajig, as the ONLY user, and ONLY to get a more or less arbitrary ID that was used in a content preview feature. This ID was used temporarily, then thrown out. It was never referenced in the database at all.

So, yes, it's sorta-kinda true that I 'admit it can sometimes fail' (thank you for so barbarously putting words in my mouth ) but it never actually failed in my particular application because I was the sole administrator/author. I took pains to explain this to the naysayers in the article comments, in spite of the fact that I make no claims to offer my snippets as safe or guaranteed in any way, and that I take it for granted (foolish though it may be) that people will RTFM before executing ganked scripts on important data.

That said, Fumigator and others are absolutely right -- this is NOT the way to get an ID that will be used in an important app as any kind of future lookup reference. Their suggestion of using last_insert_id() (or some facsimile) in combination with pending/confirmed flag is the proper and safe way, even if it does not actually get the next autoinc without an insert. I wrote that article 5 years ago when I was just a PHP padawan. That particular application is ancient history now, as is just about everything on that dusty old site. But thanks Nancy for standing your ground. You were right too -- the OP did not specify the intent, so this was a perfectly legit answer to the Q. Hope that helps put this "controversy" to bed .

[Fumigator]

Yay, so now please everyone let this thread die.

[idalatob]

use last_insert_id() and add 1.

[CFMaBiSmAd]

idalatob, firstly, Fumigator suggested to not continue adding comments to this thread.

Secondly, last_insert_id() is only available after an INSERT query has been executed in the same script.

However if you have inserted the record to establish the ID (which is the correct solution to getting a known ID and has already been stated here), there is no need to add 1 to it. Also, adding one to the last ID serves no useful purpose as concurrent visitors will make the number you come up with incorrect and unusable for any purpose except to display it (which has also been stated here.)

Could a moderator please close this thread (too bad we don't have a beating a dead horse smiley. )