Okay, I know I'm all problems lately but here it goes with another problem that is probably simple but I can't find the answer to myself:
Quite simply I want a MySQL query than can search a column of a table and return the earliest unused integer. For example, I have five rows in a table as follows:
Code: Select all
+---+---------+
| 0 | item1 |
| 1 | item2 |
| 3 | item3 |
| 5 | item4 |
| 6 | item5 |
+---+---------+
Now, in this case I would query the first column (id) and in this case it should notice that '2' is unused and return it so it can be used. Once something has been added with the id value '2' the query can be run again this time finding that '4' is unused. When 4 is also used the query would find '7' as the next unused integer and so on. I'm not fussy about it finding '0' if the table is empty as simply getting PHP to look for an empty array returned by the query will do that just fine.
I have constructed a query but it does not properly work:
Code: Select all
SELECT a.shout_id
FROM site_shoutbox a, site_shoutbox b
WHERE (a.shout_id + 1) <> b.shout_id
ORDER BY shout_id ASC
LIMIT 1
This query in theory should look at the id in the shoutbox table, and add 1 to it then see if there is a row in the same table which already has that id, if so then try another, if not then return that result (and only that result).
This however does not work, I presume because the + 1 is not doing anything and the first time that a.shout_id is not equal to b.shout_id is when a.shout_id = 0 and b.shout_id = 1 so the query always returns 0.
Thanks,