[Resolved] Next in Database

[mberman]

Alright... I'm finishing up this project so I'll stop posting so much soon 😃

Anyways, I have a database containing auto_incremented player ID numbers (which are not in n+1 order, because some were/will be removed, leaving gaps in numbering). With the current ID in a variable, I need to fetch the next ID in the table 'players', and I have no idea how to do this.

Anybody have suggestions?

[dnszero]

NOTE: This should really be posted in the DB forum.

The following query will work, however the column names have been changed to protect the innocent:

SELECT auto_id FROM players WHERE auto_id > $someNumber ORDER BY auto_id ASC LIMIT 1;

BUT, do you really need to do this? If you're going to be working with more than one per page, just get all the results you need in one query to begin with instead of doing lots of small querries. If that's really what you need though, by all means use it, but when weirdness like this pops up, it's usually a good idea to take another look at your layout and the assumptions you've made.

[mberman]

My bad. I somehow missed the DB forum. Thanks for the tip. No, it's only one query per page. I have a page displaying the player's entry form, and the user clicks insert... My script inserts the data, and needs to retreive infomation for the next player on the list. There isn't a good way (that I know of), of implementing a system to do this without using auto_increment (the trick is that players are added and removed with regularity, and SQL doesn't jump back and use unused ID's).

Anyways, thanks a bunch, dnszero!