How to add pagination in PHP/SQL

Question

$query = "SELECT DISTINCT TITLE, PID, TYPE, SUM(DAYCOUNT) AS tot, ROUND(SUM(DAYCOUNT)/(
        SELECT SUM(DAYCOUNT) FROM REPORT_LIST_VIEW), 4) AS per
        FROM REPORT_LIST_VIEW
        WHERE DAYCOUNT > '0'
        GROUP BY TITLE, PID, TYPE
        ORDER BY TITLE ASC";
        $res = db_query($query) // drupal 7;

I am using PHP and SQL (oracle) DB to fetch details. Can any one share the details to add a pager in my page to show only 10 items per page.

`function pager_array_splice($res, $limit = 9, $element = 0) {  
global $pager_page_array, $pager_total, $pager_total_items;
$page = isset($_GET['page']) ? $_GET['page'] : '';
// Convert comma-separated $page to an array, used by other functions.
$pager_page_array = explode(',', $page);
// We calculate the total of pages as ceil(items / limit).
$pager_total_items[$element] = count($res);  
$pager_total[$element] = ceil($pager_total_items[$element] / $limit);   
$pager_page_array[$element] = max(0, min((int)$pager_page_array[$element], ((int)$pager_total[$element]) - 1));  
return array_slice($res, $pager_page_array[$element] * $limit, $limit, TRUE);
}`

`$output = '';
$customArr = pager_array_splice($res, 100);
$output .= theme('views_mini_pager', array('quantity' => 100));
print $output;`

This how i tried for drupal method. But fails.

possible duplicate of [Best practice for pagination in Oracle?](http://stackoverflow.com/questions/13738181/best-practice-for-pagination-in-oracle) — Sebastien C., Aug 07 '15 at 09:07

Joshua K · Answer 1 · 2015-08-07T09:25:11.137

1

MySQL

$query = "SELECT DISTINCT TITLE, PID, TYPE, SUM(DAYCOUNT) AS tot, ROUND(SUM(DAYCOUNT)/(
SELECT SUM(DAYCOUNT) FROM REPORT_LIST_VIEW), 4) AS per
FROM REPORT_LIST_VIEW
WHERE DAYCOUNT > '0'
GROUP BY TITLE, PID, TYPE
ORDER BY TITLE ASC
LIMIT $start, 10
";

now set $start to 10 * $pageNo

Example: above sql code. PHP Code:

// show activ page + 5 pages before active page and +5 after that page.
// all in all 11 page numbers:
$maxpagenumber = ...; // thats the tricky part. see below.
$activepage = (int)$_GET['page'];
$startWith = max($activepage-5,1);
$endsWith = min($activepage+5,$maxpagenumber);

// output
echo "first page | prev | ";
for($i=$startswith;$i<=$endsWith;$i++)
  echo $i." | ";
echo "next | last page";

you have to add links to the pages above. in each link you append the parameter page with the ($i-1).

now the part with the $maxpagenumber: you use SQL_CALC_FOUND_ROWS in your sql statement.

thats all :)

edited Aug 07 '15 at 09:25

answered Aug 07 '15 at 09:06

Joshua K

2,407
1
10
13

Thanks Joshua K. Can you share more details or detailed code. – drup Aug 07 '15 at 09:09
added some more code. It should be self-explanatory – Joshua K Aug 07 '15 at 09:26
@JoshuaK OP is using **Oracle**, so why did you post **MySQL**? Also, `LIMIT` is not an Oracle syntax. – Lalit Kumar B Aug 07 '15 at 09:40
Added my code in question. Share some PHP / SQL knowledge to make it as pager. `1 of 20 >>`....`<< 15 of 20 >>`...`<< 20 of 20' – drup Aug 07 '15 at 10:07

score 0 · Answer 2 · edited May 23 '17 at 12:21

Oracle Pre-12c release

You could use ROWNUM inside subquery as pagination query.

For example,

SQL> SELECT empno, sal
  2  FROM   (SELECT empno, sal, ROWNUM AS rnum
  3          FROM   (SELECT empno, sal
  4                  FROM   emp
  5                  ORDER BY sal)
  6          WHERE ROWNUM <= 8)
  7  WHERE  rnum >= 5;

     EMPNO        SAL
---------- ----------
      7654       1250
      7934       1300
      7844       1500
      7499       1600

SQL>

Oracle 12c release

You could use Top-n Row limiting feature.

For example,

SQL> SELECT empno, sal
  2  FROM   emp
  3  ORDER BY sal
  4  OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

     EMPNO        SAL
---------- ----------
      7654       1250
      7934       1300
      7844       1500
      7499       1600

SQL>

How to add pagination in PHP/SQL

2 Answers2