How to sort randomly with pagination on MySQL

Useful information that saved my whole day. The credit goes to Nyroblog

The problem is simple: we have to list randomly data from a MySQL table, by creating a paging.

Let's start with the basis: A normal paging. To do so, you use the LIMIT parameter in the MySQL query:

SELECT * FROM user LIMIT 20,10

Where we show the second page for the users with 10 elements by page.

You will probably order the result to be easier to use. For example:

SELECT * FROM user LIMIT 20,10 ORDER BY name ASC

At this point, everything was pretty easy and you probably already knew that.

Now we want to randomly order the result. Intuitively, you will do something like:

SELECT * FROM user LIMIT 20,10 ORDER BY RAND()

Which is not totally wrong. The problem with this solution will occur when changing the page, the order will be different -for sure, it's random. By changing the page you will probably see some recurrent data, and the visitor will never see all the result by reading all the pages. That's not expected.

The solution consists to generate a random number in PHP, stores it in a session variable, and finally use it in the MySQL query inside the RAND parenthesis. And it's finish. Therefore the random number used is every time the same for the visitor session, and the global order will stay the same in the differents pages.

The PHP code to generate and stored the random number:

$rand = $_SESSION['rand'];
if (empty($rand)) {
 srand((float)microtime()*1000000);
 $rand = "0.".rand();
 $_SESSION['rand'] = $rand;
}

Of course, you have to open the session with session_start() at the top of your PHP script before every out or header send -or you can use ob_start().

Finally the MySQL query becomes:

SELECT * FROM user LIMIT 20,10 ORDER BY RAND($rand)

Voilà, you can make pagination with random order.