Printable View
I have the ID field setup on my table as auto_increment, primary key, and unique. However, when I add new items to the database, the ID is not always the next available number. Like when I delete some records (say 4 through 6) instead of starting at 5, it starts at 7. How can I prevent this?
The way I'm doing it now is specifying the ID myself by getting the ID of the previous item.
:confused:
that's how autonumber works on most (if not all?) rdms...it NEVER uses the same number twice regardless of whether the number is in use or not
So should I just shut autonumber off and find out the next number through code? I just don't want huge gaps in my ID numbers, 1, 2, 8, 14, 15, 16, 22. You know?
you will get gaps even with your current method
Say you had records
1
2
3
4
5
then you deleted 2 and 3, you'd now have
1
4
5
when you insert a new record, the id will be 6 if you get the ID of the last record and add 1 still, the same as if you just let auto_increment take care of it.
If you only ever delete records at the end of the table, then your method would work so yes you should turn auto_increment off, but it won't make any difference if you delete records in the middle of the table.
Good point. But it would still make the gaps smaller. Right now, the next autoindex is 23, and there's only 11 records in the table.
Thanks for your help.
just go in and re-number the id number. if there is only a 11 then it should be pretty fast.
if you turn off autoincrement and get the number from the last id yourself, how is that going to make the gaps smaller if you are still deleting form the middle? sorry just curious.
I expect that database to grow in time.Quote:
Originally posted by scoutt
just go in and re-number the id number. if there is only a 11 then it should be pretty fast.
Because currently, the next autoindex is like 23, while my code to get the last id will give me 10. Add one, the next number is 11. See what I'm saying? The autoindex is already way up because of how many I've deleted.Quote:
Originally posted by scoutt
if you turn off autoincrement and get the number from the last id yourself, how is that going to make the gaps smaller if you are still deleting form the middle? sorry just curious.
I don't think it works that way. you will get conflicts on that number already being used. if you get total rows, say 11, then you add one to it, mysql is going to say that that id is already in use (duplicate). you will start having problems when you DB gets bigger. say you have 500 rows and you delete # 250 so now you have 499, but you have an id of 500. if you get the total rows then add one it will give it an id of 500. see it will give you an error since there is already an id. even if you used last_ID or whatever it is, it will give you the last one, order or not.
if I am way off base here let me know, you might have to explain to me like I'm a 3rd grader, :D
Ah, but I'm not using the number of rows, I'm doing this:
Or something like that.PHP Code:$q = "SELECT id FROM newsitems ORDER by id DESC LIMIT 1";
$id = mysql_fetch_array(mysql_query($q)) or die(mysql_error());
$id++;
So it's finding the last record in the table, getting it's ID and then incrementing it for the record I'm about to add.
Do you still think it will cause a problem later on?
that shouldn't no, but you are still not filing the wholes from the ones you delete. that is like an auto-increment isn't it?
No, it's not filling the holes (if you could think of a way to do that, that'd be awesome), but it is preventing a good portion of them. Like I said, the next autoindex is 23, my code will give 11. So it's like autoincrement, but smarter. It will use the numbers twice if the number no longer exists (only at the end of the number set).
But no. Unfortunatly, it wont fill holes.
if physically in your table you have id=23 then that code you showed will return id=24. how will it get 11
The next AutoIndex is 23. Meaning that if I let autoincrement decide the ID, it would put in 23. But since the ID of the last record in the table is 10, my code gets that, increments it, and gives the new record the ID 11. In this process, AutoIndex will increment to 24. Even though my code overrides it, it still increases.
I wish I could show you. It'd all make sense fast.
This is in phpMyAdmin. The next autoindex is at 18, but I only have 4 rows (the id of the last row happens to be 4 to, so this one doesn't have holes yet).
If I were to let autoincrement chose the ID, I would have 1, 2, 3, 4, 18...but with my code, I get 1, 2, 3, 4, 5.
http://www.vbforums.com/attachment.p...postid=1025592
no I see what you are saying. its funny because my test machine at home that has apache and php and mysql on it doesn't do that. if I have a last id of 10 and I delete it, the table will still insert #10, but on my site if I did that it would not increment the same.
so, if your table has an ID of 10, how is autoindex going to return 23? if the last id is 10 it should autoindex 11 not 23. if that is the last row in the table. but if you have anything higher than that higher up in the table it will return 23 or whatever.
1
3
23
5
even though the last id is 5 the next autoindex is 24. make sure you script doesn't do that as it will return 23 instead of 5
what version do you have? mine doesn't have that one and I have 2.2.4
if that is the case then mysql is remembering the last id that was in there but had been deleted. my site doesn't do that. if I deleted #4then the next one is #4 not 5. very strange
According to phpMyAdmin, I'm running MySQL 3.23.45
:) no the version of phpmyAdmin
Oh. I'm running phpMyAdmin 2.2.2
I'll contact my host and see if they can update it to 2.2.6 (since they have one setup for every account)
that won't matter. it is mysql that is messin gup. I was jsut curious as to where you got the screen shot from since it is not on my version phpMyAdmin.
It's in 2.2.6 as well. When you click on the table to view it's properties. On the right side.
well I wonder why they skipped some version and didn't put that in there.
I will have to download that version then. thanks
So do you think they way I'm going about it is best?
Without a loop, would there be anyway to tell where the first hole is?
1, 2, 3, _, 5
So I can insert a record where the blanks are?
as far as I know tha tis a mysql thing and you can't control where it goes. mysql says it goes where it goes I guess.
You're not understanding nor answering my questions. But thanks anyways.
I thougth I did. you can't insert data in a row that has been deleted or is not there. the way you are going, that is up to you. personally I would leave it at auto-increment. but to each his own I guess.
I have another site that has something like what you are doing. somebody can insert articles and it goes in the database. that person can have it set to 1 week up to 3 months. after that time it gets deleted on it's own. it checks everytime the page is loaded and if the dates match it gets deleted. now the first one can get deleted in a week and the last one will take a month. my point is that I don't get holes like that, and it is set to auto-increment.
if your database is going to get real big, then why consider deleteing them? how can it get big if you delete them? if it gets out of hand you can dump it and re number it then load it back. that will take all of 10 minutes. and the site will never know the difference.
that is my opinion and not to be considered your only option.
I don't know what you're talking about. Just nevermind.
I just installed 2.2.6 of phpMyAdmin and that picture you showed is not in 2.2.6. I clicked on properties of a table and it is no where to be found.Quote:
Originally posted by The Hobo
This is in phpMyAdmin. The next autoindex is at 18, but I only have 4 rows (the id of the last row happens to be 4 to, so this one doesn't have holes yet).
If I were to let autoincrement chose the ID, I would have 1, 2, 3, 4, 18...but with my code, I get 1, 2, 3, 4, 5.
http://www.vbforums.com/attachment.p...postid=1025592
that little ditty has been in every version of phpmyadmin since 2.2.3 I think, at least that is the first one I got
It's in the first section to the right of the indices under the details of all the fields in the table
Why not? The numbers aren't being used anymore. Seems to work perfectly fine to me. Just developing a method to find that missing number is what I can't do (without some kind of big loop)Quote:
you can't insert data in a row that has been deleted or is not there
The point is, that mine does.Quote:
my point is that I don't get holes like that, and it is set to auto-increment.
Because my site contains snippets and tutorials that users can submit. If a user submits some vulgar thing or something malicous, I need to delete it. Also if things become outdated, etc.Quote:
if your database is going to get real big, then why consider deleteing them? how can it get big if you delete them?
well, I can't find it in 2.2.4 and 2.2.6.Quote:
Originally posted by chrisjk
that little ditty has been in every version of phpmyadmin since 2.2.3 I think, at least that is the first one I got
It's in the first section to the right of the indices under the details of all the fields in the table
you have it so it inserts the next id in a deleted row? so if you haveQuote:
Why not? The numbers aren't being used anymore. Seems to work perfectly fine to me.
1
2
4
you can stick 3 in right after 2? you are a god. I don't know why I even bother.
What the hell's your problem? Instead of being an ass about it, why don't you explain why it's not possible?Quote:
Originally posted by scoutt
you have it so it inserts the next id in a deleted row? so if you have
1
2
4
you can stick 3 in right after 2? you are a god. I don't know why I even bother.
why don't you explain to me how you plan on inserting an id in a deleted row.
since I am an ass.
so if you have
1
2
3
5
how do you plan on how you inserting 4 right after 3
:rolleyes:
INSERT INTO tablename (id,other,stuff) VALUES (4,'something','something')
Now why don't you, as I already asked, explain to me why that isn't possible? Or do we need to go back and forth for a few more posts before you get around to telling me?
I'm not saying you're wrong. I'm asking you to tell me why it's not possible. Do you understand?
sure. how are you going to tell that there is no #4? you will have to hard code it and then even if you get it to go it will go to the end of all rows, not after 3. I will run a test on a table that is setup like this and see if it does. I'll get back to you in a few, or monday
That's what I was asking you. If there was a way to do it without looping through and finding the first hole. Maybe I could create a table that would store the next available ID for all the other tables. When an item is deleted, it will specify that as the next number. When one is added in it's place, it'll get the ID after the next. Sorry if this is too confusing.Quote:
Originally posted by scoutt
sure. how are you going to tell that there is no #4?
I know it'll still put it at the end, but then I wont have links like:
http://site.com/show.php?id=1
http://site.com/show.php?id=2
http://site.com/show.php?id=4
http://site.com/show.php?id=8
http://site.com/show.php?id=12
I guess it's not really a big deal, but I'm picky.
I see what you are saying and sorry if I thought different. right off hand I don't know of anyway to do what you want. not saying there might be way, I just don't know.
how do you plan on getting the correct row if you don't specify an id in the url? specifing and id is better then the articles name or something.
I meant that I don't want spaces between my numbers, 1, 2, 4, 8, 12, but 1, 2, 3, 5 instead. I'll give what I said a try:
If an item is deleted from the table, the deleted ID number is saved in another table so it can be used again. Once it is used, it will get the last id + 1, like I said above. That way I can always know what the ID should be.
If more than one item is deleted, I'll add it as well, so I'll have:
3,14,12
and once they are all used up, just go to the last id + 1.
It's hard to explain, but I think it'll work.
on that picture you put up hobo, a few post up, about phpMyAdmin. well I don't have it and I think I know why. I have 2.2.0 at home on my test machine, it is in that one, but also I have a column where it says how much space each table is taking up, well I can't get that to work on my site either, something to do with permissions. this is the screen shot of 2.2.6 and no table like that you show.
http://www.vbforums.com/attachment.p...postid=1030758
When I downloaded 2.2.6, it was right in that big white space you have. That's really weird. Why don't you have it? I could take a wider screen shot if you'd like.
I'm not sure. I have to install this myself as my isp doesn't have anything to do with it. I believe you that you have it as I have it on my test machine also. it is wierd, but like I said it might have something to do with permissions or something.