1

I am looking at some legacy code and would like some assistance on a few things.

All of my questions relate to pagination.

First, in the following query, we are calculating the total number of pages. $itemsperpage=40 here. 

$rs0=mysqli_fetch_array(mysqli_query($dbh,"select floor(count(1)/$itemsperpage)+1 as pages from scx_media where scx_media.mediaid in (select mediaid from animated_gifs where status>0)"));

Why do we floor this, and then add 1?

Secondly,

In this piece of code we create the start variable (to be used in sql query). 

$page=(int)$_GET['page'];
$pages=$rs0[pages];
if($page>$pages)$page=$pages;
$start=($page-1)*$itemsperpage;

Why do we minus one from the page? Because we start at 0 so we must minus 1?

And finally,

Let's say that we have 40 items and the perpage item count is 40 as well. With this configuration,we'd have a second page with no data because it would display the 40 items on the first page which meets the per page requirement and then according to the pages calculation we'd have a second page but there would be no records. How do I resolve that issue?

somejkuser
  • 8,856
  • 20
  • 64
  • 130
  • 1
    _"Why do we minus one from the page?"_ Your guess seems reasonable, but none of us are going to have any idea how your legacy code works. – Alex Howansky Oct 31 '17 at 17:35
  • Generally from what I've seen in other legacy code... is the "Why do we minus one from the page?" is due to the LIMIT in sql needs to start with 0 for the first match, and so on. - edit - ... actually that sounds so wrong. We'd need more of your sql and code to know for sure lol. – IncredibleHat Oct 31 '17 at 17:36
  • 2
    _"How do I resolve that issue?"_ If you're using MySQL, you might look at `SQL_CALC_FOUND_ROWS`. – Alex Howansky Oct 31 '17 at 17:36
  • Interesting about SQL_CALC_FOUND_ROWS: https://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count – IncredibleHat Oct 31 '17 at 17:40

2 Answers2

1

Let's say you have 40 results, with 40 items to show per page. You would want 1 page with 40 results.

Lets "play" with the code. Your user asks page.phtml?page=1:

$page=(int)$_GET['page'];           # $page = 1
$pages=$rs0[pages];                 # $pages = 1
if($page>$pages)$page=$pages;       # 1 > 1, no so no change
$start=($page-1)*$itemsperpage;     # $start = (1 - 1) * 10 = 0

So the first item to show on page 1 would be 0. Page 1: start at result 0. Show 40. And you should NOT offer a "Next page" to the user!

Your user asks page.phtml?page=2

$page=(int)$_GET['page'];           # $page = 2
$pages=$rs0[pages];                 # $pages = 1
if($page>$pages)$page=$pages;       # 2 > 1, yes so $page = 1
$start=($page-1)*$itemsperpage;     # $start = (1 - 1) * 40 = 0

So if $page is bigger than $pages, you force the user back to ?page=1 and $start at result no.0 again.

Looks good in fact...

For the SQL:

select floor(count(1)/$itemsperpage)+1 as pages ...

It should be:

select ceil(count(1)/$itemsperpage) as pages ...
Nic3500
  • 8,144
  • 10
  • 29
  • 40
0

I'm doing my pagination with 2 URL variables $_GET['page'] and $_GET['pageSize']

for example /users?page=5&pageSize=30

$query ="
SELECT SQL_CALC_FOUND_ROWS *
FROM
    user
LIMIT ?
OFFSET ?
";
$variables = [$_GET['pageSize'],$_GET['page']];
 // don't forget to sanitize the pageSize , for example allow only (15,30,50)
 //then get the total found rows right after 
 $query = "SELECT FOUND_ROWS()";

This makes the pagination much easier than make MySql calculate the pages size and do more stuff that should be done at the application layer. However to get a quick fix your problem of getting an extra empty page when the number of items equals the number of items per page you can add an extra condition to the SQL statement to add 1 only if the division had a remainder

change this

select floor(count(1)/$itemsperpage)+1

to this

select (CASE WHEN floor(count(1)%$itemsperpage) THEN floor(count(1)/$itemsperpage)+1 ELSE floor(count(1)/$itemsperpage) END)

See the Modulo "%" operation

But as I said I will not use the database server for doing such work of calculating the required pages number . this is an application layer work. I even do that on the client side.

EDIT: You can calculate the $pagesNumber at the application layer(PHP) like this 
$result = $mysqli->query("SELECT FOUND_ROWS() found_rows");
$foundRows = $result->fetch_array(MYSQLI_ASSOC)['found_rows'];
$pagesNumber = ceil($foundRows / $itemsperpage); //thanks to Nic3500 answer
Accountant م
  • 6,975
  • 3
  • 41
  • 61