Links & Resources

Email Scripts
Display a Table

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.


    //Bob's Generic Display Code 
    //copyrght R Donkin 2008
    if (file_exists($include_file))include($include_file);
    $link = mysql_connect("$dbhost", "$dbuser", "$dbpass");
   $sql="SHOW COLUMNS FROM  $dbtable";
    $result=mysql_query($sql) or die (mysql_error());
    while ($data=mysql_fetch_assoc($result)){
    $titles[$i]= ucfirst($data['Field']);
    if (is_numeric($_GET['page']))$page=$_GET['page'];
    if (in_array($_GET['order'],$fields)) $order=$_GET['order'];
    if ($_GET['dir']=="0")
    if ($_GET['dir']=="1"){
    $sql= "SELECT count(*) FROM $dbtable ";
    $max= $line['count(*)'];
  <table width="<?php echo $table_width;?>">
    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 } ?> 
    $sql = "SELECT * FROM $dbtable ";
    if ($order)
    $sql.=" ORDER BY $order ";
    if ($direction=="DESC") {
    $sql.= "DESC";
    $sql.= "ASC";
    $sql .=" LIMIT ".$page*$recperpage.",".$recperpage;
    //echo $sql;
    while ($line=mysql_fetch_assoc($result)){?>
    foreach ($fields as $key=>$value){?>
  <td> <?php echo $line[$value];?></td>
  <?php }?>
  <?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 } ?>