re-auto increment my id

[y2yang]

Can I re-auto increment my id so no gap would be in between any number?

 

say i have rows 1206 and 1208, but not 1207, and I want to re-number my id so no gap like 1207 is there.

 

I use a code that call the title of the next and previous row as my link, but since there is no title on 1207, there is no title and and I don't want that.

 

I use a + and - 1 code.

[fenway]

Don't use unique IDs as counters.

[y2yang]

What do you mean?

[premiso]

He means with an auto_increment field this is not possible. To do what you want you either have to just find gaps and fill them in or re-index your table each time an id is deleted. Re-indexing is hardly an option as it is intense and if your pages a linked by google, could cause some confusion (or being linked to by members).

 

Why is this a big deal? It is better to have a previous page or number that has been deleted to stay deleted, or do not delete and just add a column "active" to the table and set it inactive, so you have an audit trail of sorts. Either way, I would suggest to deal with it or make the column non-auto-increment and increment the value yourself in your script.

[y2yang]

I got it fixed by renaming my id manually, good thing it's only 1290 rows and i have to fix only 90 rows starting with row 1207. But I can see where this is going if I would to delete say row 100th and I have over 1000 row, that's about 900 rows to rename. Me being novice, there is most likely a better way to accomplish this.

 

The thing that bothered me is that on id 1291, my last row, the image that showed up was in row 1290 and not the image from row 1291, as if row 1291 doesn't exist at all. I set it so that if the url's id is anything higher than the last row id, that it would return the real last row instead. 

 

My problem is probable better explained in an essay so I'll just move on but please answer this one last question.

 

Does re-indexing my column id require it done manually or with a script?

 

On the side note, I could create a row 1207 and  put an image in the row 1207 that say "image no longer exist" so people linking to my pages would not get a different page when they visited the pages, so as to be consistence.

[premiso]

Re-indexing can be done with a script. But as said, if you are referencing an image and someone linked to that image you essentially make it so it may not be the original image that was linked to, thus can cause some problems for them. Unless you do not want people linking to the image, but yea.

 

Re-indexing a database for a few thousand rows can be alright, but if your database grows, the longer the process can and will take and to be consistent you would need to do it everytime an image is erased, which is a mass overhaul on the DB. I would suggest against it.

 

Regarding the side note, that is a decent idea. But instead of putting that row in the DB why not just have your database test for that, IE if no row is returned for that ID display the "no image" image. That is done quite often and I would say that is the right way to go!