File: db_table.php

<?php

// Get adodb package from  http://php.weblogs.com
// The adodb is installed at /home/httpd/www/html and 
// include_path setting in /etc/php.ini has /home/httpd/www/html
include_once('adodb/adodb.inc.php');    // load code common to ADODB

// Get http://phpclasses.upperdesign.com/browse.html/package/21
require_once("table.php");

// This class displays the data from a database table.
// Usage of this class "db_table" is given below:
/*
<?php
require_once("db_table.php");
require_once("GetBrowserType.php");

//for ($sortcolno = 0; $sortcolno < 200; $sortcolno++)
//{
//	$sorder = ${"sortorder".$sortcolno};
//	if (isset($sorder))
//		break;
//}
$tb = new db_table;
$brtype = new GetBrowserType;

$tb->db_type = 'postgres7'; // access, ado, ado_access, vfp, ibase,
		// mssql, mysql, mysqlt, oci8, odbc, oracle, postgres, postgres7, 
		// sybase, db2, fbsql, ado_mssql, odbc_mssql
$tb->db_host = 'localhost';
$tb->db_user = 'xxxx'; // database username
$tb->db_pw = 'xxxx123'; // password 
$tb->db_name = 'xxxx'; // database name
$tb->db_title = 'employee list'; // Table Title header
$tb->db_query = 'select lastname as "lastname  ", firstname, 
		location, extension, cellphone as "cell           ", email as "email (URL Link)" ';
$tb->db_from = ' from employeelist ';
$tb->max_rows = 1000;

//$tb->background = 'BGCOLOR=black';
//$tb->border_color = '#84ADE8'; // red, blue
//$tb->border_width = '8';
//$tb->table_style = 'BORDER="0" CELLPADDING="2" CELLSPACING="1"';
//$tb->cell_style = 'WIDTH="100" BGCOLOR="#cccccc"';
//$tb->content_style = 'STYLE="font: bold 10pt monospace; font-family: Arial; "';
//$tb->col_style[2] = 'BGCOLOR="red"';
//$tb->col_cell_style[2] = 'STYLE="font: bold 12pt monospace; background-color: red; "';
$tb->col_email_href[6] = 1;  // 1 for email, 2 for href

if ($brtype->browser_type == 2 && $brtype->browser_version == 4) // netscape and version 4.x
{
	// set border width to 0.0 for netscape 4.x
	$tb->header_style = "style='background-color: yellow; font: bold 11pt monospace; font-family: Arial; font-weight: bold; border: solid #84ADE8; border-width: 0.0em '  ";
}
else
	$tb->header_style = "style='background-color: yellow; font: bold 11pt monospace; font-family: Arial; font-weight: bold; border: solid #84ADE8; border-width: 0.3em '  ";


// reads value from form fieldname 'sortorder'
if (trim($sortorder) == '')
{
	$sortorder = ' order by 1'; // default is - sort by 1st field

	// Now construct the where condition
	if (strlen(trim($FirstName)) > 0) // reads value from form fieldname 'FirstName'
	{
		if (strlen(trim($LastName)) > 0)
			$tb->db_where = ' where upper(firstname) like upper(\'%' . $FirstName .
			'%\') and upper(lastname) like upper(\'%' . $LastName . '%\') ';
		else
			$tb->db_where = ' where upper(firstname) like upper(\'%' . $FirstName . '%\') ';
	}
	else
	{
		if (strlen(trim($LastName)) > 0)
			$tb->db_where = ' where upper(lastname) like upper(\'%' . $LastName . '%\') ';
	}
}
else // user clicked sort field ...
{
	$colno++;
	$sortorder = ' order by ' . $colno;

	// Remove back-slash \ before single-quote '
	$db_where = stripslashes($db_where);
	$tb->db_where = $db_where;
}
//print  "<br> db_where is $db_where";

$tb->sortorder = $sortorder; // query order by statement

$tb->display();

php?>
*/

// You can also use adodb's display table function rs2html() as 
// show below. But rs2html() is not very clean as this db_table class.
/*
require_once("adodb/tohtml.inc.php"); 	// use adodb's table rendering
ADOLoadCode('postgres7');
$db = ADONewConnection();
$db->Connect('localhost','username','password','databasename');
//$rs = $db->Execute('select col1,col2,col3 from table');
$rs = $db->Execute('select employee_no, lastname, firstname, location, city,
roomno, extension, pager, email, department from employeelist order by 2');
rs2html($rs, 'BORDER=2', array('Title1', 'Title2', 'Title3'));
$rs->Close();
*/

