Zend PHP 7 Certification – Databases – PDO

23rd January 2018

This post covers the PDO section of the Databases chapter when studying for the Zend PHP 7 Certification.

The PHP Data Objects (PDO) extension assists users accessing databases in PHP. PDO provides a data-access abstraction layer which means regardless of which database you’re using, you can use the same functions to issue queries and fetch data.

To configure a database connection, a new PDO class should be instantiated passing in the database connection details. An example below shows a connection to a MySQL database.

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

Note that the DSN (the first parameter passed into the PDO class instantiation, can be set as a configuration directive within your php.ini configuration file.

pdo.dsn.mydb="mysql:dbname=testdb;host=localhost"

<?php

/* Connect to an ODBC database using an alias */
$dsn = 'mydb';
$user = '';
$password = '';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

The query() method of the PDO class executes an SQL statement, returning a result set as a PDOStatement object.

foreach($dbh->query('SELECT * from FOO') as $row) {
    print_r($row);
}

The exec() method is fairly similar to the query() method in that it also executes an SQL statement, however it returns the number of affected rows rather than the result set.

/* Delete all rows from the FRUIT table */
$count = $dbh->exec("DELETE FROM fruit WHERE colour = 'red'");

/* Return number of rows that were deleted */
print("Deleted $count rows.\n");

PDO supports the use of prepared statements, both using the ? and named parameters. The prepared() method is seen below using the question mark parameters.

<?php
$sth = $conn->prepare('SELECT name, description FROM table1 WHERE name = ?');
$sth->execute(array('John'));
$name = $sth->fetchAll();

$conn = null;

And here are the named parameters.

<?php
$sth = $conn->prepare('SELECT name, description FROM table1 WHERE name = :name');
$sth->execute(array(':name' => 'John'));
$name = $sth->fetchAll();

$conn = null;

The PDOStatement class also has a prepare() method.

<?php
/* Execute a prepared statement by binding a variable and value */
$calories = 150;
$colour = 'gre';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour LIKE :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', "%{$colour}%");
$sth->execute();

Here you can see the use of the bindParam() and the bindvalue() methods.

With bindParam(), you can only pass variables and not values. However with bindValue(), you can pass both values and variables.

Note: This article is based on PHP version 7.1.