SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select RANDOM in MySQL 3.22

    Hello,

    From a previous thread, I've found my problem to be that the version of MySQL I'm working with doesn't support rand().

    My question is, how can I select random rows in MySQL 3.22?

    I'm trying to:

    PHP Code:
    SELECT FROM table WHERE featured='yes' ORDER BY rand() LIMIT '6' 
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    New Jersey
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Try this

    If you are using PHP along with Mysql to retreive the results... try this

    PHP Code:

    ...

    $total_rows mysql_num_rows(...) - 1;

    $random_row_number rand (0$total_rows);

    $sql"Select * from table_name LIMIT $random_row_number, 1";

    $result mysql_query(...);

    ... 

  3. #3
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using PHP along with MySQL.

    One question...

    How does this allow me to only pull 6 results at a time? I need a LIMIT of 6 and those 6 to be pulled randomly.
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    New Jersey
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    6 times

    Just use a for loop and call the code i wrote 6 times.

  5. #5
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's an entry in the php manual that may help...
    Hi, here's a nice little trick to select records in random order from a table in a MySQL database prior to version 3.23

    SELECT *, (ItemID/ItemID)*RAND() AS MyRandom FROM Items ORDER BY MyRandom
    Lats...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by KDesigns
    ...the version of MySQL I'm working with doesn't support rand().
    the mysql docs say that rand() was introduced in version 3.20.6
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I meant in the ORDER BY section. It doesn't support ORDER BY rand(). Sorry about the confusion, should have been a bit clearer!
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  8. #8
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's an entry in the php manual that may help...

    Quote:

    Hi, here's a nice little trick to select records in random order from a table in a MySQL database prior to version 3.23

    SELECT *, (ItemID/ItemID)*RAND() AS MyRandom FROM Items ORDER BY MyRandom
    Works great! Thanks!
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!


Bookmarks

Posting Permissions

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