#native_company# #native_desc#

A mySQL Table to HTML Table Snippet

By Elliott Edwards
on May 7, 2002

Version: 1.2

Type: Full Script

Category: Databases

License: BSD License

Description: A General case table display snippet that allows for paging, sorting by column, and customizable HTML formatting. Brought to you by www.hachisoft.com

	    _/                    _/
	   _/ www.hachisoft.com  _/
	  _/      Presents:     _/ 
	 _/                    _/ 
	//A mySQL Table Displaying and Paging Snippet Version 1.1
	//by Elliott Edwards ([email protected])
	//Standard Disclaimer:
	//This code snippet is provided AS IS. Neither Elliott Edwards nor Hachisoft are
	//to be held responsible for any failures or security flaws in this code. Use at your
	//own risk.
	//Distribution: If you want to use this code commercially or share it with friends,
	//Go Ahead. Just kindly give credit where it is due: (Elliott Edwards & Hachisoft). Thanks
	//If you appreciate this snippet or have comments, email [email protected] 
	//Updates and improvements are already in the works. see www.hachisoft.com for more details.
	//TO INSTALL: Put this script in an appropriate file, and then
	// refer to it in a manner somewhat like the following code:
		//include our snippet
		require ('tableview.php'); //Or whatever you name the snippet
		//Override some defaults
		$tblQuery['columns']='*';						//The columns to select
		$tblQuery['tblName']='HachiSoft';				//The table to select from
		$tblQuery['where']="WebDesign='Excruciating'";	//The Where clause for the SELECT
		$tblQuery['viewSize']=10;						//How many records to a page
		//Pass along any self-referral parameters for table ordering and paging
		if (isset($orderBy))
			$tblQuery['orderBy']=$orderBy;		//The column to orderBy
		if (isset($viewStart))
			$tblQuery['viewStart']=$viewStart;  //The starting offset for record paging
		if (isset($orderDir))
			$tblQuery['orderDir']=$orderDir;    //Whether to sort ascending or descending ('ASC' or 'DESC')
		//To Set a default self-referral parameter:
		//if (isset($orderDir))
		//	$tblQuery['orderDir']=$orderDir;
		//	$tblQuery['orderDir']='DESC';
		//To set column-specific formatting and processing:
			  'Column1'=>array( //The name of the column is case-sensitive	
			  					'formatBegin'=>'<B>',		   //prefix formatting inside column
			  					'formatEnd'=>'</B>',		   //sufffix formatting inside column
			  					'colBegin'=>'<TD ALIGN=left>', //override the default column formatting prefix
			  					'colEnd'=>'</TD>',			   //override the default column formatting suffix
			  					'callback'=>'strrev'		   //output data after being processed by the named function.		
			  				),								   //Note: callback needs to be in the format:  function (p1), return a string, and can be any function (even user)
			  'Column2'=>array( //Second column-specific formatter array (limited in number only by the column count)
			  				'colBegin'=>'<TD ALIGN=left>',
		//Call the table generation function with our (modified) default parameters
		echoTable($tblFormat, $tblQuery, $dbData);
	//Begin actual code///////

	//Initialize Default Variables
	//The Database Connection Information
	if (!isset($dbData))
			'Host'=>'yourDBServer',			//DBHost to connect to
			'UserName'=>'hachisoft',		//User To Logon as
			'Password'=>'agoodpassword',	//User's Password
			'dbName'=>'Crickey'				//Default Database
	//The Customizable Table Formatting information
	if (!isset($tblFormat))
			'header'=>'',			//An Optional header for the table
			'footer'=>'',			//An Optional footer for the table
			'headingBegin'=>'<TH vAlign=top align=center bgColor=silver>', //Column heading format prefix
			'headingEnd'=>'</TH>',	//Column heading format suffix
			'tblBegin'=>'<TABLE borderColor=gray cellSpacing=0 cellPadding=5 border=1>', //Table Format prefix
			'tblEnd'=>'</TABLE>',	//Table Format suffix
			'rowBegin'=>'<TR>',		//Row Format prefix
			'rowEnd'=>'</TR>',		//Row Format suffix
			'colBegin'=>'<TD vAlign=top align=center bgcolor=#eeeeee>', //Column format prefix
			'colEnd'=>'</TD>',		//Column Format suffix
			'nextCode'=>'Next',		//HTML inside the "Next Page" Link (for paging)
			'prevCode'=>'Prev',		//HTML inside the "Prev Page" Link (for paging)
			'ascendCode'=>'Ascend',	//HTML inside the "Ascend" Link for column sorting (can be IMG tag)
			'descendCode'=>'Descend'//HTML inside the "Descend" Link for column sorting (can be IMG tag)
	//The Specifics of the underlying SQL query	
	if (!isset($tblQuery))
			'columns'=>'',		//The columns to be selected (* for all)
			'tblName'=>'',		//The table to select from
			'where'=>'',		//Conditional selection parameter
			'orderBy'=>'',		//Row ordering
			'orderDir'=>'ASC',	//Row ordering direction
			'viewStart'=>0,		//Index of first record to show in the view table
			'viewSize'=>10,		//The maximum number of records allowed in the view table
	//Purpose: to Echo a table's data
	// Inputs:	$tblFormat:	The associative array of table formatting information
	//						as described above.
	//			$tblQuery:	An associative array of the SQL query specifics driving this table
	//						as described above.
	//			$dbData:	An associative array of the Database connection information
	//						as described above.
	// Usage: Inclusion of this snippet defines the variable defaults ($tblFormat, $tblQuery, & $dbData)
	//		  Manipulate these data items and then pass them to this function.
	//Outputs: NONE		
	function echoTable( $tblFormat, $tblQuery, $dbData)
		//Verify Parameters
		if (strcasecmp($tblQuery['orderDir'], 'asc')==0 or strcasecmp($tblQuery['orderDir'], 'desc')==0)
			echo "<B>Error:</B> "orderDir" parameter must be either ASC or DESC";
		if (is_numeric($tblQuery['viewStart']))
			echo "<B>Error:</B> "viewStart" parameter must be an integer value";
		if (!$link=mysql_connect ($dbData['Host'], $dbData['UserName'], $dbData['Password']))
				echo "Could not connect to "".$dbData['Host'].""n";
		if (!mysql_select_db($dbData['dbName']))
				echo "Could not select "".$dbData['dbName']."" as Database.n";
		$endIndex = $tblQuery['viewStart']+$tblQuery['viewSize'];
		$sSQL='SELECT '.$tblQuery['columns'].' FROM '.$tblQuery['tblName'].' '
		if (!$result=mysql_query($sSQL))
				echo "<B>Error:</B><BR>Could Not "$sSQL".n";
		$rowCount = mysql_num_rows($result);
		if (isset($tblQuery['orderBy']) and strlen($tblQuery['orderBy'])>0)
			//$fields = mysql_list_fields($dbData['dbName'], $tblQuery['tblName'], $link);
			$fcount = mysql_num_fields($result);
			$valid = false;
			for ($i=0;$i<$fcount;$i++)
					if (strcmp(mysql_field_name($result, $i) , $tblQuery['orderBy'])==0)
						$valid = true;
			if (!$valid)
				echo "<B>Error:</B>"orderBy" parameter must be a valid Column in this table";
			$orderBy='ORDER BY '.$tblQuery['orderBy'].' '.$tblQuery['orderDir'];
		$sSQL='SELECT '.$tblQuery['columns'].' FROM '.$tblQuery['tblName'].' '
			.$tblQuery['where'].' '.$orderBy.' '
			.' LIMIT '.$tblQuery['viewStart'].', '.$tblQuery['viewSize'];	
		if (!$result=mysql_query($sSQL))
				echo "<B>Error:</B><BR>Could Not "$sSQL".n";
		echo $tblFormat['header'];
		echo $tblFormat['tblBegin'];
		if (mysql_num_rows($result)==0)	
			echo $tblFormat['rowBegin'].$tblFormat['colBegin'].'Empty Table'.$tbl['colEnd'].$tbl['rowEnd']."n";
			echoRows($result, $tblFormat, $tblQuery);
		echo $tblFormat['tblEnd']."n";
		echo $tblFormat['footer']."n";
		if ($tblQuery['viewStart']>0)
			$URL = buildURL($tblQuery, 'viewStart', $prevIndex);
			$prev = "<A HREF="$URL">{$tblFormat['prevCode']}</A>";
			$prev = '';
		if ($endIndex<$rowCount)
			$URL = buildURL($tblQuery, 'viewStart', $nextIndex);
			$next = "<A HREF="$URL">{$tblFormat['nextCode']}</A>";
			$next = '';
		echo "<SMALL>$prev ({$tblQuery['viewStart']}-{$endIndex}) of $rowCount $next</SMALL>";
		mysql_close ($link);
	//Purpose: Keep track of current SQL query options
	//		   And build a self-referring URL with one additional
	//		   option change.
	//  Input: $tblQ(uery): The Table's SQL Query information as it is
	//						currently and is defined above
	//		   $changeIndex: The associative array "index" of the item that would
	//						 be changed by this link.
	//		   $value:	The new value of the item to be changed by this link.
	// Output: A string version of the new URL, complete with parameters			
	//			"myscript.php?orderBy=Name&viewStart=20" For example
	function buildURL($tblQ, $changeIndex, $value)
		$tblQuery = $tblQ;
		if ($tblQuery['orderBy']!='')
			$params .= "&orderBy={$tblQuery['orderBy']}";
		if (isset($tblQuery['viewStart']))
			$params .= "&viewStart={$tblQuery['viewStart']}";
		if ($tblQuery['orderDir']!='')
			$params .= "&orderDir={$tblQuery['orderDir']}";
		if (isset($params))
			$URL .= '?'.$params;
		return $URL;
	//Purpose: To take a result set from an SQL query and iterate 
	//		   Field information and data into a query
	function echoRows($result, $tblFormat, $tblQuery)
		$colCount = mysql_num_fields ($result);
		$rowCount = mysql_num_rows ($result);
		$i = 0;
		if (strlen($tblQuery['orderBy'])>0)
			if (strcmp($tblQuery['orderDir'],'ASC')==0)
				$orderDirURL = buildURL($tblQuery, 'orderDir', 'DESC');
				$orderLink = "<A HREF="$orderDirURL">{$tblFormat['descendCode']}</A>";
			else if (strcmp($tblQuery['orderDir'],'DESC')==0)
				$orderDirURL = buildURL($tblQuery, 'orderDir', 'ASC');
				$orderLink = "<A HREF="$orderDirURL">{$tblFormat['ascendCode']}</A>";
		while ($i < $colCount) 
			$fname = mysql_field_name ($result, $i);
			$URL = buildURL($tblQuery, 'orderBy', $fname);
			echo $tblFormat['headingBegin'];
			if (strcmp($fname, $tblQuery['orderBy'])==0)
				echo "$orderLink&nbsp";
			echo "<A HREF="$URL">$fname</A>".$tblFormat['headingEnd']."n";
	    while($row = mysql_fetch_row($result))
			echo $tblFormat['rowBegin'];
			for ($i=0;$i<$colCount;$i++)
				$fname = mysql_field_name ($result, $i);
				$colFormatBegin = '';
				$colFormatEnd = '';
				$colBegin = $tblFormat['colBegin'];
				$colEnd = $tblFormat['colEnd'];
				$data = $row[$i];
				if (isset($tblFormat['colFormat'])&& isset($tblFormat['colFormat'][$fname]))
					if (isset($tblFormat['colFormat'][$fname]['formatBegin']))
						$colFormatBegin = $tblFormat['colFormat'][$fname]['formatBegin'];
					if (isset($tblFormat['colFormat'][$fname]['formatEnd']))
						$colFormatEnd = $tblFormat['colFormat'][$fname]['formatEnd'];
					if (isset($tblFormat['colFormat'][$fname]['colBegin']))
						$colBegin = $tblFormat['colFormat'][$fname]['colBegin'];
					if (isset($tblFormat['colFormat'][$fname]['colEnd']))
						$colEnd = $tblFormat['colFormat'][$fname]['colEnd'];	
					if (isset($tblFormat['colFormat'][$fname]['callBack']))
						$data = $callback($data);
				echo $colBegin.$colFormatBegin.$data.$colFormatEnd.$colEnd;
			echo $tblFormat['rowEnd'];