The problem

This is a common problem: you have entities in a category, you want to display them by pages because you have a lot of entities and you don’t want to have entities from page 1 in your page 2.

If you are using the ORDER BY RAND() function from MySQL, you will have a problem. MySQL splits up the data into pages of X posts each (paginates) and fails to include a new set of X posts on page 2 and so forth. In other words, because it is listing things in a random order, it just goes out and gets another X random posts. As a result, you will have some repeated posts instead of a new set of X random posts on page 2, etc.

The solution

Fortunately, there is a solution for this problem. You will be able to “remember” which random 10 posts were included on page 1, and then have a new set of 10 posts to put on pages 2, 3, etc. until all posts are displayed.

The MySQL RAND() function accepts a seed as an optional argument. Using a seed, it will return the same randomized result set each time. For example, if you want your posts to be random ordered, paginated with no repetition, you can write a query like this: SELECT * FROM posts ORDER BY RAND(42) to select posts.

If you do not want to have the same results for every user viewing the list, do not give an arbitrary value to the RAND function: generate a random number, store it in session and pass it to the MySQL RAND function when selecting posts.