PHP Tutorial: Fetching random record from the Database

The following code illustrates how to fetch random record from the
Database using PHP

There are several tasks where you need to output multiple records
from the database in a random order. For example it can be the
banner rotation system or the “random quote” generator. There are
different programming techniques available and I’ll show you here
two most common ways to compelete this task.

This is how I built my script when I first time tried to make a
random line of text appear on the page:

…. // database connection code
$query = “SELECT * FROM QUOTES”;
$result = MYSQL_QUERY($query);
$randval = rand(0, mysql_num_rows($result) - 1);

$quotetext = mysql_result($result, $randval, 1);
$quoteauthor = mysql_result($result, $randval, 2);

print “$quotetext<br>$quoteauthor”;

Let me explain the code written above. First, we set a simple SQL
query to return all rows from the table QUOTES. Then we choose the
random value not greater than number of rows returned by the
database. In mysql_result() function we use random value as a row
position identifier and the third argument of this function is the
data field position (column).
Note a few disadvantages of this script:
1.) We should manually seed random values generator somewhere in the
code preceding these lines. By the way it should be done with srand
() function.
2.) We should store in mind what data field we need to read, I mean
the last argument in the mysql_result() function. And if the number
of columns in the table changes, we would have to rewrite the code.

Now let me continue and show you another code example that does
exactly the same thing but in more convenient way:

$query = “SELECT * FROM QUOTES ORDER BY RAND() LIMIT 1″;
$result = MYSQL_QUERY($query);
$row = mysql_fetch_array($result);
print($row[’Text’] . ‘<br>’ . $row[’Author’]);

In this example MySQL database generates random values for us and it
returns different values each time. With some simple modifications
we can output a set of records in a random order:

$query = “SELECT * FROM QUOTES ORDER BY RAND()”;
$result = MYSQL_QUERY($query);
while($row = mysql_fetch_array($result)){
print($row[’Text’] . ‘<br>’ . $row[’Author’] . ‘<hr>’);
}

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.