Modifing Pagination-Sorting Method to Change Rows Per Page

Question

Hi i have a PHP based Pagination and Sorting Method which works fine.

I am able to sort and paginate according to any categories, but having problems when it comes to rows per page.

i have used GET Method to fetch information for pagination and sorting as

    if (isset($_GET['rpp'])&& is_numeric($_GET['rpp'])) {
    $rowsperpage = mysql_real_escape_string($_GET['rpp']);
}else{
    $rowsperpage='5';
}
if (isset($_GET['page']) && is_numeric($_GET['page'])) {
   $currentpage = (int) mysql_real_escape_string($_GET['page']);
} else {
   $currentpage = 1;
}
if (isset($_GET['order']) && in_array($_GET['order'], $order)) {
    $orderby = mysql_real_escape_string($_GET['order']);
}else{
    $orderby='id';
}
$sortby = '';
if (isset($_GET['sort'])) {
    $sortby = mysql_real_escape_string($_GET['sort']);
}else{
    $sortby='desc';
}

for pagination i have used following type of url string

echo " <li><a href='$pagename?order=$orderby&sort=$sortby&rpp=$rowsperpage&page=$nextpage'>Next&raquo;&raquo;</a>

and my mysql query is

$sql2 = "SELECT * FROM internet_security ORDER BY $orderby $sortby LIMIT $rowsperpage OFFSET $offset";

and in html i have used links to sort categories like

        <a href="?order=id&sort=desc">id-desc:</a>
        <a href="?order=id&sort=asc">id-asc:</a>

        <a href="?order=title&sort=desc">title-desc:</a>
        <a href="?order=title&sort=asc">title-asc:</a>

        **limit rows by**
        <a href="?&rpp=5">5</a>
        <a href="?&rpp=10">10</a>
        <a href="?&rpp=20">20</a>

My question is every thing is working fine except when i limit row per page via limit rows by - rows get limited to whatever limit(5,10,20) is selected but then if i click pagination every thing goes to its default set value i.e by id and in DESC order with LIMIT of 5.

and if i do it like

            <a href="?order=id&sort=desc&rpp=5">id-desc:</a>
            <a href="?order=id&sort=desc&rpp=10">id-desc:</a>            
            <a href="?order=id&sort=desc&rpp=20">id-desc:</a>

then it works but then i must have at least 12 links for full functionality.which is not a standard approach.

what i want is once a limit is set either by default or by sorting options, i shall be able to sort by id title etc.. and paginatin should works along with it.

i hope i made it clear. please see what i am doing wrong and suggest any possible solution to my approach.

you need to build your URL by java-script function do it dynamically according to the dom elements in your page — Haim Evgi
Sidenote: mysql_reaL_escape_string (as its name suggests) is useless for ints, your check for it being a number is already enough (you could also cast to int) — Damien Pirsy
Store $rpp in a $_SESSION variable, updating only if it's present in $_GET. — eggyal
@HAIM EVGI i am avoiding using javascript as i will be using AJAX after php functionallity get to work. so than even if javascript is turned off php can take over — user1411607

eggyal eggyal · Accepted Answer · 2012-05-28T11:16:55

(Upgrading to an answer)

Store $rpp in a $_SESSION variable, updating only if it's present in $_GET:

    if ( isset($_GET[    'rpp'])) $_SESSION['rpp'] = intval($_GET['rpp']);
elseif (!isset($_SESSION['rpp'])) $_SESSION['rpp'] = 5;

Then use $_SESSION['rpp'] in your query:

$sql2 = "
  SELECT   *
  FROM     internet_security
  ORDER BY $orderby $sortby
  LIMIT    $_SESSION[rpp] OFFSET $offset
";

Modifing Pagination-Sorting Method to Change Rows Per Page

1 Answers