Location: PHPKode > scripts > phpSortable > phpsortable/phpSortable/class.phpSortable.php
<?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>&nbsp;</td>";
		if (strlen($this->deleteLink) > 0)
			print "\n\t\t\t<td>&nbsp;</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\">&nbsp;</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 &nbsp; &nbsp; &nbsp; &nbsp; "; # 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>&nbsp;</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>&nbsp;</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);
	}

}
?>
Return current item: phpSortable