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>
              nt<td> $row[name]</td>
              nt<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.