Essential PDO

While PDO is a very useful library to manipulate relational data stores with PHP, its documentation and API is burdened with noise. Here's a little summary of what you probably only need to know to use it. It's not complete and doesn't cover edge cases. If while using the library you experience some inconsistencies not documented here, please refer to the official doc. The goal of this memo is to expose those few features that are more likely to be useful in your next 50 projects. Features that allow enough flexibility to express what you need in SQL and manipulate it in PHP. I wrote this because I was annoyed to have to peruse PDO's nebulous doc each time I wanted to leave the comfort of ORM libraries to work directly in SQL.

Overview

  • there are 2 main classes of interest: PDO and PDOStatement.
  • use PDO to create a connection to the db.
$connection = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
  • here are PDO's methods you should remember:
PDO::prepare()          # to create a PDOStatement (prepared statement).
PDO::beginTransaction() # to begin a transaction.
PDO::commit()           # to commit a transaction.
PDO::rollBack()         # to roll back a transaction.
  • PDO has 2 methods, query() and exec() to directly perform queries on the db, but in my opinion they don't provide much value and just contribute redundancy and noise to the API. My advice, forget them.

  • basically just remember this pattern for PDO and you should be set:

$connection = PDO($connect_string);
try{
    $connection->beginTransaction();
    $stmt = $connection->prepare($sql_template);

    # ... some work with $stmt here ...

    $connection->commit()
} catch(Exception $e){
    $connection->rollBack()
}
  • PDOStatement objects (prepared statements) are the recommended method to manipulate your data as they can escape user provided data implicitly.
  • You use the PDO::prepare() method and pass an "sql template" to create a PDOStatement object.
$sql_template = 'INSERT INTO products (sku, name, quantity) VALUES (?,?,?)';
$stmt = $connection->prepare($sql_template);
  • PDOStatement are also useful when you need to re-use the same query multiple times with different values, such as when inserting or updating multiple records at once.
foreach($rows as $r){
    $stmt->execute($r);
}
  • when the executed statement is a select type, the PDOStatement object also becomes the resultset and data can be fetched with one of its fetch*() methods (not the best design in my opinion, it feels as though an intermediate PDOResult object would have been useful here).
$stmt->execute('SELECT * FROM products');
$rows = $stmt->fetchAll();
foreach($rows as $row){
    # work with $row here
}
  • you should note though, that PDOStatement::execute() also has an actual return value, that indicates success or failure for SELECT type queries and the count of affected rows for INSERT, UPDATE and DELETE operations.
$delete_sql = 'DELETE FROM products WHERE sku LIKE ?'; 
$stmt = $connection->prepare($delete_sql);
$number_of_affected_rows = $stmt->execute('HV8%');

Examples on using the PDO object

Create a connection to the db (a PDO object)
$connection = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
select query with $connection (not recommended)

As stated earlier I advise to forgo the PDO::query() and PDO::exec() methods and just get familiar with the PDOStatement::execute() method. But quick and dirty work is unavoidable, so if you must do it...

$sql = "SELECT * FROM users WHERE name IN " . $names;
# you have to clean the statement yourself
$clean_sql = some_sql_cleaning_function($sql);
# fetching rows
$rows = $connection->query($clean_sql)
# $rows is actually a PDOStatement object that's already 
# been prepared and executed so it can act as a resultset.  
foreach($rows as $row){
    print $row['name'];
}

# If you don't fetch all of the data from $rows, it's recommended to 
# release the database resources explicitly, prior to your next call 
# to $connection->query()
$rows->closeCursor();

# you can specify a fetch mode (see FETCH constants next)
$rows = $connection->query($clean_sql, PDO::FETCH_ASSOC); 

the 4 FETCH constants you'll probably need (there are others)

# rows indexed by name. If 2 or more columns in the query have the
# same name, only one of them will be returned, so make sure to use
# aliases in your query.
PDO::FETCH_ASSOC 

# same as FETCH_ASSOC, except that columns with same name
# are bundled in an array. 
PDO::FETCH_NAMED

# indexed by column number.
PDO::FETCH_NUM

# indexed by both column name and number (this is usually the default).
PDO::FETCH_BOTH

To insert, update and delete with the PDO::exec() method (not advisable)

$sql = "DELETE FROM products WHERE category = ". $category;
# again, clean the statement yourself
$clean_sql = your_sql_escaping_func($sql);
$connection->exec($clean_sql);

Getting the id of the last inserted row. This one is safe.

