Every time you start building a complete website with some modules and functionalities, you start thinking about the “structure” of your system or in other word, the framework you use through the system. For a PHP website, MVC is the most widely use framework or programming model where M is the Model which handles the logic of the website, V is view which presents information to your users and C is controller which collaborate Views and Models together. However, the MVC itself does not include the data access layer which actually does the data manipulation work within your system and without data, your system seems to be unreal.

PDO is a very useful and widely use PEAR library for data manipulation. It can work with most kind of DBMS (DataBase Management System) such as MySQL (of course), Microsoft SQL, etc. PDO stands for PHP Data Object, a powerful and fast data access method for developers who need to work with different types of DBMS, handle stored procedures, process transactions or passing params to queries.

So let begin with a Database class using PDO. This class should be simple to use, aware about your ‘entity’ object, prevent you from being attacked by injection and the most important, bring use a methodology of writing clean, reusable and testable SQL queries.

Sounds like buzzwords but in fact, PDO does most of those things and our work is to just making a little more systematic in the way we use PDO functions. So, the first step is to declare a PDO object in our class:

class Database
{
protected $pdo;

function __construct($host, $db, $user, $password)
{
$this->pdo = new PDO("mysql:host=$host;dbname=$db", $user, $password);
}
}

The code above also shows you how to connect to an MySQL database with PDO. We as the consumer to pass basic informations to the constructor of the Database class so we can create a connection to the DB using our $pdo object.

The next key work is we want to pass parameter to our object to execute a query. This way, we can write only one query to use in different places and make sure that once the query runs properly, unexpected result can be caused only by bad parameters.

protected function parseSql($sqlElement, $params)
{
$sql = $this->pdo->prepare((string)$sqlElement);

if (!is_array($params))
$params = array($params);
try
{
for ($i = 1; $i < = count($params); $i++)
{
//Replace null param by empty string for wellform SQL query
if ($params[$i-1] == null)
$params[$i-1] = '';
$sql->bindValue($i, $params[$i-1]);
}
return $sql;
}
catch(Exception $ex)
{
echo "Error while parsing SQL query.<br />";
var_dump($sqlElement); echo "<br />";
var_dump($params); echo "<br />";
}
}

From the code above, first of all you can notice the $sqlElement. It is actually not a string of SQL query but a SimpleXML text element containing the SQL query and some other information our framework needs. We will talk about those XML files later but at this moment, we have to convert $sqlElement into string before use.

In order to pass params to a marked SQL query using PDO, you go through two steps. First, you ask PDO to prepare the query using the prepare() method and then use the prepared query to bind marked parameters with values using the bindValue() method.

The PDO library provide you two binding functions, bindParam() and bindValue(). They are similar in binding mechanism but different in processing the result query. Both bind a PHP variable to a question mark or named param in a query and you can specify name, data type and also the length of the parameter. However, the bindParam hold a referent to the PHP variable you bind and the result query is evaluated only at runtime. bindValue() result an evaluated query immeidately.

protected function execute($sqlId, $params, &$sql)
{
$sql = $this->parseSql($this->getSqlElement($sqlId), $params);
try
{
$sql->execute();
return true;
}
catch(Exception $ex)
{
echo "Error while executing $sqlId <br />$sql->errorInfo()";
}

}

protected function getSqlElement($sqlId)
{
$tmp = explode('.', $sqlId);
$sqlFile = SitePath . 'data' . DIRSEP . $tmp[0] . '.xml';

$root = simplexml_load_file($sqlFile);
foreach($root->sql as $sql)
{
if ($sql['Id'] == $tmp[1])
return $sql;
}
return null;
}

Back to our class, what we need now is where to get the SQL queries and how to execute one query after having it parsed with params. You may implement the SQL query resource in many different ways but for me, I prefer to put all SQL queries into XML files. That way, I never write SQL code in my PHP files meaning the code is very clean and easy to debug. I can also write and test my query first in a MySQL query client tool and the copy the tested code to my XML file, remove testing values with question marks so that I can pass to them the parameters I want from PHP code.

