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].
Now you are connected, you can perform a query on the database and use the result to iterate through the rows: