SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: random record

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,

    I was wondering if it's possible to select a random record from a table in a mysql database.

    i've read the php.net manual, and below is the code that they suggested:
    Code:
    SELECT * FROM table ORDER BY RAND() LIMIT 0,1
    but it doesn't seem to work.

    can somebody help?

    Thank you
    Sang N.
    -------------------------------
    Send an E-card today!
    http://ecard.vinasite.com

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    ORDER BY RAND() works only as of MySQL 3.23.3. Paul DuBois' book, MySQL, provides an alternate method that works on older versions of MySQL.

    What you need to do is SELECT a column of random numbers and then sort on that column. However, the following query will not quite work:

    SELECT ..., RAND() AS rand_col FROM ... ORDER BY rand_col LIMIT 1

    The MySQL optimizing engine sees this query and says "oh, rand_col is just the result of the same function in every row, so I'll just run RAND() once and treat rand_col as if every row contained the constant result of that function." This effectively optimizes the ORDER BY clause right out of the query!

    To fool the optimizer into running the RAND() function to get a different random number for each row, refer to another column in the table in the expression. For example, if you have an ID column in your table, you can do this:

    SELECT ..., ID*0+RAND() AS rand_col FROM ... ORDER BY rand_col LIMIT 1

    Ta da!

    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it seems the same thing with length() ???
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Depends how you're using it. Can you provide an example?
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,

    so what would my sql statement be if i have a table with 2 columns, ID , and Email, and there's 11 records?

    forgive me for asking too much since i've just started with this!

    Sang N.
    -------------------------------
    Send an E-card today!
    http://ecard.vinasite.com

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    SELECT *, ID*0+RAND() AS rand_col FROM your_table_name ORDER BY rand_col LIMIT 1
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is rand_col a variable?
    Sang N.
    -------------------------------
    Send an E-card today!
    http://ecard.vinasite.com

  8. #8
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Nope, rand_col is a temporary column name (known as an alias). You could actually name this anything you wanted, as long as the two occurrences in the query match.

    For more information about aliases, see Part Nine of my article series:

    http://www.webmasterbase.com/article...d=228&pid=1066
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

    I got it to work!

    by the way, is there a limit to tables in a database?
    Sang N.
    -------------------------------
    Send an E-card today!
    http://ecard.vinasite.com

  10. #10
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Each table is represented by a file on your hard disk (three files, actually). As long as your file system can accomodate more files, MySQL can accomodate more tables.

    You should never need an unbounded number of tables, though.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  11. #11
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot!
    Sang N.
    -------------------------------
    Send an E-card today!
    http://ecard.vinasite.com


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
  •