class db_table  extends html_table_pretty
{
	// Public variables - database specific
	var $db_type; // access, ado, ado_access, vfp, ibase,
		// mssql, mysql, mysqlt, oci8, odbc, oracle, postgres, postgres7, 
		// sybase, db2, fbsql, ado_mssql, odbc_mssql
	var $db_host; // database server host name - "localhost"
	var $db_user; // database server user name
	var $db_pw; // database server password 
	var $db_name; // database name
	var $db_title; // Title header of table
	var $db_query; // query to be sent to database
	var $db_from; // query from statement
	var $db_where; // query where statement
	var $sortorder; // query order by statement
	var $max_rows = 500; // limit the maximum number of rows returned from database

	// Public variables - style and colors, default values - do not change. 
	// But you can override from calling function.
	var $background = 'BGCOLOR=black';
	var $border_color = '#84ADE8'; // red, blue
	var $border_width = '8';
	var $table_style = 'BORDER="0" CELLPADDING="2" CELLSPACING="1"';
	var $cell_style = 'WIDTH="100" BGCOLOR="#cccccc"';
	var $content_style = 'STYLE="font: bold 10pt monospace; font-family: Arial; "';
	var $header_style = "style='background-color: yellow; font: bold 11pt monospace; font-family: Arial; font-weight: bold; border: solid #84ADE8; border-width: 0.3em '  "; // "style='color: red; font: italic 14pt sans-serif;'"

	// Private variables and functions always starts 
	// with underscore _ and followed by lowercase letters
	var $_conn;
	var $_recordSet;
	var $_totalrows = 0;
	var $_totalcols = 0;

