Using PHP Data Objects

• Knowledge needed: Some knowledge of PHP and SQL is useful
• Requires: A web server (such as Apache) with PHP loaded, and a relational database management system capable of PDO connectivity (such as MySQL); ODBC and Doctrine 2 are optional extras
• Project Time: 1-3 hours (or more, depending on your serendipitous learning path and capacity)
• Source file

This article first appeared in issue 231 of .net magazine.

Dynamic website and application development seems more commonplace than static website creation these days, and with dynamic development comes the need to store data.

A popular database management system used alongside the PHP language is the MySQL database, with Microsoft SQL, PostgreSQL and Oracle also being fairly common. The PHP group of developers initially eased connecting between PHP and the database systems using database-system specific functions such as:

MySQL: resource mysql_query ( string $query [, resource $link_identifier ] )
Microsoft SQL: mixed mssql_query ( string $query [, resource $link_identifier [,
int $batch_size = 0 ]] )
PostgreSQL: resource pg_query ([ resource $connection ], string $query )
Oracle Database: bool oci_execute ( resource $statement [, int $mode = OCI_
COMMIT_ON_SUCCESS ] )

As you can see from the definitions they have no standardised form, so if you had to change your database system from Oracle to MySQL you’d have to work through your code and alter how you connect to your database. It’s also a thorn in the side of learning about database connectivity in PHP: you can’t just transfer your knowledge from, for example, PostgreSQL to Microsoft SQL.

01. The PHP Data Objects (PDO) philosophy

Thankfully a database connectivity saviour does exist in PHP – and this is in the form of the three letter acronym PDO, which stands for PHP Data Objects. The idea of the PDO library is that it provides a standardised foundation on which you can connect to any relational database management system (RDBMS) that can be queried using SQL.

At the time of writing this includes CUBRID, Firebird, Interbase, IBM DB2, Informix, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, SQLite, 4D – and any database that’s connectable via ODBC.

Taking a quick conceptual example of the changes, we would previously have seen:

<name-of-rdbms>_query($sql);

We now see a standardised set of functions, which looks a bit like this:

$conn->query($sql);

But that’s quite enough theory for now – let’s have a look at this powerful library in action! There are two distinct aspects to PDO, the first being the connection – which is clearly a required element – while the second side is the querying element.

02. Connecting to a database management system

Before we query any data we must connect to an already installed and set-up database management system. We’ll connect to a MySQL database, which is running on localhost, for the first example:

PDO('mysql:host=localhost;dbname=yourdbname', 'username', 'password');

Let’s compare that with connecting to a PostgreSQL database:

w PDO('pgsql:host=localhost;dbname=yourdbname', 'username', 'password');

The connections are standardised thanks to the first example using the same function, while the second employs the standardised data source name (DSN) system. You can start to see how easy it is if you just want to switch from one database management system to another.

03. Querying and reading results

Querying data is initially more interesting than creating data, and as this is a tutorial on data connectivity rather than on SQL, we’ll get straight to querying and visit creation, insertion, updating and deletion later.

We’re going to presume we have a table called profile, which lists various details about users of a hypothetical web application. As an example, let’s run a quick profile fetch in SQL through PDO and then simply echo out the full names. Notice how the query function can be used as an iterator – a function that stores a pointer, in memory, to the current element of an array (or result set in this case). When this is combined with a foreach loop it allows for a quick and easy method of accessing rows:

$conn = new $conn = new $conn = new $conn = neforeach ($conn->query("SELECT * FROM profile") as $row) echo
$row['fullname'];

Of course, this is quick and easy, but we hardly ever want to fetch all rows; therefore let us add some conditionals through variable injection. Here we utilise a more robust querying method, which prepares the query and injects the variables:

