Developing MySQL Database Applications With PHP Part 3:
Using the PDO Extension With MySQL Driver, pdo_mysql

Print-friendly Version

This series of tutorials shows the essential steps involved in installing PHP with MySQL extensions mysql, mysqli, and pdo_mysql, and with support for the MySQL native driver for PHP, mysqlnd. The tutorials also provide simple examples to connect, insert, and retrieve data from a MySQL database. Because the focus is on database connectivity from a PHP script, these tutorials assume that some kind of MySQL database is already up and accessible from the client machine.

For general information about accessing MySQL with PHP database extensions, a list of the major PHP API offerings, and instructions on setting up the test database used in these tutorials, see the first sections of the first tutorial in this series, Developing MySQL Database Applications With PHP Part 1: Using the MySQL Improved Extension, mysqli.

Application developers who are new to the development of MySQL database applications with PHP are the target audience of this article.

• Use the PHP Data Objects (PDO) Extension With MySQL Driver, pdo_mysql
• Install PHP With the PDO Extension and the PDO_MySQL Driver, pdo_mysql
• Test the MySQL Database Connectivity Using the PDO Extension
• Transaction Support in PDO
• Explore Other Tutorials in This Series
• For More Information

The PHP Data Objects extension (PDO) defines a lightweight, consistent interface for accessing a variety of databases from PHP applications. PDO provides a database abstraction layer, meaning regardless of which database you're using, you use the same functions to execute SQL queries and fetch data.

Because PDO is just an abstraction layer, you cannot perform any database functions like inserting a row of data into the table using the PDO extension by itself--you must use a database-specific PDO driver to access a database server from the PHP code. The database driver that implements the PDO interface exposes the database-specific features as regular extension functions. PDO_MYSQL is a database driver that implements the PDO interface to enable access from PHP to MySQL 3.x, 4.x and 5.x databases. The PDO_MYSQL driver is implemented using the PHP extension framework, and it sits in the layer below PDO that provides MySQL-specific functionality. The PHP programmer calls the PDO API, and then PDO uses the PDO_MYSQL driver to carry out communication with the MySQL server.

PDO is useful when you need to build applications capable of talking to different database systems like MySQL, Oracle, or PostgreSQL.

Although PDO has many advantages like a clean, simple, lightweight, portable object-oriented API, its main disadvantage is that it does not allow you to use all of the features that are available in the latest versions of MySQL. For example, the PDO API does not support mixing SQL statements that return result sets with the SQL statements that do not return any data. MySQL allows you to mix all kinds of queries in one call when using the Multiple Statements feature. Using ext/mysqli, you can execute such a query and handle its results properly. However with PDO you cannot. See the first tutorial in this series, Developing MySQL Database Applications With PHP Part 1: Using the MySQL Improved Extension, mysqli, for more information about mysqli.

• PHP 5.1 and later on UNIX / Linux systems
   
  PDO comes packaged with PHP 5.1 and later versions by default, so if you're running 5.1 or later, you do not need any additional steps to enable PDO extension. It is recommended that you build PDO as a shared extension from the PHP source code, because this will allow you to take advantage of updates that are made available via PECL. The recommended configure line for building PHP with PDO support should enable zlib support (for the pecl installer) as well. Also you need to enable the PDO driver for the MySQL database. If you build PDO as a shared extension, you must build the PDO database driver as a shared extension. On UNIX/Linux systems, a typical configuration line looks like this:
   
  

  bash# ./configure --with-zlib --enable-pdo=shared --with-pdo-mysql=shared --with-mysql=shared [other options]

   
  Then the PDO extension with the PDO_MySQL driver relies on the MySQL client library, libmysql, to talk to the MySQL database server.
   
  Alternatively, if you wish to use the MySQL native driver for PHP (mysqlnd) in place of the MySQL client library (libmysql) to communicate with the MySQL server, check the installation instructions in the tutorial Developing MySQL Database Applications With PHP Part 4: Using the MySQL Native Driver for PHP, mysqlnd.
   
  After installing PDO as a shared module, edit your php.ini file to enable the PDO extension and the MySQL driver. Make sure that the MySQL driver (pdo_mysql.so) is listed after the pdo.so, as PDO must be initialized before the database-specific extensions are loaded.
   
  

  bash# cat php.ini ... extension=pdo.so extension="pdo_mysql.so" extension="mysql.so" ...

   
  Having PDO as a shared module allows you to upgrade the extension by running the simple command: pecl upgrade pdo. This step does not force you to rebuild the whole PHP from scratch though you must upgrade the PDO MySQL driver as well at the same time.
   
  
• PHP 5.0 and later until 5.1 on UNIX/Linux systems
   
  PDO is available as a PECL extension. Installation can be performed with the help of the pecl tool. You should ensure that PHP is configured --with-zlib in order for pecl to be able to handle the compressed package files.
   
  Run the following command to download, build, and install the latest stable version of PDO.
   
  

  bash# pecl install pdo

   
  The pecl command automatically installs the PDO module into your PHP extensions directory. To enable the PDO extension on UNIX/Linux operating systems, add the following line to php.ini:
   
  

  extension=pdo.so

   
  
• Users of all other OS platforms, check the PDO installation documentation at PHP.net.
• Finally, verify that PHP can load the PDO extension module and pdo_mysql database driver.
   
  

  bash# php -m | grep -i pdo PDO pdo_mysql ..

   
  To display the available PDO database drivers, call the function pdo_drivers().
   
  For example:
   
  

  <?php print_r(pdo_drivers()); ?>

   
  

The following sample PHP script shows how to connect to a MySQL database running on the same host, using the PDO interface. The code sample connects to the MySQL database test by using the PDO API, sends out a query to retrieve all the rows from the table City, extracts the data from the result set, displays the data on the standard output, inserts a couple of new records into the City table using prepared statements, and finally commits the INSERT transactions. To minimize the dependencies, this script is shown as a stand-alone script rather than a PHP page that can be accessed over the web server.

The sample code is provided for the purpose of demonstration only; it is not meant to indicate that readers should adopt a particular style of coding. To keep it simple, the sample code assumes that the user always provides well-formed input, hence there is no explicit error checking code in the following example. Use discretion in reusing the sample code.

bash# cat PHPpdoClient.php

<?php

    $dsn='mysql:host=localhost;port=3306;dbname=test';
    $user='root';
    $pass='admin';

    try {

        /* obtain a database connection handle */
        $dbh = new PDO($dsn, $user, $pass);

    } catch (PDOException $exception) {
            printf("Failed to connect to the  database. Error: %s",  $exception->getMessage());
    }

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, false);

    $sql = "SELECT * FROM City";

    /* execute the query */
    $rs = $dbh->query($sql);

    print "\nRetrieved " . $rs->rowCount() . " row(s).\n";
    print "\nCityName\n--------\n";

    /* retrieve the data from the result set */
    $rs->setFetchMode(PDO::FETCH_ASSOC);

    while ($row = $rs->fetch()) {
        $data = $row["CityName"];
        print "$data\n";
    }

    /* close the result set */
    $rs->closeCursor();

    try {

        /* create a prepared statement */
        $query = "INSERT INTO City VALUES (?)";
        $stmt = $dbh->prepare($query);

        $city = 'London, UK';

        /* bind the parameter */
        $stmt->bindParam(1, $city);

        $dbh->beginTransaction();

        /* execute the SQL */
        if ($stmt->execute()) {
            $dbh->commit();
            echo "\nSuccessfuly inserted " . $city . " into the table, City.";
        } else {
            print_r($dbh->errorInfo());
        }

        $city = 'Paris, France';

        /* bind the parameter with another value */
        $stmt->bindParam(1, $city);

        $dbh->beginTransaction();

         /* execute the SQL again */
         if ($stmt->execute()) {
                 $dbh->commit();
                 echo "\nSuccessfuly inserted " . $city . " into the table, City.\n";
                } else {
                        print_r($dbh->errorInfo());
                }

        $sql = "SELECT * FROM City";

        /* execute the query */
        $rs = $dbh->query($sql);

        print "\nRetrieved " . $rs->rowCount() . " row(s).\n";
        print "\nCityName\n--------\n";

        /* retrieve the data from the result set */
        $rs->setFetchMode(PDO::FETCH_OBJ);

        while ($row = $rs->fetch()) {
            $data = $row->CityName;
            print "$data\n";
        }

        /* close the result set */
        $rs->closeCursor();

    } catch (PDOException $exception) {
        print "\nException: " . $exception->getMessage();
        $dbh->rollBack();
    }

    /* close the database connection */
    $dbh = null;

?>

bash# php PHPpdoClient.php

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

Successfuly inserted London, UK into the table, City.
Successfuly inserted Paris, France into the table, City.

Retrieved 5 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia
London, UK
Paris, France

Some of the important steps in the above code sample are explained below.

Before interacting with a database using PDO, the first thing to do is to create a PDO object to use as the database handle. This is accomplished by calling PDO's constructor.

	PDO::__construct (string $dsn [, string $username [, string $password [, array $driver_options]]])

