Resort Record ID's

[johnalpi]

Hi all heres my first post:)

I have a db with news items in there. there is a id, date, title and body.
I have a first page with date(a link) and title. the link links to the news item with next and prev buttons for news items. the problem comes when a record is deleted, say #44 of 50 news items, so when a user click next from news item 43, there isnt a item ith the id #44, so it boots you bact to the news listings, but there are other news items. lol, am I making any sense!

So what i am need(and have no idea how to do) is resort items when one is deleted, say move #45 to #44 etc to fill in the gaps.

Thanks for any help,
John.

[Netizen]

Welcome to WebMasterWorld, johnalpi.

I think the best solution for yourself and the user of the site is to work out what the actual previous and next id's are rather than reassigning id's when you delete an article. If you reassign id's then if someone has bookmarked that news item previously then the bookmark will take them to a completely different news article.

To work out the prev and next id's you could do

prev: mysql_query("SELECT id FROM mytable WHERE id < $currentID ORDER by id DESC LIMIT 1");

and

next: mysql_query("SELECT id FROM mytable WHERE id > $currentID ORDER BY id ASC LIMIT 1");

I hope that helps.

[Obviously the code is missing the get the result from the query result - keeping example simple!]

[timster]

I think the best solution for yourself and the user of the site is to work out what the actual previous and next id's are rather than reassigning id's when you delete an article.

That's true for a lot of reasons. Here are two:

If you add functionality to the site, for instance letting users comment on articles, you'll use the id to link the comment to the article. If you change those ID's, the comments could "get lost."

Depending on your db, changing primary key values on the fly can mess up a full-text index, if you have one.

Short answer: Leave the primary key field alone whenever possible.

BTW, are you using MySQL, or some other database? (Netizen's suggestion is good, but the syntax is MySQL-specific.)

[johnalpi]

I'm using MySQL/phpMyAdmin
I will give this a try, see how I get on. Whatever I find works, I'll post the solution.
It wont be today mind, im going out on the town;)

John.

[johnalpi]

Just to clear things up.
prev: mysql_query("SELECT id FROM mytable WHERE id < $currentID ORDER by id DESC LIMIT 1");

would give me (if the current id is 10) id=9. that is how im doing it at the moment. getting the current id, taking one away from it to give the link for the back button, adding for the forward button. The problem comes when the news item is 10, and the next button is linking to 11, which was actually deleted. then another part of my script,

$thisID = $_GET['id'];
if(!$thisID){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=news.php\">";
exit();
}else{
get on with it.

so if there is a gap between the news id 10 to 15 say, the user gets booted back to the news listings.

So i understand that I have to leave the db alone, for the reasons given above(thanks:)) but im still stuck with this problem of getting from news iten 10 to 15 without being booted back to th news listings.
Any suggestions?

[johnalpi]

and then the penny dropped;)
thanks for your help guys.

[timster]

im still stuck with this problem of getting from news iten 10 to 15 without being booted back to th news listings.

Would this do?

next: mysql_query("SELECT id FROM mytable WHERE id > '$currentID' ORDER by id ASC LIMIT 1");

[johnalpi]

That works better than the code above! how did you do it;)