$query = $conn->prepare("SELECT * FROM profile WHERE username =
:username LIMIT 1");
$query->bindParam(":username", "knightofarcadia");
$query->execute();
$profile = $query>fetch( PDO::FETCH_ASSOC );
echo $profile['fullname'];

The code above will restrict the profile table search to just one profile with the user name knightofarcadia. Like our first example it’ll simply print out the full name after that – but you can certainly imagine building up an entire XHTML page, which has the data passed to them.

We might, however, have more than one row returned in our query, and therefore we have the capability of using the fetch method as an iterator. Here we see a different query which returns a multiple row result set:

$query = $conn->prepare("SELECT * FROM profile WHERE hometown = :hometown");
$query->bindParam(":hometown", "Wessex");
$query->execute();
foreach($query->fetch(PDO::FETCH_ASSOC) as $row) {
echo $row["fullname"];
}

The instance above will search through the profile database and return all profiles that have the hometown set to Wessex. The fetch method is then used to return all those results, and for a straightforward example we can just print out the full name to screen – although this could be a more complex XHTML page operation.

04. Creating

Now, although I would advocate the creation of a database structure being done in SQL directly into the database management system, it is possible to dynamically create database tables using SQL prepared by PDO:

$createsql = $conn->prepare("CREATE TABLE profiles (username VARCHAR(64), fullname VARCHAR (128), hometown VARCHAR(128)"));
$conn->query($createsql);

Please do remember that the query object in this instance won’t return anything of value because it is a creation command. As already mentioned, it’s worth trying to avoid the use of dynamically creating tables in this way in a web application, although I can imagine it being used in ‘run-once’ web-based systems (such as server web applications installers), as well as in simple nonweb based server scripts.

05. Inserting

Inserting data is very important in a dynamic system, and particularly in contemporary Web 2.0 and Web 3.0 systems, which are orientated towards collaboration and co-operation – how can users collaborate if they have no capability to store and share data? Therefore, let’s insert some data into our profile table thus:

$insertsql = "INSERT INTO profiles (username, fullname, hometown) VALUES (:username, :fullname, :hometown)";
$query = $conn->prepare($insertsql);
$query->bindParam(":username", "knightofarcadia");
$query->bindParam(":fullname", "Arthur Pendragon");
$query->bindParam(":hometown", "Wessex");
$query->execute();

Like the query function in the creation of tables, this execute function won’t return anything of value because it’s simply an insert command to the database. You’ll also notice that we’re using the ‘prepare, bind and execute’ technique in order to inject our variables into our SQL.

06. Updating

Updating, like inserting and deleting, is crucial in a collaborative system and PDO makes this easy. Updating is quite similar to insertion:

$query = $conn->prepare("UPDATE profiles SET fullname = :fullname WHERE
username = :username");
$query->bindParam(":fullname", "Arthur Pendragoon");
$query->bindParam(":username", "knightofarcadia");
$query->execute();

The above block of code merely replaces the full name of a user, but you’ll notice that it’s virtually identical to the insertion code. We bind a conditional result, in this case the username, and we bind a setting result, in this example the new full name.

07. Deletion

Finally let’s take a quick look at deletion, which is often a simpler process than inserting or updating.

$query = $conn->prepare("DELETE FROM profiles WHERE "username" =
:username );
$query->bindParam(":username", "knightofarcadia");
$query->execute();

The above SQL simply deletes a profile where we match on a username. We simply bind the username to the conditional variable.

08. Switching database management system

Now, providing that the database table structure is identical and that we aren’t using anything non-standardised within a proprietary SQL system, we can simply change our data source name to point from one RDBMS – in our initial example the Microsoft SQL Server – to another (IBM DB2, for instance). The whole code that we’ve done from then will work – without the need of changing any SQL.

We will start with our connection string looking like this:

$conn = new PDO("sqlsrv:server=localhost;database=yourdbname",
"username",
"password");
$conn = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=yourd
bname;HOSTNAME=localhost;PORT=56789;PROTOCOL=TCPIP;","username",
"password");

09. Transactions

