How to Implement Pagination with Random Ordering in PHP MySQL?

PHP MySQL Pagination with Random Ordering

As mentioned, a website is facing an issue with ordering search results on their content page using a pagination system. The SQL query being used is as follows:

<code class="sql">SELECT * FROM table ORDER BY RAND() LIMIT 0,10;</code>

This issue arises in three scenarios:

1. Exclusion of Previously Seen Results: When a user navigates to the next page, previously displayed results must be excluded from subsequent queries while maintaining the random ordering.
2. Fresh Results on Page 1: The user consistently encounters a random ordering upon returning to the first page. This behavior raises the question of whether pagination can be effectively utilized in this scenario.
3. Practical Implementation of Seed: Exploring ways to implement the seed function in MySQL to achieve the desired random ordering.

Solution:

The MySQL RAND(SEED) function addresses this issue. As stated in the documentation, "If a constant integer argument N is specified, it is used as the seed value." (http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand).

Applying this to the example query results in a scenario where the order remains random while being consistent for a given seed:

<code class="sql">SELECT * FROM your_table ORDER BY RAND(351);</code>

The seed can be modified each time the user accesses the first results page and stored in their session. This ensures a different random ordering each time the first page is visited without affecting the random order of subsequent pages.

The above is the detailed content of How to Implement Pagination with Random Ordering in PHP MySQL?. For more information, please follow other related articles on the PHP Chinese website!