Ordering lists/results

[salasis]

Hi there,

It may seem a bit vaque but i would be REALLY grateful if you could help me in this.

I would like to be able to set the order in which results display:

e.g.

Result 1
Result 2
Result 3
Result 4
Result 5


I would like that so i could decide in which order it displays.
e.g so i could do this:

Result 4
Result 2
Result 5
Result 1
Result 3

So i know its basically just adding another field and caling it something like "order" and just updating it to what i want.

BUT my question is....

If the table stands as this:



[b]Name:[/b] Result 1 [b]Ord:[/b] 4
[b]Name:[/b] Result 2 [b]Ord:[/b] 3
[b]Name:[/b] Result 3 [b]Ord:[/b] 2
[b]Name:[/b] Result 4 [b]Ord:[/b] 1
[b]Name:[/b] Result 5 [b]Ord:[/b] 5

(So i would just use [i]SELECT * FROM `table` ORDER BY `Ord` DESC[/i])

But when i update it say, using a form. The values are gona conflict...(if that makes sence).

E.g

When i change [b]Result 3[/b] to have a [b]Ord[/b] value of [b]1[/b], Result 4, will already have a Ord value of 1.

So both need to change, but after 4 changes, another one will have to change etc etc....

Please help if you can, thankyou!.

[wickning1]

It depends on whether you move something up or down.

If you move it up, then everything from the new value to the old value need to shift +1. If you move down, things need to shift -1.

This can be done in three queries: (this is pseudocode)

[code]SELECT (`Ord` - newpos) as range FROM table WHERE name='nametomove'

IF range > 0 THEN
UPDATE table SET `Ord`=`Ord`+1 WHERE `Ord` BETWEEN newpos AND newpos+range
ELSE
UPDATE table SET `Ord`=`Ord`-1 WHERE `Ord` BETWEEN newpos+range AND newpos
END IF

UPDATE table SET `Ord`=newpos WHERE name='nametomove'[/code]

[salasis]

Thanks, but thats kinda confusing lol....

I found this on the internet:

[code]  function processMoviesOrder($key)
    {
        if (!isset($_POST[$key]) || !is_array($_POST[$key]))
            return;

        $movies = getMovies();
        $queries = array();
        $ranking = 1;

        foreach ($_POST[$key] as $movie_id) {
            if (!array_key_exists($movie_id, $movies))
                continue;

            $query = sprintf('update movies set ranking = %d where movie_id = %d',
                             $ranking,
                             $movie_id);

            mysql_query($query);
            $ranking++;
        }
    }[/code]

But its confusing as well lol.

[wickning1]

Ok, let's say you have 5 people in the database and you want to order them as you described. The database would start like this:
[code]Mark    1
John    2
Susan   3
Nick    4
Zach    5[/code]
Now let's say you want to move Nick to position 2:
[code]Mark    1
Nick    4
John    2
Susan   3
Zach    5[/code]
Mark and Zach still have the correct number, because they were not between 4 and 2, but everyone between 4 and 2 needs 1 added.
[code]Mark    1
Nick    5
John    3
Susan   4
Zach    5[/code]
Now we just set Nick to 2, like we intended:
[code]Mark    1
Nick    2
John    3
Susan   4
Zach    5[/code]
The code you use to do all this is what I posted earlier. Hopefully that will get you started.

[salasis]

Ahhh, i see, thankyou very much :D