PHP - Introduction To PDO Part 1

PHP Data Objects (PDO) is a simple database abstraction extension that allows you to interact with databases. This tutorial introduces you to the PDO extension and provides sample code to help you get started.

Part 1 - PDO

Before the PDO extension was available, PHP developers commonly used the mysql_* functions to interact with MySQL. As PHP continued to develop as a language and implemented many OOP features, it introduced PDO in version 5.1. It's aim was to provide a database abstraction layer while being object oriented. Since the introduction of PDO, the use of mysql_* functions have been heavily discouraged. Some developers will argue that the mysql_* extension is insecure while others prefer the OOP syntax of PDO. In any case, the mysql_* extension is no longer supported and will be phased out of PHP in future releases.

The PDO extension is shipped with PHP 5.1and above, however the PDO extension it self can not interact with a database. PDO requires, database specific PDO drivers to be installed and enabled. For example, in order to connect to a MySQL database, the pdo_mysql extension must be installed and enabled. Since PHP and MySQL are childhood best friends, you will find that in many environments, the pdo_mysql extension is installed and enabled.

Since PDO is a database abstraction layer and requires the use of a database specific PDO driver, it's usage is determined by the driver capabilities.

The following code sample demonstrates how to connect to a MySQL database running on localhost.

1. <?php         
2.     $pdo = new PDO('mysql:dbname=database_name;host=127.0.0.1', 'root', '');   
3.     $stm = $pdo->query('SELECT * FROM users');   
4.     $rows = $stm->fetchAll();   
5.        
6.     print_r($rows);   
7. ?>  

In the code sample above above, a PDO instance is created. The first argument to the PDO constrcutor is a Data Source Name (DSN), which is a string containing connection details. The first segment in the string is the database driver. The remaining segments are key/value pairs, that define the host name and database name. The second and third constructor arguments are login credentials, which are required by many database drivers.

Once a successful connection has been made, you can use the query() method of the $pdo object to issue an SQL statement. The query() method will return a PDOStatement object when a valid SQL statement has been issued.

The PDOStatement object can be used to fetch results if the SQL statement issued was a SELECT statement or get a count of the affected rows if a INSERT, UPDATE or DELETE statement was issued.

In the code sample above, the fetchAll() method returns an array of table rows that match the SELECT query statement. Each row is an array that consists of both an index and a key. The index is the column ordinal while the key is the column name. This results in each row containing duplicate data.

The fetchAll() method can accept three arguments, the first of which is a fetch_style, which is used to control the contents of the row array. The following code sample returns an associative array for each row and does not contain indexed column ordinals.

1. <?php         
2.     $pdo = new PDO('mysql:dbname=database_name;host=127.0.0.1', 'root', '');   
3.     $stm = $pdo->query('SELECT * FROM users');   
4.     $rows = $stm->fetchAll(PDO::FETCH_ASSOC);   
5.        
6.     print_r($rows);   
7. ?>  

In the next article Introduction To PDO Part 2, we take a look at the PDOStatement class and examine some of it's other methods.