PHP: Introduction to PDO
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. |
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");
PHP Code:
$db = new PDO("sqlite::memory");
MySQL
PHP Code:
$dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
Oracle
PHP Code:
$dbh = new PDO("OCI:", "username", "password")
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();
}
$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);
$row = $dbh->query("SELECT * FROM <table>")->fetch();
print_r($row);
PHP Code:
// Select
foreach($dbh->query('SELECT * from <table>') as $row) {
print_r($row);
}
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;
. "VALUES('Mark','Twain','mark@twain.com')";
$affected = $dbh->exec($query);
print $affected;
Quote:
| 1 |
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;
. "SET `email`='MarkTwain@mt.com' "
. "WHERE `first`='Mark' "
. "AND `last`='Twain'"
;
$affected = $dbh->exec($query);
print $affected;
Code:
1
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!

wow ive never heard about PDO, sure it will come in handy in the future
thnx for sharing, i wish there was some way to repay you :D
Glad you liked it, Amr! It can be real handy if you want to develop an app that may use many different types of databases. Thanks for your comment!