<?php
/**
* The phpSortable class
* phpSortable is a Class that shows database query results in a HTML Table.<br>
* As the name Sortable says, the shown table displays also the columns <br>
* headers as links, so the query result of a table can be sorted.<br>
* Furthermore it gives the possibility to link the single rows with diferents<br>
* other scripts types, like for editing, deleting, inserting and so on.<br><br>
* And if there are too many results to be displayed, the phpSortable <br>
* class also gives the user the possibility to select how much rows have <br>
* to be displayed. The table can also be navigated with the last and next links.<br>
* In this second Version of the Class, it is possible to work with database abstraction.
* Therefore it is required to work with the class activeDBLib.
*
* <b>requires:</b> <br>
* - class.phpSortColumn.php <br>
* - img/asc.gif <br>
* - img/desc.gif <br>
* - img/edit.gif <br>
* - img/delete.gif <br>
* - img/add.gif <br>
* - activeDBLib/activeDBLib.php<br>
*
* last review: 07.02.07 <br><br>
*
* @author Wagner O. Wutzke
*
* @version 2.0
*
* @link http://www.phpclasses.org/browse/package/3704.html
*
* @package phpSortable
*
*/
require "class.phpSortColumn.php";
require_once "activeDBLib/activeDBLib.php";
class phpSortable {
/**
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* user defined properties
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
/**
* @var activeDBLib object after executing a sql query
*/
var $db = NULL;
/**
* @var string key name for url links
*/
var $urlKeyName = "";
/**
* @var string name of the table column to used as key value
*/
var $tableKeyName = "";
/**
* @var string link for the edition page
*/
var $editLink = "";
/**
* @var string target window for the edition page
*/
var $editTarget = "";
/**
* @var string window format for the edition page.
* Example:
* <code>$sortable->editWindowFormat = "'width=300,height=400'";</code>
*/
var $editWindowFormat = "";
/**
* @var string link for the the delete page or script
*/
var $deleteLink = "";
/**
* @var string link for the the new entries page
*/
var $addLink = "";
/**
* @var string target window for the the new entries page
*/
var $addTarget = "";
/**
* @var string onMouseOver text for the the new entries link
*/
var $addText = "";
/**
* @var string confirm message shown on clicking the edit link
*/
var $editPromptMsg = "";
/**
* @var string confirm message shown on clicking the delete link
*/
var $deletePromptMsg = "";
/**
* @var string image filename for the ascending arrow on the sorting header
*/
var $asc_img = "";
/**
* @var string image filename for the descending arrow on the sorting header
*/
var $desc_img = "";
/**
* @var string image filename for the edit image
*/
var $edit_img = "";
/**
* var string image filename for the delete image
*/
var $delete_img = "";
/**
* @var string image filename for the new entries image
*/
var $add_img = "";
/**
* @var string cellpadding table parameter
*/
var $cellpadding = "";
/**
* @var string cellspacing table parameter
*/
var $cellspacing = "";
/**
* @var string tableborder table parameter
*/
var $tableborder = "";
/**
* @var string title shown on the table header
*/
var $tableTitle = "";
/**
* @var string default number of rows to be displayed on calling printTable()
*/
var $defaultRowsNum = "";
/**
* @var string directory name for the images
*/
var $imgFilePath = "";
/**
* @var string full path and file name for the css format file
*/
var $cssFilePath = "";
/**
* @var string message to be displayed if the table has no results
*/
var $emptyMsg = "";
/**
* @var phpSortColumn[] array with phpSortColumn Objects for the table
*/
var $columns = NULL;
/**
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* vars just for internal use
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
/**
* @var string contains the script name with the appended
* urls vars given by the application
*/
private $selfLink = "";
/**
* @var string contains the urls vars given by the application
*/
private $urlVars = "";
/**
* @var string total number of rows found in the query
*/
private $totalRows = "";
/**
* @var string current number of displayed rows
*/
private $shownRows = "";
/**
* @var string current number of columns
*/
private $colsNum = "";
/**
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* URL vars
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
/**
* @var string current disered number of rows
*/
private $rows = "10";
/**
* @var string where to start in the query
*/
private $begin = "0";
/**
* @var string SORT BY query value
*/
private $sortby = "";
/**
* @var string ascending or descending sort
*/
private $sortdir = "DESC";
/**
* @access public
* @return phpSortable default constructor for this class
* @param activeDBLib $db activeDBLib object with an executed query
* @param phpSortColumn[] array with the defined phpSortColumn objects
*/
public function __construct($db = NULL, $columns = NULL) {
if (!($db instanceof activeDBLib)) {
print "<p>Error: Passed <strong>\$db</strong> argument must be a " .
"valid <strong>activeDBLib</strong> instance!</p>";
exit();
}
if ($columns == NULL) {
print "<p>Error: Passed <strong>\$columns</strong> argument might be a " .
"valid array of phpSortColumn objects!</p>";
exit();
}
$this->db = $db;
$this->columns = $columns;
$this->tableTitle = $this->tableName; # default table title is the table name
/**
* * * * * * * * * * * * * * * * *
* Vars Initialization
* * * * * * * * * * * * * * * * *
*/
$this->imgFilePath = "phpSortable/img/";
$this->cssFilePath = "phpSortable/phpSortable.css";
$this->asc_img = "asc.gif";
$this->desc_img = "desc.gif";
$this->add_img = "add.gif";
$this->delete_img = "delete.gif";
$this->edit_img = "edit.gif";
$this->defaultRowsNum = 10;
$this->emptyMsg = "No data to be shown.";
$this->editWindowFormat = "";
}
/**
* @access public
* @return void set the format strings for the table
* @param string $cellpadding cellpading table parameter
* @param string $cellspacing cellspacing table parameter
* @param string $border border table parameter
*/
public function setTableFormat($cellpadding, $cellspacing, $border) {
$this->cellpadding = $cellpadding;
$this->cellspacing = $cellspacing;
$this->tableborder = $border;
}
/**
* @access private
* @return void this is the main function, which should be called by
* the application do display the html table
*/
public function printTable() {
$this->sortby = $_GET['sortby']; // column to sort
$this->rows = $_GET['rows']; // amount of rows to be shown
$this->begin = $_GET['begin']; // where to begin in the total of rows
$this->sortdir = $_GET['sortdir']; // sort direction
# picks the url vars from the application, if they exist
$this->selfLink = $_SERVER['SCRIPT_NAME'] . "?";
$this->urlVars = "";
foreach ($_GET as $key => $value) {
if (($key != "sortby") && ($key != "rows") && ($key != "begin") && ($key != "sortdir")) {
$this->selfLink .= "&" . $key . "=" . $value;
$this->urlVars .= "&" . $key . "=" . $value;
}
}
#default begin value is 0
if (strlen($this->begin) < 1)
$this->begin = 0;
# table max rows is default $this->defaultRowsNum
if (strlen($this->rows) < 1) {
$this->rows = $this->defaultRowsNum;
$this->begin = 0;
}
# changes the sort direction for the columns
$this->sortdir = ($this->sortdir == "ASC") ? "ASC" : "DESC";
$this->sortimg = ($this->sortdir == "ASC") ? $this->asc_img : $this->desc_img;
$this->totalRows = $this->db->rowCount();
$this->colsNum = count($this->columns);
print "\n\n<!--- HIER STARTS THE SORTTABLE OUTPUT --->\n";
# declare the css file if it is defined, else use the default
print "<link href=\"" . $this->cssFilePath. "\" rel=\"stylesheet\" type=\"text/css\">";
print "\n\n<div class=\"sortable\">";
$this->printTitle();
# if table is empty, shows the Empty Message
if ($this->totalRows < 1) {
$this->printEmptyMsg();
}
else
{
$this->printHeader();
$this->printContent();
}
$this->printFooter();
print "\n\n</div>";
}
/**
* @access private
* @return void print the html table title
*/
private function printTitle() {
print "\n\n<!--- TITLE TABLE --->\n";
print "\n\t<table class=\"titleTable\">";
print "\n\t\t<tr>";
# if defined, prints the add icon and its link
if (strlen($this->addLink) > 0) {
print "\n\t\t\t<td width=\"2%\">";
print "\n\t\t\t\t<a href=\"" . $this->addLink . "\"";
if (strlen($this->addTarget) > 0)
print " target=\"" . $this->addTarget . "\"";
print ">";
print "<img src=\"" . $this->imgFilePath . $this->add_img . "\" border=\"0\" title=\"" . $this->addText . "\"></a>";
print "\n\t\t\t</td>";
}
# prints the table title
print "\n\t\t\t<td>" . $this->tableTitle . "</td>";
print "\n\t\t</tr>";
print "\n\t</table>";
}
/**
* @access private
* @return void print the table title and column titles
* with the links for the sorting function
*/
private function printHeader() {
# gets the table format in a string together
$formatString = " ";
if (strlen($this->cellpadding) > 0)
$formatString .= "cellpadding=\"" . $this->cellpadding . "\" ";
if (strlen($this->cellspacing) > 0)
$formatString .= "cellspacing=\"" . $this->cellspacing . "\" ";
if (strlen($this->tableborder) > 0)
$formatString .= "border=\"" . $this->tableborder . "\"";
print "\n\n<!--- HEADER AND CONTENT TABLE --->\n";
print "\n\t<table class=\"contentTable\" " . $formatString . ">";
print "\n\t\t<tr class=\"headerRow\">";
# looping over the $this->columns array. It contains all the column objects
foreach ($this->columns as $column) {
# builds the link for sort function
$sortLink = $this->selfLink . "&rows=" . $this->rows . "&begin=" . $this->begin;
$sortLink .= ($this->sortdir == "ASC") ? "&sortdir=DESC" : "&sortdir=ASC";
$sortLink .= "&sortby=" . $column->name;
# prints the title with the sort link, plus sort rule (asc or desc)
print "\n\t\t\t<td class=\"headerCell\" nowrap>";
print "\n\t\t\t\t<a href=\"" . $sortLink . "\">" . $column->title . "</a>";
if ($this->sortby == $column->name)
print "\n\t\t\t\t <img src=\"" . $this->imgFilePath . $this->sortimg . "\">";
print "\n\t\t\t</td>";
}
if (strlen($this->editLink) > 0)
print "\n\t\t\t<td> </td>";
if (strlen($this->deleteLink) > 0)
print "\n\t\t\t<td> </td>";
print "\n\t\t</tr>";
}
/**
* @access private
* @return void print the tables content and buttons
* for delete and edit of single rows
* @param $query MySQL query result from a mysql_query() call.
* This is the 2 dimensional array to be fetched.
*/
private function printContent() {
$count = 0;
$results = array();
while ($row = $this->db->GetArray()) {
$results[] = $row;
$sortArray[] = $row[$this->sortby];
}
$sortDir = ($this->sortdir == "DESC") ? SORT_DESC : SORT_ASC;
array_multisort($sortArray, $sortDir, $results);
print "\n\n<!--- QUERY CONTENT --->\n";
for ($i = $this->begin; ($i < $this->begin + $this->rows) && ($i < count($results)); $i++) {
$row = $results[$i];
# changes the row color with css style
$style = ($count % 2 == 0) ? "contentRow1" : "contentRow2";
print "\n\t\t<tr class=\"" . $style . "\">";
foreach ($this->columns as $key => $column) {
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # PRINT THE COLUMN DATA # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
$readVal = $row[$column->name];
# if dontShowIf is set and it is true, just print the empty cell and continue
if (strlen($column->dontShowIf) > 0 && eval($column->dontShowIf)) {
print "\n\t\t\t<td class=\"contentCell\"> </td>";
continue;
}
print "\n\t\t\t<td class=\"contentCell\">";
# evaluate the format function if there is one
# the function shall already execute the print statement!!!
if (strlen($column->formatFunction) > 0 && $column->type != "link") {
print eval ($column->formatFunction);
}
# if column data type is "date"
else if ($column->type == "date") {
print $this->dateFormat($row[$column->name], 2);
}
# if column data type is "link"
else if ($column->type == "link") {
# append the data to the link, if desired
if ($column->appendDataToLink == true) {
$link = $column->link . $row[$column->name];
}
# else, append the key to the link
else {
$link = $column->link . "&" . $this->urlKeyName . "=" . $row[$this->tableKeyName];
}
# append the second key and its value if defined
if (!empty($column->secondKey) && !empty($column->secondKeyColumn)) {
$link .= "&" . $column->secondKey . "=" . $row[$column->secondKeyColumn];
}
# print the passed link
print "\n\t\t\t\t<div align=\"center\"><a href=\"" . $link . "\"";
if (strlen($column->target) > 0) {
print " target=\"" . $column->target . "\"";
}
print ">";
# print the defined image for this link, if there is one
if (!empty($column->img)) {
print "\n\t\t\t\t\t<img src=\"" . $column->img . "\" border=\"0\">";
}
else if (strlen($column->formatFunction) > 0) {
print ($column->formatFunction);
}
else {
print "\n\t\t\t\t\t" . $row[$column->name];
}
print "\n\t\t\t\t</a></div>";
}
# no column type
else
print "\n\t\t\t\t" . $row[$column->name];
print "\n\t\t\t</td>";
}
# gets the key and its value for this row
$key = $this->urlKeyName;
$keyVal = $row[$this->tableKeyName];
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # PRINT THE EDIT AND DELETE BUTTONS DATA # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # PRINT THE EDIT BUTTON # # # # # #
if (strlen($this->editLink) > 0) {
if (strstr($this->editLink, "?"))
$eLink = $this->editLink . "&" . $key . "=" . $keyVal;// . $this->urlVars;
else
$eLink = $this->editLink . "?" . $key . "=" . $keyVal;// . $this->urlVars;
print "\n\t\t\t<td width=\"2%\" alt=\"edit\" title=\"edit\">";
print "\n\t\t\t\t<a";
# print the link, only if no wondow format was defined
if (strlen($this->editWindowFormat) < 1) {
print " href=\"" . $eLink . "\"";
# check for a window target
if (strlen($this->editTarget) > 0) {
print " target=\"" . $this->editTarget . "\"";
}
}
print ">";
# print the image, if there is one
print "\n\t\t\t\t\t<img src=\"" . $this->imgFilePath . $this->edit_img . "\" border=\"0\"";
if (strlen($this->editWindowFormat) > 0) {
print " onClick=\"";
if (strlen($this->editPromptMsg) > 0) {
print "javascript: if (confirm('" . $this->editPromptMsg . "')) ";
}
print "window.open('" . $eLink . "', '" . $this->editTarget . "', " . $this->editWindowFormat .");\"";
}
# if a prompt message was defined, print it
else if (strlen($this->editPromptMsg) > 0)
print " onClick=\"return confirm('" . $this->editPromptMsg . "')\"";
print ">";
print "\n\t\t\t\t</a>";
print "\n\t\t\t</td>";
}
# # # # # # PRINT THE DELETE BUTTON # # # # # #
if (strlen($this->deleteLink) > 0) {
if (strstr($this->deleteLink, "?"))
$dLink = $this->deleteLink . "&" . $key . "=" . $keyVal;// . $this->urlVars;
else
$dLink = $this->deleteLink . "?" . $key . "=" . $keyVal;// . $this->urlVars;
print "\n\t\t\t<td width=\"2%\" alt=\"delete\" title=\"delete\">";
print "\n\t\t\t\t<a href=\"" . $dLink . "\">";
# print the image, if there is one
print "\n\t\t\t\t\t<img src=\"" . $this->imgFilePath . $this->delete_img . "\" border=\"0\"";
# if a prompt message was defined, print it
if (strlen($this->deletePromptMsg) > 0)
print " onClick=\"return confirm('" . $this->deletePromptMsg . "')\"";
print ">";
print "\n\t\t\t\t</a>";
print "\n\t\t\t</td>";
}
print "\n\t\t</tr>";
$count++;
}
print "\n\t<table>";
}
/**
* @access private
* @return void print the tables navigation links like next,
* last and amount of rows in a single page
*/
private function printFooter() {
# calculates the page navigation position in the total of rows
$from = ($this->totalRows == 0) ? 0 : $this->begin + 1;
$to = (($this->rows + $this->begin) > $this->totalRows) ? $this->totalRows : $this->rows + $this->begin;
$total = $this->totalRows;
print "\n\n<!--- FOOTER TABLE --->\n";
print "\n\t<table class=\"fooTable\" border=\"0\" cellspacing=\"0\" cellpadding=\"5\">";
# prints the number of shown rows and the position by the navigation => "6 to 10 of 20"
print "\n\t\t<tr class=\"fooRow\">";
print "\n\t\t\t<td class=\"fooNavLeft\">" . $from . " to " . $to . " of " . $total . "</td>";
# last and next begin variable for navigation links
$nextBegin = $this->begin + $this->rows;
$lastBegin = $this->begin - $this->rows;
# builds the link for table navigation function
$nextLink = $this->selfLink . "&rows=" . $this->rows . "&sortby=" . $this->sortby . "&sortdir=" . $this->sortdir . "&begin=" . $nextBegin;
$lastLink = $this->selfLink . "&rows=" . $this->rows . "&sortby=" . $this->sortby . "&sortdir=" . $this->sortdir . "&begin=" . $lastBegin;
# prints the links for navigation.
# if no more rows to list, just print the text without link
print "\n\t\t\t<td class=\"fooNavMiddle\">";
if ($lastBegin >= 0)
print "\n\t\t\t\t<a href=\"" . $lastLink . "\"><< last " . $this->rows . "</a>";
else
print "\n\t\t\t\t<< last " . $this->rows;
print "\n\t\t\t\t "; # a space between the 2 links
if ($nextBegin < ($this->totalRows + 1))
print " \n\t\t\t\t<a href=\"" . $nextLink . "\">next " . $this->rows . " >></a>";
else
print " \n\t\t\t\tnext " . $this->rows . " >>";
print "\n\t\t\t</td>";
# builds the links for amount of rows in a table
$rowsLink = $this->selfLink . "&sortby=" . $this->sortby . "&sortdir=" . $this->sortdir . "&begin=0";
# prints the links with the rows number to be shown
print "\n\t\t\t<td class=\"fooNavRight\">";
print "\n\t\t\t\t<a href=\"" . $rowsLink . "&rows=5\">5</a>, ";
print "\n\t\t\t\t<a href=\"" . $rowsLink . "&rows=10\">10</a>, ";
print "\n\t\t\t\t<a href=\"" . $rowsLink . "&rows=20\">20</a>, ";
print "\n\t\t\t\t<a href=\"" . $rowsLink . "&rows=30\">30</a>, ";
print "\n\t\t\t\t<a href=\"" . $rowsLink . "&rows=50\">50</a> ";
print "\n\t\t\t</td>";
print "\n\t\t</tr>";
print "\n\t</table>";
}
/**
* @access private
* @return string convert the date string "dateStr" to the desired format<br>
* for func = 1 => year-month-day<br>
* for func = 2 => day.month.year
*/
private function dateFormat($dateStr, $func) {
if (empty ($dateStr)) {
return "";
}
if ($func == 1) { //insert conversion
list ($day, $month, $year) = split('[.]', $dateStr);
$date = "$year-$month-$day";
return $date;
}
if ($func == 2) { //output conversion
list ($year, $month, $day) = split('[-]', $dateStr);
$date = "$day.$month.$year";
$date = str_replace("..", "", $date);
return $date;
}
}
/**
* @access private
* @return string print the empty message
*/
private function printEmptyMsg() {
print "\n\t<table class=\"contentTable\" " . $$this->formatString . ">";
print "\n\t\t<tr class=\"contentRow1\"><td> </td></tr>";
print "\n\t\t<tr class=\"contentRow1\">";
print "\n\t\t\t<td class=\"emptyMsg\">" . $this->emptyMsg . "</td>";
print "\n\t\t</tr>";
print "\n\t\t<tr class=\"contentRow1\"><td> </td></tr>";
print "\n\t</table>";
}
/**
* @return void prints the string until the given length "len". The rest of
* the string won´t be displayed.
* @param string $string the string to be displayed
* @param int $len length of the displayed string
*/
private function printShortString($string, $len) {
print substr($string, 0, $len);
}
}
?>