	// Protected variables and functions always starts 
	// with _T and followed by lowercase letters
	//var $_Tsampvar;

function display() // Public function
{
	$this->_db_connect();
	$this->_show_table();
	$this->_cleanup();
}

function _db_connect() // Private function
{
	$this->_conn = &ADONewConnection($this->db_type);    // create a connection
	if (!$this->_conn->PConnect($this->db_host, $this->db_user, $this->db_pw, $this->db_name))
		die("Cannot connect to database ". $this->db_name);

	// Find the number of rows returned by doing select count(*)
	$query_stmt = "select count(*) " . $this->db_from . $this->db_where;
	$this->_recordSet = $this->_conn->Execute($query_stmt);
	if (!$this->_recordSet)
	{
		print "<br>Fatal Error in count(*): May be a bad query. query_stmt was : $query_stmt";
		exit;
	}
	//print "<br>count(*) is : " . $this->_recordSet->RecordCount();
	$colno = 0;
	if ($this->_recordSet->fields[$colno] > $this->max_rows )
	{
		print "<br><h1>Warning:</h1> <b>Too many rows returned from database.";
		print "<br> Number of rows returned is : " . $this->_recordSet->fields[$colno] . "</b>";
		print "<br><br><b>Solution: Reconstruct your query so that the number of rows returned from database will be smaller.</b>";
		exit;
	}
	/*
	$fld = $this->_recordSet->FetchField($colno);
	$fldmetatype = $this->_recordSet->MetaType($fld->type);
	print "Field value is " . $this->_recordSet->fields[$colno];
	print "Field name is " . $fld->name . '<br>';
	print "Field type is " . $fld->type . '<br>';
	print "Field max length is " . $fld->max_length . '<br>';
	print "Field metatype is " . $fldmetatype . '<br>';
	*/

	$query_stmt = $this->db_query . $this->db_from . $this->db_where . $this->sortorder;
	$this->_recordSet = $this->_conn->Execute($query_stmt);
	if (!$this->_recordSet)
	{
		print "<br>Fatal Error: May be a bad query. query_stmt was : $query_stmt";
		exit;
	}

	$this->_totalrows = $this->_recordSet->RecordCount();
	$this->_totalcols = $this->_recordSet->FieldCount();
	//print "Total rows = " . $this->_totalrows . " columns = " . $this->_totalcols .'<BR>';
	if ($this->_totalrows == -1)
		"RecordCount returns -1<br>";
}

function _show_table() // Private function
{
	// define table size.
	//$this->init(array("cols"=>3, "rows"=>"3"));
	$this->init(array("cols"=> $this->_totalcols));
	$this->add_rows($this->_totalrows + 1);  // add 1 for the header

	// Print the table header
	/*
	print "<form> <b>Sort By:";
	print "<select name='sortorder' size='1' > <option>val1 </option>
		<option>val2 </option> 
		<option>val3 </option> 
		<option>val4 </option> 
		<option>val5 </option> 
		<option>val6 </option> </select> ";
	print "</b></form>";
	*/

	print "<html><head></head> <body> <h3><center><u><b>". strtoupper($this->db_title) . "</b></u></center></h3>";

	/*
	print "<form> <b>";
	print "<INPUT TYPE=SUBMIT NAME='search' VALUE='Firstname'>";
	print "</b></form>";
	*/

	print "<form action='emp.php?sortorder=1'>";
	print "<b>";

	print "<center><font color='green'>(Click on title field buttons to sort ";
	print "- To make corrections email to </font> <a href='mailto:user@company.com'>Name</a><font color='green'>)</font></center>";
	print "<center>";
	for ($colno = 0; $colno <  $this->_totalcols; $colno++)
	{
		$tbcol = $colno + 1;
		$fld = $this->_recordSet->FetchField($colno);
		$fldmetatype = $this->_recordSet->MetaType($fld->type);
		/*
		print_r ($fld);
		print "Field name is " . $fld->name . '<br>';
		print "Field type is " . $fld->type . '<br>';
		print "Field max length is " . $fld->max_length . '<br>';
		print "Field metatype is " . $fldmetatype . '<br>';
		*/

		$this->cell[1][$tbcol]["cell_style"] = 'CLASS="header"';
		//$this->cell[1][$tbcol]["style"] = $this->header_style ;
		//$this->cell[1][$tbcol]["content"] = $fld->name;

		// When a form is submitted, the current value of each INPUT element
		// within the FORM is sent to the server as name/value pairs. The 
		// INPUT element's NAME attribute provides the name used. The value sent
		// depends on the type of form control and on the user's input.
		// Hence use the surrounding <form> tags, surrounding <td> tags ... 
		$this->cell[1][$tbcol]["cell_form"] = 'Y';
		$this->cell[1][$tbcol]["content"] = "<INPUT TYPE='SUBMIT'" . 
			" title='Click on this to sort' id='idval' " . 
			" NAME='sortorder' "  
			. $this->header_style . " VALUE='". strtoupper($fld->name) 
			. "'> <input type='hidden' name='colno' value='"
			. $colno. "'>  <input type='hidden' name='db_where' value=\"  " 
			. $this->db_where . "  \">  ";

		/*
		// If you do not use surrounding <form> tags, on submit it sends all the input fields
		$this->cell[1][$tbcol]["content"] = "<form><BUTTON NAME='sortorder' TYPE='submit' VALUE='x123' " 
				. " TITLE='" . $fld->name .  "' "
				. " onClick='submit();' "
				. $this->header_style 
				. "> <b>" 
				. strtoupper($fld->name) . "</b> </BUTTON> </form> " ;
		*/
	}

	for ($rowno = 2; !$this->_recordSet->EOF; $rowno++)
	{
		for ($colno = 0; $colno <  $this->_totalcols; $colno++)
		{
			$tbcol = $colno + 1;
			// You can specify individual cell properties
			/*
			if ($rowno == 2 && $colno == 3)
			{
				$this->cell[$rowno][$tbcol]["rowspan"]=2;
				$this->cell[$rowno][$tbcol]["colspan"]=3;
			}
			*/

			// define fancy styles for individual cells.
			/*
			$this->cell[$rowno][$tbcol]["style"] = 'STYLE="color: red; font: italic 18pt sans-serif;"';
				// 'STYLE="font: 18pt monospace;"', 'STYLE="background: red"'
				// 'STYLE="color: white; font: bold 18pt serif;"'
			$this->cell[$rowno][$tbcol]["cell_style"] = 'ALIGN="right" bgcolor="blue"';
			*/

			// Define table content
			/*
			print $this->_recordSet->fields[0].' '.$this->_recordSet->fields[1].'<BR>';
			print $this->_recordSet->fields[2].' '.$this->_recordSet->fields[3].'<BR>';
			print_r( $this->_recordSet->fields); print '<BR>';
			*/

			// In netscape if cell is blank the background is black, put some blankspace value
			if (trim($this->_recordSet->fields[$colno]) == "")
				$this->cell[$rowno][$tbcol]["content"] = "&nbsp;";
		 	else	
				$this->cell[$rowno][$tbcol]["content"] = $this->_recordSet->fields[$colno];
		}

		$this->_recordSet->MoveNext();
	}

	// code and display.
	$this->code_pretty();
	$this->display_pretty();
	print "</center> </b> </form> </body> </html>";
}

function _cleanup()  // Private function
{
	$this->_recordSet->Close(); // optional
	$this->_conn->Close(); // optional
}

} // end of class db_table
php?>