PHP Base Database Access Layer

Jon • 4th Nov 2008 • Sandbox

When writing an application that uses a database, the best practice is to keep the code that actually connects to the database and executes a query in a single class.

This is my PHP class to connect to a MySQL database.

class BaseDal
{
	private $link = "";
	private static $dbName="DB_NAME";
	private static $host = "HOST_NAME";
	private static $username = "USERNAME";
	private static $password = "PASSWORD";

	function  __construct()
	{
		$this->link = mysql_connect(self::$host, self::$username, self::$password, false, 131072);
		if (! $this->link)
		{
			throw new Exception ("Could not connect to the database!");
		}
		mysql_select_db(self::$dbName);
	}
	function __destruct()
	{
		mysql_close($this->link);
	}
	private function printf_array($format, $arr)
	{
	    return call_user_func_array('sprintf', array_merge((array)$format, $arr));
	}
	private function _createQuery($format, $args)
	{
		$finalArgsArray = Array();
		foreach($args as $item)
		{
			$finalArgsArray[] = mysql_real_escape_string($item);
		}
		return self::printf_array($format, $finalArgsArray);
	}

	public function executeQuery($queryFormat, $queryArgs, $parser)
	{
		$this->query = $this->_createQuery($queryFormat, $queryArgs);
		$this->result = mysql_query($this->query, $this->link) or die(mysql_error($this->link));
		$temp = $parser($this->result);
		mysql_free_result($this->result);
		return $temp;
	}

}

view the full source code

A couple key things to point out about it:

First is the fact that it does not use stored procedures. Some would say that it is bad code to have SQL commands in the code base. For my host, they don’t give my MySQL users execute access, which makes it impossible to use stored procedures. That is why I did write in support for stored procedures.

Second the code that calls into the executeQuery is expecting a string format like “SELECT * FROM myTable WHERE id=%s;”. Then an array with the values for each of the %s in the string format passed with it. This allows the base dal to prevent sql injection automatically.

Third  is that this class is designed to have it be extended.  The idea is that one would create other classes that would extend this one. The other class would hold all the methods relating to a certain aspect of the database.  One example would be all methods that one can call relating to users in the database. This would be, adding, deleting, retrieving records, authentication, etc.

Here is a very basic extention of the BaseDal:

require_once 'BaseDal.php';
class UserDal extends BaseDal
{
	public function getUser($userid)
	{
		$queryFormat = "SELECT id, name FROM User WHERE User.id = %s";
		function myParser($result)
		{
			$resultUser = array();
			while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
			{
				$temp = new User();
				$temp->setName($row["name"]);
				$temp->setId($row["id"]);
				$resultUser[] = $temp;
			}
			return $resultUser;
		}
		return parent::executeQuery($queryFormat, array($userid), myParser);
	}
}

view the full source code

In any other part of the code, that I would need to pull a user by id from the database, I can create an instance of this UserDal object. Call to the function getUser and get back an instance of the custom User object I have created.  I don’t clutter the rest of my code with the code for database connection, sql statement, sql injection protection, etc.