Roll your own PDO PHP Class
Oct 01, 2012
Table of contents:
- Setting up the files
- Construct
- Query
- Bind
- Execute
- Result Set
- Single
- Row Count
- Last Insert Id
- Transactions
- Debug Dump Parameters
- Using your PDO class
- Insert a new record
- Insert multiple records using a Transaction
- Select a single row
- Select multiple rows
- Conclusion
Last week I introduced PDO, a database abstraction layer that you can use in your applications to allow for future flexibility in your database choice and protect you from SQL Injection.
Whilst the class we looked at is great for getting started with PDO quickly, it does have some restrictions if you want to do more complicated things than simple selects, inserts, updates and deletes.
I think it is also far more beneficial to know exactly what is going on under the hood of your code. By knowing exactly how something works you will gain a much deeper sense of understanding and you will be able to troubleshoot and advance your code when the time comes.
So if you haven’t read last week’s post, go back and read that as an introduction to PDO, Prevent PHP SQL Injection with PDO Prepared Statements.
Setting up the files
The first thing to do is to create the files we are going to need to work with.
Firstly we need a file to hold our class. I will be referring to this as databass.class.php.
Secondly we need a file to use our new class, I will be referring to this as tutorial.php.
Next, in the database.class.php we need to create the class. Copy and paste the following into that file.
<?php
class Database
{
}Here we are simply defining a new class called Database.
Now in the tutorial.php file we need to include the class. Copy and paste the following line to include the database class in your tutorial file.
// Include database class
include "database.class.php";Next, still in tutorial.php, we need to define our connection configuration.
// Define configuration
define("DB_HOST", "localhost");
define("DB_USER", "username");
define("DB_PASS", "password");
define("DB_NAME", "database");Back in database.class.php, we need to set up the variables in the class.
private $host = DB_HOST;
private $user = DB_USER;
private $pass = DB_PASS;
private $dbname = DB_NAME;And finally we need to instantiate a new instance of the database class.
// Instantiate database.
$database = new Database();Hopefully you understand all of the above as there is nothing specifically PDO related so far.
Construct
The first method we will look at is the construct method.
public function __construct() {
}When a class has a construct method, it is automatically called on all newly-created objects. This enables you to set up any initialisation you might need on the new object before it is used.
Database Source Name
The next thing to do is to set the Database Source Name.
When using PDO, you need to set the database connection method because PDO can be used with many different types of database. Each different database has a slightly different connection string. To set a particular database connection type, we just set the specific string.
// Set DSN
$dsn = "mysql:host=" . $this->host . ";dbname=" . $this->dbname;In this example I’m setting the DSN to MySQL and I’m using the host and the database name we defined earlier.
If you know you are going to be using MySQL for the foreseeable future, this is probably going to be fine. However, it does take away some of the flexibility and reusability of PDO by hardcoding the connection into a string like this. To make this more flexible and resizable you would use Dependency Injection. I think Dependency Injection deserves it’s own tutorial, so I’ll leave that for another day. Just be aware that you could make your construct a lot more future proof.
Options
When connecting to the database we can set a couple of different attributes. To set these options on connection, we simply include them as an array.
// Set options
$options = [
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];PDO::ATTR_PERSISTENT
This option sets the connection type to the database to be persistent. Persistent database connections can increase performance by checking to see if there is already an established connection to the database.
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
PDO can use exceptions to handle errors. Using ERRMODE_EXCEPTION will throw an exception if an error occurs. This then allows you to handle the error gracefully.
To read more about PHP exceptions, take a look at The ins and outs of PHP exceptions.
Handling exceptions in PHP deserves it’s own tutorial and is out of the scope of this tutorial. I’ll write a tutorial for handling exceptions in the coming weeks.
Try/Catch
Next we use a try/catch block to attempt to make a connection, or handle any exceptions if an error occurs.
First we need to create a new variable, $dbh (Database Handler).
Declare a new variables at the top of your class for the Database Handler and any errors.
private $dbh;
private $error;Back in the construct method we attempt a new PDO instance.
try {
$this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
}Next we catch any errors that might occur.
// Catch any errors
catch (PDOException $e) {
$this->error = $e->getMessage();
}Now whenever you create a new instance of your database class, a new PDO instance will be created. If any errors occur when trying to create a new PDO instance, they will be handled gracefully.
So far your class should look like this:
class Database
{
private $host = DB_HOST;
private $user = DB_USER;
private $pass = DB_PASS;
private $dbname = DB_NAME;
private $dbh;
private $error;
public function __construct()
{
// Set DSN
$dsn = "mysql:host=" . $this->host . ";dbname=" . $this->dbname;
// Set options
$options = [
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];
// Create a new PDO instanace
try {
$this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
} catch (PDOException $e) {
// Catch any errors
$this->error = $e->getMessage();
}
}
}Query
The next method we will be creating is the query method.
The query method introduces the $stmt variable to hold the statement.
So firstly, at the top of the class, declare the new variable.
private $stmt;Prepare
The query method also introduces the PDO::prepare function.
The prepare function allows you to bind values into your SQL statements. This is important because it takes away the threat of SQL Injection because you are no longer having to manually include the parameters into the query string.
Using the prepare function will also improve performance when running the same query with different parameters multiple times.
Copy and paste the following method into your class:
public function query($query) {
$this->stmt = $this->dbh->prepare($query);
}Bind
The next method we will be looking at is the bind method. In order to prepare our SQL queries, we need to bind the inputs with the placeholders we put in place. This is what the Bind method is used for.
The main part of this method is based upon the PDOStatement::bindValue PDO method.
Firstly, we create our bind method and pass it three arguments.
public function bind($param, $value, $type = null) {
}Param is the placeholder value that we will be using in our SQL statement, example :name.
Value is the actual value that we want to bind to the placeholder, example “John Smith”.
Type is the datatype of the parameter, example string.
Next we use a switch statement to set the datatype of the parameter:
if (is_null($type)) {
switch (true) {
case is_int($value):
$type = PDO::PARAM_INT;
break;
case is_bool($value):
$type = PDO::PARAM_BOOL;
break;
case is_null($value):
$type = PDO::PARAM_NULL;
break;
default:
$type = PDO::PARAM_STR;
}
}Finally we run bindValue.
$this->stmt->bindValue($param, $value, $type);The final bind method is:
public function bind($param, $value, $type = null) {
if (is_null($type)) {
switch (true) {
case is_int($value):
$type = PDO::PARAM_INT;
break;
case is_bool($value):
$type = PDO::PARAM_BOOL;
break;
case is_null($value):
$type = PDO::PARAM_NULL;
break;
default:
$type = PDO::PARAM_STR;
}
}
$this->stmt->bindValue($param, $value, $type);
}Execute
The next method we will be look at is the PDOStatement::execute. The execute method executes the prepared statement.
public function execute() {
return $this->stmt->execute();
}Result Set
The Result Set function returns an array of the result set rows. It uses the PDOStatement::fetchAll PDO method. First we run the execute method, then we return the results.
public function resultset() {
$this->execute();
return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
}Single
Very similar to the previous method, the Single method simply returns a single record from the database. Again, first we run the execute method, then we return the single result. This method uses the PDO method PDOStatement::fetch.
public function single() {
$this->execute();
return $this->stmt->fetch(PDO::FETCH_ASSOC);
}Row Count
The next method simply returns the number of effected rows from the previous delete, update or insert statement. This method use the PDO method PDOStatement::rowCount.
public function rowCount() {
return $this->stmt->rowCount();
}Last Insert Id
The Last Insert Id method returns the last inserted Id as a string. This method uses the PDO method PDO::lastInsertId.
public function lastInsertId() {
return $this->dbh->lastInsertId();
}Transactions
Transactions allows you to run multiple changes to a database all in one batch to ensure that your work will not be accessed incorrectly or there will be no outside interferences before you are finished. If you are running many queries that all rely upon each other, if one fails an exception will be thrown and you can roll back any previous changes to the start of the transaction.
For example, say you wanted to enter a new user into your system. The create new user insert worked, but then you had to create the user configuration details in a separate statement. If the second statement fails, you could then roll back to the beginning of the transaction.
Transactions also prevent anyone accessing your database from seeing inconsistent data. For example, say we created the user but someone accessed that data before the user configuration was set. The accessing user would see incorrect data (a user without configuration) which could potentially expose our system to errors.
To begin a transaction:
public function beginTransaction() {
return $this->dbh->beginTransaction();
}To end a transaction and commit your changes:
public function endTransaction() {
return $this->dbh->commit();
}To cancel a transaction and roll back your changes:
public function cancelTransaction() {
return $this->dbh->rollBack();
}Debug Dump Parameters
The Debut Dump Parameters methods dumps the the information that was contained in the Prepared Statement. This method uses the PDOStatement::debugDumpParams PDO Method.
public function debugDumpParams() {
return $this->stmt->debugDumpParams();
}Using your PDO class
Now that we’ve finished writing the database class, it’s time to test it out.
The remaining bit of this tutorial will be using the tutorial.php file you created at the very start.
I’m going to be using the same MySQL table that I created in last week’s tutorial. If you haven’t read last week’s tutorial, you can create the table by running the following SQL.
CREATE TABLE mytable (
ID int(11) NOT NULL AUTO_INCREMENT,
FName varchar(50) NOT NULL,
LName varchar(50) NOT NULL,
Age int(11) NOT NULL,
Gender enum('male','female') NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;Insert a new record
Firstly you need to instantiate a new database.
$database = new Database();Next we need to write our insert query. Notice how I’m using placeholders instead of the actual data parameters.
$database->query(
"INSERT INTO mytable (FName, LName, Age, Gender) VALUES (:fname, :lname, :age, :gender)"
);Next we need to bind the data to the placeholders.
$database->bind(":fname", "John");
$database->bind(":lname", "Smith");
$database->bind(":age", "24");
$database->bind(":gender", "male");And finally we run execute the statement.
$database->execute();Before running the file, echo out the lastInsertId function so you will know that the query successfully ran when viewed in the browser.
echo $database->lastInsertId();Insert multiple records using a Transaction
The next test we will try is to insert multiple records using a Transaction so that we don’t have to repeat the query.
The first thing we need to do is to begin the Transaction.
$database->beginTransaction();Next we set the query.
$database->query(
"INSERT INTO mytable (FName, LName, Age, Gender) VALUES (:fname, :lname, :age, :gender)"
);Next we bind the data to the placeholders.
$database->bind(":fname", "Jenny");
$database->bind(":lname", "Smith");
$database->bind(":age", "23");
$database->bind(":gender", "female");And then we execute the statement.
$database->execute();Next we bind the second set of data.
$database->bind(":fname", "Jilly");
$database->bind(":lname", "Smith");
$database->bind(":age", "25");
$database->bind(":gender", "female");And run the execute method again.
$database->execute();Next we echo out the lastInsertId again.
echo $database->lastInsertId();And finally we end the transaction:
$database->endTransaction();Select a single row
The next thing we will do is to select a single record.
So first we set the query.
$database->query(
"SELECT FName, LName, Age, Gender FROM mytable WHERE FName = :fname"
);Next we bind the data to the placeholder.
$database->bind(":fname", "Jenny");Next we run the single method and save it into the variable $row.
$row = $database->single();Finally, we print the returned record to the screen.
echo "<pre>";
print_r($row);
echo "</pre>";Select multiple rows
The final thing we will do is to run a query and return multiple rows.
So once again, set the query.
$database->query(
"SELECT FName, LName, Age, Gender FROM mytable WHERE LName = :lname"
);Bind the data.
$database->bind(":lname", "Smith");Run the resultSet method and save it into the $rows variable.
$rows = $database->resultset();Print the return records to the screen.
echo "<pre>";
print_r($rows);
echo "</pre>";And finally display the number of records returned.
echo $database->rowCount();Conclusion
And there you have it, your first rolled PDO database abstraction class. Hopefully this tutorial has introduced the main aspects of PDO and how it actually works. I think actually creating something like this is far more beneficial than using someone else’s library because you need to fully understand how all of this works, even if it takes longer in the long run.
I’ve missed out, or brushed over a number of important areas throughout this tutorial to try and keep it on topic. If you are interested in learning more about these related areas, subscribe to our RSS feed or follow us on Twitter to keep up to date with the latest posts and tutorials.
If you have any questions, or you can a think of a way to improve this class, please leave a comment!