where: $dsn is the Data Source Name, which is composed of the following elements: the name of the PDO database driver followed by a colon, followed by optional driver-specific information. In the case of the PDO_MYSQL driver, the DSN prefix is "mysql:", and the PDO mysql driver accepts host, port, dbname and unix_socket as the optional parameters. Note that host or port and unix_socket are mutually exclusive, and hence should not be mixed in the same DSN.

The following statement in the sample PHP script attempts to connect to a MySQL server running on the localhost on the default port 3306 with the credentials root/admin, and selects the test database to perform the subsequent transactions against.

$dbh = new PDO('mysql:host=localhost;port=3306;dbname=test', 'root', 'admin');

The above statement can be rewritten as shown below with the assumption that /tmp/dbconnect exists with appropriate file permissions that enable PHP to read the file.

$dbh = new PDO('uri:file:///tmp/dbconnect', 'root', 'admin');

bash# cat /tmp/dbconnect
mysql:host=localhost;port=3306;dbname=test;

If the host is localhost, then the port number is ignored and 3306 is assumed by default. If the MySQL server is running on any port other than the default port 3306, use the IP address 127.0.0.1 in place of localhost in the host argument.

If you wish to use persistent connections, you must set the attribute PDO::ATTR_PERSISTENT in the array of driver options passed to the PDO constructor. The database driver does not use persistent connections if this attribute has been set with PDO::setAttribute() after instantiating the object.

For example:

$dbh = new PDO('mysql:host=localhost;port=3306;dbname=test', 'root', 'admin', array(PDO::ATTR_PERSISTENT => true));

PDO::__construct() throws a PDOException if the attempt to connect to the requested database fails.

There are several connection-related options for PDO that you can consider passing to the $driver_options array. Alternatively, you can set those options against the database connection handle by using the PDO::setAttribute().

The following statement in the sample script sets the PDO error reporting mode to PDO_ERRMODE_EXCEPTION, which enables PHP to throw exceptions similar to that of other programming languages. An exception can be thrown and caught within PHP. Code can be surrounded in a try block to facilitate the catching of potential exceptions.

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The following statement disables the default auto-commit behavior of MySQL.

$dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, false);

To enable buffered queries by default, set the attribute PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to TRUE as shown below. Buffered queries gives you the ability to work with the entire result set rather than one row at a time, or to move to any record of your choice randomly.

$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

Check the PDO_MYSQL driver documentation for MySQL driver-specific constants.

The PDO::query() method executes an SQL statement and returns a result set that can be retrieved using any of the PDOStatement::fetchXX() methods.

The following code fragment in the sample PHP script executes the SQL query.

$sql = "SELECT * FROM City";
$rs = $dbh->query($sql);

When executing queries that do not return a result set such as INSERT, DELETE, and UPDATE, use the PDO::exec() method. The exec() method returns the number of rows that were modified or deleted by the SQL statement. If no rows were affected, exec() returns 0.

If an SQL statement has to be executed multiple times, doing so is more efficient with prepared statements. (For a discussion of prepared statements, see the first tutorial in this series, Developing MySQL Database Applications With PHP Part 1: Using the MySQL Improved Extension, mysqli.) In order to use prepared statements, prepare a PDOStatement object with PDO::prepare(), and then execute the SQL statement with PDOStatement::execute().

The following code fragment in the sample PHP script inserts a new record into the City table, using a prepared statement.

$query = "INSERT INTO City VALUES (?)";
$stmt = $dbh->prepare($query);

$city = 'London, UK';

$stmt->bindParam(1, $city);
$stmt->execute();

PDO has no direct support for MySQL's Multiple Statements.

When working with the PDO extension, auto-commit mode is enabled by default. In the auto-commit mode, the database driver implicitly commits every successful update to the database. Note that MySQL also automatically issues an implicit COMMIT when a DDL statement such as CREATE TABLE or DROP TABLE is issued within a transaction. The implicit COMMIT prevents you from rolling back any other changes within the transaction boundary.

To change the default auto=commit behaivor, use the transaction support in PDO. Calling PDO::beginTransaction() initiates a transaction and turns off the auto-commit mode. While auto-commit mode is turned off, changes made to the database via the PDO object instance are not committed until you terminate the transaction by calling PDO::commit(). Calling PDO::rollBack() rolls back all changes to the database and returns the connection to the auto-commit mode.

If the PHP script exits unexpectedly, or when you close a database handle with pending transactions, PDO calls the rollBack() method automatically on all the pending transactions to reduce the risk of committing the incomplete transactions to the database.

