Resequence top 10 after deleting rows

[jilltwfl]

I have a table that has a key, a display_order, and news_headline. I have a php page in a content management form that the business owner uses to add, delete, change & select the top 10 items to display in a news pull-out box on the website. They are in no particular order not date, alphabetic, just arbitrarily chosen by the business owner. My question is, is the a way with just MySQL to update the display_order numbers if the owner deletes rows for display_order 3 and, say 7? I want to renumber all remaining rows to be 1 through n, with n being however many rows are in the table. Can I do this with a MySQL command or do I need to read the entire table into the php script & do an "Update" for each row with the new number from a php variable?

[clown[NOR]]

maybe .. just maybe (remember, i'ma mysql noob) this will work..

 

$query = "SELECT * FROM contacts";
$result = mysql_query($query);
$num = mysql_num_rows($result);
$num = $num+1;
mysql_query("INSERT INTO yourtable (id) VALUES ('$num')");

 

you can try it, but.. i have absolutely NO idea if it will work properly

[fenway]

I'm confused... if they're in no order, why do you want to renumber them?

[jilltwfl]

The display order is set by the business owner to show the "important" items at the top of the display list (kind of like a "top 10"), as links to other pages. They are displayed in the order selected by the business owner, not in date, alphabetic, or first-in/first-out, etc. order. So, if there are 15 items on the list, the business owner can re-arrange them to display whatever they deem as "important" at the top of the list.

[MadTechie]

??? erm.. ok

 

why don't use just use 'Order By' deleting rows will have no effect then!!

 

maybe some code will help..

[gluck]

Before deleting he rows you will need to change the display sequence of the other items. One way is updating all rows after or before the delete. Another way could be assigning an auto generated display order to every item when it is created. The benefit of that is that whenever you delete a row you wouldn't need to rearrange the rest of rows as the other rows would always contain a auto generated sequence display number.

[fenway]

The display order is set by the business owner to show the "important" items at the top of the display list (kind of like a "top 10"), as links to other pages. They are displayed in the order selected by the business owner, not in date, alphabetic, or first-in/first-out, etc. order. So, if there are 15 items on the list, the business owner can re-arrange them to display whatever they deem as "important" at the top of the list.

And why don't you deal with the sequence at this stage?