Auto Increment Effect of Deleting Row

Anon

Haloo friends i need help please. Say it i have 2 fields in muysql database. there are "no" and "name". in "no" field i set auto increment. So when someone add to database it would increase 1, 2, 3...etc.
The problem is when i delete unused row. example :
no name
1 a
2 b

3 c

When i delete record no 2. it will display :
no name
1 a
3 c

How to solve that problem (in PHP) ,so it will display automatically :
no name
1 a
2 b

Thanks for helping me

Anon

Ok, I'm not sure if I am fully understanding what you want to do. If you want to reindex your table to assign the "deleted" id numbers to users then you will have to drop the sequence and recreate the sequence on the table. Either that or cook up some sort of script that would do that for you. Not sure.

If, however, all you want to do is DISPLY them with nice sequential numbers beside them, then just use the row number as a basis.

I am really confused by your last line of

no name
1 a
2 b

as you DELETED record 2 with name "b". You want to rename "c" to "b" and change the index from 2 to 1??? Sorry if I misunderstood.

Tim Frank

theDog

Resurrecting a very old post... I have the same question, i think.

Except i just want to replace the missing numbers and have a nice incremental ID column. IOW, as noted above, if i delete record no. 2, the table looks like this:

no name
1 a
3 c

I want to reindex the table (in PHP) so there are no missing numbers:

no name
1 a
2 c

Seems like there should be a way to do that. Or What about if a variable comes looking for ID #2, and there's no such thing, i set it to #3? Would that be easier?

Oh, wait, what if there's no #3 either... i need to reset the variable to the next increment that does exist. Sorry, i would have posted in the n00b forum but this was where my search brought me.

Thanks
🙂

ultraslacker

Autoincrement is for uniqueness of key values. If you want a sequence for returned rows, use a counter in your script.

dmxking512

theDog;10265550 wrote:
Resurrecting a very old post... I have the same question, i think.

Except i just want to replace the missing numbers and have a nice incremental ID column. IOW, as noted above, if i delete record no. 2, the table looks like this:

no name
1 a
3 c

I want to reindex the table (in PHP) so there are no missing numbers:

no name
1 a
2 c

Seems like there should be a way to do that. Or What about if a variable comes looking for ID #2, and there's no such thing, i set it to #3? Would that be easier?

Oh, wait, what if there's no #3 either... i need to reset the variable to the next increment that does exist. Sorry, i would have posted in the n00b forum but this was where my search brought me.

Thanks
🙂

So how excatly would you do this ? im fairly new to php and ive got a site setup that does just that

basicly it works as a slideshow using data from each row.

and the page automaticly redirects to say Slide.php?row=+1

so that it would work its way through the rows in the database

What do i need to modify on the insert code to get it to insert into what ever number is avalible ?

Thanks

bradgrafelman

You shouldn't expect AUTO_INCREMENT columns to always be sequential (or, rather, that the table will always contain sequential values for that column). That's not what it was designed for.

Instead, the situation described in the quoted text could easily be solved by constructing the query to look for the row whose ID is greater than or equal to (not just equal to) the current row's ID plus one. So if you're on ID #2, SELECT the row whose ID value is >= 3.