Pagination - where am I going wrong?

PHP
1

I am trying to display a list of up to 5 per page and have a clickable link that displays more results on the next page

I have got this code running, limiting the display to five per page, but is displaying the same results for each clickable link

My code is:
I am using mysqli (if this is needed)


// get the current product set
$list = new ListDB();
$list->ListDisplay();

$per_page = 5;
$list_count = count($list->data);
$pages = ceil($list_count/$per_page);

if(!isset($_GET['page']))
{
    header("location: list.php?page=1");

}  else {
    $page = $_GET['page'];
}

$start = (($page - 1)*$per_page);

and to get the clickable links is


echo "<br/>".$start;

for($number = 1;$number<=$pages;$number++){
    echo '<a href="?page'.$number.'">'.$number.'</a>';
}
2

Hi cssbonding,

You’ve got a problem with your link markup. There should be a ‘=’ between the variable name page and the value:


// before
echo '<a href="?page'.$number.'">'.$number.'</a>';

// after
echo '<a href="?page='.$number.'">'.$number.'</a>';

Also, it would be better to avoid an unnecessary redirect if $_GET[‘page’] isn’t set:

$page = isset($_GET['page']) ? $_GET['page'] : 1;
3

Hi there, thanks for the re-direct tip, but the first (after code) didn’t change anything as regards to the clickable link(s)

4

I’ve just noticed that you’re calling $list->ListDisplay() but you’re not telling it how many records to return. Could you post the code for the ListDisplay method please?

5

Hi there, I am wanting to call all records to be returned, the code for ListDisplay is a bit elongated and won’t make sense if I only post a small code

6

If you don’t offset or limit the amount of records that are returned, there won’t be any difference between the pages and each one will show the full result set.

7

Hi there, the MySQL query is limited by the ‘id’ in the database if that is what you mean

8

What I mean is, if your results are displayed 5 per page and you want to display the 3rd page, your query would look something like this:

SELECT * FROM myTable
LIMIT 10, 5

Here we’re telling MySQL to return only 5 records (the limit), and to start selecting those records 10 in from the beginning (the offset). This is much more efficient that returning the whole data set and then choosing only the relevant records using PHP.

9

I don’t want to limit the display to 5 per query, rather unlimited query, but 5 per page in the pagination script to be displayed per clickable link

10

I think perhaps your misunderstanding me. The normal way to do pagination is that when the user clicks a link (eg. for page 3) the script gets the page number from the URL (as you’re doing in your script) and then re-queries the DB, requesting just the records for page 3. Why would you want to get all the records from the DB if you’re only going to display a fraction of them?

11

Whoa, thanks now I get you, sorry, will try that

12

Unless you want your mysql server to get an massive load when your website grows, don’t do this.

The problem with LIMIT X, Y is that is you want to get five records from post one million, MySQL actually loops through one million and five posts to get your result. This is as you might guess highly ineffective.

Instead use this type of query:


SELECT * FROM myTable WHERE myTableId>Y LIMIT 5

Of course this approach create a significant different problem to solve, how to get the different “myTableId” that need to be in the page links, you can solve this by assigning an operator which you inside a sub query increase for each record run, then on the outer query you limit by modulus on the limit number you want.

Doing this both on DESC and then ASC will get you the records you need to create the pagination result.

13

I’ve never come across this problem before - presumably it only starts becoming an issue with data sets over a certain size? Do you have any links you could share where I could do some further reading on this?

14

It becomes an issue when the dataset grows, though even on smaller (few hundred thousand records, or even smaller if it is hit frequently) you will notice the difference if you try both queries and compare the time it needs.

Been a few years since I read the article, but luckily I remembered who wrote it (Rick James) so was not that hard to locate it. Also found a conference slide on the topic (same guy) that also explain more of it.
http://mysql.rjweb.org/doc.php/pagination

15

This is doing my nut in…I am only able to display 5 results only with this code


$page = isset($_GET['page']) ? $_GET['page'] : 1;
$per_page = 5;
$list_count = count($list->data);
$pages = ceil($list_count/$per_page);
$start = (($page - 1)*$per_page);
$list = new ListDB();
$list->ListDisplay($start,$per_page);

This is to display what is going on with the script


echo "Pages: ".$pages;
echo '<br />Photos: <b>'.$list_count.'</b><br />';
echo 'Page No: '.$page.'<br />';
echo 'Start: '.$start.'<br />';

Where $pages shows 1, $list_count is showing 5, $page showing 1 and $start showing 0. $list_count is showing the wrong set of result, it should be 25. And the link below, i.e. the pagination links, there is only number 1

The query in ListDB is

$query = "SELECT * FROM photoupload LIMIT $start,$per_page}";

Can anyone show me where I am going wrong?

16

Oh well, I’ve solved the major problem of pagination, by simply introducing another function to query and get a count of the results and the other function just to limit the amount displayed…whoop ass!!!