Selecting a Random Row from a MySQL Table

Album Cover: No Code

"Are you woman enough to be my man?"
Pearl Jam / Hail Hail

Posted on July 29, 2007 5:49 PM in Web Development
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

One of the new features of my latest web design is the random, rotating song lyrics that appear just above the sidebar. In order to ensure that I could select a random lyric with every page request, I needed to be able to select a random row from a lyrics table in my database.

To accomplish this, I first needed to get a count of all the lyrics currently available in the table. This is fairly straightforward:

SELECT COUNT(*) AS total FROM lyrics;

Then, using the returned count, I needed to get a random number in the zero-based range of available lyrics (so somewhere between 0 and n-1 where n is the total number of lyrics available). This is fairly easy to do using PHP's built-in rand() function:

$num = rand(0, $total - 1);

The example above assumes that $total has been set to the count returned by the aforementioned MySQL query.

Then, the only step that remains is to query for a single record from the lyrics table using the offset option of LIMIT, like so:

$sql = "SELECT * FROM lyrics ORDER BY artist LIMIT $num, 1";

I included some PHP code in the example above just to show where the variable returned by the rand() is included in the query.

The offset is included just before the comma, which is then followed by the number of rows you want to retrieve. Since I only need a single lyric, I use 1 as the number of rows, and let the random number I calculated be the offset.

I included ORDER BY artist in the query as an attempt to keep the result set somewhat predictable, but its necessity is debatable.

Comments

Elvard on July 30, 2007 at 12:57 AM:

I'm using this:
SELECT * FROM table ORDER BY RAND () LIMIT 1;

Permalink

Bernie Zimmermann on July 30, 2007 at 1:07 AM:

Thanks for the tip, Elvard. That's a heck of a lot easier than what I was doing. I've updated my code.

Permalink

Post Comments

If you feel like commenting on the above item, use the form below. Your email address will be used for personal contact reasons only, and will not be shown on this website.

Name:

Email Address:

Website:

Comments:

Check this box if you hate spam.