PHP: Introduction to PDO

In Programming

In this tutorial I'll introduce you to using PDO and show examples of its use. The tutorial assumes you already have a database and tables setup (I'll be using MySQL and SQLite) for testing.

What is PDO?
PDO is a powerful PHP extension that provides a consistent (wrapper) interface to database access. PDO allows developers to use the same set of functions for database development making your applications highly portable across platforms and databases. In the words of the PHP Manual:

Quote:

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features.

Since PDO requires the object oriented features of PHP it only works with PHP 5.0 and higher. PDO comes standard with PHP 5.1+ and needs a PECL extension for PHP 5.0.

PDO Currently supports the following databases/drivers:

PDO_DBLIB
- FreeTDS / Microsoft SQL Server / Sybase
PDO_FIREBIRD
- Firebird/Interbase 6
PDO_IBM
- IBM DB2
PDO_INFORMIX
- IBM Informix Dynamic Server
PDO_MYSQL
- MySQL 3.x/4.x/5.x
PDO_OCI
- Oracle Call Interface
PDO_ODBC
- ODBC v3 (IBM DB2, unixODBC and win32 ODBC)
PDO_PGSQL
- PostgreSQL
PDO_SQLITE
- SQLite 3 and SQLite 2

Creating the Connections
Each database connection is a little different but one connected many of the PDO methods are the same which is what makes PDO so powerful. Below are the various methods used to connect to different databases.

SQLite

Connect by file:

PHP Code:

$db = new PDO("sqlite:/database/path/file.sdb");

Connect by Memory:

PHP Code:

$db = new PDO("sqlite::memory");

Note: If you open a SQLite database that is of a different version than your SQLite driver (2.x and 3.x) the open will be successful but your queries will fail producing an invalid resource error.

MySQL

PHP Code:

$dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);

Set the variables to match your MySQL settings ($hostname, $username, $password);

Oracle

PHP Code:

$dbh = new PDO("OCI:", "username", "password")

ODBC

PHP Code:

$dbh = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\accounts.mdb;Uid=Admin");

Errors: Try/Catch
Since PDO supports exceptions, you should wrap these in a try/catch block:

PHP Code:

Try {
    $dbh = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\accounts.mdb;Uid=Admin");
}
catch (PDOException $e)
{
    echo $e->getMessage();
}

Select
PDO::query()executes an SQL statement in a single function call, returning the result set (if any) returned by the statement as a PDOStatement object. The PDOStatement class implements the SPL traversble iterator which means we can directly loop through the results of a query (see multiple rows below).

Select a Single Row with PDO

PHP Code:

// Fetch One Row
$row = $dbh->query("SELECT * FROM <table>")->fetch();
print_r($row);

Select Multiple Rows with PDO

PHP Code:

// Select
        foreach($dbh->query('SELECT * from <table>') as $row) {
            print_r($row);
        }

Insert and Update
Using PDO::exec() you can execute SQL commands such as insert and update. The exec() function does not return a result set so it can not be used with SELECT statements. PDO::exec() does return the number of rows that were deleted or modified by your SQL statement or 0 if no rows were affected.

Inserting a row:

PHP Code:

$query = "INSERT INTO test(first, last, email)"
                . "VALUES('Mark','Twain','mark@twain.com')";

        $affected = $dbh->exec($query);
        print $affected;

Output:

Quote:

Update
Updating a table is done in the exact same way as inserting.

PHP Code:

$query = "UPDATE test "
                . "SET `email`='MarkTwain@mt.com' "
                . "WHERE `first`='Mark' "
                . "AND `last`='Twain'"
                ;

        $affected = $dbh->exec($query);
        print $affected;

Output:

Code:

Conclusion
Hopefully you can tell why PDO is so powerful from this tutorial. You can allow your programs to change database types by simply changing the connector. Code once for multiple database types.

This is a basic tutorial and does not go in depth. There are many more advanced features of PDO and ways to connect to many different database types.

Have any questions? Feel free to ask here!