This table class wraps up a SELECT query and takes care of sorting columns and paging if there are many table rows.

Sample of use:

require_once 'activetable.php';

function FormatDate( $date )
{
return substr( $date, 6, 2 ) . "-" . substr( $date, 4, 2 ) . "-" . substr( $date, 0, 4 );
}

$table = new ActiveTable(
"localhost", "authors", "anonymous", "anonymous",
"select * from news",
"select count(*) from news",
2,
new Column( "Date", create_function( '$row', 'return FormatDate($row["timestamp"]);' ), "timestamp", "*" ),
new Column( "Author", create_function( '$row', 'return $row["author"];' ),"author", "75%" )
);

$table->Display();

?>

By : Atrox

<?php

class Tabular
{
function Tabular( $widths )
{
$this->widths = $widths;
$this->alternate = 0;
}

function begin( $headers )
{
echo '<table cellspacing="0" cellpadding="0" border="0" class="widetable">';
echo '<tr>';
$i = 0;
foreach( $headers as $header )
{
echo '<td class="rowheader" width="' . $this->widths[$i] . '">' ;
echo $header;
echo '</td>';
$i++;
}
echo '</tr>';
}

function row( $datas )
{
echo "<tr class='row{$this->alternate}'>";
$this->alternate = 1 - $this->alternate;
foreach( $datas as $data ) echo "<td>$data</td>";
echo '</tr>';
}

function end()
{
echo '</table>';
}
}

class Column
{
var $header;
var $formatter;
var $width;

function Column(
$header, // Header text, e.g. "Author"
$formatter, // Cell formatter function, e.g. create_function( '$row', 'return $row["author"];' )
$sortfield, // DB field to use for sorting, e.g. "author"
$width // Column width, e.g. "*" or "75%"
)
{
$this->header = $header;
$this->formatter = $formatter;
$this->width = $width;

if( $sortfield != false )
{
$this->header = "<a href='{$_SERVER['PHP_SELF']}?__sort={$sortfield}'><img border='0' src='img/sort.png'></a>&nbsp;" . $header;
}
}

function format( $row )
{
$f = $this->formatter;
return $f( $row );
}
}

class ActiveTable
{
function ActiveTable(
$host, // Host name, e.g. "localhost"
$dbname, // Database name, e.g. "mydb"
$user, // Database user name, e.g. "anonymous"
$password, // Password, e.g. "mypass"
$sql, // SQL query, e.g. "select * from news"
$countsql, // SQL query to count records, e.g. "select count(*) from news"
$maxrows // max # rows on-screen, 0 for no paging.
) {
// Store instance data.
$this->host = $host;
$this->dbname = $dbname;
$this->user = $user;
$this->password = $password;
$this->sql = $sql;
$this->countsql = $countsql;
$this->maxrows = $maxrows;

// Store columns in an array.
$args = func_get_args();
for( $i = 0; $i < 7; $i++ ) array_shift( $args );
$this->columns = array();
foreach( $args as $arg ) array_push( $this->columns, $arg );
}

function Display()
{
// Read row offset from POST variables.
$offset = $_GET["__offset" ];
if( $offset == null ) $offset = 0;
if( $offset < 0 ) $offset = 0;

// Read sort column from POST variables.
$sort = $_GET["__sort"];

// Open DB connection.
$db = mysql_connect( $this->host, $this->user, $this->password );
mysql_select_db( $this->dbname, $db );

// Count records in result if paging is on.
if( $this->maxrows > 0 )
{
$result = mysql_query( $this->countsql, $db );
$row = mysql_fetch_array($result);
$count = (int) $row[0];
mysql_free_result( $result );
}

// Prepare actual query.
$query = $this->sql;
if( $sort != false ) $query .= " ORDER BY " . $sort . " ";
if( $this->maxrows > 0 ) $query = $query . " LIMIT " . $offset . "," . $this->maxrows;
$result = mysql_query( $query, $db );

// Start table.
$table = new Tabular( array_map( create_function( '$col', 'return $col->width;' ), $this->columns ) );
$table->begin( array_map( create_function( '$col', 'return $col->header;' ), $this->columns ) );

// Print table rows.
while( $row = mysql_fetch_array($result) )
{
$strs = array();
foreach( $this->columns as $col )
{
array_push( $strs, $col->format( $row ) );
}
$table->row( $strs );
}

// Add first/prev/next/last buttons, if required.
if( $this->maxrows > 0 && ( $offset > 0 || $count - $offset > $this->maxrows ) )
{
echo "<tr><td colspan=" . count($this->columns) . ">";
echo '<table cellpadding="0" cellspacing="0" border="0" width="100%" class="prevnext">';
echo '<tr>';
echo '<td align="left">';

if( $offset > 0 )
{
$vars = "__offset=0";
if( $sort != false ) $vars .= "&__sort={$sort}";
echo "<a href='{$_SERVER['PHP_SELF']}?{$vars}'><img align='middle' src='img/first.gif'></a>";

echo "&nbsp;";

$newoffset = $offset - $this->maxrows;
$vars = "__offset={$newoffset}";
if( $sort != false ) $vars .= "&__sort={$sort}";
echo "<a href='{$_SERVER['PHP_SELF']}?{$vars}'><img align='middle' src='img/prev.gif'></a>";
}

echo '</td>';
echo '<td align="right">';

if( $count - $offset > $this->maxrows )
{
$newoffset = $offset + $this->maxrows;
$vars = "__offset={$newoffset}";
if( $sort != false ) $vars .= "&__sort={$sort}";
echo "<a href='{$_SERVER['PHP_SELF']}?{$vars}'><img align='middle' src='img/next.gif'></a>";

echo "&nbsp;";

$newoffset = $count - $this->maxrows;
$vars = "__offset={$newoffset}";
if( $sort != false ) $vars .= "&__sort={$sort}";
echo "<a href='{$_SERVER['PHP_SELF']}?{$vars}'><img align='middle' src='img/last.gif'></a>";
}

echo '</td>';
echo '</tr>';
echo '</table>';
echo '</td></tr>';
}

$table->end();

// Delete DB connection.
mysql_free_result( $result );
}
}

?>

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

More Database Code Articles
More By Codewalkers

blog comments powered by Disqus