Abstraction Layers
|
|
|
|
<?
/********************************************************************/
/* Filename : db.class.php */
/* Created : 20/01/06 */
/* Created By : Massimiliano Wosz */
/* Version : 1.01 */
/********************************************************************/
/********************************************************************/
/* This DBClass allows a coder not only to easily run SQL */
/* queries and catch/log errors in case of failure but also to */
/* easily get result data represented into php arrays or */
/* html tables with a built-in pagination control. */
/********************************************************************/
/********************************************************************/
/* Additions and modifications are allowed as long as this */
/* message remains. */
/* If you do make any change please contact me at this address. */
/* */
/* massimiliano.wosz@evolvetechnologies.it */
/* */
/********************************************************************/
/********************************************************************/
/* CUT'N'PASTE SAMPLES */
/********************************************************************
//Create new DB object
$database=new db("localhost","root","root","test");
//Simple values iteration.
echo $database->get_db_value("select field from table");
while($database->next_value())
echo $database->get_db_value();
//Simple rows iteration.
print_r($database->get_db_row("select * from table"));
while($database->next_value())
print_r($database->get_db_row());
//Get_rowno sample.
//this sample will print out the row number 2 of the
//current_result resource.
$database->get_db_row("select * from table");
print_r($database->get_rowno(2));
//fill_result_array sample.
//this sample will print the resulting array filled
//with all the values from the query using the fild n.1
//as array value and the field n.0 as array key.
print_r($database->fill_result_array("select * from table",1,0));
//pagination sample.
//this sample will set the number of row displayed per page to 5,
//the current page to che $_REQUEST['page'] value
//and print out the results.
$database->set_pagination(5,$_REQUEST['page']);
print_r($database->get_db_row("select * from table"));
while($database->next_value())
print_r($database->get_db_row());
//print_result_table with print_navigation_command sample.
//this sample will set the number of row displayed per page to 5,
//the current page to che $_REQUEST['page'] value
//and print out the results as an html table.
//additional navigation commands are added by the function
//print_navigation_command to navigate throught records.
$database->set_pagination(5,$_REQUEST['page']);
//$headers=array("FIELD1","FIELD2");
//echo $database->print_result_table("select * from table",$headers);
echo $database->print_result_table("select * from table");
echo $database->print_navigation_command();
/********************************************************************/
/********************************************************************/
/* Turn off error reporting */
/********************************************************************/
error_reporting(0);
class db{
/********************************************************************/
/* Connection variables
/********************************************************************/
protected $connection; //Connection link
protected $host; //DB Hostname
protected $user; //DB Username
protected $password; //DB Password
protected $dbname; //Database name
/********************************************************************/
/* Current pointers
/********************************************************************/
protected $current_result; //Current result resource
protected $current_rowno=0; //Current row
/********************************************************************/
/* Last error variables
/********************************************************************/
public $last_error; //Last error
public $last_db_error; //Last db error
/********************************************************************/
/* Error stacks
/********************************************************************/
public $error_dump=array(); //array of all error
public $db_error_dump=array(); //array of all db error
/********************************************************************/
/* Pagination variables
/********************************************************************/
public $current_page; //Current page diplayed
public $page_size=0; //Number of record per page
public $total_record; //Total record for a query
/********************************************************************/
/* ENGINE variables
/********************************************************************/
public $db_engine; //database engine
/********************************************************************/
/* Constructor. */
/* Parameters: */
/* $host = Database hostname DEFAULT = localhost */
/* $user = Database username DEFAULT = root */
/* $password = Database password DEFAULT = localhost */
/* $dbname = Database name DEFAULT = [BLANK] */
/* $dbengine = Database engine DEFAULT = mysql */
/* */
/* Creates the DB class object, set the internal Connection */
/* variables and tries to connect to the database engine. */
/* If a "database name" (optional) is passed too it tries */
/* to select that database. */
/********************************************************************/
function db($host="localhost",$user="root",$password="root",$dbname="",$dbengine="mysql")
{
if(!include("db/$dbengine.inc.php"))
{
$this->set_error("FATAL ERROR: Unknown DB engine.","");
die($this->last_error);
}
$this->host=$host;
$this->user=$user;
$this->password=$password;
$this->connect();
if($dbname!="")
{
$this->dbname=$dbname;
$this->select_db($this->dbname);
}
}
/********************************************************************/
/* set_error($error,$db_error) */
/* Parameters: */
/* - $error = Error string DEFAULT = - */
/* - $db_error = db_error DEFAULT = - */
/* */
/* Sets the last_error variables and the db_last_error */
/* variables and pushes into the error stacks the two values */
/* passed. */
/********************************************************************/
protected function set_error($error,$db_error)
{
$this->last_error=$error;
$this->last_db_error=$db_error;
array_push($this->error_dump,$error);
array_push($this->db_error_dump,$db_error);
}
/********************************************************************/
/* connect() */
/* Parameters: */
/* - None - */
/* */
/* Connects the DB object to the database specified in the */
/* Connection variables and fills error variables in case */
/* of fault. */
/********************************************************************/
protected function connect()
{
$this->connection=@connect($this->host,$this->user,$this->password) or $this->set_error("ATTENZIONE:Impossibile connettersi al server.",error());
}
/********************************************************************/
/* select_db($dbname) */
/* Parameters: */
/* $dbname = Database name DEFAULT = [BLANK] */
/* */
/* Selects the database to be used for the queries using the */
/* the "database name" passed as parameter and fills error */
/* variables in case of fault. */
/********************************************************************/
function select_db($dbname)
{
@select_db($dbname,$this->connection) or $this->set_error("ATTENZIONE:Database '$dbname' non trovato.",error());
}
/********************************************************************/
/* query($query,$dbname="") */
/* Parameters: */
/* - $query = Sql string to execute DEFAULT = - */
/* - $dbname = Database name DEFAULT = [BLANK] */
/* */
/* Executes the sql string passed in the parameter $query, */
/* sets the internal current_recult pointer to the result obtained */
/* with the query executed and fills error variables in case */
/* of fault. */
/* If "Pagination variables" are set it modifies the query passed */
/* adding the sql LIMIT command and sets the total_record variable */
/********************************************************************/
function query($query,$dbname="")
{
if($dbname=="")
$dbname=$this->dbname;
if($this->page_size!=0)
{
$this->total_record=num_rows(db_query($dbname,$query));
$start=$this->page_size*($this->current_page-1);
$query.=" LIMIT $start,$this->page_size";
}
$this->current_result = @db_query($dbname,$query) or $this->set_error("ATTENZIONE:Errore durante l'esecuzione dell'istruzione.",error());
}
/********************************************************************/
/* get_db_value() */
/* Parameters: */
/* - $query = Sql string to execute DEFAULT = [BLANK] */
/* */
/* Extract and return as string the value requested in the $query */
/* command. If more than one values are extracted by the */
/* command only the first value will be returned. */
/* */
/* Calling the method with a blank query will return the string */
/* value of the current_result resource */
/* - used after calling next_value / prev_value() */
/********************************************************************/
function get_db_value($query="")
{
if($query!="")
$this->query($query,$this->dbname);
$row=@fetch_array($this->current_result) or $this->set_error("ATTENZIONE:Errore durante la serializzazione dei dati.",error());
return $row[0];
}
/********************************************************************/
/* get_db_row() */
/* Parameters: */
/* - $query = Sql string to execute DEFAULT = [BLANK] */
/* */
/* Extract and return as an array the first row selected with */
/* the $query command. */
/* */
/* Calling the method with a blank query will return the current */
/* row selected */
/* - used after calling next_value / prev_value() */
/********************************************************************/
function get_db_row($query="")
{
if($query!="")
$this->query($query,$this->dbname);
$row=@fetch_array($this->current_result) or $this->set_error("ATTENZIONE:Errore durante la serializzazione dei dati.",error());
return $row;
}
/********************************************************************/
/* next_value() */
/* Parameters: */
/* - none - */
/* */
/* Increments the pointer to the current row and the pointer */
/* to the db result resource and fills error variables in */
/* case of fault. */
/* */
/********************************************************************/
function next_value()
{
$this->current_rowno++;
if(@data_seek($this->current_result,$this->current_rowno) or $this->set_error("ATTENZIONE:Raggiunta l'ultima riga.",error()))
return true;
else
{
$this->current_rowno=0;
return false;
}
}
/********************************************************************/
/* prev_value() */
/* Parameters: */
/* - none - */
/* */
/* Decrements the pointer to the current row and the pointer */
/* to the db result resource and fills error variables in */
/* case of fault. */
/* */
/********************************************************************/
function prev_value()
{
$this->current_rowno--;
if(@data_seek($this->current_result,$this->current_rowno) or $this->set_error("ATTENZIONE:Raggiunta la prima riga.",error()))
return true;
else
{
$this->current_rowno=0;
return false;
}
}
/********************************************************************/
/* numrows() */
/* Parameters: */
/* - none - */
/* */
/* Returns the number of rows selected in the current_result */
/* db resource. */
/* */
/********************************************************************/
function numrows()
{
return @num_rows($this->current_result);
}
/********************************************************************/
/* get_rowno($rowno) */
/* Parameters: */
/* - $rowno = Number of the row to return DEFAULT = - */
/* */
/* Extract and return as an array the "$rowno" row from the */
/* current_result. */
/* */
/********************************************************************/
function get_rowno($rowno)
{
@data_seek($this->current_result,$rowno) or $this->set_error("ATTENZIONE:Impossibile trovare la riga richiesta.",error());
$this->current_rowno=$rowno;
$row=@fetch_array($this->current_result) or $this->set_error("ATTENZIONE:Errore durante la serializzazione dei dati.",error());
return $row;
}
/********************************************************************/
/* fill_result_array($query,$value,$key="") */
/* Parameters: */
/* */
/* */
/********************************************************************/
function fill_result_array($query,$value,$key="")
{
$return=array();
$row=$this->get_db_row($query);
if($key=="")//autoincrement indexes
$return[$this->current_rowno]=$row[$value];
else //use key passed
$return[$row[$key]]=$row[$value];
while($this->next_value())
{
$row=$this->get_db_row();
if($key=="")//autoincrement indexes
$return[$this->current_rowno]=$row[$value];
else //use key passed
$return[$row[$key]]=$row[$value];
}
return $return;
}
/********************************************************************/
/* set_pagination($page_size,$current_page=1) */
/* Parameters: */
/* */
/* */
/********************************************************************/
function set_pagination($page_size,$current_page=1)
{
if($current_page=="")$current_page=1;
$this->page_size=$page_size;
$this->current_page=$current_page;
}
/********************************************************************/
/* print_navigation_command() */
/* Parameters: */
/* */
/* */
/********************************************************************/
function print_navigation_command()
{
if($this->numrows()>0)
{
$navigation="<table><tr>";
$query_string=$_SERVER['QUERY_STRING'];
$pos=strpos($query_string,"page=");
$part1=substr($query_string,0,$pos);
$start_secondaparte=($pos+5+strlen($this->current_page));
$part2=substr($query_string,$start_secondaparte);
if($this->page_size!=0)
{
$total_pages=floor($this->total_record/$this->page_size);
//if($this->current_page>$total_pages)
//$this->current_page=$total_pages;
//print first button
if($this->current_page!=1)
$navigation.="<td><a href='?".$part1."page=1".$part2."'>first</a></td>";
else
$navigation.="<td>first</td>";
//print prev button
$prev=$this->current_page-1;
if($prev>0)
$navigation.="<td><a href='?".$part1."page=$prev".$part2."'>prev</a></td>";
else
$navigation.="<td>prev</td>";
//print commands before current page
$page_before=$this->current_page-5;
if($page_before<=0)$page_before=1;
while ($page_before>0 && $page_before<$this->current_page) {
$navigation.="<td><a href='?".$part1."page=$page_before".$part2."'>$page_before</a></td>";
$page_before++;
}
//print commands for current page
$navigation.="<td><b>".$this->current_page."</b></td>";
//print commands after current page
$page_after=$this->current_page+1;
//if($page_after>$total_pages)$page_after=$total_pages;
while ($page_after<=$total_pages && $page_after<=$this->current_page+5) {
$navigation.="<td><a href='?".$part1."page=$page_after".$part2."'>$page_after</a></td>";
$page_after++;
}
//print next button
$next=$this->current_page+1;
if($next<=$total_pages)
$navigation.="<td><a href='?".$part1."page=$next".$part2."'>next</a></td>";
else
$navigation.="<td>next</td>";
//print last button
if($this->current_page!=$total_pages)
$navigation.="<td><a href='?".$part1."page=$total_pages".$part2."'>last</a></td>";
else
$navigation.="<td>last</td>";
}
$navigation.="</tr></table>";
}
return $navigation;
}
/********************************************************************/
/* print_result_table($query,$custom_headers=array()) */
/* Parameters: */
/* */
/* */
/********************************************************************/
function print_result_table($query,$custom_headers=array(),$table_class="",$header_class="")
{
$table="<table border=1 ";
if($table_class!="")
$table.="class='$table_class'";
$table.=">";
$this->query($query);
$field=0;
$table.="<tr ";
if($header_class!="")
$table.="class='$header_class'";
$table.=">";
while ($field < @num_fields($this->current_result))
{
if(count($custom_headers)>0)
{
/**************************************/
/* Print custom headers
/**************************************/
$table.="<td><b>".$custom_headers[$field]."</b></td>";
}
else
{
/**************************************/
/* Print table headers
/**************************************/
$header=fetch_field($this->current_result,$field);
$table.="<td><b>".$header->name."</b></td>";
}
$field++;
}
$table.="</tr>";
$row=@fetch_row($this->current_result);
$table.="<tr>";
foreach ($row as $value)
$table.="<td>$value</td>";
$table.="</tr>";
while($this->next_value())
{
$row=@fetch_row($this->current_result);
$table.="<tr>";
foreach ($row as $value)
$table.="<td>$value</td>";
$table.="</tr>";
}
$table.="<table>";
return $table;
}
}
?>
/* This section should be in a file named mysql.inc.php */
<?
function connect($hostname,$username,$password="")
{
return mysql_connect($hostname,$username,$password);
}
function select_db($database_name)
{
return mysql_select_db($database_name);
}
function db_query($database_name,$query)
{
return mysql_db_query($database_name,$query);
}
function fetch_array($result)
{
return mysql_fetch_array($result);
}
function data_seek($result,$row_number)
{
return mysql_data_seek($result,$row_number);
}
function num_rows($result)
{
return mysql_num_rows($result);
}
function error()
{
return mysql_error();
}
function num_fields($result)
{
return mysql_num_fields($result);
}
function fetch_field($result)
{
return mysql_fetch_field($result);
}
function fetch_row($result)
{
return mysql_fetch_row($result);
}
?>
/* This section should be in a file named mssql.inc.php */
<?
function connect($hostname,$username,$password="")
{
return mssql_connect($hostname,$username,$password);
}
function select_db($database_name)
{
return mssql_select_db($database_name);
}
function db_query($database_name,$query)
{
mssql_select_db($database_name);
return mssql_query($query);
}
function fetch_array($result)
{
return mssql_fetch_array($result);
}
function data_seek($result,$row_number)
{
return mssql_data_seek($result,$row_number);
}
function num_rows($result)
{
return mssql_num_rows($result);
}
function error()
{
return mssql_get_last_message();
}
function num_fields($result)
{
return mssql_num_fields($result);
}
function fetch_field($result)
{
return mssql_fetch_field($result);
}
function fetch_row($result)
{
return mssql_fetch_row($result);
}
?>
|
|
|
Usage Example
|
/********************************************************************/
/* CUT'N'PASTE SAMPLES */
/********************************************************************
//Create new DB object
$database=new db("localhost","root","root","test");
//Simple values iteration.
echo $database->get_db_value("select field from table");
while($database->next_value())
echo $database->get_db_value();
//Simple rows iteration.
print_r($database->get_db_row("select * from table"));
while($database->next_value())
print_r($database->get_db_row());
//Get_rowno sample.
//this sample will print out the row number 2 of the
//current_result resource.
$database->get_db_row("select * from table");
print_r($database->get_rowno(2));
//fill_result_array sample.
//this sample will print the resulting array filled
//with all the values from the query using the fild n.1
//as array value and the field n.0 as array key.
print_r($database->fill_result_array("select * from table",1,0));
//pagination sample.
//this sample will set the number of row displayed per page to 5,
//the current page to che $_REQUEST['page'] value
//and print out the results.
$database->set_pagination(5,$_REQUEST['page']);
print_r($database->get_db_row("select * from table"));
while($database->next_value())
print_r($database->get_db_row());
//print_result_table with print_navigation_command sample.
//this sample will set the number of row displayed per page to 5,
//the current page to che $_REQUEST['page'] value
//and print out the results as an html table.
//additional navigation commands are added by the function
//print_navigation_command to navigate throught records.
$database->set_pagination(5,$_REQUEST['page']);
//$headers=array("FIELD1","FIELD2");
//echo $database->print_result_table("select * from table",$headers);
echo $database->print_result_table("select * from table");
echo $database->print_navigation_command();
/********************************************************************/
|
|
|
Rate This Script
|
|
|
|
Store
Your email: