Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Sep 2006
    Posts
    92
    Thanks
    4
    Thanked 1 Time in 1 Post

    Display sql results in random order.

    Hello,

    I am not sure how to do the following task:

    I want to order by a certain collumns, but if there are rows that contain the same value in the collumn that I do the order by on then I would like to display the results differently everytime I do the query....

    For example:


    id lvl
    1 1
    2 2
    3 2

    Now if I do SELECT * FROM table ORDER by lvl DESC the result will always be:

    id lvl
    2 2
    3 2
    1 1

    I would like to see the result above change randomly to

    id lvl
    3 2
    2 2
    1 1

    and perhaps next time the query is executed the result is again

    id lvl
    2 2
    3 2
    1 1

    Any suggestions (I hope I made it clear enough)?

    I have posted this on the MySQL forum as well and with the help of the people there sort of got it to work by ordering on multiple collumns, but this is not 100% what I was looking for. Is there a way to add this random feature using php?

    Thanks,
    twomt.

  • #2
    Regular Coder
    Join Date
    Jun 2004
    Posts
    565
    Thanks
    0
    Thanked 18 Times in 18 Posts
    PHP Code:
    $result mysql_query('SELECT * FROM table ORDER by lvl DESC, id '.((mt_rand(01)) ? 'DESC' 'ASC'));
    // do something with $results 
    dumpfi
    "Failure is not an option. It comes bundled with the software."
    ....../)/)..(\__/).(\(\................../)_/)......
    .....(-.-).(='.'=).(-.-)................(o.O)...../<)
    ....(.).(.)("}_("}(.)(.)...............(.)_(.))Ż/.
    ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
    Little did the bunnies suspect that one of them was a psychotic mass murderer with a 6 ft. axe.

  • #3
    New Coder
    Join Date
    Sep 2006
    Posts
    92
    Thanks
    4
    Thanked 1 Time in 1 Post
    Quote Originally Posted by dumpfi View Post
    PHP Code:
    $result mysql_query('SELECT * FROM table ORDER by lvl DESC, id '.((mt_rand(01)) ? 'DESC' 'ASC'));
    // do something with $results 
    dumpfi
    Not really random then right? It'll either be sorting on the 2nd collumn in ASC or DESC if I understood your code right.

  • #4
    Regular Coder
    Join Date
    Jun 2004
    Posts
    565
    Thanks
    0
    Thanked 18 Times in 18 Posts
    Well, yes. It does sort randomly in ascending or descending order.

    If a fully random order is needed, this is probably better:
    PHP Code:
    $result mysql_query('SELECT * FROM table ORDER by lvl DESC, RAND()'); 
    dumpfi
    "Failure is not an option. It comes bundled with the software."
    ....../)/)..(\__/).(\(\................../)_/)......
    .....(-.-).(='.'=).(-.-)................(o.O)...../<)
    ....(.).(.)("}_("}(.)(.)...............(.)_(.))Ż/.
    ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
    Little did the bunnies suspect that one of them was a psychotic mass murderer with a 6 ft. axe.

  • #5
    New Coder
    Join Date
    Sep 2006
    Posts
    92
    Thanks
    4
    Thanked 1 Time in 1 Post
    PERFECT !!

    thanks mate.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •