Page 1 of 1

Random Quote Generator

Posted: Wed Jun 24, 2009 1:46 pm
by pftodd
Hi,
as part of a CMS I'm trying to make for myself, I want to build a random quote generator where, at the same time, I can add/edit/delete quotes from a database.

To display my quotes I'm using the following code:

Code: Select all

<table width="525" border="0" cellspacing="2px" cellpadding="2px">
<?php
include ("../library/config.php");
include ("../library/opendb.php");
    $query = "SELECT * FROM `pft_quotes` ORDER BY id ASC";
    $result = mysql_query($query)
    or die("Error in query: $query . " . mysql_error());
                            
    if(mysql_num_rows($result) > 0)
        {
    while($row = mysql_fetch_object($result))
        {
?>
                                
                                
    <tr>
     <td width="25px" align="center" valign="top"><?php echo $row->id; ?></td>
    <td width="600px" valign="top">
        <div class="quote_content">
        <?php echo $row->quote; ?>
        </div>
        <div class="quote_source">
        <?php echo $row->source; ?>
        </div>
        <br />
    </td>
    </tr>
                                
<?php
    }
                            
    }
    else
    {
        echo 'Sorry, there currently are no quotes to display. ';
    }
?>
 
</table>
Is it possible for me to combine this code with a script like this one to produce a random quote?
If not, how would I go about producing a random quote from my above code?

Thanks very much,
Patrick

Re: Random Quote Generator

Posted: Wed Jun 24, 2009 2:14 pm
by Christopher

Code: Select all

    $random_number = rand(1, $num_records_in_table);
    $query = "SELECT * FROM `pft_quotes` LIMIT 1 OFFSET $random_number";
Then you don't need the while() loop.

Re: Random Quote Generator

Posted: Wed Jun 24, 2009 2:20 pm
by Eric!
I think 0 is a valid row number too. But someone PLEASE correct me if I'm wrong. This would change the rand function slightly

Code: Select all

$random_number = rand(0, $num_records_in_table-1);
$query = "SELECT * FROM `pft_quotes` LIMIT 1 OFFSET $random_number";

Re: Random Quote Generator

Posted: Wed Jun 24, 2009 2:33 pm
by pftodd
Hi, and thanks for the quick response.

My code is now:

Code: Select all

 
<?php
    include ("../library/config.php");
    include ("../library/opendb.php");
                                
    $random_number = rand(1, $num_records_in_table);
    $query = "SELECT * FROM `pft_quotes` LIMIT 1 OFFSET $random_number";
    $result = mysql_query($query)
    or die("Error in query: $query . " . mysql_error());
                            
    if(mysql_num_rows($result) > 0)
        {
        while($row = mysql_fetch_object($result))
            {
?>
                                
    <div class="quote_content">
        <?php echo $row->quote; ?>
    </div>
    <div class="quote_source">
        <?php echo $row->source; ?>
    </div>
<?php
            }
                            
        }
        else
        {
        echo 'Sorry, there currently are no quotes to display. ';
        }
?>
 
When the page loads, it seems to only be loading my quote/source with id '2'. I've tried it many times, so unless id '2' is being randomly selected every time (there are 12 quotes in the table) I think I may have made another mistake somewhere. What do I need so edit so that a random quote is displayed on each load of the page?

Thanks again,
Patrick

Re: Random Quote Generator

Posted: Wed Jun 24, 2009 2:39 pm
by Eric!
What are you setting $num_records_in_table to?

Code: Select all

$result = mysql_query("SELECT * FROM table1");
$num_records_in_table = mysql_num_rows($result);
Did you see my note about the RAND function above, can you check that your current code will find the first and last quote?

EDIT: changed to match posted code's variables (except the table1 name)...too lazy

Re: Random Quote Generator

Posted: Wed Jun 24, 2009 2:59 pm
by pftodd
Thanks for all your help. It's working now and my code is:

Code: Select all

 
<?php
                                include ("../library/config.php");
                                include ("../library/opendb.php");
                            $query1 = "SELECT * FROM `pft_quotes` ORDER BY id ASC";
                            $result1 = mysql_query($query1);
                            $num_records_in_table = mysql_num_rows($result1);
                            $random_number = rand(0, $num_records_in_table-1);
                            $query = "SELECT * FROM `pft_quotes` LIMIT 1 OFFSET $random_number";
                            $result = mysql_query($query)
                            or die("Error in query: $query . " . mysql_error());
                            
                            if(mysql_num_rows($result) > 0)
                            {
                                while($row = mysql_fetch_object($result))
                                {
                                ?>
                                
                                <div class="quote_content">
                                <?php echo $row->quote; ?>
                                </div>
                                <div class="quote_source">
                                <?php echo $row->source; ?>
                                </div>
                                <?php
                                }
                            
                            }
                            else
                            {
                                echo 'Sorry, there currently are no quotes to display. ';
                            }
                            ?>
This is probably very messy, but at least it works. :)

Thanks again,
Patrick.

Re: Random Quote Generator

Posted: Wed Jun 24, 2009 3:02 pm
by pickle
I'm pretty sure this can be done entirely in SQL (untested, but I've seen this before)

Code: Select all

SELECT
  *
FROM
  `pft_quotes`
ORDER BY
  RAND()
LIMIT
  1

Re: Random Quote Generator

Posted: Wed Jun 24, 2009 4:37 pm
by Eric!
Yeah that works too. I saw that done here along with a faster way to do it with big tables

http://www.greggdev.com/web/articles.php?id=6

I always thought mysql's rand() was for floating points, but I read closer in the manual and found it behaves differently in a WHERE clause:

RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. However, you can retrieve rows in random order like this:

mysql> SELECT * FROM tbl_name ORDER BY RAND();
ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows:

mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d -> ORDER BY RAND() LIMIT 1000;
RAND() is not meant to be a perfect random generator, but instead is a fast way to generate ad hoc random numbers which is portable between platforms for the same MySQL version.
from http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html