# e.g. MySQL
$connection->lastInsertId();

# e.g. PostgreSQL
$sequence = 'users_id_seq';
$connection->lastInsertId($sequence);

Transactions

try {
    $connection->beginTransaction();

    # ... perform your queries here ...

    $connection->commit();
} catch (Exception $e) {
    $connection->rollBack();
}

Prepared statements

Prepared statements are useful for queries that have to be performed multiple times, with different values.

using named placeholders
# $connection is a PDO object
$stmt_A = $connection->prepare('INSERT INTO products (sku, name, quantity) 
                                VALUES (:sku, :name, :quantity)');
using questionmark placeholders
$stmt_B = $connection->prepare('INSERT INTO products (sku, name, quantity) 
                                VALUES (?, ?, ?)');
Executing prepared statement by binding values
  • named placeholders
$stmt_A->bindValue(':sku', 'HS8301');
# optional 3rd argument explicitly indicates the sql data type 
$stmt_A->bindValue(':name', 'McCavity Candies', PDO::PARAM_STR);
$stmt_A->bindValue(':quantity', 30, PDO::PARAM_INT);
$stmt_A->execute();

# inserting another record, with the same prepared statement
$stmt_A->bindValue(':sku', 'HS8403');
$stmt_A->bindValue(':name', 'Chocolate Splash');
$stmt_A->bindValue(':quantity', null, PDO::PARAM_NULL);
$stmt_A->execute();
  • questionmark placeholders
$stmt_B->bindValue(1, 'HS8301');
$stmt_B->bindValue(2, 'McCavity Candies');
$stmt_B->bindValue(3, 30);
$stmt_B->execute();
Executing prepared statement by binding params
  • named placeholders
$stmt_A->bindParam(':sku', $sku, PDO::PARAM_STR);
$stmt_A->bindParam(':name', $name);
$stmt_A->bindParam(':quantity', $quantity);

# inserting a row
$sku = 'HT5301';
$name = 'Pop Tangerine';
$quantity = 22;
$stmt_A->execute();

# inserting another row (we just change the value of bound variables)
$sku = 'VB3200';
$name = 'Chuppa Chups';
$quantity = 87;
$stmt_A->execute();
  • questionmark placeholders
$stmt_B->bindParam(1, $sku, PDO::PARAM_STR);
$stmt_B->bindParam(2, $name);
$stmt_B->bindParam(3, $quantity);

$sku = 'RM0923';
$name = 'Iceburger';
$quantity = 92;
$stmt_B->execute();
Executing prepared statement with array of values
  • named placeholders
$stmt_A->execute(array(
    ':sku'=>'VM0283',
    ':name'=>'Ginger Tea',
    ':quantity' => 87,
));
  • questionmark placeholders
$stmt_B->execute(array('GB2084', 'Snack Crackers', 87));
Fetching data with prepared statements
    $stmt_C = $connection->prepare('select * from products where category = ?');
    $stmt_C->execute(array('drinks'));

    # fetching all rows in an array
    $rows = $stmt_C->fetchAll(PDO::FETCH_NAMED);
    # fetching a single row
    $row = $stmt_C->fetch(PDO::FETCH_NAMED);

the FETCH style parameter is optional and defaults to FETCH_BOTH (see FETCH constants)

Gotchas

  • don't use a placeholder more than once in a statement
# don't
$sql = "INSERT INTO table_Y (id, created_at, updated_at) 
        VALUES (:id, :init_time, :init_time)";
# do 
$sql = "INSERT INTO table_Y (id, created_at, updated_at) 
        VALUES (:id, :create_time, :update_time)";
  • don't put a placeholder in the middle of a value
# don't
$stmt = $connection->prepare('SELECT * FROM users WHERE name LIKE "%?%"');
$stmt->bindValue(1, $name);

# do
$stmt = $connection->prepare('SELECT * FROM users WHERE name LIKE ?');
$stmt->bindValue(1, "%{$name}%");
  • there's no way to automate the placeholding in an IN clause.
# don't
$id_list = array(2, 49, 83, 120, 77);
$connection->prepare('SELECT * FROM users WHERE id IN (:id_list)');

# do (this has been my best attempt so far)
$id_list = array(2, 49, 83, 120, 77);
$placeholders = join(',', array_fill(0, count($id_list), '?'));
$stmt = $connection->prepare("SELECT * FROM users WHERE id IN ($placeholders)");
$stmt->execute($id_list);