A statistical impossibility

If you’re a PHP developer, you are of course aware of PDO; PHP Data Objects. Or are you?
PDO is the new form to communicate with databases, for example MySQL, PostgreSQL and MSSQL. When you’re using MySQL, you may be using the following methode to connect to your database:

$connection = new mysqli('localhost','username','password');

Or, even worse:

mysql_connect('localhost','username','password');

This of course is hopelessly outdated, and with PHP version 6 on the way, will soon be removed from the standard installed libraries. But there is a better, more Object Oriented way to connect to your database, and that is PHP Data Objects.

What is PDO?
According to php.net:

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server.

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. You should use a full-blown abstraction layer if you need that facility.

PDO ships with PHP 5.1, and is available as a PECL extension for PHP 5.0; PDO requires the new OO features in the core of PHP 5, and so will not run with earlier versions of PHP.

Connecting to your MySQL database
So, how does one use it? Below is an example for MySQL (other databases might require a slightly different approach):

$connectionString = "mysql:host=localhost;dbname=database";
$pdo = new PDO($connectionString, 'username', 'password');

Now you can use the $pdo variable to do things, e.g.:

Retrieve information from tables

$results = $pdo->query("SELECT * FROM table");
foreach($results as $result)
{
	echo $result['field'] . "<br />\r\n";
}

This is how you can read data from your database tables. Note that you don’t use the while loop and the fetch_num or fetch_assoc (or similar) in this case, but a foreach loop. You can access the field values as you would in an ordinary array.

Queries that don’t return anything: the wrong way to do it
For queries that don’t return anything, for example INSERT and UPDATE queries, PDO provides the exec method. This method returns the amount of rows affected (if any) by the query. Using it is simple:

$pdo->exec("INSERT INTO 'table' (id, value) VALUES ('1','this is the value')");

But this method is susceptible to SQL injections. Therefore, the PDO class gives us another method to insert or alter information in/from the database: prepared statements. A prepared statement is SQL injection safe and the right way to do things, especially if you need to insert or alter user submitted information. Here an example of how to use prepared statements:

$stmt = $pdo->prepare("INSERT INTO table (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
 
// insert a row
$name = 'one';
$value = 1;
$stmt->execute();

This is a safe way to insert or update data in your database. Of course you can use prepared statements with SELECT queries as well.

The Basics
This provides you with the basics to select, insert and update data from.to your MySQL database. Later I will expand on this subject, and dive into the more complex possibilities of PDO, as wel as how to access other databases than MySQL.

Related Links

• PDO on php.net
• SQL Injection on Wikipedia