Simple PHP Pagination script

Question

I have rows of data coming from database  I would like to have a table with a simple pagination  what is the easiest way of doing it  I d be glad if anyone could provide

User · Answer

lt  php    Custom PHP MySQL Pagination Tutorial and Script    You have to put your mysql connection data and alter the SQL queries both queries   mysql connect  DATABASE Host Here   DATABASE Username Here   DATABASE Password Here   or die  mysql error     mysql select db  DATABASE Name Here   or die  mysql error                     QUERY THE MEMBER DATA INITIALLY LIKE YOU NORMALLY WOULD  sql   mysql query  SELECT id  firstname  country FROM myTable ORDER BY id ASC                                         Pagination Logic                                                                           nr   mysql num rows  sql      Get total of Num rows from the database query if  isset   GET  pn          Get pn from URL vars if it is present      pn   preg replace     0-9  i         GET  pn        filter everything but numbers for security new         pn   ereg replace    0-9          GET  pn        filter everything but numbers for security deprecated    else      If the pn URL variable is not present force it to be value of page number 1      pn   1      This is where we set how many database items to show on each page  itemsPerPage   10     Get the value of the last page in the pagination result set  lastPage   ceil  nr    itemsPerPage      Be sure URL variable  pn page number  is no lower than page 1 and no higher than  lastpage if   pn  lt  1       If it is less than 1      pn   1     force if to be 1   else if   pn  gt   lastPage       if it is greater than  lastpage      pn    lastPage     force it to be  lastpage s value      This creates the numbers to click in between the next and back buttons    This section is explained well in the video that accompanies this script  centerPages        sub1    pn - 1   sub2    pn - 2   add1    pn   1   add2    pn   2  if   pn    1         centerPages      amp nbsp   lt span class  pagNumActive  gt      pn     lt  span gt   amp nbsp         centerPages      amp nbsp   lt a href        SERVER  PHP SELF       pn      add1      gt      add1     lt  a gt   amp nbsp      else if   pn     lastPage         centerPages      amp nbsp   lt a href        SERVER  PHP SELF       pn      sub1      gt      sub1     lt  a gt   amp nbsp         centerPages      amp nbsp   lt span class  pagNumActive  gt      pn     lt  span gt   amp nbsp      else if   pn  gt  2  amp  amp   pn  lt    lastPage - 1          centerPages      amp nbsp   lt a href        SERVER  PHP SELF       pn      sub2      gt      sub2     lt  a gt   amp nbsp         centerPages      amp nbsp   lt a href        SERVER  PHP SELF       pn      sub1      gt      sub1     lt  a gt   amp nbsp         centerPages      amp nbsp   lt span class  pagNumActive  gt      pn     lt  span gt   amp nbsp         centerPages      amp nbsp   lt a href        SERVER  PHP SELF       pn      add1      gt      add1     lt  a gt   amp nbsp         centerPages      amp nbsp   lt a href        SERVER  PHP SELF       pn      add2      gt      add2     lt  a gt   amp nbsp      else if   pn  gt  1  amp  amp   pn  lt   lastPage         centerPages      amp nbsp   lt a href        SERVER  PHP SELF       pn      sub1      gt      sub1     lt  a gt   amp nbsp         centerPages      amp nbsp   lt span class  pagNumActive  gt      pn     lt  span gt   amp nbsp         centerPages      amp nbsp   lt a href        SERVER  PHP SELF       pn      add1      gt      add1     lt  a gt   amp nbsp         This line sets the  LIMIT  range    the 2 values we place to choose a range of rows from database in our query  limit    LIMIT      pn - 1     itemsPerPage        itemsPerPage     Now we are going to run the same query as above but this time add  limit onto the end of the SQL syntax     sql2 is what we will use to fuel our while loop statement below  sql2   mysql query  SELECT id  firstname  country FROM myTable ORDER BY id ASC  limit                                     END Pagination Logic                                                                                                                        Pagination Display Setup                                                                        paginationDisplay          Initialize the pagination output variable    This code runs only if the last page variable is ot equal to 1  if it is only 1 page we require no paginated links to display if   lastPage     1           This shows the user what page they are on  and the total number of pages      paginationDisplay     Page  lt strong gt      pn     lt  strong gt  of      lastPage    amp nbsp    amp nbsp    amp nbsp            If we are not on page 1 we can place the Back button     if   pn    1             previous    pn - 1           paginationDisplay       amp nbsp    lt a href        SERVER  PHP SELF       pn      previous      gt  Back lt  a gt                  Lay in the clickable numbers display here between the Back and Next links      paginationDisplay      lt span class  paginationNumbers  gt      centerPages     lt  span gt           If we are not on the very last page we can place the Next button     if   pn     lastPage             nextPage    pn   1           paginationDisplay       amp nbsp    lt a href        SERVER  PHP SELF       pn      nextPage      gt  Next lt  a gt                                                   END Pagination Display Setup                                                                                Build the Output Section Here  outputList       while  row   mysql fetch array  sql2          id    row  id         firstname    row  firstname         country    row  country          outputList      lt h1 gt      firstname     lt  h1 gt  lt h2 gt      country      lt  h2 gt  lt hr   gt          close while loop   gt   lt html gt   lt head gt   lt title gt Simple Pagination lt  title gt   lt  head gt   lt body gt      lt div style  margin-left 64px  margin-right 64px   gt        lt h2 gt Total Items   lt  php echo  nr    gt  lt  h2 gt      lt  div gt         lt div style  margin-left 58px  margin-right 58px  padding 6px  background-color  FFF  border  999 1px solid   gt  lt  php echo  paginationDisplay    gt  lt  div gt         lt div style  margin-left 64px  margin-right 64px   gt  lt  php print   outputList     gt  lt  div gt         lt div style  margin-left 58px  margin-right 58px  padding 6px  background-color  FFF  border  999 1px solid   gt  lt  php echo  paginationDisplay    gt  lt  div gt   lt  body gt   lt  html gt

