MySQL provides a very simple method to select rows in random order. It uses the
ORDER BY RAND()statement. If you will be used the
ORDER BY RAND() statement, MySQL will create a temporary table with all results, then will be assigned a random index to each row and finally will return the random rows! This makes the
ORDER BY RAND() quite slow. In fact, some developer prefers alternative techniques, such as PHP random functions or special SQL select.
However, go ahead and let us see how to use
ORDER BY RAND() in a pagination context. First of all, review this SQL statement:
SELECT * FROM `table` LIMIT amount OFFSET offset;
The select above will return
amount rows from
offset without a specified order.
SELECT * FROM `table` ORDER BY RAND() LIMIT amount OFFSET offset;
The select above, instead, will return
amount rows from
offset in random order. However, the select above doesn’t work in a pagination context because it will return different rows each time.
We can fix that by using a very simple trick. As said above, MySQL will create a temporary table when we use the
ORDER BY RAND()statement. The random indexes will be created through a seed. Usually, this seed is null and MySQL will create it under the hood. We can set this seed with a custom own value:
SELECT * FROM `table` ORDER BY RAND(1234) LIMIT amount OFFSET offset;
This magical SQL statement will return a set of rows in random order by this random order will always be the same.
How to generate a random seed?
Now, you would like to find a method in order to create a different seed for each user session. We may use:
$ip = str_replace('.', '', $_SERVER['REMOTE_ADDR']); $seed = ($ip + date('Hjn') );
As usual, please don’t hesitate to leave any questions or comments in the feed below, and I’ll aim to respond to each of them.