View Full Version : php -- inserting the missing ID problem

07-28-2007, 06:50 AM
hi friends

I have tha database table the ID whic is the PK of the table
Now when the users adds something this i use MAX(ID) to insert the next ID automatically

But when sometimes the users deletes that quantity then thst IDs are missing and the ID always go on incrasing

What i want is is it possible to first inser those missing IDs and then if all the missing IDs are full then use MAX(ID) and isert data

07-28-2007, 06:51 AM
What kind of database are you using? If its a mysql database why not use auto_increment?

07-28-2007, 07:21 AM
i am using mysql databse
The auto_increment is also same as max(ID)
it does not fill the missing colums

like i have id 1 2,3,4,5
if i delete id = 3 and insert next record then with auto_increment the next record will be added with ID 6 not at 3

07-28-2007, 07:49 AM
That's how its supposed to work. Why do you need the IDs to be sequential?


07-28-2007, 08:07 AM
it looks good when the rows are squential otherwise the rows number will be very large after sometime
I am thinking of one solution tell me if thats ok or not

Will it be better that when i delete any rows i will store that ids in different table column and when i need to insert any data
the i will select the min(ID) from that column name and if there is no ID in that column then i will use select MAX(id) from the current table

Is this solution viable

07-28-2007, 08:11 AM
It works in theory, but I would advise you against any such plan. There's no reason that IDs need to be sequential; you'll eventually end up with large ID#'s anyhow, and its not a problem. No one sees the database, so no one will know what it looks like

My advice is don't worry about it - focus your energies on the actual coding of your site.



07-28-2007, 08:28 AM
Thanks for your help whizard

I want to ask one thing if you can guide

In the PHPforums database desgin when all the registered users posts new topic or send reply then do all the posts from the all users store in same table with fields something like

post_id , sender-name , .............

or the posts from different users are stored in differnt tables

I want to know because this way post_id coulmn will be very very bigger

07-28-2007, 08:31 AM
I don't know for sure, but I would imagine that all the posts are stored in one table... having a seperate table for each user would be dumb, in my opinion.


07-28-2007, 12:51 PM
Usually the reason the auto increment appends the record to the end of the table its to keep integrity. For instance if someone had an ID 12 then posted a comment before there account was deleted then what would happen if you filled the missing ID with a new instance. Then you would be saying the post was submitted by the wrong person. I usually always keep the data in the table even if i delete the users account then i would have a boolean field that i could set to 1 for active and 0 for inactive, so if a user leaves i can just set it to inactive

07-28-2007, 01:04 PM
it looks good when the rows are squential otherwise the rows number will be very large after sometime
I am thinking of one solution tell me if thats ok or not

The size of an auto number ID is relatively small in comparison to say a large varchar or a floating point variable.

07-28-2007, 05:26 PM
I'll expand a little on something timgolding has stated. In real applications, database records are not really deleted. They are just marked as being deleted/inactive and normal queries don't include them. Even for something like a Forum, "deleted" records are just marked as such and still exist so that they can be retrieved if needed or have reports run on them...

07-29-2007, 12:19 AM
Yes and even if you do decide it is OK to remove them then in most cases you should still use a unique ID