User · Answer

This is a mix of HTML and code but it s pretty basic  easy to understand and should be fairly simple to decouple to suit your needs I think   try           Find out how many items are in the table      total    dbh- gt query           SELECT             COUNT            FROM             table       - gt fetchColumn            How many items to list per page      limit   20          How many pages will there be      pages   ceil  total    limit           What page are we currently on       page   min  pages  filter input INPUT GET   page   FILTER VALIDATE INT  array           options    gt  array               default      gt  1               min range    gt  1                              Calculate the offset for the query      offset     page - 1      limit          Some information to display to the user      start    offset   1       end   min   offset    limit    total           The  back  link      prevlink     page  gt  1      lt a href   page 1  title  First page  gt  amp laquo  lt  a gt   lt a href   page       page - 1       title  Previous page  gt  amp lsaquo  lt  a gt       lt span class  disabled  gt  amp laquo  lt  span gt   lt span class  disabled  gt  amp lsaquo  lt  span gt            The  forward  link      nextlink     page  lt   pages      lt a href   page       page   1       title  Next page  gt  amp rsaquo  lt  a gt   lt a href   page      pages      title  Last page  gt  amp raquo  lt  a gt       lt span class  disabled  gt  amp rsaquo  lt  span gt   lt span class  disabled  gt  amp raquo  lt  span gt            Display the paging information     echo   lt div id  paging  gt  lt p gt     prevlink    Page     page    of     pages    pages  displaying     start   -    end    of     total    results     nextlink     lt  p gt  lt  div gt            Prepare the paged query      stmt    dbh- gt prepare           SELECT                       FROM             table         ORDER BY             name         LIMIT              limit         OFFSET              offset                 Bind the query params      stmt- gt bindParam   limit    limit  PDO  PARAM INT        stmt- gt bindParam   offset    offset  PDO  PARAM INT        stmt- gt execute            Do we have any results      if   stmt- gt rowCount    gt  0               Define how we want to fetch the results          stmt- gt setFetchMode PDO  FETCH ASSOC            iterator   new IteratorIterator  stmt               Display the results         foreach   iterator as  row                echo   lt p gt     row  name      lt  p gt                     else           echo   lt p gt No results could be displayed  lt  p gt             catch  Exception  e        echo   lt p gt     e- gt getMessage      lt  p gt

User · Answer

Some of the tutorials I found that are easy to understand are:

http://www.phpfreaks.com/tutorial/basic-pagination

It makes way more sense to break up your list into page-sized chunks, and only query your database one chunk at a time. This drastically reduces server processing time and page load time, as well as gives your user smaller pieces of info to digest, so he doesn't choke on whatever crap you're trying to feed him. The act of doing this is called pagination.

A basic pagination routine seems long and scary at first, but once you close your eyes, take a deep breath, and look at each piece of the script individually, you will find it's actually pretty easy stuff

The script:

// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM numbers";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db 
$sql = "SELECT id, number FROM numbers LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
   // echo data
   echo $list['id'] . " : " . $list['number'] . "<br />";
} // end while

/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for

// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>

http://www.tonymarston.net/php-mysql/pagination.html

This tutorial is intended for developers who wish to give their users the ability to step through a large number of database rows in manageable chunks instead of the whole lot in one go.

[php] Simple PHP Pagination script

The answer is

Examples related to php

Examples related to sql

Tags

[php] Simple PHP Pagination script

The answer is

Examples related to php

Examples related to sql

Examples related to pagination

Tags