From LXF Wiki

Table of contents

1 Practical PHP Programming 2 All prePEARed? 3 ComPEARing the packages 3.1 Accelerate your code 3.2 Benchmark performance 3.3 Parse XML 3.4 Find files 4 Don't disPEAR! 5 Power functions 6 Query meta-information 7 PrePEARed statements 8 ImPEARed performance? 9 Where's the partridge? 9.1 Dog food 9.2 PEAR::DB DBs

Practical PHP Programming

(Original version written by Paul Hudson for Linux Format magazine issue 48.)

On the fourth day of Christmas, we give you four calling birds, three french hens, two turtle doves, and a partridge in a PEAR tree...

Writing PHP code is quite a fun task, as we're sure of you will agree - it's an easily learnt, flexible, and feature-rich language that lends itself very well to scripts of all shapes and sizes. Almost certainly as a result of this comparitive ease of use, many PHP programmers take to re-inventing the wheel and spend a lot of time writing and rewriting "new and improved" code to handle authentication, caching, forums, opinion polls, and other systems that have been coded and recoded in the past.

This is not unique to PHP as a language. C++, for example, utilises the Standard Template Library (STL) to provide abstract data types (ADTs) for programmers that are pre-written, pre-optimised, and are cross-platform. As a result, a programmer writing for Windows using C++ can create and use STL stacks, vectors, maps, and queues, and have them recompile smoothly on Mac OS, OS/390, Solaris, and, of course Linux. This is immensely timesaving, and so it's unsurprising that many other languages have their own versions of this plan. In PHP, this is known as PEAR, the PHP Extension and Application Repository, and is available from http://pear.php.net. The purpose of this tutorial is to briefly outline what PEAR can offer, with a particular focus (thanks to popular demand) on PEAR::DB, the advanced database abstraction library that forms the most popular part of PEAR.

All prePEARed?

Switching to an off-the-shelf solution for a variety of common problems might seem like a hard task - after all, each problem is unique, right? In most cases, yes, we want to solve a problem that has yet to be solved. This is what makes programming a fun and challenging tasks, and, quite frankly, the problem wouldn't be a problem if it were already solved! However, while most problems is unique, they can usually be broken down into a set of smaller problems, of which each can be broken down into a smaller set of problems, and so on. This recursivity was put into prose by Augustus De Morgan:

Great fleas have little fleas upon their backs to bite 'em, and little fleas have lesser fleas, and so, ad infinitum

Of course, problems (and fleas) can only be reduced to a certain level, at which point you actually need to go about solving them. However, it's at this point - when we've broken our large, complex, and unique problem down into itty-bitty parts - that we find we can re-use components easily, and can indeed benefit from doing so. For example, handling file uploads is a fairly common task that is usually one small part of a larger system, and there are many ways you can handle it. Now, imagine for a moment you're joining a large company where file uploads are handled, and you ask what method was used to handle the files - would you rather hear "it's our own custom code, written by one of our junior programmers", or "we use the PEAR code"?

Unless you enjoy suffering, you'll certainly prefer the option that gives you code that's written by a set of experts in their field, tested by thousands, written according to strict style rules to make the source code easy to read, and also designed to be flexible. This is where PEAR wins out, and once you make it over the initial barrier to entry - that is, finding one or two places where PEAR can help you immediately and give it a try - you'll almost certainly stick with it.

If you really get into the swing of using PEAR, you may even find it worthwhile to build up your own modules that are compilations of PEAR modules, which models your code along the same building-block lines as PEAR itself. If you manage to achieve this nirvana - it helps if you use OOP - then you'll find you can turn around even the largest projects by putting together your small building blocks into several larger blocks, then those larger blocks into one finished solution, thus completing De Morgan's poem:

And the great fleas themselves, in turn, have greater fleas to go on; While these again have greater still, and greater still, and so on.

ComPEARing the packages

