Introduction to PHP 5 PDO

Hi! I’m David Mytton the new SitePoint PHP Blogger and I am going to be writing for this blog for at least the next 3 months. I am going to try and focus on technical posts to provide some useful code and information about new (and old) features in PHP, so hopefully it will be a good learning experience for myself and readers.

A little bit about me:

I have written several articles for SP in the past, mostly reviews or interviews and I have also had a few articles published in International PHP Magazine. I am also the author of Invision Power Board 2: User Guide, a new book for IPB due out very shortly. In addition to writing for print, I also write commercial and open source PHP software through my limited company, Olate, in the UK.

Aside from computing, I do scuba diving and fencing (yes, with swords!).

If you have any ideas for posts for the blog, please get in touch. There is a box on the bottom left of this post “Suggest a Post” which allows you to do this.

Anyway, onto the main post…

[u]Introduction to PHP 5 PDO[/u]

The new PHP Data Objects (PDO) have been covered in a past blog post but I thought that I’d cover it in a little more detail.

After reading the blog post and also Wez Furlong’s blog I had a play around with some PDO code. Although there are already some PHP database abstraction classes available, the advantage of PDO is that it will be available in any PHP installation (so long as it is enabled) at runtime, without any need for recompiling. In addition, since it has been written from scratch specifically for PHP 5, it has a high level of performance. And if you are like me and often only want the key features, then PDO provides all the essential functions you need to connect to different databases. If you need the “full works”, then the PEAR MDB2 package can handle that.

[u]Note:[/u] PHP 5.1 Beta 3 was released on 14th July and included a number of updates to PDO. You can find out about these changes at http://netevil.org/node.php?nid=325

Before we go on, here is an important note from Wez Furlong (the developer of PDO):

Please note that PDO and its drivers are currently in an “alpha” state; this means that we are reasonably sure that there are no major bugs, but that the package is not feature complete�there’s plenty more we want to add. While we are encouraging you to test it, we really don’t recommend putting it into production at this time.

The drivers currently available are:

Firebird
FreeTSD
Interbase 6
MSSQL
MySQL 3.x/4.x
Oracle
ODBC v3 (IBM DB2 and unixODBC)
PgSQL
SQLite 3.x
Sybase

This list is kept up to date on the php.net manual page.

Assuming that you have PDO already available, then you can get right in and create a connection. PDO is a class, so you create the connection by instantiating an object. In this example, I am going to be using MySQL:

$pdo = new PDO('mysql:host=localhost;dbname=pdo', 'mrted', 'tedsdog');

This will create a new PDO object, $pdo, connecting to MySQL with the following details:

Server: [font=Courier New]localhost[/font]
Username: [font=Courier New]mrted[/font]
Password: [font=Courier New]tedsdog[/font]

The prototype of the constructor for PDO is:

[font=Courier New]PDO PDO::__construct ( string dsn [, string username [, string password [, array driver_options]]] )[/font]

The DSN (Data Source Name) is a string which provides the necessary information to connect to the database – it provides the driver name and any database specific connection syntax. This is the part that will determine which database you are going to be working with. These are all explained on the php.net manual page.

You can perform checks in your code to see which drivers are available to you. This is done by using the [font=Courier New]extension_loaded()[/font] function. In the example below, I check that PDO itself is available, then for the MySQL driver. If MySQL is not available, I try and load it dynamically using [font=Courier New]dl()[/font].

pdo - available'; if (!extension_loaded('pdo_mysql')) { // If not loaded we could try loading it manually $prefix = (PHP_SHLIB_SUFFIX == 'dll') ? 'php_' : ''; if (@dl($prefix . 'pdo_mysql.' . PHP_SHLIB_SUFFIX)) { echo '

pdo_mysql - available

';
        }
        else
        {
            echo 'pdo_mysql - unavailable';
        }
    }
    else
    {
        echo 'pdo_mysql - available';
    }
}
else
{
    echo 'pdo - unavailable';
}
?>

Now you are connected, you can perform a query on the database and use the result to iterate through the rows:

query('SELECT name FROM people') as $person) { echo $person['name'].' '; } ?>

The [font=Courier New]query()[/font] function returns the result set as a PDOStatement object which you would then use to access the data in different ways (for example, counting the rows).

Find out more

This was a very quick introduction to PDO, which I think will hopefully encourage further takeup of PHP 5. Here are some useful links so you can investigate PDO further:

Share and Enjoy:

