SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Not Bad, eh? Justin Sampson's Avatar
    Join Date
    Aug 2000
    Location
    N.S., Canada
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,
    I'm working on a banner rotator for my site but I can get everything working but the part that selects the banner

    Here's my Problem:

    I'm trying to count the rows in a table and get a random number between the number of rows and 0, here's what I have:

    PHP Code:
    $query "select count(*) from ads";
    $result mysql_query($query);
    $row mysql_fetch_row($result);
    $count $row[0];
    echo 
    "Total Ads: $count<p>";
    $id mt_rand(1$count);
    echo 
    "Random Num:$id"
    Insted of getting a random number it just echo's the same number as the total. What am I doing wrong?

    Is there way to select a random row all in one query(with the one above I'd have to make another query to get the banner)?

    Thanks,
    Justin Sampson

  2. #2
    SitePoint Wizard
    Join Date
    Apr 2000
    Posts
    1,483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try putting this line:
    Code:
    srand((double)microtime()*1000000);
    Above your line with mt_rand

  3. #3
    Not Bad, eh? Justin Sampson's Avatar
    Join Date
    Aug 2000
    Location
    N.S., Canada
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks James

    Actually I figured out how to do it all in one step:

    SELECT id, image FROM ads where type = '480x60' ORDER BY RAND() LIMIT 1

    It selects a random row from the table all in one step.

  4. #4
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ooh - nice bit of SQL. I've been looking for a way to get a random item out of a database for ages.

    The problem with your original method is that it selects a random ID then pulls that out of the database. Now imagine a database that looks like this:
    Code:
    ID     Name
    ========
    1      eBay
    2      Yahoo
    3      gameplay
    4      IMDB
    If we delete gameplay from the above list, we are left with a "hole" in the table. If your random number is '3' no resut would be returned.

  5. #5
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ORDER BY RAND() LIMIT 1

    Groovy Justin. I learn a new thing here every day!

  6. #6
    Not Bad, eh? Justin Sampson's Avatar
    Join Date
    Aug 2000
    Location
    N.S., Canada
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Skunk
    Ooh - nice bit of SQL. I've been looking for a way to get a random item out of a database for ages.

    The problem with your original method is that it selects a random ID then pulls that out of the database. Now imagine a database that looks like this:
    Code:
    ID     Name
    ========
    1      eBay
    2      Yahoo
    3      gameplay
    4      IMDB
    If we delete gameplay from the above list, we are left with a "hole" in the table. If your random number is '3' no resut would be returned.
    I noticed that to, so I did this. It might not be the most efficiant way but it works:
    PHP Code:
    <? 
    //check to make sure it wasnt looping more then 20 times
    while ($array[id] == "" && $tri <= "20")
    {
    //get the random banner
    $query mysql_query("SELECT id, views, image, expires FROM ads where type = '480x60' ORDER BY RAND() LIMIT 1");
    $array mysql_fetch_array($query);
    $ad "<a href=\"ad/log.php?id=".$array[id]."\"><img src=\"".$array[image]."\" border=\"0\"></a>";
    mysql_query("UPDATE ads SET views=views+1 where id = '$array[id]'");
    $tri++;
    }
    //after looping 20 times just serve the default one
    if ($tri >= "20"
    {
    $ad "<a href=\"$PHP_SELF\"><img src=\"images/banner.gif\" border=\"0\"></a>"

    ?>


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
  •