While most of the requests we've received to cover PEAR have centred on PEAR::DB, we can hardly cover PEAR and not discuss the dozens of other modules that make it up. So, before we jump into the ins and outs of PEAR::DB, let's first look at what else PEAR can do to revolutionise your PHP programming:

Accelerate your code

Back in LXF34 we reviewed various PHP Accelerators to see which would provide maximum acceleration for your PHP scripts, and at the bottom of that test, with a lowly 3/10, came the Alternative PHP Cache. Since then, APC was contributed to the PEAR project and taken onboard by George Schlossnagle (the author of Advanced PHP Programming), where it has been revolutionised into a much faster and leaner product that competes well with Zend Performance Suite.

Benchmark performance

If you're sick of trying to hack together a decent profiler with microtime(), PEAR's benchmarking framework is just what you're looking for - you can check and time performance data based upon overall script execution, as well as more specific things such as individual function execution.

Parse XML

Although PHP's XML-parsing featureset is powerful and will be even better in PHP 5, PEAR contains a number of packages that provide more specific XML parsing capabilities such as RSS and RDDL handling.

Find files

PHP has solid filesystem-handling support, but, as with XML, the PEAR developers have found ways to make things even easier for us. For example, finding files in a given directory is now as simple as using the pre-written directory search component bundled with PEAR.

While most parts of PEAR are easy as easy to use as any other part of PHP, the most popular and most advanced part of it also happens to be the most complicated to fully grasp. Having said that, PEAR::DB, for it is that of which we speak, is worth spending a few hours tinkering with, as it's actually rather good.

Don't disPEAR!

Although it's hard to learn fully, we're going to look over some of the various features of PEAR::DB in a simple way - hopefully everyone should be able see why it's a good thing. First, let's take a look at what PEAR::DB offers:

• Complete database abstraction for a variety of databases (see the box "PEAR::DB DBs" for a list)
• Object-orientated querying
• A selection of quick "power" functions that perform a query and extract data in one call
• Prepared statements

We'll be looking at each of these, so if you're not sure what a prepared statement is you can relax as it will be covered shortly. The first two items there, though, are fairly elementary, so we can just dive right in with a lump of code:

<?php
1  include_once('DB.php');
2  $conn = DB::connect("mysql://user:pass@server/mydb");
3  if (DB::isError($conn)) {
4    echo 'Connection error: ', $conn->getMessage();
5    exit;
6  } else {
7    $qresult = $conn->query("SELECT Field1, Field2 FROM sometable;");
8    while ($qresult->fetchInto($qrow, DB_FETCHMODE_ASSOC)) {
9      extract($qrow);
10      echo "$Field1 $Field2<BR /?\n";
11    }
12    $qresult->free();
13  }
14  $conn->disconnect();
?>

That's quite a bit of code to absorb all at once, so let's look at the important lines...

Line 1: This includes the PEAR::DB code that's neccessary to connect and query using PEAR::DB. Note that we just specify "DB.php", as the PEAR include directory is usually in the PHP include directory directive by default.

Line 2: This connects to your database using username "user", password "pass" on server "server", then selects the database "mydb". The "mysql://" part specifies that this is a MySQL server, and you can replace that with one of the other databases listed in "PEAR::DB DBs" to change the connection type. Note that DB::connect() returns a database connection object, which is stored in $conn.

Line 3: This will return true if the last function called on $conn returned an error.

Line 4: getMessage() returns the last human-readable message stored in the connection. In the case of an error, this will store a small amount of text describing the error.

Line 7: query() simply sends a query to the database object and returns the result as an object. To maintain the cross-platform advantages of PEAR::DB, steer clear of non-ANSI SQL extensions, such as the "LIMIT" clause in MySQL. Note that DELETE, INSERT, and UPDATE queries do no return an object because they don't return any data - instead you can expect true ("DB_OK") or an error.

