PHP: MySQL Abstraction and Wrappers

January 17, 2011 by PHP  

Happy new year to everyone, may this be the best year ever... (and every person that visit this site make a trillion euro this year)

Okay, now with all the cliches out of the way, I am going to start my blogging year out with a PHP post.(Originally I decided to abandon this post, but perhaps someone out there can find some value in this content.)

It is commonplace for PHP developers to use MySQL as their database engine; the database engine of choice for most.

PHP features a number of options/extensions in order to access MySQL, we've got the legacy mysql extension thats been around since PHP 4, the mysqli extension which was introduced with the advent of PHP 5 and the PDO extension.

Generally we need to avoid using the legacy mysql extension when we're faced with new development and rather use the new mysqli extension - since it supports the new functionality (e.g. Stored Procedures, charsets, prepared statements etc) added to MySQL over the years.

The PDO extension by contrast functions as a common abstraction that uses a driver based approach, which supports MySQL or any other db engine with a PDO driver (not going to go into too much detail).

Assuming that we decided on using the mysqli extension, returning rows of data from a query will look something like this:


// Check if MySQLI Extension exists

if (!function_exists('mysqli_connect')) {
	throw new Exception('MySQLI Extension not found!');

// Connect to MySQL database

$mysqli = @new mysqli("host", "user", "pass", "db");

if (mysqli_connect_errno()) {
    throw new Exception(mysqli_connect_error());

// Sanitize values

$id = (int)$_POST['id'];
$title = $mysqli->real_escape_string($_POST['title']);
$body = $mysqli->real_escape_string($_POST['body']);

$query = "SELECT * FROM something WHERE title = '$title' AND body LIKE '%$body%' AND id = $id";

// Execute Query

$result = $mysqli->query($query);

// Throw exception if something goes wrong with the query
// supposed to be default behavior in my opinion

if ($mysqli->error) {
	throw new Exception($mysqli->error);

// Render rows

if (is_object($result))
	echo '<table>';
	while ($row = $result->fetch_assoc()) 
		echo '<tr>'. 
	echo '</table>';

// Close connection - apparently not necessary according to PHP documentation 



The preceding snippet is a rather horrible snippet if we use it as is.

- from a reusability perspective
  1. The preceding snippet represents a common task when working with mysql e.g. returning rows of data - rather place this functionality in a common place, like a class/function.

  2. Connection string hardcoded, this needs to be in some kind of configuration file somewhere.

  3. Entangled SQL/HTML/PHP can lead to some serious unmanageable code (spaghetti code).

- from a coupling perspective
  1. Being solely dependent on the mysqli extension doesn't necessarily make sense, since there is a number of extensions available for Mysql.

  2. Dependent on Mysql - PHP supports quite a number of database engines, why restrict your script?

For this purpose (among others) I created a wrapper class that contains some of the basic functionality you'll need in order to access MySQL.

The following snippet is a rewrite of the previous snippet using the wrapper class, observe:
// Place within config file - Note this won't open an active MySQL Connection

MySQL::Config('host', 'user', 'pass', 'db');

// Open MySQL Connection and Execute Query, Connection closed on object destruct
// Perhaps move this to some kind of datalayer e.g. one for MySQL, one for PostgreSQL etc...

$rows = MySQL::Create('SELECT * FROM something 
			WHERE title = ?title 
			AND body LIKE ?body 
			AND id = ?id')
	->Parameter('title', $_POST['title'])
	->Parameter('body', '%'.$_POST['body'].'%')
	->Parameter('id', $_POST['id'], 'integer')

// Consider replacing with datagrid control/class

if (isset($rows))
	echo '<table>';
	foreach($rows as $row)
		echo '<tr>'. 
	echo '</table>';

This means less code and also makes us less dependent on the mysqli extension e.g. if mysqli gets deprecated tomorrow we simply change the underlying code within the MySQL wrapper.

Now this is where it gets a bit more interesting and the reason I initially sacked this post.

In the download that accompanies this post, you will notice a folder named "alternative", this folder contains an implementation of the strategy design pattern (won't go into too much detail in this post).

The basic idea is to create different "strategies" that represents the best possible way to access MySQL in context of what is available on the server that hosts the script.

In order to create a strategy, you need to extend the MySQLStrategy abstract class (like seen below) - which requires one to provide an implementation for the required methods.

abstract class MySQLStrategy
	protected $resource;
	protected $count;
	protected $host;
	public function __construct(MySQL $host) {
		$this->host = $host;

	abstract public function AffectedRows();
	abstract public function Count();
	abstract public function Escape($value);
	abstract public function LastId();
	abstract public function Open();
	abstract public function Query();
	abstract public function NonQuery();
	abstract public function Scalar();
	abstract public function Close();	


Within the 1.0/alternative/includes folder, you will find two implementations I already created, one represents the mysql legacy extension (MySQL_Strategy.php), the other the mysqli extension (MySQLI_Strategy.php), didn't create one for the MySQL PDO yet.

In the MySQL class constructor (MySQL.php) we determine the "context" in order of which extension is best and available, observe:
private function __construct(array $settings) {
	$this->settings = $settings;
	if (function_exists('mysqli_connect')) {
		$this->_strategy = new MySQLI_Strategy($this);
	} else if (function_exists('mysql_connect')) {
		$this->_strategy = new MySQL_Strategy($this);
	} else {
		throw new Exception("No compatible MySQL strategy found");

I feel this approach (strategies) might be a bit of an overkill (comes with performance penalties), especially if we ask the question if we'll ever be in the position (when working with PHP 5) where the mysqli extension won't be available?

Even if it isn't available (when using a wrapper), we can easily migrate by changing the underlying code of the wrapper to another extension either way - without needing the additional overhead.

But hey, whats your opinion?

Additional Reading:

Leave a Comment

Related Downloads

Simple PHP MySQL Wrapper