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.
Comments.
#1 by Gilles
Thank you, nice tip!
the Thursday 24 July 2008 at 02:54:15 PM
#2 by BluesRocker
Great idea, tnx. Only I don't understand why you added zero in $rand = "0.".rand(); because there are only 2 different orders that way. Mysql function RAND() only uses rounded numbers, and in that way it uses 0 and 1. Without "0.". there are more different orders.
the Thursday 21 August 2008 at 03:30:41 PM
#3 by Leonardo
Such a simple solution to a tricky problem, bravo!
Helped a lot, thanks!
the Friday 27 March 2009 at 04:22:37 AM
#4 by RLHawk
BluesRocker, actually, that's not true. 'SELECT RAND();' is not a whole number. Mysql's RAND() function returns a number between 0 and 1. It is PHP's rand function that returns a whole number. Which is why he used "0.".rand().
Thanks for the tip!
the Tuesday 09 June 2009 at 12:58:14 AM
#5 by RLHawk
Ooh oh. Nevermind what I said. BluesRocker is right. I was confused about the way ORDER BY RAND() worked. I thought it would be equivalent to "ORDER BY 0.".rand()."
But BluesRocker is right. It should be something like this:
$rand = $_SESSION['rand'];
if (empty($rand)) {
srand((float)microtime()*1000003);
$rand = rand();
$_SESSION['rand'] = $rand;
}
the Tuesday 09 June 2009 at 01:15:04 AM
#6 by Mehmet Hazar Artuner
Thank you so much, you saved my day :) but just one thing i noticed that, when i use floating point number, it gives me the same random order every time. after i used random integer number as key, the order changes as i want. Maybe it cause from the mysql version i'm not sure. just want to say if anybody cant use your sample, he or she should try to use random integer number. thanks a lot again :)
the Friday 02 March 2012 at 03:24:49 PM
Add a comment.