SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jan 2007
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow Random Selection

    In my database I have a hundred or so items layed out like this,
    Code:
    id   name   shown
    1   Pez   yes
    2   Giles  no
    3   Bob   yes
    etc...
    I am trying to randomly select one of the entries where shown is yes.
    What is an easy way to do this? I have to be able to expand the database and it still work.

    So someone clicks give me a name and the php randomly choses a name from the database but only out of ones that shown is == to yes.
    However users will be able to add their name to the database, then a admin will alow the name to be shown (to prevent abuse) . And the new name will be added to the list of randomly choosable names.

    Thanks,

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    assuming your db is mysql, and your php > 4.2.0 (if not, you may need to seed the random number generator - see www.php.net/srand), try something like this:

    PHP Code:
    $res mysql_query("select name from mytable where shown='yes'");
    $max  mysql_num_rows($res) - 1;
    $choice rand(0$max);
    $selected_name mysql_result($res$choice"name");

    echo 
    $selected_name
    haven't tested it but should work... let me know how it goes!

    shai
    age 15

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT name FROM table ORDER BY RAND() LIMIT 1

  4. #4
    SitePoint Addict Php_penguin's Avatar
    Join Date
    Aug 2004
    Location
    Colwyn Bay, Wales, UK
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yet another person trying to make php do something mysql can do better?

    however, in this case it doesnt make much difference. the RAND() part means that mysql will effectively go each row and stop depending on a rand value, rather than generating a random ID between 0 and MAX(). There may well be a way to write a query along these lines. For example, do a MySQL query to find the MAX id (or num rows) and then do a php rand, reselect. This may be the most efficeint way of doing this.

    Might be a good plan to do a few tests based on this and post the results, if you have the time

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you have over 10,000 rows in the table and are worried about performance, here's a faster all-SQL method:

    http://jan.kneschke.de/projects/mysql/order-by-rand/

    But if the table is small, ORDER BY RAND() won't take a millisecond.

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi guys , you miss something here, that you don't delete any thing from the table , if you delete any thing then the random number may refrence a non existing row


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
  •