Posts for tag: MySQL 1

How to paginate a random list using PHP and MySQL

Displaying a list of data from your MySQL database and the breaking the result up in to pages is fairly straightforward to achieve, you simply need to two variables in PHP. One for the amount of data you wish to display on each page $amount=20; and another to control the pagination $offset=0. The MySQL query is a follows:

$query="SELECT * FROM my_table LIMIT $amount OFFSET $offset";

You can then set up your NEXT / PREVIOUS links and pass the $offset value in the URL. If you have set $amount to 20, then $offset=0 will display page 1, $offset=20 page 2 & $offset=40 page 3 etc...

Very easy — you can adjust your query to display data in any order you like, alphabetical, reversed by date etc. — but what if you want to display the data in a random order?

$query="SELECT * FROM my_table ORDER BY RAND() LIMIT $amount OFFSET $offset";

You can see the problem instantly — the first page will display $amount of data in a random order, OK, but the second page will display $amount of data in a new random order — it may contain items that were displayed on the first page!

So how can you move between pages simply, but retain your random list? The solution is to 'seed' a random number. It works like this:


$query="SELECT * FROM my_table ORDER BY RAND($seed) LIMIT $amount OFFSET $offset";

Once RAND() is seeded MySQL will produce a randomized list that is repeatable. I this way it is possible to repeat the query using LIMIT & OFFSET to produce pagination. In the above example $seed is a fixed number, but it would be better to set $seed to a random number and store it as a $_SESSION variable.

Here's an example of a page that has pagination and data presented in a random order. In this example, instead of storing the $seed in a $_SESSION variable, the seed is generated using a combination of the visitor's IP address and the current date and hour...

//generate individual seed...
$seed=($ip+$hour+$day+$month); this way, everyone sees a different random list that is reordered every hour.