Steven Berridge

Web Developer

Creating a MySQL class using PDO

In this tutorial we'll be using the PDO library to create a PHP class for handling MySQL queries. We'll be covering some basic Object Oriented programming techniques along with the use of the PDO library.

First off we'll define our Sql class.

class Sql {

	private static $dbHandle;

	public function __construct() {

	}
}

Above you'll see the base for the Sql class, right now it only has a construct function and a static property for holding the connection to the database.

The construct function will run every time an instance of the class is created, and we'll use that to check if our database connection has been created. The connection itself will be held in a static property, meaning all instances of the SQL class will share the same connection.

public function __construct() {
	if(is_null(self::$dbHandle)) {
		self::$dbHandle = new PDO(
			'mysql:host=localhost;dbname=myDatabase;',
			'username',
			'password',
			array(
				PDO::ATTR_EMULATE_PREPARES=>false
			)
		);	
	}
}

Whenever an instance of the Sql class is created it will now check if the static dbHandle property is null, if it is then a database connection will be created via the PDO class and stored within the property.

As a quick explanation of PDO; you create a database connection by constructing a PDO object and passing it 4 arguments, a connection string, a username, a password and an optional options array. When connecting to a MySQL database the connection string begins with "mysql:" and is then followed by the database host and the database name.

Now that we have a method of connecting to the database we can now add some methods to create a raw query.

To begin with we'll need to add some new properties to the Sql class.

private $raw = false;
private $query;
public $params = array();

These properties will, respectively, define whether or not this Sql instance is running a raw query, hold our query and hold any parameters used within the query, i.e. any "where" variables.

Next we'll add the method which will initiate the raw querying process, because this is the first step we'll make the function static for convenience.

public static function raw($query,$params=array()) {
	$sql = new Sql;
	$sql->query = $query;
	$sql->raw = true;
	$sql->params = $params;
	return $sql;
}

This method takes a query string and an array of parameters. The method creates a new instance of Sql, sets the query, raw and param properties and then returns the instance so we can chain the next method.

public function run() {
	if(!$this->raw) {
		throw new Exception('Query is not raw');
	}
	$query = self::$dbHandle->prepare($this->query);
	$this->execute($query,$this->params);
	return $query->fetchAll(PDO::FETCH_ASSOC);
}
private function execute($query,$params=array()) {
	if(!$query) {
		$errorInfo = self::$dbHandle->errorInfo();
		throw new Exception('There was an error with the query: '.$errorInfo[2]);
	}
	$query->execute($params);
}

The run method first checks that this Sql instance has been set up to run a raw query, then prepares the query and executes it using the execute() method. This method takes a PDO query object and an array of parameters, checks the query doesn't have any errors and then executes it. The run method then returns all the results from the query.

With these functions and parameters in place we now have enough to perform raw SQL queries.

$results = Sql::raw('SELECT * FROM myTable WHERE id = ?',array(1))->run();

Now that we can have a function for executing queries we could take this a step further, and create a way to build queries up using a chain of different functions. After doing this we will be able to build queries like this.

$results = Sql::table('myTable')->select('*')->where('id','=',1)->get();
Sql::table('myTable')->whereIn('id',array(1,2,3))->get();

To begin with we'll need to add a few new properties to the Sql class to store some extra values.

private $table;
private $select = array();
private $where = array();

These properties will hold the table we're selecting from, the columns we want to select and any conditions the data has to pass to be selected. Next we need to add the methods which set these properties, the method which initiates the query building process will be table(), so we'll make that a static method.

public static function table($table) {
	$sql = new Sql;
	$sql->table = $table;
	return $sql;
}

Nothing too complicated here, the table function simply takes a table name, creates a new instance of Sql, sets the table property then returns the Sql instance.

public function select($columns) {
	if(!is_array($columns)) {
		$columns = array($columns);
	}
	$this->select = $columns;
	return $this;
}

Again, nothing too complicated, select() simply takes a string or an array of column names, we're checking that $columns is an array, if it isn't then we're assuming it's a string and putting it inside an array, then we're storing those columns in the "select" property. At the end we're returning $this so we can chain methods together.

public function where($column,$comparator,$value) {
	$this->where[] = array(
		$column,
		$comparator,
		'?',
		'AND'
	);
	$this->params[] = $value;
	return $this;
}

The where() function takes 3 arguments, a column name, a comparator such as = or > and a value. These are put into an array and added to the "where" property along with the logic for the WHERE operation, i.e. AND or OR. When put together these will form the where portion of our query. FInally we add the value to the "params" property and return $this so we can chain the next method.

public static function generatePlaceholders($array) {
	return substr(str_pad('',count($array)*2,'?,'),0,(count($array)*2)-1);
}

public function whereIn($column,$params=array()) {
	$this->where[] = array(
		$col,
		'IN',
		'('.self::generatePlaceholders($params).')',
		'AND'
	);
	$this->params = array_merge($this->params,$params);
	return $this;
}

The whereIn() function is a little more complex. It only takes 2 arguments, a column name and an array of parameters. Another array is added to the "where" property containing the column name, the comparator "IN" and a string of placeholders encased in brackets in the place of the single placeholder that's seen in the where() function. Since the parameters being passed into this function are in an array we need to merge them with any parameters that have already been added to the "params" property, which is done using the array_merge() function.

To help us create the placeholders in the query we've added another static helper function, generatePlaceholders(), which takes an array and returns a string of placeholders.

$placeholders = self::generatePlaceholders(array(1,2,3));
echo $placeholders;
// result: '?,?,?'

Now for the final function, get(), this will build our query and execute it returning any results.

public function get() {
	$sqlString = 'SELECT ';

	if(is_array($this->select) && count($this->select) > 0) {
		$sqlString .= implode(',',$this->select);
	} else {
		$sqlString .= '*';
	}

	$sqlString .= ' FROM '.$this->table;

	if(isset($this->where) && count($this->where) > 0) {
        $sqlString .= " WHERE ";
        foreach($this->where as $key=>$where) {
        	if($key > 0) {
        		$sqlString .= " ".$where[3]." ";
        	}
            $sqlString .= $where[0]." ".$where[1]." ".$where[2];
        }
    }
	
	$query = self::$dbHandle->prepare($sqlString);
	$this->execute($query,$this->params);
	$results = $query->fetchAll(PDO::FETCH_ASSOC);
	return $results;
}

There's quite a lot going on in this function so I'll break it down step by step.

When put together this class can now run raw queries and build up simple queries with ease. This class could be extended further to provide access to more Sql features, such as table joins and result ordering, as well as more "where" methods.

So go ahead and build on this, experiment and see what you can come up with.