Line 8: fetchInto() is the equivalent of mysql_fetch_array(), and takes one row from the result, and places it as an array specified in parameter 1. The second parameter specifies what data is stored, and is optional - DB_FETCHMODE_ASSOC will make the first parameter into an associative array, with field name being used as the key.

Line 12: This frees the result, which is always a smart move to keep your "house" clean

Line 14: Finally, we disconnect from the database, again another good housekeeping move.

Power functions

Point three of the four helpful things that PEAR::DB does for you is provide power functions that accomplish a lot of functionality in just one line of code. The simplest of these is getOne(), which takes a simple SQL query to execute on a database connection, executes the query, and returns the selected value. For example:

$averageage = $db->getOne("SELECT AVG(Age) FROM userlist;");
echo "The average member age is $averageage<BR />";

Naturally this isn't much good for more complicated queries as it only returns the first field from the first row, however there are other calls that add a little more flexibility: getRow(), for example, executes the query passed and returns all columns of the first row returned by the query, whereas getCol() returns the first column of all rows in the query. Both getRow() and getCol() return their information in an array, whereas getOne(), as you've seen, returns a simple variable.

Query meta-information

As well as reading the actual results of your query, you can also read back various information about what the query returned - the equivalent of mysql_num_rows(), mysql_num_cols(), and mysql_affected_rows() all have their counterparts in PEAR::DB in the form of numRows(), numCols(), and affectedRows().

As with MySQL, numRows() and numCols() should be used when data has been selected and you want to know how much data there is, whereas affectedRows() should be used when data has been changed (ie, you've used DELETE, UPDATE, or INSERT) and want to know how many rows have changed as a result of your query. In use, numRows() and numCols() work alike, whereas affectedRows() is slightly different. Put simply, numRows() and numCols() are called upon the data result itself and simply counts the number of items in the data set. On the other hand, affectedRows() is used for queries where no result is returned, and so you need to call it using the main database object itself.

To make this clear, take a look at the following example:

<?php
    $qresult = $db->query("SELECT User, Pass FROM memberlist;");
    echo 'Rows returned: ' . $qresult->numRows() . ' rows\n';
    echo 'Cols returned: ' . $qresult->numCols() . ' cols\n';
    $qresult = $db->query("INSERT INTO memberlist VALUES ('Luke', 'Jedi');");
    echo 'Rows affected: ' . $db->affectedRows() . ' rows\n';
    $result->free();
  }
  $db->disconnect();
?>

The calls to numRows() and numCols() are both called on the $qresult object returned by the SELECT query, but note that the return value from the INSERT query /isn't/ used to call affectedRows(). This is because queries that change data rather than return data don't send back a resource object, so you need to go direct to the DB. Keep this in mind, because if you try to call numRows() or numCols() on the return value from a non-SELECT query you'll get an error.

PrePEARed statements

By now you should be able to see that switching to PEAR::DB means you can centralise all DB-specific code so that the only line that varies across databases is the original connection - this is a great benefit by itself, but there's a lot more that PEAR::DB can do to make itself stand out amongst the crowd even more. Perhaps the most helpful feature is prepared statements, which allows you to define approximately what a query will do without having to pass exact values into it. For example, you can prepare a query that will insert rows into a field without specifying what the field values will be, then, later, you can call that query with your values.

This might all sound complicated at first, but essentially it makes an SQL query almost like a function - what your prepared statement does won't change, but the values it works with will. If you don't think this is helpful, consider how hard it is to read long SQL queries - if you're specifying twenty fields and values it can get complicated to understand precisely what's going on! All this changes with prepared statements, as, because the prepared statement already knows what fields it's working worth, you only need to specify the values; you essentially split the command from the data.

Your basic prepared statement looks like this:

  INSERT INTO memberlist VALUES (?, ?);

Each question mark there symbolises one field for which we don't currently know the value, and you can have as many question marks as you have fields. You can also mix and match question marks and hard-coded values, like this:

  INSERT INTO memberlist VALUES (?, 'blue', ?, 10, 0, ?, ?);