Writing SQL queries this way is a similar way of writing one-query-procedure. It is not only very reusable but also make the teamwork of many developers easy and help you deploy the system from testing environment to production environment quickly. Just forget about finding all PHP files where you have just change your SQL queries inside and copy them to another place.

Enough talk. Back to the XML and the getSqlElement function in our class. Our coding convention (which is very important in a PHP framework) is having the sqlId of two parts separated by a period (.). First part is the XML filename and the second part is the ‘id’ of the SQL element in that file. We don’t want to put all queries into just one file because many developers can work on that file at the same time. Also we don’t want the getSqlElement function to look through a long text file just to find out a short SQL query to run. For your information, PDO cannot run as fast as pure PHP data manipulation function. It as rate at the second position, with the great Database library ADOdb.

Now, once we have the query from XML file, pass parameters to it, the execution is simple by calling execute() function in PDO. After execute, the $sql variable holds result data that you can fetch is in many different way.

I don’t like processing data rows and columns as array of array so I use PDO fetch object style. It’s easier for you whenever you pull data form the DB, they are in form of a ‘Model’ object (in an MVC framework) and ready for you to call some logic function. That’s why we don’t process an array of columns just to create an object to continue our programming flow.

As you can see in the code below, except for querying data for just one column of the first returned row, we all fetch data as object or list of object. If we pass the class name of the object to the queryXXX function, it try to create an object of the desired class for us, otherwis it create a standard PHP object.

public function queryForColumn($sqlId, $params, $columnIndex, $defaultValue = null)
{
$sql = null;
$ret = $this->execute($sqlId, $params, $sql);
if ($ret === true)
return $sql->fetchColumn($columnIndex);
else
return $defaultValue;
}

public function queryForObject($sqlId, $params, $className = null, $defaultValue = null)
{
$sql = null;
$ret = $this->execute($sqlId, $params, $sql);
if ($ret === true)
{
if ($className != null)
$sql->setFetchMode(PDO::FETCH_CLASS, $className);
else
$sql->setFetchMode(PDO::FETCH_OBJ);

return $sql->fetch();
}
else
return $defaultValue;
}

public function queryForList($sqlId, $params, $className = null, $defaultValue = null)
{
$sql = null;
$ret = $this->execute($sqlId, $params, $sql);
if ($ret === true)
{
if ($className != null)
$sql->setFetchMode(PDO::FETCH_CLASS, $className);
else
$sql->setFetchMode(PDO::FETCH_OBJ);

return $sql->fetchAll();
}
else
return $defaultValue;
}

I do like fetching data into named object as PHP5 provides magic getter/setter methods that make my class become more reusable from project to project. The idea is a map to translate columns in Database into properties in PHP code. That way, I never break my PHP coding standard no matter what the DB naming standard is. Another roughly example is in some websites you use email as the useranme while in others you really have an username column in the database beside the email column. In the first cases, I just map email to the Username property so my code to handle user related logic does not change else where.

Here is how to create an EntityBase class with magic setter function.

class EntityBase
{
/**
* @desc Column map for consistent entity property
*/
protected $columnMap = array();

public function __set($column, $value)
{
$property = $this->columnMap[$column];
$setter = "set$property";
if (!method_exists($this, $setter))
$this->$property = $value;
else
$this->$setter($value);
}
}

Last thing, we don’t want to call a function like queryForXXX to execute an action query like Insert/Update/Delete data. So we just add a simpe query() funtion for this task.

public function query($sqlId, $params)
{
$sql = null;
return $this->execute($sqlId, $params, $sql);
}

Now, we have an useful Database class and to use it, just create an users.xml file of queries like what I have for my simple demo blog site:

< ?xml version="1.0" encoding="UTF-8"?>
<queries>
<sql Id="getUserById">
Select *
From users
Where Id = ?
</sql>

<sql Id="validateUser">
Select Count(Id)
From users
Where Email = ? AND Password = ?
</sql>

<sql Id="getIdByName">
Select Id
From users
Where email = ?
</sql>
</queries>