Webrick Article Submission - Base of articles - How to create a simple Database Access Layer (DBAL)

View Article Base

Serving 496 articles.

Articles > PHP (Programming Languages) > How to create a simple Database Access Layer (DBAL)

How to create a simple Database Access Layer (DBAL)

Written by David Domingues
Created on 2008-09-29 06:57:55	Nb. of Hits: 567	Rating:	No rating yet

Introduction

Defining all accesses to a MYSQL database on a dynamic page can be painful when you have a lot of information fields to manage. This small class is the beginning for a database layer abstraction (DBAL). Using it, you will never have to make a query again, deal with its syntax, know where to put quotes and not, know what the primary keys of the tables are, etc.... In fact, this information already exists in a database called information_schema. If performance of a site is not the main issue and you want it to make your site maintainable before all, then using this information to simplify the life of the programmer can be the key. But know that these kind of things, and by extension every DBAL, Object Relational Mapper (ORM), like Doctrine for instance, or frameworks like Cake or Zend have huge costs in terms of performance, but will ease your job. It's only a question of choice...

Principles

The first idea was about answering one question: "why to I have to deal with a bunch of fields I don't even need when writing a query, if those fields are perfectly identified into the database?". As a programmer, why should I have to worry about knowing the complete structure of my table to insert a row? Instead of writing:

INSERT INTO MyTable VALUES ('Myvalue', '', '', 0, '', 0, '', '');

Why can't I do something like:
$MyFlied1 = 'MyValue';
$result = doInsert('MyTable');

The procedure should be able to retrieve alone the structure of the table of the specified database and deal alone with the fields and their types.

The second idea was about building a bridge between tables and forms. With identical name, the field of a form should be easily linkable to its homonym on the table and vice-versa. After retrieving the content of a table, it should be easy to fill a form with corresponding fields calling one method, instead of moving each value to each form field. On the contrary, when the form is complete and the data are to be inserted in the table, a method could be called to pass each of the form field value to the table fields.

Coding the Classes

Connecting to a specific database

Getting fields and types from the schema database

Getting primary keys from the schema database

Selection of rows

Key select, returns only one row

Field select with limitation

Insert Method

Update Method

Update the field corresponding to the primary key value but only with know properties.

Delete Method

Erase the field corresponding to the primary key value.

Bulk moving fields from database to Form and vice versa.

Those 2 functions move the value of the fields object to the post values of the form, when names are identical, and vice versa.

The complete Class

class clsDBConnect
{
	public $configfile;
	public $hostname;
	public $database;
	public $admin;
	public $password;
	public $prefix;
	public $db;
	
	//$configfile is the path to the database configuration file data
	//$database is the name of the specified database which by default is the one in the configuration file. 
	function __construct($configfile,$sdatabase="")
	{
		$this->configfile = $configfile;
		require $this->configfile;
		$this->hostname = $hostname;
	  $this->admin = $admin;
	  $this->password = $password;
	  if ($sdatabase != '')
	  	$this->database = $sdatabase;
	  else	
	  	$this->database = $database;
	  $this->prefix = $prefix;
	  $this->db = mysql_connect ($this->hostname, $this->admin, $this->password) or die('Database access denied!');
	}
}

//$field receives the name of all fields of the selected table
//$type receives the types of all fields of the selected table
class clsDBStruct
{
	public $field;
	public $type;
	
	//$tablename: the name of the table
	//$dbfunc: connection to the database where $tablename is
	//$dbstruct: connection to the database where "information_schema" is
	function __construct($tablename, $dbfunc, $dbstruct)
	{
		mysql_select_db ($dbstruct->database, $dbstruct->db);
		$query = "select COLUMN_NAME, DATA_TYPE from COLUMNS where TABLE_SCHEMA = \"".$dbfunc->database."\" and TABLE_NAME = \"".$tablename."\";";
		$result = mysql_query($query, $dbstruct->db);
		$db_row = mysql_fetch_row($result);

while ($db_row)
		{
			$this->field->$db_row[0] = $db_row[0];
			$this->type->$db_row[0] = $db_row[1];
			$db_row = mysql_fetch_row($result);
		}
	}
}

