Jump to content

Archived

This topic is now archived and is closed to further replies.

salasis

Ordering lists/results

Recommended Posts

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!.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.