The following code fragment in the sample PHP script initiates a transaction, issues an INSERT statement to add a new record to the database, and finally commits the transaction to the database based on the return value of PDO::exec(). In the case of an exception, the pending INSERT gets rolled back.

try {

    $query = "INSERT INTO City VALUES (?)";
    $stmt = $dbh->prepare($query);

    $city = 'London, UK';

    $stmt->bindParam(1, $city);

    $dbh->beginTransaction();

    if ($stmt->execute()) {
        $dbh->commit();
    } else {
        print_r($dbh->errorInfo());
    }

}  catch (PDOException $exception) {

    print "\nException: " . $exception->getMessage();
    $dbh->rollBack();

}

PDO's data retrieval methodology is quite similar to that of other extensions like mysqli and mysql. The PDOStatement class has four different fetchXX() methods to retrieve the data from the result set. The fetch() method fetches the next row from a result set, the fetchAll() method returns an array containing all of the result set rows, the fetchObject() method fetches the next row and returns it as an object, and the final method fetchColumn() returns a single column from the next row of a result set.

Before calling the fetch() and fetchAll() methods, you can set the fetch style using PDOStatement->setFetchMode() to control how the rows in the result set are returned to the caller. It is also possible to set the fetch style by setting one of the optional input parameters to the fetch() and fetchAll() methods. Check the documentation of the PDOStatement->fetch() method for the supported fetch styles.

The following code fragments in the sample PHP script used PDO::FETCH_ASSOC and PDO::FETCH_OBJ fetch styles to retrieve the result set. The PDO::FETCH_ASSOC method returns the result set as an associative array indexed by column name, and PDO::FETCH_OBJ returns an anonymous object with property names that correspond to the column names returned in the result set.

...

$rs->setFetchMode(PDO::FETCH_ASSOC);

while ($row = $rs->fetch()) {
    $data = $row["CityName"];
    print "$data\n";
}

...
...

$rs->setFetchMode(PDO::FETCH_OBJ);

while ($row = $rs->fetch()) {
    $data = $row->CityName;
    print "$data\n";
}

...

Alternatively, you can retrieve all the rows in the result set with a single call to the PDOStatement::fetchAll() method.

Once the required data has been retrieved from the result set, it is a recommended practice to free up the connection to the database server so that further queries can be executed. Use PDOStatement::closeCursor() to accomplish this task. The closeCursor() method is useful for database drivers that do not support executing a PDOStatement object when a previously executed PDOStatement object still has unfetched rows.

The following code fragment in the sample PHP script closes the result set cursor.

$rs = $dbh->query($sql);
$row = $rs->fetch();
$rs->closeCursor();

When you are finished with the database, close the MySQL database connection by assigning a NULL value to the PDO object.

$dbh = null;

It is not always necessary to close a connection when you are finished, but it is necessary to close the connection to the database if you want to open up a new connection to a different database.

To find out more about using MySQL with PHP, see all tutorials in the series:

• Developing MySQL Database Applications With PHP Part 1: Using the MySQL Improved Extension, mysqli -- Describes mysqli, the MySQL Improved Extension that is the successor to the mysql extension. This extension gives you access to all MySQL server features provided by MySQL 4.1 and above; for example, support for Prepared Statements, Character Sets, and Stored Procedures, which are not available with mysql.
• Developing MySQL Database Applications With PHP Part 2: Using the MySQL Extension, mysql -- Describes the original extension designed to develop PHP applications that interact with a MySQL database. Although the mysql extension is compatible with MySQL 4.1 and above, it does not support the extra functionality that these versions provide.
• Developing MySQL Database Applications With PHP Part 4: Using the MySQL Native Driver for PHP, mysqlnd -- Describes the MySQL native driver for PHP, mysqlnd, which is an alternative to the MySQL client library, libmysql, for connecting from PHP 5 and PHP 6 to MySQL Server 4.1 or later. The mysqlnd driver is tightly integrated into PHP starting with the release of PHP 5.3, thus mysqlnd eliminates the dependency on MySQL client programming support when the database extensions and the database driver are built with support for mysqlnd.

• It is strongly recommended to read the extensive documentation that is available at php.net and dev.mysql.com before choosing an appropriate extension for your application.
• Also check the comparison of features among those three extensions at php.net.
• PHP Manual
• PHP Database Extensions
• MySQL 5.1 Reference Manual
• Beginning PHP and MySQL: From Novice to Professional, W. Jason Gilmore
• Ulf Wendel's Blog: Internet Super Hero

Ulf Wendel, Sun | MySQL AB