class clsDBKeys
{
	//$tablename: the name of the table
	//$dbfunc: connection to the database where $tablename is
	//$dbstruct: connection to the database where "information_schema" is
	function __construct($tablename, $dbfunc, $dbstruct)
	{
		mysql_select_db ($dbstruct->database, $dbstruct->db);
		$query = "select COLUMN_NAME from KEY_COLUMN_USAGE where TABLE_SCHEMA = \"".$dbfunc->database."\" and CONSTRAINT_NAME = \"PRIMARY\" and TABLE_NAME = \"".$tablename."\" order by ORDINAL_POSITION;";
		$result = mysql_query($query, $dbstruct->db);
		$db_row = mysql_fetch_row($result);

while ($db_row)
		{
			$this->$db_row[0] = $db_row[0];
			$db_row = mysql_fetch_row($result);
		}
	}
}

//$dbfunc: connection to the database where $tablename is
//$dbstruct: connection to the database where "information_schema" is
//$fields: Field stucture object of $tablename 
//$types: Type stucture object of $tablename 
//$keys: Keys stucture object of $tablename 
//$tablename: the name of the table
class clsDBchange
{
	private $dbfunc;
	private $dbstruct;
	public $fields;
	private $types;
	public $keys;
	public $tablename;
	
	//$configfile is the path to the database configuration file data
	//$database is the name of the specified database which by default is the one in the configuration file. 
	function __construct($configfile, $tablename)
	{
		$this->dbfunc =	new clsDBConnect($configfile, "");
		$this->dbstruct = new clsDBConnect($configfile,"information_schema");
		$this->keys = new clsDBKeys($tablename, $this->dbfunc, $this->dbstruct);
		$this->fields = new clsDBStruct($tablename, $this->dbfunc, $this->dbstruct);
		$this->tablename = $tablename;
	}
	
	//puts quotes around the field when its type requires it. Protects values from injections
	function quotesmart($value, $type) 
	{ 
		$noquotestypes = array('bigint','tinyint','smallint','int','double');
		// Stripslashes 
		if (get_magic_quotes_gpc()) { 
			 $value = stripslashes($value); 
		} 
		// Quote if not a number or a numeric string 
		if (!in_array($type, $noquotestypes)) { 
			 $value = "'" . mysql_real_escape_string($value) . "'"; 
		} 
		return $value; 
	} 
	
	//clean the field and type objects values
	function DBclear()
	{
		$class_var = get_object_vars($this->fields);

while ($entry = each($class_var)) 
			$this->$entry['key'] = '';
			
		$class_var = get_object_vars($this->types);

while ($entry = each($class_var)) 
			$this->$entry['key'] = '';	
	}
	
	//select from the $tablename knowing the primary key
	function DBselect()
	{
		$query = "select * from ".$this->tablename." where ";
		$class_keys = get_object_vars($this->keys);
		$first = true;
		while ($entry = each($class_keys)) 
		{
			if (!$first) $query .= " and ";
			$query .= $entry['key']." = ".$this->quotesmart($this->$entry['key'], $this->fields->type->$entry['key']);
			$first = false;
		}
		mysql_select_db ($this->dbfunc->database, $this->dbfunc->db);
		$result = mysql_query($query, $this->dbfunc->db);
		$db_row = mysql_fetch_object($result);

if ($db_row)
		{
			while ($entry = each($db_row))
			{ 	
				$this->$entry['key'] = $entry['value']; 
			}
		}
	}

//select $recnumber records from the $tablename from $recinit position
	function DBsearch($recnumber=1, $recinit=0)
	{
		$query = "select * from ".$this->tablename." where ";
		$class_vars = get_object_vars($this->fields->field);
		$first = true;
		while ($entry = each($class_vars)) 
		{
			if ($this->$entry['key'] != '')
			{
				if (!$first) $query .= " and ";
				$query .= $entry['key']." = ".$this->quotesmart($this->$entry['key'], $this->fields->type->$entry['key']);
				$first = false;
			}
		}
		
		$query .= " limit ".$recinit.", ".$recnumber.";";
		
		mysql_select_db ($this->dbfunc->database, $this->dbfunc->db);
		$result = mysql_query($query, $this->dbfunc->db);
		$db_row = mysql_fetch_object($result);

$i=0;
		while ($i < $recnumber && $db_row)
		{
			while ($entry = each($db_row))
			{
				if ($i == 0)
					$this->$entry['key'] = array();
				array_push($this->$entry['key'], $entry['value']); 
			}
		
			$db_row = mysql_fetch_object($result);
			$i++;
		}
	}

//insert fields into $tablename according to $fields object description
	function DBinsert()
	{
		$class_var = get_object_vars($this->fields->field);
		$query = "insert into ".$this->tablename." values (";
		$first = true;
		while ($entry = each($class_var)) 
		{
			if (!$first) $query .= ", ";
			$query .= $this->quotesmart($this->$entry['value'], $this->fields->type->$entry['value']);
			$first = false;
		}
		$query .= ");";

mysql_select_db ($this->dbfunc->database, $this->dbfunc->db);
		$result = mysql_query($query, $this->dbfunc->db);
		if ($result)
			return true;
		else
			return false;	
	}
	
	
	//primary key update $tablename according to $fields object description
	function DBupdate()
	{
		$class_var = get_object_vars($this->fields->field);
		$query = "update ".$this->tablename." set ";
		$first = true;
		while ($entry = each($class_var)) 
		{
			if (!$first) $query .= ", ";
			$query .= $entry['value']." = ".$this->quotesmart($this->$entry['value'], $this->fields->type->$entry['key']);
			$first = false;
		}
		$query .= " where ";
		$class_keys = get_object_vars($this->keys);
		$first = true;
		while ($entry = each($class_keys)) 
		{
			if (!$first) $query .= " and ";
			$query .= $entry['value']." = ".$this->quotesmart($this->$entry['value'], $this->fields->type->$entry['key']);
			$first = false;
		}
		$query .= ";";
		
	
		mysql_select_db ($this->dbfunc->database, $this->dbfunc->db);
		$result = mysql_query($query, $this->dbfunc->db);
		if ($result)
			return true;
		else
			return false;	
	}
	
