Pagination keeps showing the same portion of SQL data

Question

I have a really large data set from SQL that i need to paginate.

I have an issue with my pagination code. The code does show the page number in the URL and it does give me pagination hyperlinks at the bottom of the table. However, any page I click on, it outputs the same exact portion of the sql datatable.

Also, I'm doing this in wordpress.

// define how many results you want per page
$results_per_page = 10;

// find out the number of results stored in database
$sql='SELECT * FROM ETF';
$result = mysqli_query($con, $sql);
$number_of_results = mysqli_num_rows($result);

// determine number of total pages available
$number_of_pages = ceil($number_of_results/$results_per_page);

// determine which page number visitor is currently on
if (!isset($_GET['page'])) {
  $page = 1;
} else {
  $page = $_GET['page'];
}

// determine the sql LIMIT starting number for the results on the displaying page
$this_page_first_result = ($page-1)*$results_per_page;

// retrieve selected results from database and display them on page
$sql='SELECT * FROM ETF LIMIT ' . $this_page_first_result . "," .$results_per_page;

$result = mysqli_query($con, $sql);

while($row = mysqli_fetch_array($result)) {
  echo $row['ETF'] . ' ' . $row['ETF NAME']. '<br>';
}


// display the links to the pages
for ($page=1;$page<=$number_of_pages;$page++) {
  echo '<a href="index.php/stocks/sec-forms/?page=' . $page . '">' . $page. '</a>';
}

Answer 1

Try:

$sql='SELECT * FROM ETF LIMIT ' . $results_per_page . ' OFFSET ' . $this_page_first_result;

Also as mentioned you should sort by a certain column with 'ORDER BY' for consistent results.

Answer 2

You should change

$sql='SELECT * FROM ETF';
$result = mysqli_query($con, $sql);
$number_of_results = mysqli_num_rows($result);

to something like this

$count = mysqli_fetch_assoc(mysqli_query($con,"SELECT COUNT(*) AS RC FROM ETF"));
$number_of_results=$count['RC'];

because it is faster to get count from mysql (index if its a large table) instead of fetching all table data (because SELECT *...) for looping trough it just for row counting.

// retrieve selected results from database and display them on page
$sql='SELECT * FROM ETF LIMIT ' . $this_page_first_result . "," .$results_per_page;

You using good query, but using wrong function to get its results.

Query should be

$sql='SELECT * FROM ETF LIMIT ' . $results_per_page . "," .$this_page_first_result;

or:

$sql='SELECT * FROM ETF LIMIT ' . $results_per_page . " OFFSET " .$this_page_first_result;

Both of these work.

Change

while($row = mysqli_fetch_array($result)) {
  echo $row['ETF'] . ' ' . $row['ETF NAME']. '<br>';
}

to

while($row = mysqli_fetch_assoc($result)) {
  echo $row['ETF'] . ' ' . $row['ETF NAME']. '<br>';
}

because "mysqli_fetch_array" fetch array width numbered indexes (for example 0, 1, 2...) not a text ones you are trying to use, but "mysqli_fetch_assoc" does that you need.

Full code should be

<?php
$con=mysqli_connect('your server','login to database','database logins password','database name');
// define how many results you want per page
$results_per_page=10;
// find out the number of results stored in database
$count=mysqli_fetch_assoc(mysqli_query($con,"SELECT COUNT(*) AS RC FROM ETF"));
$number_of_results=$count['RC'];
// determine number of total pages available
$number_of_pages=ceil($number_of_results/$results_per_page);
// determine which page number visitor is currently on
if(!isset($_GET['page']))
{
    $page=1;
}
else
{
    $page=$_GET['page'];
}
// determine the sql LIMIT starting number (OFFSET) for the results on the displaying page
$this_page_first_result=($page-1)*$results_per_page;
// retrieve selected results from database and display them on page
$sql="SELECT * FROM ETF LIMIT $results_per_page,$this_page_first_result";
$result=mysqli_query($con, $sql);
// Loop to show results
while($row = mysqli_fetch_assoc($result))
{
    echo $row['ETF'].' '.$row['ETF NAME'].'<br>';
}
// display the links to the pages
for($page=1;$page<=$number_of_pages;$page++)
{
    echo'<a href="?page='.$page.'">'.$page.'</a> ';
}
mysqli_close($con);
    ?>

Answer 3

Using OFFSET and LIMIT for pagination of web pages leads to two bugs -- duplicated rows shown, and rows not shown.

Why?

1. You are pondering the "first" 10 rows on one web page.
2. Meanwhile a row is INSERTed or DELETEd that belongs in the first 10.
3. You click [Next] and go to the page that shows the next 10 rows. But wait, it is not showing the "next" 10 rows, it is showing rows #11-20. And, since something changed in rows 1-10, the rows are not really continuing where you left off.

I just gave you a hint of how to avoid it -- "remember where you left off" instead of using OFFSET. More .