Getting unused numbers

[Dabrowski]

Have a friend who's working on the public library's database, and was wondering if there is a query he could run in SQL that would tell him what ids are unused. It should be fairly irrelevent information, but he says that they want to go back and fill in the unused spaces for some reason.

Is it possible?

[YvetteKuhns]

http://www.webmasterworld.com/forum88/7634.htm
Unused Variables Causing Errors in CMS Script - code posted for similar situation

Question: Are the ids autoincremented?

[mjp]

You might try to explain that it's not necessary to "fill in the unused spaces" in a MySQL database. A lot of people are concerned with how their data is stored in the db (because people who use databases are typically 'organizers,' and a lack of order in the db rubs them the wrong way), but where the data is in the db is irrelevant.

Anyway, that should be a simple query, but how to do it depends on how they determine who is active. If they have some kind of "last access date" you could use that, and delete any accounts that haven't been accessed in over xxx days.

[mitchind]

I agree, mjp.

You enter into dangerous grounds when you try to manage something yourself that the database thinks it is managing.

[YvetteKuhns]

Anyway, that should be a simple query, but how to do it depends on how they determine who is active. If they have some kind of "last access date" you could use that, and delete any accounts that haven't been accessed in over xxx days.

We were using the "last visit date" for members on the travel database, but when PowWeb refactored mysql and just last month those dates went crazy. (Alot of the admin settings went back to the default, not unlike our email settings.) That would make me think that you can simply create a list of old accounts and manually delete those you wish to delete rather than have them automatically deleted.

[Dabrowski]

I agree. The principle is not worth explaining, I was just wondering if it could be done with an SQL query.

Yes, they are auto-incremented, but I don't think it has anything to do with last visit dates since these are book entries.

[YvetteKuhns]

wondering if there is a query he could run in SQL that would tell him what ids are unused
Yes, they are auto-incremented
If the auto-increment is for the ids, then there are no empty ids. The other columns in the row may be empty. You could search each row by id from 1 to the last id in the database. If the other fields are empty, then maybe you can make that id available when adding a new person or thing with that id. Still, it is unnecessary.

[mitchind]

You can have gaps if records get deleted. They gaps in auto-increment ids don't get filled automatically.

[YvetteKuhns]

You can have gaps if records get deleted. They gaps in auto-increment ids don't get filled automatically.

I surely know this. At least Leona isn't worried about filling in the gaps. :D