Jump to content


Photo

Ordering lists/results


  • Please log in to reply
4 replies to this topic

#1 salasis

salasis
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 23 March 2006 - 07:49 PM

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:



Name: Result 1 Ord: 4
Name: Result 2 Ord: 3
Name: Result 3 Ord: 2
Name: Result 4 Ord: 1
Name: Result 5 Ord: 5

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

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

E.g

When i change Result 3 to have a Ord value of 1, 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!.

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 23 March 2006 - 08:16 PM

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)

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'


#3 salasis

salasis
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 23 March 2006 - 08:45 PM

Thanks, but thats kinda confusing lol....

I found this on the internet:

  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++;
        }
    }

But its confusing as well lol.

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 24 March 2006 - 03:24 AM

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:
Mark    1
John    2
Susan   3
Nick    4
Zach    5
Now let's say you want to move Nick to position 2:
Mark    1
Nick    4
John    2
Susan   3
Zach    5
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.
Mark    1
Nick    5
John    3
Susan   4
Zach    5
Now we just set Nick to 2, like we intended:
Mark    1
Nick    2
John    3
Susan   4
Zach    5
The code you use to do all this is what I posted earlier. Hopefully that will get you started.

#5 salasis

salasis
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 24 March 2006 - 12:29 PM

Ahhh, i see, thankyou very much :D




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users