Here the second, fourth, and fifth values are hard-coded as 'blue', 10, and 0, but the first, second, sixth, and seventh values are variable and need to be provided later. Note that the question marks need to be exactly as shown, as anything else is considered to be a hard-coded value. For example, if you want all rows in the first field to start with "foo-", you couldn't use a query like this:

  INSERT INTO memberlist VALUES ('foo-?', 'blue', ?, 10, 0, ?. ?);

While it might seem logical that the question mark in 'foo-?' is treated as a wildcard and replaced with the value later on, it isn't, so repeat after me: anything other than a plain question mark is treated as a hard-coded value.

With that in mind, let's take a look at a working code example of prepared statements. For this example, you'll need a table, "future_publishing", that has three fields: "user", "pass", and "magazine".

$teamlxf = array(
  array("Matt", 'peekaboo'),
  array("Nick", 'tulip'),
  array("Julian", 'pembrokeshire')
);
$statement = $db->prepare("INSERT INTO future_publishing VALUES (?, ?, 'LXF');");
foreach($teamlxf as $teammember) {
    $db->execute($statement, $teammember);
}

You'll need to wrap all that up in the connect/disconnect code from earlier before you try it. The two key parts there are prepare() and execute(), which take two parameters and one parameter respectively. The prepare() function is used, unsurprisingly, to prepare a statement, and it takes the statement you want prepared as its parameter, returning the number that's been assigned to the prepared statement. In our code this value is kept locked away in $statement for use two lines later.

A foreach() loop is used to cycle through each team member in the $teamlxf array, which is an array of arrays. Each element in $teamlxf is an array containing a username and password for our prepared statement, and the foreach() loop extracts each team member and sends it off to the execute() function as parameter two. So, parameter two for execute() is the data we want to send in; parameter one, as you can see, is the prepared statement to use. Note that the question marks in the prepared statement are filled with the $teammember array from $teamlxf directly - you don't need to break the array apart to send them off to PEAR::DB, which makes your code a great deal easier to read.

ImPEARed performance?

Common sense says that directly calling PHP's MySQL functions will be faster than using an abstracted code library written in PHP. After all, every call you make using PEAR::DB needs to jump through a variety of hoops to handle the variety of databases, whereas calling mysql_query() is entirely unambiguous and therefore likely to be much faster.

In practice, PEAR::DB usually runs at about 1/2 the speed of the equivalent DBMS-specific code, although this may depend on the kinds of query you're running. Of course, it's impossible for PEAR::DB to ever outpace DBMS-specific calls simply because once the database abstraction has been resolved internally to PEAR::DB it will go ahead and execute the native calls itself.

However, you need to weigh this hefty performance hit against the extra flexibility offered by PEAR::DB - if you sprinkle mysql_* calls throughout your script then later want to port to Oracle you'll find yourself with a fair amount of work on your hands. If you use PEAR::DB, you just need to change the connection line, which is a darn sight easier. The addition of prepared statements is also quite attractive, particularly when you need to handle large amounts of fairly similar data, but also because ASP programmers (using Microsoft's ADO technology) have had prepared statements for some time, and so it makes the transition to PHP easier for them.

Where's the partridge?

PEAR, particularly PEAR::DB, offers an immense amount of code that's easily available and, more importantly, easy to re-use across projects. While we've focused almost wholly on PEAR::DB here, simply because it's the most advanced module in PEAR, there's a lot more out there - did you know, for example, that you can grab a Mono (yes, the cross-platform .NET system by Miguel et al) extension for PHP? No? You should check the PEAR site more often!

So, in conclusion, PEAR is a great resource for PHP developers who want to take their projects a little further - either by using the advanced components on offer there, or by using the basic components there and thereby spend their programming time working on the more interesting and challenging topics. Even if you're not sure whether you want to commit to using PEAR just yet, at least give it a look over and see whether it has anything that can directly help you.

Alright, we lied about giving you a partridge...