PHPBuilder - MySQL Database Abstraction Class



RSS Twitter
Snippets Databases

MySQL Database Abstraction Class

by: Simon Johnson
|
July 11, 2002

Version: 1

Type: Class

Category: Databases

License: GNU General Public License

Description: Use this class to separate database processing from application logic. Supports persistant and non-persistant connections, error handling and logging, action instance logging and tracking, to debug any troublesome code. Will format and return a tabulated dataset of any query, or will return just the result resource for use.



<?PHP

###############################################
#
#WRITTEN BY SIMON JOHNSON
#10/07/2002
#A SIMPLE OBJECT ORIENTATED DATABASE ABSTRACTION LAYER.
#NO LICENSE OR WARRENTIES, THE AUTHOR HOLDS NO RESPONSIBLITY
#OF USE.
#FEEL FREE TO USE PROFESSIONALLY OR PERSONALLY, AND DEVELOP AS REQUIRED
#ANY THOUGHTS OR QUESTIONS EMAIL SIMON.JOHNSON@WHICH.NET.
#
################################################


/***********************************************

FUNCTION DEFINITIONS
SetUp([string hostname],[string username],[string password],[string databasename])
Connect([int persisitant])
Query([String query],[int display])
Disconnect()


*/


/************************************************

EXAMPLE USE

require("dbase.class.php");

CREATE OBJECT
$con = new Dbase(); 

EXPLICITLY SET ATTRBUTES
$con -> SetUp("Localhost","username","password","paye");

CONNECT TO DATBASE 1 for persistant, blank for non-persistant
$con -> Connect(1); 

CREATE YOUR QUERY STRING
$string= "select * from shop"; 

RUN QUERY SECOND PARAM =1 TO RETURN FORMATTED RESULT
$con -> Query($string,1);

OBTAIN RESULT RESOURCE
$result=$con->getQryresult();

OBTIAN NUMBER OF ROWS
$numOfRows=$con->getQryrows();


**************************************************/





