PHP MySQL Pagination with Random Ordering

Maintaining random ordering in MySQL pagination can pose challenges when seeking to prevent duplicate results and ensure varying sets on the first page. Here are solutions to these concerns:

1. Preventing Duplicate Results

To exclude previously seen results on subsequent pages, augment your SQL query with an exclusion criterion based on previously fetched rows. Utilize a PHP array to store fetched content, and incorporate conditions in the query for exclusion:

$excludeIDs = []; // Array to store excluded IDs

$query = "SELECT * FROM table ";
if(!empty($excludeIDs)) {
    $query .= "WHERE id NOT IN (" . implode(",", $excludeIDs) . ") ";
}
$query .= "ORDER BY RAND() LIMIT 0,10;";

2. Varying Results on the First Page

Yes, pagination can be used with random ordering by introducing a seed value to the RAND() function. By varying the seed (e.g., based on time or user's IP address), you can obtain different result sets each time.

3. Using RAND(SEED)

Specify a seed value using RAND(SEED) to influence the random ordering. By setting a constant integer as the seed, you can control the result sequence:

$seed = 521; // Example seed value

$query = "SELECT * FROM table ORDER BY RAND(" . $seed . ") LIMIT 0,10;";