	//primary key delete $tablename
	function DBdelete()
	{
		$query = "delete from ".$this->tablename." where ";
		$class_keys = get_object_vars($this->keys);
		$first = true;
		while ($entry = each($class_keys)) 
		{
			if (!$first) $query .= " and ";
			$query .= $entry['value']." = ".$this->quotesmart($this->$entry['value'], $this->fields->type->$entry['key']);
			$first = false;
		}
		$query .= ";";

mysql_select_db ($this->dbfunc->database, $this->dbfunc->db);
		$result = mysql_query($query, $this->dbfunc->db);
		if ($result)
			return true;
		else
			return false;	
	}
	
	//moving all fields values from the database to their homonym post variable.
	function DBtoform()
	{
		$class_var = get_object_vars($this->fields->field);
		while ($entry = each($class_var)) 
			$_POST[$entry['value']] = $this->$entry['value'];
	}
	
	//moving all post variable values to their homonym fields values in the database table.
	function formtoDB()
	{
		$class_var = get_object_vars($this->fields->field);
		while ($entry = each($class_var)) 
			$this->$entry['value'] = $_POST[$entry['value']];
	}
	
}

Improvements

Of course as you can see, to have a real DBAL application, a lot of work needs to be done. These classes however set some principles up. We could complete them with:
- Full Select construction functions with COUNT, MAX, MIN functions,
- Join capabilities on several tables,
- Order, distinct, group by, like clauses,
- Inclusion of operators like >, >=, <, <=, NOT,
- Inclusion of Boolean operators AND, OR.

Sample

The sample below was created with the following table: Let's show some quick samples of what we can do. Creating the object related to the table Inserting one row to the table: Updating one row to the table: Retrieving one field from the key: Check this link out.

History

2008-09-29: First version

Last Updated on

David Domingues - David Domingues is a professional IT analyst.

[Back]

Rate this article
		Brilliant
		Very good
		Good
		Average
		Bad
		Null

[Back]

Comments
You should login in order to register your comments.

[Back]

Introduction

Principles

INSERT INTO MyTable VALUES ('Myvalue', '', '', 0, '', 0, '', '');

Why can't I do something like: $MyFlied1 = 'MyValue'; $result = doInsert('MyTable');

The procedure should be able to retrieve alone the structure of the table of the specified database and deal alone with the fields and their types.

Coding the Classes

Connecting to a specific database

Getting fields and types from the schema database

Getting primary keys from the schema database

Selection of rows

Insert Method

Update Method

Delete Method

Bulk moving fields from database to Form and vice versa.

The complete Class

Improvements

Sample

History

Why can't I do something like:
$MyFlied1 = 'MyValue';
$result = doInsert('MyTable');