for and while looping together

[Anon]

I have a db (MySql) That list my music if I list in a order from 1 to 20 for music I have avaiable if the first three music cd are move from the db I want to re order the list

ie
1 - 20 will become 4 - 20

I want to reset it to 1 - 17

This is what I got so far
<?

for ($i = 1; $i <= 20; $i++) {
while($num = $i) {

$sql_bin = "UPDATE music SET
m_order = '$num' WHERE user_id = 1";
$bin_result = mysql_query($sql_bin);
}
}
?>

TIA

[amcgrath]

Correct me if im wrong, but if you take out an entry (no matter where between 1-20) then the others will naturally fill in behind, without having to change the order number.... (4 will be first, for example, since it is the lowest number in the order column...)

[amcgrath]

Your query, btw, will just continually update every entry for user each time.... its wrong.

[[deleted]]

Why do you store the order of the records in the database?

[[deleted]]

lol

Allen, don't you think that is the reason why he posts it here? to get help to fix it so it does work? :-)

[Anon]

I have it so that the user can list the order of the music they want buy favorite so when they view there favorites it listed in order if they remove lets say the 5th one I want it to adjust the table to reset the order

[amcgrath]

Yea, I know, and at somes it is kinda aggravating, but hell, i was there one day too, and god knows I've stared at a simple problem thinking there was no solution.

[amcgrath]

Again, Richie, refer to my first post: if you remove say "5" then the order would be 1,2,3,4,6,7..... which gives you the same result.

[Anon]

I know that the result is the same but by having the hole in the order makes some of the other scripts not work correctly so if I can reorder the list that would be easier then rewriting the other scripts.

[[deleted]]

What you need to understand is that ther IS no order. The sequence of numbers should be created after you request the records.

First you do the select,
then you parse the results and give your results a number.

Do not store this number in the database, because there is no point to it. :-)

[amcgrath]

Totally agree Vince, but if he wants to do it stupidly, here ya go Richie, buddie:

update order set order = order - 1 where order > $removed_order_number

Assuming that $removed_order_number is the number you deleted....

[[deleted]]

No nah, uh oh... it seems I have been reading with my nose again...
A few posts back, Richie wrote this:

"I have it so that the user can list the order of the music they want buy favorite so when they view there favorites it listed in order if they remove lets say the 5th one I want it to adjust the table to reset the order"

That certainly warrants the use of a seperate ordering field.

Sorry Richie!

Allen's solutionis still valid though;

UPDATE table SET order_column = order_column - 1 WHERE order_column > $removed_order_number