...

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



thindrakhya
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

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

thindrakhya
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

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

Dan

thindrakhya
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

whizard
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.

;)

Dan

thindrakhya
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

whizard
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.

Dan

timgolding
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

timgolding
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.

CFMaBiSmAd
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...

timgolding
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum