Display and paginate a Database Table

Here is a simple script to display and paginate a table from a database into a HTML table.

You must set the variables to connect to the database and also the table name (these could be stored separately in a config file ). Then set $recperpage to the number of records to display on each page.

The program will display the first page of (20) records. At the bottom of the page there are hyperlinks to allow you to navigate to further pages.

The column names at the top of each page are also hyperlinked to allow you to sort the data. Clicking once organises the data in ascending order clicking again sorts it into descending order.

 

  <?php
    //Bob's Generic Display Code 
    //copyrght R Donkin 2008
    //
    $dbhost="localhost";
    $dbuser="";
    $dbpass="";
    $dbdbase="";
    $dbdbase="";
    $dbtable="";
    $recperpage=20;
    $include_file="config.php";
    if (file_exists($include_file))include($include_file);
    $table_width="100%";
    $link = mysql_connect("$dbhost", "$dbuser", "$dbpass");
    mysql_select_db("$dbdbase");
   $sql="SHOW COLUMNS FROM  $dbtable";
    $result=mysql_query($sql) or die (mysql_error());
    $i=0;
    while ($data=mysql_fetch_assoc($result)){
    $fields[$i]=$data['Field'];
    $titles[$i]= ucfirst($data['Field']);
    $i++;
    }
    if (is_numeric($_GET['page']))$page=$_GET['page'];
    if (in_array($_GET['order'],$fields)) $order=$_GET['order'];
    if ($_GET['dir']=="0")
    {
    $direction="ASC"; 
    $dir="0";
    }
    if ($_GET['dir']=="1"){
    $direction="DESC";
    $dir="1";
    }
    $sql= "SELECT count(*) FROM $dbtable ";
    $result=mysql_query($sql);
    $line=mysql_fetch_assoc($result);
    $max= $line['count(*)'];
    ?>
  <table width="<?php echo $table_width;?>">
  <tr>
  <?php 
    foreach ($titles as $key=>$value){
    $title="Sort Ascending";
    if ($_GET['order']==$fields[$key] and !$dir)   $title="Sort Descending";
    ?>
  <td style="width:<?php echo $fieldwidth[$key];?>">
  <a href="<?php echo $_SERVER['SCRIPT_NAME']?>?order=<?php echo $fields[$key];if ($_GET['order']==$fields[$key]) echo "&amp;dir=".!$dir; ?>" title="<?php echo $title ?>"><?php echo $value ?></a></td>
  <?php } ?> 
  </tr>
  <?php
    $sql = "SELECT * FROM $dbtable ";
    if ($order)
    {
    $sql.=" ORDER BY $order ";
    if ($direction=="DESC") {
    $sql.= "DESC";
    }
    else 
    {
    $sql.= "ASC";
    }
    }
    $sql .=" LIMIT ".$page*$recperpage.",".$recperpage;
    $result=mysql_query($sql);
    //echo $sql;
    while ($line=mysql_fetch_assoc($result)){?>
  <tr>
  <?php
    foreach ($fields as $key=>$value){?>
  <td> <?php echo $line[$value];?></td>
  <?php }?>
  </tr>
  <?php  }?>
  </table>
  <?php
    if  ($max>$recperpage) echo "Page: &nbsp;";

   $startpage=0;
    $maxpages=round($max/$recperpage,0);
    if ($maxpages>10){
    ?>
  <a href="?page=0<?php if ($_GET['order']) echo "&amp;order=".$_GET['order']."&amp;dir=".$dir; ?>">1</a>.....
  <?php
    if ($page<5){
    $startpage=1;
    }
    else if ($page>($maxpages-5))
    {
    $startpage=$maxpages-11;
    }
    else {
    $startpage=$page-5;
    if ($startpage==0)$startpage=1;
    }
    }
    $endpage=$startpage+10;
    if ($maxpages<$startpage+10)$endpage=$maxpages+1;
    for ($i=$startpage;$i<$endpage;$i++){?>
  <a href="?page=<?php echo $i; if ($_GET['order']) echo "&amp;order=".$_GET['order']."&amp;dir=".$dir; ?>">
  <?php echo $i+1 ?></a>
  <?php }
    if ($maxpages>10){
    ?>
    .....<a href="?page=<?php echo $maxpages; if ($_GET['order']) echo "&amp;order=".$_GET['order']."&amp;dir=".$dir; ?>"><?php echo $maxpages+1; ?></a>
  <?php } 
    ?>