<?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"] = " ";
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?>
|
Download
