What Is PDO?

PDO stands for PHP Data Objects. It is a layer built into PHP that gives you one set of functions for working with many databases.

With PDO, running a query always looks like this, whether the database is MySQL, PostgreSQL or SQLite:

1
2
3
4
5
6
7
8
9

<?php
$pdo = require __DIR__ . "/db.php";

$statement = $pdo->prepare("SELECT name FROM users WHERE id = :id");
$statement->execute(["id" => 2]);

$user = $statement->fetch();

echo $user["name"];

1

Ben

(db.php is the connection file you will write in Database Connections. It returns a PDO object.)

The two classes

You create a PDO once. Each query gives you a PDOStatement.

The main methods

Why PDO

1. Prepared statements protect against SQL injection (see Prepared Statements).
2. Exceptions for errors, so failures are not silent.
3. Fetch modes return rows as arrays, objects or your own classes.
4. One style for many databases. You can run tests on SQLite and production on MySQL.

query() vs prepare()

1
2
3
4
5
6
7

<?php
$pdo = require __DIR__ . "/db.php";

// Safe: the SQL has no outside input
foreach ($pdo->query("SELECT name FROM users ORDER BY name") as $row) {
    echo $row["name"] . " ";
}

1

Ana Ben Cy

query() runs SQL straight away. Use it only when no value from outside is part of the SQL. The moment a variable that came from a visitor goes into the query, use prepare().

PDO is not a database

PDO does not store anything. It is the telephone line between PHP and the database. You still need a MySQL (or other) server, a database and tables.

PDO vs an ORM

An ORM such as Eloquent (Laravel) or Doctrine builds on top of PDO. It lets you write User::find(2) instead of SQL. The ORM still produces SQL and sends it with PDO. Learning PDO first helps you understand what the ORM does.