PHP: Database Access With PDO

PDO, or PHP Data Objects, is a database abstraction extension that allows you to use a common set of commands to access any database for which PDO has a driver. This includes MySQL and compatibles (e.g., MariaDB), PostgreSQL, Oracle, Microsoft SQL Server, IBM DB2, SQLite, and others.

While it’s nice not having to rewrite all your queries for every database platform, PDO offers some additional benefits, like simpler syntax than database-specific libraries and easy query parameterization for avoiding SQL injection attacks. As of PHP 5.1, PDO is bundled with PHP; to check if it’s installed, you can run php -i from the command line or phpinfo() from a PHP script.

The initial database connection is typically the only part of a PDO procedure that requires any platform-specific arguments:

<?php 

$user = 'username';
$pass = 'password';
 
try {
    $dbh = new PDO('mysql:host=localhost;dbname=events', $user, $pass, 
               array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
} catch (PDOException $e) {
    return 'Fail: ' . $e->getMessage();
}

(Put your file containing your username and password outside your web root.) This is mostly self-explanatory: we’re connecting to a hypothetical MySQL database called events on localhost using the specified username and password. MYSQL_ATTR_INIT_COMMAND supplies the MySQL-specific command SET NAMES to configure our connection for the utf8 character set. The connection attempt is wrapped in a try-catch block to display the error in case something goes wrong.

Here’s a simple query:

$event_id = 25;

$stmt = $dbh->prepare('SELECT * FROM events WHERE event_id < :event_id ORDER BY date');
$stmt->bindParam(':event_id', $event_id, PDO::PARAM_INT);
$stmt->execute();
 
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo '<p>' . $row['date'] . '</p>';
}
 
?>

This binds the value of the $event_id variable to the event_id parameter in our SQL command to be executed. The prepare() method also encapsulates all parameters as data, which eliminates the need to perform manual escaping of variables being passed in to neutralize ‘ and ” characters that can be used for SQL injection. FETCH_ASSOC sets the fetch mode that determines how each row will be returned to the calling statement, the default being FETCH_BOTH. Finally, the while loop iterates over the results and prints them.

I find that specifying the datatype you’re expecting by using bindParam() with PDO constants like PARAM_INT or PARAM_STR is more intuitive for anyone (myself included) trying to use my queries. But if you don’t care to do this, queries can be even more concise:

$stmt= $connection->prepare('SELECT * FROM events WHERE event_id = ?');
$results = $connection->execute([$event_id]);

Other queries work as you’d expect:

$event_name = 'iPhone Exaltation Society';

$stmt = $dbh->prepare('DELETE FROM events WHERE event_name = :event_name');
$stmt->bindParam(':event_name', $event_name, PDO::PARAM_STR);
$stmt->execute();

To use SQL syntax that permits multiple values, like the MySQL IN condition, construct an array of your data and then use a function like str_pad() to insert a placeholder for each value:

$event_ids = array(1, 2, 3, 4, 5);

/* 
    Count the values in $event_ids and add a ? placeholder for each. You have to tweak the output of count() 
    because of how str_pad() works -- it pads a string with x number of characters. If you just do count($n_ids), 
    which equals 5, you get '?,?,?', which is 5 characters, obviously not enough to hold all the array values. 
    What we need is '?,?,?,?,?', or 9 characters, so we have to double the count and subtract by 1. 
*/
$stmt = $dbh->prepare('SELECT * FROM events WHERE event_id IN ('.str_pad('', count($event_ids)*2-1, '?,').')');

/* 
    Next we loop over the event_ids array and substitute each value for IN (?, ?, ?, ?, ?) in our prepared statement.
    Note: bindParam() binds by reference, so using $q => $id here gives you the last element of the iterated array (5).
    Use &$id to place each array element in our prepared statement during the loop. bindValue() would 
    work here with only $id. 
*/
foreach ($event_ids as $q => &$id) {
    $stmt->bindParam(($q+1), $id, PDO::PARAM_INT);
}

$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo '<p>' . $row['event_name'] . ': ' . $row['event_id'] . '</p>';
}

Finally, remember to release the connection handle:

$dbh = null;

Pretty easy, eh?