Logic problem with auto_increment

israfel

In my project I wanted to display random messages across the top of the screen. I decided the best way to do this would be to make a table with rows id and content and simple build forms for adding/deleting/editing these two rows.

First I do a SELECT COUNT to get the number of rows and then a rand with the results of the SELECT COUNT query and zero as it's arguments. Lastly I do a normal select-and-result query WHERE id='$therandomnumber.

The problem arises when I want to delete one of the messages. It causes a gap in the ID numbers and, if the random number picks an id that was deleted then my next query will fail because it has a nonexistant id.

My first workaround for this was to set set it up as a recursive function that would keep executing if the query returns nothing but then performance just decreases more and more as messages as deleted from the database and the function has execute more times to find a valid message.

An addition to this I also made it possible to edit the messages id from the control panel so one could fill in the gaps where ids are missing and this worked as expected.

The remaining problem is that, even when I edit quotes to fill in gaps the table resumes naming the ids where it left off instantly creating new gaps. Is there any way I can make mysql assign the auto_incrementing id based on the id of the previous row or would I have to take the auto_increment out of the id column and figure out some way to retrieve the latest id from the row already, increment that by one and feed it as the new id. The problem with that idea is that it appears to be what mysql already does with the auto_increment clause in the first place and it's a big pain in the ass for something that's probably obvious and right under my nose.

Any ideas?

tomkleijkers

If you want to fill up the gaps, and the order of the images doesn't matter (it will be picked random), the best way to fix the problem I think is to creat a script which finds the gaps and fills them up using the last id's. This script should be run each time a id is deleted.

lprocks

I've had a similar problem before and yes IT SUCKS, but if your going to go with Tom's idea then forget the whole auto_increment behavior, infact, your function (logically) should work without it and without any modifications... I wish there was a keyword you could use with auto_increment, I tried to research the topic but yielded nothing after like 3 hours... I hope there are some better approaches...

Good luck,

the_Igel

It's well known and not a bug but a feature 🙂
MySQL keeps track of last auto_increment id it set. So, the next one will be that number + 1, no matter what is the real biggest id value you have in the table at the moment. So there's no simple workaround.
AFAIK, once it was related with table type (ISAM or myISAM), and in the latest releases MySQL does it always that way.
Or maybe it's compile-time option or configuration variable.
You may check the MySQL manual, i think it is described in there.

Hope this helps a bit.

Weedpacket

The most straightforward workaround would be to not use the id, since that can end up with gaps.

Instead, generate the random number in the range [0 .. count-1] as at present, and use it as the offset in a LIMIT clause:

"SELECT ... FROM ... LIMIT 1,$random"

If you wanted n different selections at once it would have to be trickier: get all the current ids into an array, pick out n different elements from it, and build a "WHERE id IN ($comma-separated-list-of-picked-out-ids)" clause.