PHP has typically been partnered along side of MySQL as a DBMS for a long time now. While MySQL is very powerful PHP can utilize many other DBMS’s as well such as PostgresSQL, Oracle, and even Microsoft SQL. The problem is that connecting to and using the different databases means you have to load different drivers with different coding conventions. For a long time you couldn’t use the same code with Microsoft SQL as you did with MySQL. Not until PDO.

PHP Data Objects (PDO) is a powerful data abstraction layer that enables you to utilize very powerful database functionality such as prepared queries. For those of you who don’t know what a prepared query is, here is alittle background. When PHP sends a query request to a DBMS such as MySQL, MySQL receives the query, and then compiles the query, then runs it. This takes a good amount of overhead especially compiling the query. Lets say that you have an application that needs to send the same insert command multiple times just with different data. Every time you send that sql command MySQL has to receive the query and compile it. Thats alot of overhead. With a prepared query, you send the initial prepared SQL insert statement and MySQL compiles it. Then you send the values that you want to run within that command. Instead of recompiling the same sql code over and over, you simply execute the already compiled command just with different values. Now some of you may be thinking "um, yes, we can already do this with mysqli" and you would be correct. The problem is that every time you want to use a different DBMS you have to learn the nuances of connecting to different databases. This can be very annoying after awhile.

PHP Data Objects (PDO) is a great solution that allows you to quickly connect to many different databases using the exact same code. The only difference is in the initial connection you specify what DBMS you are connecting too and PDO takes care of the rest!

The Code

Ok, lets get down to brass tax. What does this look like? Well I wrote a little database abstraction layer object that is built on PDO to help make things even easier. Here’s the code:

<?php
class db
{
	private $dbh;
	public $result;
	public $column_count = 0;
	public $row_count = 0;
	public $affected_rows = 0;
	public $last_inserted_id = 0;
	private $stmt;
 
	public function __construct()
	{
		$this->connect();
	}
 
	private function connect()
	{
		$this->dbh = new PDO(config::db_dbms . ':host=' . config::db_host . ';dbname=' . config::db_name, config::db_username, config::db_password);
 
		if (config::debug_mode == false)
		{
			$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);		
		}
		else
		{
			$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		}
	}
 
	public function command($sql)
	{
		if (!$this->dbh->getAttribute(PDO::ATTR_CONNECTION_STATUS))
		{
			$this->connect();
		}
 
		if ($sql == "{prev}")
		{
			$numargs = func_num_args();
 
			for ($i = 1; $i < $numargs; $i++)
			{
				$this->{"arg$i"} = func_get_arg($i);
			}	
 
			$this->stmt->execute();
 
			if (intval($this->stmt->errorCode()) > 0)
			{
				if (config::debug_mode == true)
				{
					$err = $this->stmt->errorInfo();
					throw new Exception($err[2]);
				}
				else
				{
					throw new Exception("There was an error in your sql statement");
				}
				$this->row_count = $this->stmt->rowCount();
 
				$this->column_count = $this->stmt->columnCount();
			}
		}
		else
		{
			$count = count(split("\?", $sql));
			$numargs = func_num_args();
 
			if ($count != $numargs)
			{
				throw new Exception("Number of arguments does not match the number of required parameters");
			}
 
			try
			{
				$this->stmt = $this->dbh->prepare($sql);
			}
			catch (PDOException $e)
			{
				echo $e->getMessage();
				die();
			}
 
			for ($i = 1; $i < $numargs; $i++)
			{
				$this->stmt->bindParam($i, $this->{"arg$i"});
			}
 
			for ($i = 1; $i < $numargs; $i++)
			{
				$this->{"arg$i"} = func_get_arg($i);
			}	
 
			$this->affected_rows = $this->stmt->execute();
 
			$this->last_inserted_id = $this->dbh->lastInsertId();
 
			if (intval($this->stmt->errorCode()) > 0)
			{
				if (config::debug_mode == true)
				{
					$err = $this->stmt->errorInfo();
					throw new Exception($err[2]);
				}
				else
				{
					throw new Exception("There was an error in your sql statement");
				}
			}
		}
	}
 
	public function query($sql)
	{
		if (!$this->dbh->getAttribute(PDO::ATTR_CONNECTION_STATUS))
		{
			$this->connect();
		}
 
		$count = count(split("\?", $sql));
		$numargs = func_num_args();
 
		if ($count != $numargs)
		{
			throw new Exception("Number of arguments does not match the number of required parameters");
		}
 
		try
		{
			$this->stmt = $this->dbh->prepare($sql);
		}
		catch (PDOException $e)
		{
			echo $e->getMessage();
			die();
		}
 
		for ($i = 1; $i < $numargs; $i++)
		{
			$this->stmt->bindParam($i, ${"arg$i"});
		}
 
		for ($i = 1; $i < $numargs; $i++)
		{
			${"arg$i"} = func_get_arg($i);
		}	
 
		$this->stmt->execute();
 
		if (intval($this->stmt->errorCode()) > 0)
		{
			if (config::debug_mode == true)
			{
				$err = $this->stmt->errorInfo();
				throw new Exception($err[2]);
			}
			else
			{
				throw new Exception("There was an error in your sql statement");
			}
		}
 
		$this->row_count = $this->stmt->rowCount();
 
		$this->column_count = $this->stmt->columnCount();
	}
 
	public function get_column($column_num)
	{
		return $this->stmt->fetchColumn($column_num);
	}
 
	public function fetch($type = "both", $direction = "forward")
	{
		if ($type=="both")
		{
			return $this->stmt->fetch(PDO::FETCH_BOTH);
		}
		elseif ($type=="assoc")
		{
			return $this->stmt->fetch(PDO::FETCH_ASSOC);
		}
		elseif ($type=="num")
		{
			return $this->stmt->fetch(PDO::FETCH_NUM);
		}
	}
 
	public function get_result_array()
	{
 
	}
}
?>

