Random record set to display correctly in pagination

Question

I want to display results randomly each time so in my $query3 I've added ORDER BY RAND(). That works - until you use the pagination. Page 2 displays a page 2 worth of random records from the total records. So an item could be shown on page 1 and 2 and 3 etc. What I want is for the total records to be created in a random order initially, but for the subsequent pages of pagination to display the balance of the total records from that original random set. Any thoughts appreciated. Thanks

<?php
//How many records per page
$rpp = 10;

//Check for set page
isset($_GET['page']) ? $page = $_GET['page'] : $page = 0;

//Check for page 1

if($page > 1){
    $start = ($page * $rpp) - $rpp;
}else{
    $start = 0;
}

//Query db for TOTAL records
$query2 = "SELECT id FROM posters WHERE display_on_web = 1";
$resultSet = mysqli_query($conn, $query2);

//Get total records
$numRows = $resultSet->num_rows;

//Get total number of pages
$totalPages = $numRows / $rpp;

//Query results
$query3 = "SELECT * FROM posters WHERE display_on_web = 1 ORDER BY RAND() LIMIT $start, $rpp";
$resultSet = mysqli_query($conn, $query3);
?>

<!-- end of pagination -->

<div class="container-fluid">
<div class="headspace"></div>

<div class="row">

<?php 
$result = mysqli_query($conn, $query3);
$i = 1;
while ($data = mysqli_fetch_assoc($result)) {
?>

//Results are display here

<?php
for($x = 1; $x <= $totalPages=ceil($numRows/$rpp); $x++)
{
echo "<a href='?page=$x'><button class='pagination-button'> $x </button></a>";
}
?>
</div>

You could fetch all records in one go (depending on the amount of records ofc), convert it into JSON, read, output and paginate through that dataset using JavaScript (without reloading the page). If you order them randomly, you won't be able to paginate through the database, since the next page will also select them randomly. — M. Eriksson, Sep 14 '22 at 12:19
If you _seed_ the RAND() function with a constant value, you will always get the _same_ random order. You could determine a random seed value for the user once, and then store that into the session - so that you can use it as argument for RAND(). — CBroe, Sep 14 '22 at 12:29
Does this answer your question? [PHP MySQL pagination with random ordering](https://stackoverflow.com/questions/10729633/php-mysql-pagination-with-random-ordering) — CBroe, Sep 14 '22 at 12:29
Thank you everyone above. I am thinking about your comments but first I am trying to make the proposed code below work (which it doesn't right now.) — fredquimby, Sep 14 '22 at 16:02

Shahid Ullah Khan · Accepted Answer · 2022-09-21T21:34:55.023

In order to generate same sort on next pages you can create seed on first page & then use the seed on subsequent pages.

For example:

$seed = @$_GET['seed'];
if ($page === 0) {
  $seed = rand()
}

Then use the seed inside your query RAND method. For example:

$query = "SELECT * FROM user ORDER BY rand($seed) LIMIT 0, 10";

Here the seed will make sure that you get same order of records if you provide the same seed every time.

Your code will look something like this:

<?php
//How many records per page
$rpp = 10;

//Check for set page
isset($_GET['page']) ? $page = $_GET['page'] : $page = 1;
//Check for page 1

if($page > 1){
    $start = ($page * $rpp) - $rpp;
    $seed = @$_GET['seed'];
}else{
    $seed = rand();
    $start = 0;
}

//Query db for TOTAL records
$query2 = "SELECT id FROM posters WHERE display_on_web = 1";
$resultSet = mysqli_query($conn, $query2);

//Get total records
$numRows = $resultSet->num_rows;

//Get total number of pages
$totalPages = $numRows / $rpp;

//Query results
$query3 = "SELECT * FROM posters WHERE display_on_web = 1 ORDER BY RAND($seed) LIMIT $start, $rpp";
$resultSet = mysqli_query($conn, $query3);
?>

<!-- end of pagination -->

<div class="container-fluid">
<div class="headspace"></div>

<div class="row">

<?php 
$result = mysqli_query($conn, $query3);
$i = 1;
while ($data = mysqli_fetch_assoc($result)) {
?>

//Results are display here

<?php
for($x = 1; $x <= $totalPages=ceil($numRows/$rpp); $x++)
{
$seedParam = $x > 1 ? "seed=$seed" : "";
echo "<a href="?page=$x&$seedParam"><button class='pagination-button'> $x </button></a>";
}
?>
</div>

Thank you. I tried your suggestion but the result is the same as before - each page just throws up random results from the overall results? — fredquimby, Sep 14 '22 at 16:00
Shahid Ullah Khan, thank you very much - you're answer DOES work. I just looked again and it's simply a missing $ - page=$x&seedParam at the bottom needs to be page=$x&$seedParam. — fredquimby, Sep 20 '22 at 13:19
Thanks for the suggestion @fredquimby, i've made the edit. I'm glad it worked for you. — Shahid Ullah Khan, Sep 21 '22 at 21:35

Random record set to display correctly in pagination

1 Answers1