This entry was posted on Wednesday, July 20th, 2005 at 7:23 am, contains 736 words, 6 images, and is filed under PHP. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed. The views and opinions in this blog post are those of its author.

This post has 16 responses so far

Great opening post, David. Welcome to the gang ;)
AlexW
July 20th, 2005 at 7:42 am
Although there are already some PHP database abstraction classes available, the advantage of PDO is that it will be available in any PHP installation (so long as it is enabled) at runtime, without any need for recompiling.

PDO is -not- a database abstraction layer. The need for something like ADOdb or PEAR::DB is still present to provide an easier method of switching from one database to another and writing portable sql. PDO does -not- provide that level of functionality to programmers. PDO is merely a common interface to all the databases.

ADOdb has also included a PDO based driver since the very begging of PDO’s existance, infact that was a recent post on John Lim’s blog about PDO. http://phplens.com/phpeverywhere/?q=node/view/209
Eric.Coleman
July 20th, 2005 at 8:06 am
Eric, David said nowhere in that quote that PDO was a DB-abstraction layer.
Dean C
July 20th, 2005 at 8:46 am
Is it possible with PD0 to write ftp-like dns strings, because the ones used here do have something in common with these, but are still slightly different? Let me give a little example; It would be nice to be able to write something like:

$dns=’mysql://user:password@127.0.0.1/myDatabase’;
try
{
$dbo = new PDO($dsn);
}
catch(PDOException $e)
{
echo $e;
}

I ask this so I can start extending my dns-string parser-functions again for yet an other format. They should start standardising these
momos
July 20th, 2005 at 8:49 am
There is no harm in trying, but I don’t think you can do that momos because the DSN string is database driver specific.

php.net: In general, a DSN consists of the PDO driver name, followed by a colon, followed by the PDO driver-specific connection syntax

Have a look at http://www.php.net/manual/en/function.pdo-construct.php – that gives examples of the DSNs for each database driver.
Olate
July 20th, 2005 at 9:19 am
Eric, David said nowhere in that quote that PDO was a DB-abstraction layer.

I never said he did ;) I was responding to the portion I quoted…
Eric.Coleman
July 20th, 2005 at 9:29 am
I can see the point of using the PDO for an ODBC style database, but I fail to see the benefit in using it with MySQL. I have not run any tests, but I’m usre the built in MySQL functions (as well as the classes that use them) would be much more efficient for data handling than PDO.
Sketch
July 20th, 2005 at 3:45 pm
Sketch, I might be wrong, but I think the PDO stuff goes all the way down to c code, i.e. using the underlying code in the mysql extension. I do -not- thing it’s built “on-top” of anything
Eric.Coleman
July 20th, 2005 at 4:32 pm
I guess this makes DB_DataObject practically obsolete… ?
pdxi
July 20th, 2005 at 5:24 pm
PDO is -not- a database abstraction layer

Why isn’t PDO a database abstraction layer? It performs the same role as other classic db abstraction layers such as ODBC and JDBC, it abstracts the mechanism for accessing a database and manipulating the returned records. Perhaps are you thinking of a query abstraction layer?

The need for something like ADOdb or PEAR::DB is still present to provide an easier method of switching from one database to another and writing portable sql.

In all honesty PEAR:DB and ADOdb don’t do much in the way of query abstraction either, writing portable SQL is still largely the job of the programmer.
Lachlan
July 20th, 2005 at 7:52 pm
http://netevil.org/node.php?nid=366
andreask2
July 21st, 2005 at 3:14 am
[...] Introduction to PHP 5 PDO by David Mytton, Sitepoint (Furlong’s comments) [...]
Unofficial DreamHost Blog » Blog Archive » PHP Data Object support
April 6th, 2006 at 7:02 am
ggfhg

e>

July 11th, 2006 at 2:21 pm
i got the point from PDO, and i think the developers want to make something like JDBC, it’s good, but could you tell me the procedure of this way. like how to fetch, load and return the response to know if this way is good or not

Best Regards
(nice step david)
PDO - like JDBC?
December 7th, 2006 at 6:11 pm
Although there are already some PHP database abstraction classes available, the advantage of PDO

Now this is definitely implying in covert language that PDO is an abstraction layer. Just wanted to make that clear.

Then go on and check http://www.php.net/pdo where PDO is under the category ‘abstraction layers’.
blonkm
November 15th, 2008 at 11:33 am
trocdronp
bocrold
December 7th, 2008 at 4:21 pm