class Dbase
{

//CLASS ATTRIBUTES

/*****************************************
*Set the 4 attributes below to save time,
*or set them explicitly using the SetUp() function.
*EG: SetUp(hostname,username,password,dbname)
*This will maintain scope throughout the object life
*Of course they can be changed using the accessor methods.
*/


var $hostname;
var $username;
var $password;
var $dbname;

/*****************************************
*To turn error and action reporting on, set to one, else leave blank.
*If it is set on, create a directory /logs, in the same filesystem as the class.
*Create two files, action_log.txt and dbase_log.txt.
*/
var $err_rep=1;
/******************************************/

var $dbtype;
var $qryrows;
var $qryfields;
var $tblname;
var $error;
var $conn;
var $action = "No Processes have been undertaken";
var $querystring;
var $qryresult;

//ACCESSOR METHODS
//SETTERS

function setDbname($newdbname)
	{
	 $this->dbname=$newdbname;
	}
function setDbtype($newdbtype)
	{
	 $this->dbtype=$newdbtype;
	}
function setHostname($newhostname)
	{
	 $this->hostname=$newhostname;
	}
function setUsername($newusername)
	{
	 $this->username=$newusername;
	}
function setPassword($newpassword)
	{
	 $this->password=$newpassword;
	}
function setQryrows($newqryrows)
	{
	 $this->qryrows=$newqryrows;
	}
function setQryfields($newqryfields)
	{
	 $this->qryfields=$newqryfields;
	}
function setTblname($newtblname)
	{
	 $this->tblname=$newtblname;
	}
function setError($newerror)
	{
		$this->error=$newerror;
		$date = date("j m Y");
		if ($this->err_rep=="1")
			{
				$filelocation="logs/dbase_log.txt";
				$newfile = fopen($filelocation,"a+");
				$add = "\n"."$this->error: $date";
				fwrite($newfile, $add);
				fclose($newfile);
			}
		else
			{
				//Do nothing
			}

	}
function setConn($newconn)
	{
	 $this->conn=$newconn;
	}
function setAction($newaction)
	{
	 $this->action=$newaction;
	 $date = date("G:i : j m Y");
	 if ($this->err_rep=="1")
		 {
			 $filelocation="logs/action_log.txt";
			 $newfile = fopen($filelocation,"a+");
			 $add = "\n"."$this->action: $date";
			 fwrite($newfile, $add);
			 fclose($newfile);
		 }
	 else
		 {
			//Do nothing
		 }
	}
function setQuerystring($newquerystring)
	{
	 $this->querystring=$newquerystring;
	}

function setQryresult($newqryresult)
	{
	 $this->qryresult=$newqryresult;
	}

function setErr_rep($newerr_rep)
	{
	 $this->err_rep=$newerr_rep;
	}

//GETTERS

function getDbname()
	{
	 return $this->dbname;
	}

function getDbtype()
	{
	 return $this->dbtype;
	}

function getHostname()
	{
	 return $this->hostname;
	}

function getUsername()
	{
	 return $this->username;
	}

function getPassword()
	{
	 return $this->password;
	}

function getQryrows()
	{
	 return $this->qryrows;
	}

function getQryfields()
	{
	 return $this->qryfields;
	}

function getTblname()
	{
	 return $this->tblname;
	}

function getError()
	{
	 return $this->error;
	}

function getConn()
	{
	 return $this->conn;
	}

function getAction()
	{
	 return $this->action;
	}

function getQuerystring()
	{
	 return $this->querystring;
	}

function getQryresult()
	{
	 return $this->qryresult;
	}

function getErr_rep()
	{
	 return $this->err_rep;
	}

//OPERATOR FUNCTIONS

function SetUp($hostname,$username,$password,$dbname)
	{
		$this->setHostname($hostname);
		$this->setUsername($username);
		$this->setPassword($password);
		$this->setDbname($dbname);
	}

function Connect($type)
	{

	if(($this->username=="")||($this->password=="")||($this->hostname==""))
		{
		echo"You have failed to insert Your Username, Password or Hostname for connection.";
		}
	else
		{

			if ($type==1)
			{
			$this->conn = mysql_pconnect($this->hostname,$this->username,$this->password);
				if ($this->conn)
				{
					$this->setAction("Persistant Connection Made to Database on $this->hostname");
					mysql_select_db($dbname);
				}
				else
				{
					$this->setAction("Persistant Database Connection Attempt Failed See Error log");
					$this->setError("Persistant Database Connection Error, Unable to communicate");
				}
			}
			else
			{
				$this->conn = mysql_connect($this->hostname,$this->username,$this->password);
				if ($this->conn)
					{
						$this->setAction("Connection Made to Database on $this->hostname");
						mysql_select_db($dbname);
					}
				else
					{
						$this->setAction("Database Connection Attempt Failed See Error log");
						$this->setError("Database Connection Error, Unable to communicate");
					}
			}
		}
	}

function Disconnect()
	{
		mysql_close($this->conn);
		mysql_free_result($this->qryresult);
		$this->setAction("Database Connection closed");
	}

function Query($string, $display)
	{
		$this->setQuerystring($string);
		$this->setQryresult(mysql_db_query($this->dbname, $this->querystring));
		$this->setQryrows(mysql_num_rows($this->qryresult));
		$this->setQryfields(mysql_num_fields($this->qryresult));

		if($this->qryresult)
			{
				$this->setAction("Query Execution Success");
				if ($display=="1")
					{
						$this->setAction("Attempting to display query results");
						$c=$this->qryfields;
						$r=$this->qryrows;
						echo"<table border=1><tr>";
							for ($i = 0; $i < $c; $i++)
								{
									$cols = mysql_field_name($this->qryresult, $i);
									$cols=strtoupper($cols);
									echo "<td border=1><b><font face=arial size=1>$cols</b><br>";
										for ($g = 0; $g < $r; $g++)
										{
											$rows = mysql_result($this->qryresult, $g, $cols);
											echo "<font face=arial size=1><br>$rows<br><br>";
										}
								}
						echo"</font></table>";

					}
					else
					{
						$this->setAction("No Display of query requested, populating result resource");
					}
			}
		else
			{
				$this->setAction("Query Execution Failure");
				$this->setError("Query Execution Error");
			}
	}

}

?>

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Simon Johnson

Comment:



Comment:

(Maximum characters: 1200). You have characters left.