At one time or another, most of us find the need to display a random item from a batch of items on our Web site. Whether you are displaying a random banner or promotional item on your home page, or displaying a random post on your blog, you need to know how to do so effectively and efficiently. This article will show you two ways to do so.
First, you can use the RAND() function within MySQL. To do that, you would simply use something like:
SELECT * FROM table ORDER BY RAND() LIMIT 1;
That will return a single random result from your table. That’s the simple way to select a random item from your table. Using the LIMIT part of that query, you could actually select multiple items from your table in random order. For instance, if you wanted to shuffle a playlist, you could select your entire table and order it by RAND() to randomize the order of your results. You can, of course, add a WHERE clause to your query, as well.
The example above is purely SQL, so it will work with all MySQL tables and databases, no matter what language you’re using to interpret and format the results (PHP, Perl, VBScript, etc.). However, the RAND() function is not an optimal function in MySQL, and can become extremely slow depending on the number of rows in your table.
Therefore, you may want to generate the random number with the server-side interpreter language you’re using rather than doing so with MySQL. Then, you can use MySQL’s LIMIT clause to pull a single result. To do so with PHP, you would do something like:
$ct = 0;
$sql = "SELECT COUNT(*) FROM table";
if($sql = mysql_query($sql)) {
if($ct = mysql_fetch_row($sql)) {
$ct = $ct[0];
}
}
$r = rand(0,($ct-1));
$sql = "SELECT * FROM table LIMIT $r,1";
if($sql = mysql_query($sql)) {
while($rs = mysql_fetch_assoc($sql)) {
/* Do whatever you need to do with your result */
}
}The example above does three things. First, it gets the total number of rows in your table. If you want to, you can include a WHERE clause to only select rows with specific criteria. Then, we use PHP to generate a random number between 0 and one less than the total number of rows. Finally, we use an SQL query with the LIMIT clause to select a single row using the random number we generated as the offset, so it selects the row randomly from the table. If you used a WHERE clause above, you need to make sure you include it in your second SQL query, too.
Also, if you want to retrieve multiple rows, simply change the “1” at the end of the LIMIT clause in your second query to however many results you want to retrieve. However, doing so will return the rows in whatever order MySQL uses to return them (in many cases, that is based on the primary key, though not always; without an “ORDER BY” clause, MySQL always attempts to optimize the order in which rows are returned). It will not randomize the order of the results returned; it will only randomize which row is returned first. Also, if you are going to retrieve more than one row using this method, you will need to subtract that number of rows from the maximum value returned by the PHP rand() function. For instance, if you wanted to retrieve five rows using this method, your code would look something like the following.
$howmany = 5;
$ct = 0;
$sql = "SELECT COUNT(*) FROM table";
if($sql = mysql_query($sql)) {
if($ct = mysql_fetch_row($sql)) {
$ct = $ct[0];
}
}
$r = rand(0,($ct-($howmany+1)));
$sql = "SELECT * FROM table LIMIT $r,$howmany";
if($sql = mysql_query($sql)) {
while($rs = mysql_fetch_assoc($sql)) {
/* Do whatever you need to do with your result */
}
}Hopefully this will help you the next time you’re looking to display random information on your site. Good luck.
Post Your Comment