What are PHP Data Objects?

PHP recommends instead of using the standard mysql_connect() and mysql_query() functions that instead you use PHP Data Objects, or mysqli_() functions for database interactions. PHP Data Objects are a robust, easy-to-learn and easy-to-use object-oriented interface for interacting with databases. Object-oriented programming is a conceptual programming model for developing applications, and results in cleaner, more organised and structured code. I would assume you have some familiarity with object-oriented programming already, as it would help to understand how to use PHP Data Objects proficiently.

Connecting to a database using PHP Data Objects is incredibly simple.

$string = “mysql:dbname=db_name;host=localhost”;
$user = ‘db_user’;
$password = “your password here”;
try
{
$db = new PDO($string, $user, $password);
}
catch (PDOException $e) {
echo “Connection failed: ” . $e->getMessage();
die();
}

The Try, Catch block is used to try a block of code and if any problems occur, the errors are returned in the catch block. In this instance, the PDO() object would return an PDOException() object if an error occurred with the connection to the database. The getMessage() method of the PDOException class would return the error in question. It’s a super efficient way of handling errors that may otherwise cause errors to be displayed on the page.

The $string variable contains the DSN (Data Source Name) which essentially is to tell PHP Data Objects what database system you want to connect to and the name of the database and where the database is located (in this instance, the database is located on the same server as the PHP application being executed on – hence localhost). For more information on DSN, see the PHP documentation.

Executing database queries.

To execute SQL queries, here’s an example:

$query = $db->query(“SELECT * FROM users”);
foreach($query as $row)
{
echo $row[‘column’];
}

This will obviously loop through each result row and return the column named column as specified in the array index name of $row. Essentially, $query is returning an associative array that you loop through using a foreach() loop.

Some more examples include:

$query = $db->exec(“UPDATE users SET username = ‘$username’ WHERE username = ‘$username_old'”); // returns number of affected rows

if($query)
{
echo “Row updated”;
}

Note: The exec() method does not work for SELECT queries. The exec() method executes an SQL query and returns the number of affected rows. A SELECT query does not affect any existing rows, which is why a SELECT statement doesn’t work. Do check how many rows are returned from a SELECT query, you can do this:

$query = $db->query(“SELECT * FROM users”);
echo $query->rowCount();

In this instance, the method rowCount() is used to count the amount of rows returned by the query. You may notice that the query() method will return an entire object (specifically a PDOStatement object) and in the PDOStatement class there is a method called rowCount() which returns an integer value representing the amount of rows returned by the query in question. However, you can also loop through the returned results of the query() method using a foreach loop, or perhaps the first row result using the fetch() method of the PDOStatement class, which returns the next row from the result set (so in this instance it will be the first row).

Unlike the PEAR library which you need to install separately on your ESDS Dedicated Server (the PEAR library has its own class for database interaction), the PDO extension is enabled by default as of PHP 5.1.0 and above.