0

Google failed me, so I seek expert-help :)

Let's say I have a database consisting of "ID" and "Name". Currently in this database I have these records:

001 - John

002 - Eric

003 - Kathy

To generate the next ID i used COUNT in mysql and plussed 1 in php, but I obviously didn't take in account that users can be deleted. If 002 - Eric is deleted, the script will try to use the ID 003, which already exists.

Any way to check this? I figured a while loop that goes through the values, but if the database is long, this would probably take too much resources (and I'm not sure what to loop through)?

As of now, I have this:

SELECT COUNT(id) FROM users
$id = $thecount + 1;
dbso
  • 636
  • 1
  • 5
  • 14

1 Answers1

0

You could set the datatype of the your ID field to AUTOINCREMENT but if for whatever reason you're needing to do this in PHP, you're probably better off doing something like:

SELECT MAX(id) FROM users
$id = $thecount + 1;

Though I would recommend that you use the AUTOINCREMENT datatype instead.

LiamGu
  • 5,317
  • 12
  • 49
  • 68
  • AUTOINCREMENT does kinda work, but what if I want the ID 002 to be used again? – dbso Nov 08 '12 at 11:35
  • If it's a user table, or any table for that matter in a relational DB, I wouldn't recommend reusing ID values. I'd just use `AUTOINCREMENT`. – LiamGu Nov 08 '12 at 13:16