Using PDO for Database Access in PHP

History

PDO stands for PHP Data Objects and was introduced in version 5.1.   It's a data access abstraction layer which provides a consistent interface for accessing different types of databases.   

Prior to PDO, developers relied on extensions tailored to each database engine such as mysql/mysqli (MySQL) or pgsql (Postgres).    The mysql extension was deprecated in 5.5 and removed entirely in version 7.   The mysqli (MySQL improved) extension is still supported, but many developers have started using PDO because it offers some important benefits.

Advantages of PDO

  • Support for 12 different database engines – the abstraction offerred by PDO allows a developer to painlessly migrate to a different database without major code changes
  • Prepared statements – offers protection from SQL injection attacks which compromise database security
  • Transaction Processing –  wrapping statements in a transaction allows you to rollback the entire set if you encounter an error.   
  • Stored Procedures – PDO has support for calling stored procedures when working with database engines such as MySQL, MS SQL Server etc.
  • Reduced learning curve – universal API eliminates the need for developers to learn different database access functions 

Check if PDO is installed

If you're running a recent version of PHP, chances are that PDO is already installed.   The extension should be enabled along with the driver for the database engine you are using.

php -i | grep PDO
PDO
PDO support => enabled
PDO drivers => mysql
PDO Driver for MySQL => enabled

You can also run the following code in PHP to display available database drivers:

var_dump(PDO::getAvailableDrivers());

Create a PDO object

We'll use the "new" operator to create an object and pass a connection string to the constructor.  This connection string includes the database type (mysql), hostname, database name, username, and password.

Wrapping the code in a try-catch block will allow us to handle any exceptions gracefully. 

We also set an attribute so PDO will throw exceptions on errors (default is to remain silent).   

try 
{
    $db = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', 'password');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) 
{
    return $e;
}

prepare/execute

When you are passing input data to a query, SQL injection becomes a concern.  The prepare method allows you to bind parameters to avoid the need to escape or quote them.

The execute method will execute the prepared statement.  If the statement includes parameters like the one below, then you can pass these to execute using an array.

$sth = $db->prepare("
      SELECT *
      FROM users
      WHERE id = :id");
      
$sth->execute(array(':id' => $id));

fetch vs fetchAll

There are 2 methods available for retrieving records from the database

fetch() will retrieve the next row from the query result

$result = $sth->fetch(PDO::FETCH_ASSOC);

The PDO::FETCH_ASSOC argument passed to fetch is called a fetch style.   It tells the method to return an array with column names for the indexes in the results.

So if we looked at the contents of $result, we would see something like below:

Array
(
[id] => 100
[firstname] => John
[lastname] => Doe
)

If you have multiple rows, then you can use a while loop to iterate through each row:

while($row = $sth->fetch(PDO::FETCH_ASSOC)) 
{
	// Do something with $row 
}

fetchAll() will retrieve all the rows from the query result

$sth = $db->prepare("SELECT * FROM orders WHERE type = :type");
$sth->execute([
	  ':type' => $type
 ]);
$result = $sth->fetchAll();

Getting the row count

We can use the rowCount method from the statement to get the number of rows affected by a query.   Note that we may use the query method here instead prepare/execute because we aren't passing any user input data.

$result = $db->query("SELECT * FROM orders);
$count = $result->rowCount();

Getting the last insert ID

$stmt = $db->prepare("
INSERT INTO users (first_name,last_name,email,active,password)
VALUES ('John','Doe','test@email.com', true, 1, '12345');
");
$stmt->execute();
$id = $db->lastInsertId();

Official Documentation 

http://php.net/manual/en/book.pdo.php