Paging using php and mysql

Here this script is for splitting MySQL query result into multiple pages/dynamic paging.
CSS is provided for sample style only.

# No. indicates changes needed or you would like to change. It will show 2 rows per page only. Change $recordsPerPage for more records per pages.

Change at marked places #1, #2 and #3 is must in case your table structure is different, which is most likely.

Here and there commented code are left for some additional information.

<style type="text/css" >
table.data {
  background-color:#fff;
   color:#213243;
}
.data th {background-color:gray; color:#ffffff}
.pagingDiv { width:270px; height:20px}
.pNo {float:left; width:30px; margin-left:3px; margin-right:3px; border:0px solid gray;}
.pNo a {text-decoration:none;}
</style>

<?php
/*
Suppose my table has two columns.
name (varchar 100) and date (date).
CREATE TABLE `tblfour` (
`name` varchar(20) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
KEY `name` (`name`)
) ENGINE=MyISAM
*/
require 'database connection file'; // use file for db conn or use below codes.
/*
// Database connection
$host    = 'localhost';
$user     = 'username';
$pwd     = 'pwdhere';
$dbName = 'dbName';

$conn = mysql_connect ($host, $user, $pwd) or die ('Connection Failed.<br>');
$db = mysql_select_db ($dbName) or die ('DB selection failed.');
*/
// Number of records to show per page
$recordsPerPage = 2;  # 0

// default startup page
$pageNum = 1;

if(isset($_GET['p'])) {
  $pageNum = $_GET['p'];
  settype($pageNum, 'integer');
}

$offset = ($pageNum - 1) * $recordsPerPage;

$query  = "SELECT name, date FROM tblfour WHERE name='a' LIMIT $offset, $recordsPerPage;";  # 1. Main query

$result = mysql_query($query) or die('Mysql Err. 1');

// print table
$data = '<table class="data" border="1" cellpadding="3" cellspacing="0" rules="rows" frame="below">
<tr>
  <th>Name</th>
  <th>Date</th>
</tr>
';

# 2 change/add columns name
while($row = mysql_fetch_assoc($result)) {

  $data .= "\n<tr>
              \n\t<td> $row[name]</td>
              \n\t<td> $row[date] </td>
      \n</tr>
      ";
}
$data .= "\n</table>\n";

  # Update this query with same where clause you are using above.
  $query   = "SELECT COUNT(date) AS dt FROM tblfour WHERE name='a';"; # 3

  $result  = mysql_query($query) or die('Mysql Err. 2');
  $row     = mysql_fetch_assoc($result);
  $numrows = $row['dt'];  # 4

  $maxPage = ceil($numrows/$recordsPerPage);

  $nav = '';
  

Navigation part (next, prev, first, last, etc):

  for($page = 1; $page <= $maxPage; $page++)
  {
       if ($page == $pageNum)
       {
            $nav .= "<div class=\"pNo\">$page</div>";
       }
      else
       {
            $nav .= "<div class=\"pNo\"><a href=\"?p=$page\">$page</a></div>";
       }
  }

  if ($pageNum > 1)  {

       $page = $pageNum - 1;
       $prev = "<a href=\"?p=$page\"><strong><</strong></a>";

       $first = "<a href=\"?p=1\"><strong><<</strong></a>";
  }
  else {
       $prev  = '<strong>  </strong>';
       $first = '<strong>   </strong>';
  }

  if ($pageNum < $maxPage) {
       $page = $pageNum + 1;
       $next = "<a href=\"?p=$page\"><strong>></strong></a>";

       $last = "<a href=\"?p=$maxPage\"><strong>>></strong></a>";
  }
  else {
       $next = '<strong>  </strong>';
       $last = '<strong>   </strong>';
  }
  

Everything is ready for paging, just output to browser:

  echo $data;

  echo '<br />';

  echo "<div class=\"pagingDiv\">
      <div class=\"pNo\">$first</div>
      <div class=\"pNo\">$prev</div>
      $nav
      <div class=\"pNo\">$next</div>
      <div class=\"pNo\">$last</div></div>";
?>

Now, you know how to implement paging. "Paging implemented in Ajax" is using almost same PHP code with Ajax facility.

Possibly Related posts:

• Paging in PHP, MySQL and Ajax
• MySQL: Remove Duplicate Rows/Records from Table
• Get Data from Internet