A transaction, in database terms, is where you save up a set of queries to batch process at a later time. PDO provides a mechanism for building up transactions – but as these are highly dependent on the database management system, PDO transactions only work when connecting to a subset of the RDBMS that PDO supports. If you try to begin a transaction on an RDBMS that doesn’t support transactions then you’ll get a rather nasty PDO transaction. So let’s analyse some transaction code:

try {
$conn->beginTransaction();
$insertsql = $conn->prepare("INSERT INTO profiles (username, fullname,
hometown) VALUES ('wilfred', 'Wilfred Jones', 'Scarborough')");
$deletesql = $conn->prepare("DELETE FROM profiles WHERE username =
'username'" );
$conn->exec($insertsql);
$conn->exec($deletesql);
$conn->commit();
} catch (Exception $e) {
$conn->rollBack();
// message accessible with: $e->getMessage();
}

First we begin a try-catch so that we can catch any bad exceptions, including those you may get through trying to connect to an RDBMS that does not have transaction support. We begin a transaction with $conn->beginTransaction() before continuing building up our query executions, but these won’t be fully executed in the database until the $conn->commit() function is run, and they’ll be done in an efficient serial sequence – meaning you can do various other PHP processes between the execution commands with no impact on the database.

If we find that the database doesn’t support transactions then the transaction simply won’t happen; if any other exception is thrown then we execute $conn->rollBack(), which will roll back any changes made by the transaction. It’s worth noting that when connecting to a transactions-supported RDBMS, PDO will enter into an ‘auto-commit’ state, where each exec command is itself its own committed transaction; however, if you would like to work in a safe way then you can always use beginTransaction, and have access to the batch commit and the rollback functionality.

10. Problems with PDO

There are no real problems from the PHP perspective in using PDO. It’s object oriented meaning it is extensible and flexible, and it works with many systems in connectable fashion. The problem comes when we consider that while the majority of relational database management systems follow SQL standardisation (so helping us switch from one system to another), many systems have their own proprietary syntax and functions, which are not common other systems.

Therefore it is crucial that in order for a smooth transition from one system to another you follow the SQL standards and only use commonly found functions. To exemplify this there is a function commonly used in queries: we can look at the function for randomisation in SQL. Here are the function specifications for various languages:

MySQL: SELECT RAND([seed]);
MS SQL: SELECT RAND([seed]);
PostgreSQL: SELECT RANDOM(); (to set the seed you must run SETSEED([seed])
beforehand)
Oracle DB: SELECT dbms_random.random FROM dual;
SQLite: SELECT RANDOM();

So we must keep this in mind, and work out whether we can either employ a standardised SQL technique instead of the proprietary function, or use a PHP procedure and inject the result into the SQL query (in the randomisation example we could use the rand() function which PHP provides).

11. Object relational mapping by example

Of course, we can go further in abstraction by entering into the modelling world – no, not by exhibiting our project on a catwalk, but via mapping Plain Old PHP Objects to database tables. There’s a problem with this beacuse PHP objects have an object-oriented model, whereas SQL databases have a relational one. This is where object relational mapping (ORM) comes into play: it enables you to map objects to tables, often using a bit of magic and some sparkle.

You’re probably asking what the benefit of ORM is. Quite simply, you don’t have to deal with any of the database connectivity or SQL querying; you simply use PHP objects and their methods directly, and the ORM system deals with all the connectivity and the create-read-update-delete transactions in the background. There are quite a few ORM libraries for PHP out there, PdoMap, Propel and Redbean being a few of the good ones, but the best I’ve used is Doctrine 2 – it has the benefit of being either usable on its own or as part of an MVC setup such as Symfony, CodeIgniter or Zend.

With Doctrine 2, and a few other ORM systems, usually you define a set of objects and the kind of relationships that they have with each other (such as one-to-many; many-to-one; many-to-many), with special details about the connection between properties and the methods. Of course, there’s no need to define normalised link tables because these are irrelevant in the object model. This human-executed process is usually done using custom syntax, for instance in XML or YAML, and Doctrine 2 allows for object relational definitions within PHP doc-blocks. The following model and code describes a real-world example.

<?php
use Doctrine\Common\Collections\ArrayCollection;
/** Description of Member
* @Entity
*/
class Member {
/**
* @Id @GeneratedValue
* @Column(type="integer")
* @var int
*/
protected $id;
/** @Column(type="string")
* @var string
*/
protected $firstname;
/** @Column(type="string")
* @var string
*/
protected $surname;
/** @Column(type="string")
* @var string
*/
protected $email;
/** Many members have a membership of many groups
* @ManyToMany(targetEntity="Group")
* @var Group[]
**/
protected $groups;
/**
* Constructor
*/
public function __construct() {
$this->groups = new ArrayCollection();
// ...
}
// --- Basic getter and setter examples --- //
/** Gets the (internal) ID of the member
* @return int
*/
public function getId() {
return $this->id;
}
/** Gets the Firstname of the member
* @return string
*/
public function getFirstname() {
return $this->firstname;
}
/** Sets the firstname of the member
* @param string $firstname
*/
public function setFirstname($firstname) {
$this->firstname = $firstname;
}
// --- More complex getter and setter examples --- //
/** Gets the groups array of the member
* @return Group[]
*/
public function getGroups() {
return $this->groups;
}
/** Assigns a group to a member
* @param Group $group
*/
public function assignToGroup(Group $group) {
$this->groups[] = $group;
}
/** Removes a member from a group
* @param Group $group
*/
public function removeFromGroups(Group $group) {
$this->getGroups()->removeElement($group);
}
// ...
}
?>

A machine-executed process can then generate the SQL to create a database (this can be in a variety of SQL syntaxes, such as for MySQL, for SQL Server or for PostgreSQL); it can do this by dumping the SQL, or by connecting with the database and executing it itself. It will create the many-to-many link tables itself.

doctrine orm:schema-tool:create

or

doctrine orm:schema-tool:create --dump-sql

You will find you’ve now got the PHP objects and the tables in place. Now you can deal with the objects without needing to know either how to connect with the database management system, or the structure of the relational tables.

$group = new Group();
// set group details, persist and flush (as below)
$member = new Member();
$member->setFirstname("");
$member->setSurname("");
$member->setEmail("");
$member->assignToGroup($group);
$entityManager->persist($member);
$entityManager->flush();
echo "Created Member with ID " . $member->getId() . "\n";

Of course there’s a bit more to it than the code examples above, primarily regarding connection details and bootstrapping, but I thought that I should give you a flavour of the power of ORM and Doctrine in particular. I should add that Doctrine 2, and a couple of the other PHP ORM libraries, use PDO in order to achieve support for various different RDBMSes.

12. Conclusion

I have used PDO in commercial projects, and from my own experience it greatly increases the ease of connecting to a database – and dealing with the data afterwards – thanks to its object-orientation. There’s no need to use PDO just for its system switching capability; the functions are there for you to use, have good efficiency and are ideal for contemporary programming design patterns.

There are many systems out there still using the procedural-style systemspecific functions, but I certainly have seen a shift in focus to PDO. We can also see PDO in action in the wild, because Drupal, MediaWiki, WordPress and many other popular open source web applications now fully or partially support the PDO system for connecting to databases. I have a vision in which data is no longer closely-coupled with databases, and databases are no longer closelycoupled with code, and we see more general purpose systems being configured to specific applications. I think that ORM systems, NoSQL and linked data are three things to look out for in the present and in the future. The killer features of future web applications will, firstly, have the foundations of the connectivity of data, and secondly, by the efficiency of the manipulation of that data.

Discover 101 CSS and Javascript tutorials.

Daniel Lewis is a web app developer, consultant, tutor and speaker.