PDO (PHP Data Objects) - Why I recommend them

PHP Data Objects, also known as PDO, is an interface for accessing databases in PHP without tying code to a specific database. This is my favorite method of accessing the database because who wants to tie all of their eggs to one basket? Rather than directly calling the MySQL, MySQLi, and PG extensions, developers can use the PDO interface, simplifying the porting of applications to other databases.

PHP Data Objects (PDO) doesn’t account for database-specific syntax, but still allows for switching databases and platforms to be mostly painless, by just switching the connection string in most cases.

PHP Data Objects can be used on any database that has a PDO driver written for it. At the time of writing this article, the following drivers and databases are supported:

Driver name	Supported Databases
PDO_CUBRID	Cubrid
PDO_DBLIB	FreeTDS / Microsoft SQL Server / Sybase
PDO_FIREBIRD	Firebird
PDO_IBM	IBM DB2
PDO_INFORMIX	IBM Informix Dynamic Server
PDO_MYSQL	MySQL 3.x/4.x/5.x
PDO_OCI	Oracle Call Interface
PDO_ODBC	ODBC v3 (IBM DB2, unixODBC and win32 ODBC)
PDO_PGSQL	PostgreSQL
PDO_SQLITE	SQLite 3 and SQLite 2
PDO_SQLSRV	Microsoft SQL Server / SQL Azure
PDO_4D	4D

Some of these databases probably won’t be supported on your system, but here is an easy way to tell which are:

<?php
    print_r(PDO::getAvailableDrivers());
?>

We are only going to talk about the MySQL database method right now, but keeping in mind that when using PHP Data Objects, we have the ability to change that one day without the need of rewriting a lot of code to do so.

Connecting to MySQL Database

A connection to MySQL using PDO should be done by using a try/catch statement. This will allow you to catch any connection errors. You can do this by doing the following:

<?php
try {
    $db = new PDO("mysql:host=$db_address;dbname=$db_name", $db_username, $db_secure);
} catch(PDOException $e) {
    $error_log = fopen('pdo_connection_errors.log', 'a');
    fwrite($error_log, "[" . date("F jS, Y @ g:i:s", time()) . "] ERROR: " . $e->getMessage() . ":: in file " . __FILE__ . " on line " . __LINE__ . "\n");
    fclose($error_log);
    die ("ERROR: " . $e->getMessage());
}
?>

There is a vast amount of information on PHP Data Objects (PDO) that can be found on PHP.net. This is where I spent most of my time learning PDO.

Insert and Update Statements

Inserting new data, or updating existing data is probably one of the most common things we do with databases. Here is how I would write an INSERT statement using PDO:

<?php
    $query = "INSERT INTO table (col1,col2,col3) VALUES (:col1,:col2,:col3)";
    $statement = $db->prepare( $query );
    $statement->execute( array( ':col1' => $col1, ':col2' => $col2, ':col3' => $col3 ) );
?>

And here is an UPDATE statement:

<?php
    $query = "UPDATE table SET col2 = :col2 WHERE col1 = :col1 and col3 = :col3 limit 1";
    $statement = $db->prepare( $query );
    $statement->execute( array( ':col2' => $col2, ':col1' => $col1, ':col3' => $col3 ) );
?>

There are two reasons why I write my statements like this. The first reason being that when you write out a statement in this matter, you aren’t as vulnerable to SQL injection attacks as you would be if you just included the variable within the query. The second reason being that if you wanted to do multiple insert statements using the same query, writing a prepared statement is a simple way to do this.

<?php
    $query = "INSERT INTO table (col1,col2,col3) VALUES (:col1,:col2,:col3)";
    $statement = $db->prepare( $query );
    $statement->execute( array( ':col1' => $col1, ':col2' => $col2, ':col3' => $col3 ) );
    $statement->execute( array( ':col1' => $col1, ':col2' => $col2, ':col3' => $col3 ) );
    $statement->execute( array( ':col1' => $col1, ':col2' => $col2, ':col3' => $col3 ) );
?>

The reason I like to use PDO is because things change all the time. You never know if one day, the database you are using will all of a sudden be the slowest thing out there. If this is the case, PDO gives you the ability to change your database without the hassle of having to re-write all of your code. There still may be a few lines that you will have to change, but not everything. That is why I recommend using it.

I hope this shows how simple PHP Data Objects can be and also how useful. There is so much more that could be covered, but I just thought that I would give a brief run down of PHP Data Objects and how to use them. As always, if you have questions, please feel free to start a conversation below and I would be happy to answer them.

PDO (PHP Data Objects) – Why I recommend them