Display a Database Table

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

You must set the variables to connect to the database and also the table name. Then set $recperpage to the number of records to display on each page.

Now decide which columns from your database table you wish to display and put the fieldnames into the $fields array. Put the description you wish to display in the html table columns in the $titles array. Finally enter the display column widths in the $fieldwidth array.

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 descriptions 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="";
    $link = mysql_connect("$dbhost", "$dbuser", "$dbpass");
    mysql_select_db("$dbdbase");
    $recperpage=20;
    $titles=array("Email Address","Name");
    $fields=array("email","name");
    $fieldwidth=array("200px","200px");
    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>
    <tr>
    <?php 
    foreach ($titles as $key=>$value){
    $title="Sort Ascending";
    ?>
    <td style="width:<?php echo $fieldwidth[$key];?>">
    <a href="?order=<?php echo $fields[$key]; 
    if ($order==$fields[$key])
    {
    echo "&amp;dir=".!$dir;
    if (!$dir)$title="Sort Descending";
    }
    ?>
    " 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>
    Page: 
  <?php
    $startpage=0;
    $maxpages=round($max/$recperpage,0);
    if ($maxpages>10){
    ?>
  <a href="?page=0&amp;order=<?php echo $order ?>
  &amp;dir=<?php echo $dir ?>">1</a>.....
  <?php
    if ($page<5){
    $startpage=1;
    }
    else if ($page>($maxpages-5))
    {
    $startpage=$maxpages-11;
    }
    else {
    $startpage=$page-5;
    }
    }
    $endpage=$startpage+10;
    if ($maxpages<$startpage+10)$endpage=$maxpages+1;
    for ($i=$startpage;$i<$endpage;$i++){?>
  <a href="?page=<?php echo $i ?>&amp;order=
  <?php echo $order ?>&amp;dir=<?php echo $dir ?>">
  <?php echo $i+1 ?></a>
  <?php }
    if ($maxpages>10){
    ?>
    .....<a href="?page=<?php echo $maxpages-1; ?>
   &amp;order=<?php echo $order ?>
   &amp;dir=<?php echo $dir ?>"><?php echo $maxpages; ?></a>
  <?php } ?>