dcsimg
A Flash Developer Resource Site

Results 1 to 5 of 5

Thread: best way to select a random row from a mysql table with php?

  1. #1
    Senior Member
    Join Date
    Mar 2000
    Posts
    584
    I wrote a quick script to pull a random e-card and display that on my main page. At this point, the card id's go from 1 to 30 and no numbers are skipped in between.

    So I can do:

    Code:
    mt_srand((double)microtime()*1000000);//seed random num generator 
    $sql=mysql_query("SELECT COUNT(card_id) FROM cards");//count number of rows
    $num_cards=mysql_result($sql, 0, 0);//pass the number of rows to $num_cards
    $rand_card=mt_rand(1, $num_cards);//pick a random number between 1 and the number of rows
    //select and set the values for the random card to display:
    $sql="SELECT * FROM cards WHERE card_id='$rand_card'";
    $sql_result=mysql_query($sql, $connection);
    However, I'd like to plan for the card_id's not all being in sequence as I'm sure I'll be deleting some and adding more.

    I was thinking of
    -selecting all the card_id's into a php array
    -getting the length of the array
    -generating a random number with the maximum value being the length of the card_id array
    -using that random number as the index element of the card_id array and using that value to pull the data from the db

    Something like:

    mt_srand((double)microtime()*1000000);
    $sql=mysql_query("SELECT card_id FROM cards");
    $sql_result=mysql_result($sql, $connection);//if there were 30 card_ids, $sql_result would be an array with 30 values
    $num_of_cards=count($sql_result);
    $rand_place_in_array=mt_rand(1, $num_cards);//
    $rand_card_id=$sql_result[$rand_place_in_array];//the random number is used to select a random element in the $sql_result array rather being a random card_id itself.
    $sql="SELECT * FROM cards WHERE card_id='$rand_card_id'";
    etc.

    The later seems like the best way, but I think I'm messing up here: $sql_result=mysql_result($sql);//

    Is that an array? If not, how can I get the card id's from mysql into a php array quickly and then use a random number to select a place in that array?

    I'll keep fiddling with it and it's a bit long, but if you see what I'm getting at, let me know your thoughts.

    [Edited by mbannonb on 05-31-2002 at 11:39 PM]

  2. #2
    Senior Member
    Join Date
    Mar 2000
    Posts
    584
    This is the best I could come up with:

    Code:
    mt_srand((double)microtime()*1000000); 
    $sql=mysql_query("SELECT COUNT(card_id) FROM cards"); 
    $num_cards=mysql_result($sql, 0,0);
    $rand_place_in_array=mt_rand(1, $num_cards);// 
    $sql=mysql_query("SELECT card_id FROM cards");
    $rand_card_id=mysql_result($sql, $rand_place_in_array,0);
    $sql="SELECT * FROM cards WHERE card_id='$rand_card_id'"; 
    $sql_result=mysql_query($sql, $connection) or die ("failed at $sql.");
    Is that the fastest way to do it?

  3. #3
    Now listen heah! Mac8myPC's Avatar
    Join Date
    Aug 2000
    Location
    Steve Job's Doorstep
    Posts
    695
    it seems you should be able to write an SQL statment that does all that.... i'm looking into how..

    i'm thinking along the lines of this

    grab number of records
    create random number
    put that random number in an sql satement with the LIMIT command..

    ie "SELECT * FROM table LIMIT 1, $randNum"

    does that make sense?? i think that means pull one record starting at our random number...

    now if there was an SQL command to generate that random number.... i'm googling for it...

  4. #4
    Now listen heah! Mac8myPC's Avatar
    Join Date
    Aug 2000
    Location
    Steve Job's Doorstep
    Posts
    695
    HA HA!

    select * from cards order by rand() limit 1;

    it doesn't get any easier than that...

  5. #5
    Senior Member
    Join Date
    Mar 2000
    Posts
    584
    Awesome!

    Thanks a bunch!

Posting Permissions

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




Click Here to Expand Forum to Full Width

HTML5 Development Center