Jump to content

Ordering lists/results


salasis

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!.
Link to comment
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]
Link to comment
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.
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.