The first thing you’ll notice is the constructor calls the "connect" function that uses the config static properties to connect to whatever database you are trying to connect too. The config file should look something like this:

<?php
class config
{
	// DB settings
	Const db_host     		= 'localhost';
	Const db_username 		= 'root';
	Const db_password 		= 'password';
	Const db_name     		= 'dbname';
	Const db_dbms	  		= 'mysql';
        Const debug_mode		= true;
}
?>

Using class constants is much faster than using the typical "define" global variable delcaration and is more secure.

Next we have the "command" function. The "command" function is the function you use to send commands (INSERT, UPDATE, DELETE) to your database. This function receives the $sql parameter and then a variable number of parameters after it.

$dbo = new dbo();
$sql = "INSERT INTO database (field1, field2) VALUES(?, ?)";
$dbo->command($sql, "FirstName", "LastName");

Super simple right? Now you may be asking yourself, "Wait, where do you specify your quotes? And why aren"t you escaping your values?". The answer is that PDO automatically detects what data types are passed to it and escapes any strings so your code is immune to SQL injection. So how do you take advantage of those cool prepared queries that are only compiled once? Simple!

$db = new db();
$sql = "INSERT INTO database (field1, field2) VALUES(?, ?)";
$db->command($sql, "John", "Doe");
$db->command("{prev}", "Jim", "Bob");
$db->command("{prev}", "Beau", "Brownlee");

The "{prev}" command tells the "command" function not to send the query again, but to use the previous query and just send the next 2 values and execute the pre comiled query again. This can reduce overhead on your DBMS significantly and an added bonus is the fact that you don’t have to send your query over the TCP connection to your database. This means that you will only be sending the values you want to use in your query instead of the entire query every single time. This can save a TON of bandwidth and increase the overall performance of your system.

So now we know how to send commands, how do we query data? Simple, just use the "query" command. This command is optimized to query data and returns data to the "rowcount" and "columncount" properties in the database object. To get the data we simply use the "fetch" function that will (by default) return an associative and a numeric array. So here’s how we use this:

$db = new db();
$sql = "SELECT * FROM users WHERE ID = ? ORDER BY FirstName";
$db->query($sql, 10);
 
while ($row = $db->fetch())
{
     echo $row[0] . "<br />";
     echo $row['lastname'];
}

Yay! So easy, secure, and database independant! This same code will work for other DBMSs such as Microsoft SQL and Postgres, simply change the dbms in the config file and your good to go!

Debugging

Debugging your database code is always very sensitive as far as security is concerned. You want to have as much information passed to you when you are developing but when you move to a production environment you want only the bare minimum information passing from your php application to the browser. To toggle this, simply turn debug_mode to "true" and this will display more detailed errors when you are in a development environment. Switch this back to false to display only the bare minimum information to your application users.

Conclusion

PDO is a powerful mechanism for connecting to database management systems. PHP6 will be including the functionality by default and PHP is tending to move more in the direction of PDO and away from individual drivers that require specific coding conventions. Generic database abstraction layers are very powerful in helping you to build a robust application very quickly and with a smaller learning curve. It frees you to think about the logic of your application, not stressing over how to connect to a database.

Tags: PDO, PHP

This entry was posted on Monday, October 27th, 2008 at 10:50 pm and is filed under PHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.