Getting a random row from a MySQL database

• Recommended Method
• Workaround for MySQL 3.23

Abstract

I hate to admit it, but I was recently asked to write a PHP script to display random banner ads on a website. The idea was to keep the banner information including how many times each banner was clicked, displayed and so on, in a MySQL database. Then randomly pick one to display each time the page came up.

Recommended method

The MySQL book and many online help forums recommend using the RAND() function in a query, thus:

SELECT * FROM table ORDER BY RAND() LIMIT 1

This returns all the fields of one database row from a collection sorted in random order. It works fine in versions of MySQL above about 3.25 on some platforms, and on all platforms using version 4.x

My application needed backwards compatibility to MySQL version 3.23, however. In that version a known bug causes the first random number generated by RAND() function to be always the same. This resulted in a repeating display of the same banner all the time in my application.

Workaround for MySQL 3.23

While the first random number for any seed in the RAND(<seed>) function was always the same, the rest of the numbers generated varied as expected for a pseudo-random number generator. That meant that if I could choose any row but the first one, I'd get a "random" banner.

The answer came from the MySQL book again. It occurred to me that if I just inverse-sorted the collection and picked the first row (which was previously the bottom row), I'd get one of the random numbers. Sure enough, the book's index had an entry for a "REVERSE" function. So I didn't need to fool around in PHP, I can still do it all in a simple query:

SELECT * FROM table ORDER BY REVERSE(RAND()) LIMIT 1

Lo, and behold, I get random banners.