Section 14.2. PEAR::DB

14.2. PEAR::DB

PEAR::DB is an advanced, object-oriented database library that provides full database abstractionthat is, you use the same code for all your databases. If you want your code to be as portable as possible, PEAR::DB provides the best mix of speed, power, and portability. However, if your scripts are only ever going to run locally, there is no compelling reason to use PEAR::DB.

PEAR::DB works by abstracting not only the calls neccessary to work with the databases (such as mysql_connect( ), pgsql_query( ), etc.), but also clashes with SQL syntax, such as the LIMIT clause. In PHP 5.1, there's a new extension called PHP Data Objects (PDO) that abstracts only the functions, which is halfway between PEAR::DB and using normal DB calls. PEAR::DB is likely to be updated to use PDO, as it's much more efficient.

This script below provides a good demonstration of how PEAR::DB works:

     include_once('DB.php');     $conninfo = "mysql://username:password@localhost/phpdb";     $db = DB::connect($conninfo);     if (DB::isError($db)) {             print $db->getMessage( );             exit;     }     $result = $db->query("SELECT * FROM people;");     while ($result->fetchInto($row, DB_FETCHMODE_ASSOC)) {             extract($row);             print "$Name:  $NumVisits\n";     }     $result->free( );     $db->disconnect( ); 

PEAR::DB uses a URL-like connection string, often called a Data Source Name (DSN), to define its connection. This is the same method as seen in JDBC, so it should already be familiar to Java developers. The string can be broken down into parts, as shown in Table 14-1.

Table 14-1. The different parts of a PEAR::DB connection string

mysql://	Connection type
Username	Your username
Password	Your password
@localhost	The address of your server
/phpdb	The database name to use

If any part of your DSN contains characters that might be confused for separators (such as :, @, or /), you should use rawurlencode( ) to %-escape them. For example:

     $username = "paul";     $password = "p|trp@tr";     $username = rawurlencode($username);     // does nothing; our username is safe     $password = rawurlencode($password);     // $password is now p%7Ctrp%40tr     $conninfo = "mysql://$username:$password@localhost/phpdb"; 

The connection type is the kind of server you are connecting to. You can choose from the list shown in Table 14-2.

Table 14-2. Database providers for PEAR::DB

fbsql	FrontBase
ibase	InterBase
ifx	Informix
msql	Mini SQL
mssql	Microsoft SQL Server
mysql	MySQL
oci8	Oracle 7/8/8i
odbc	ODBC (Open Database Connectivity)
pgsql	PostgreSQL
sqlite	SQLite
sybase	SyBase

Once the DSN is prepared, you must pass it into a call to DB::connect( ) as its first parameter. This will return a reference to the object you can use for querying. PEAR::DB is object-oriented, which means you need to hang on to the return value from DB::connect( ).

The DB::isError( ) function is a special function call that takes the value to check as its parameter, and returns true if that value is one of PEAR::DB's error types. In our example, $db is passed in so we can check whether DB::connect( ) failed. On the off chance that an error has occurred, it will be stored in the getMessage( ) function of your database connection.

However, if things go well, you can start querying the system using the query( ) function of our $db object. This takes the SQL query to perform as its only parameter, and returns another kind of object that contains the result information. To cycle through the result information, a while loop is used, taking advantage of the fetchInto( ) PEAR::DB function. This will return false if it cannot return any more rows, and takes two parameters: where it should send the data it fetches, and how it should store the data there. Using DB_FETCHMODE_ASSOC means that PEAR::DB will set up $row to be an associative array of one row in the result set, recursively iterating through the rows with each while loop.

At the end of the script, we call the free( ) and disconnect( ) functions to clean up.

14.2.1. Quick PEAR::DB Calls

PEAR::DB has the getOne( ), getRow( ), and getCol( ) functions for making easy queries , and each takes an SQL query to execute as its parameter. The first executes the query and then returns the first row of the first column of that query, the second returns all columns of the first row in the query, and the last returns the first column of all rows in the query. The getOne( ) function returns just one value, whereas getRow( ) and getCol( ) both return arrays of values.

Here is an example demonstrating each of these functions in action, using a table of people:

     include_once('DB.php');     $db = DB::connect("mysql://phpuser:alm65z@localhost/phpdb");     if (DB::isError($db)) {             print $db->getMessage( );             exit;     } else {             $maxage = $db->getOne("SELECT MAX(Age) FROM people;");             print "The highest age is $maxage<br />";             $allnames = $db->getCol("SELECT Name FROM people;");             print implode(', ', $allnames) . '<br />';             $onecol = $db->getRow("SELECT * FROM people WHERE Name = 'Ildiko';");             var_dump($onecol);     }     $db->disconnect( ); 

14.2.2. Query Information

Because PEAR::DB smooths over the differences between database servers, it is very helpful for measuring the effects of queries. Three particularly helpful functions are numRows( ), numCols( ), and affectedRows( ), which return information about what a query actually didnumRows( ) returns how many rows were returned from a SELECT statement, numCols( ) returns how many columns (fields) were returned from a SELECT statement, and affectedRows( ) returns how many rows were altered by an UPDATE, INSERT, or DELETE statement. For example, if we have three rows with Age 35 in our people table and execute the query UPDATE people SET Name = 'xxx' WHERE Age = 35, affectedRows( ) would return 3.

Here is an example of these functions in action:

     include_once('DB.php');     $db = DB::connect("mysql://phpuser:alm65z@localhost/phpdb");     if (DB::isError($db)) {             print $db->getMessage( );             exit;     } else {             $result = $db->query("SELECT * FROM people;");             print 'Query returned ' . $result->numRows( ) . ' rows\n';             print 'Query returned ' . $result->numCols( ) . ' cols\n';             print 'Query affected ' . $db->affectedRows( ) . ' rows\n';             $db->query("INSERT INTO people VALUES ('Thomas', 36);");             print 'Query returned ' . $result->numRows( ) . ' rows\n';             print 'Query returned ' . $result->numCols( ) . ' cols\n';             print 'Query affected ' . $db->affectedRows( ) . ' rows\n';             $result->free( );     }     $db->disconnect( ); 

The first PEAR::DB query is a SELECT statement, which means that it will return values for numRows( ) and numCols( ). The affectedRows( ) function is not a function of the PEAR::DB query result objectnumRows( ) is $result->numRows( ), numCols( ) is $result->numCols( ), but affectedRows( ) is $db->affectedRows( ).

This is because SELECT statements are read from the database and return a result object from $db->query( ). INSERT, UPDATE, and DELETE statements only return success or failure, and because affectedRows( ) only returns a meaningful value when used with these types of statements, it would be pointless to put affectedRows( ) into the query( ) result.

This is illustrated in the next block of codethis time, we insert a new person into the table, and again print out the three functions. Note that we do not capture the return value of the function, because it does not return anything useful in this script. This time around, printing out numRows( ) and numCols( ) returns the same values as before, because the $result object is unchanged from the previous call.

Calling $db->affectedRows( ) should return 1, because we inserted a row. To illustrate the situation with the return value of query( ), try editing the code to this:

     $result = $db->query("INSERT INTO people VALUES ('Thomas', 0);"); 

This time, you should get the following error when you try to run the script:

     Fatal error: Call to a member function on a non-object 

This is because the return value from query( ) will be TRue if it succeeds, and an error otherwise. As a result, calling $result->numRows( ) is calling a function on TRue, which will not work.

Use numRows( ) and numCols( ) only with SELECT queries, and use affectedRows( ) only with INSERT, UPDATE, and DELETE queries.

14.2.3. Advanced PEAR::DB: Prepared Statements

PEAR::DB is capable of prepared statements a technique to handle repetitive SQL statements. Prepared statements let you treat an SQL query somewhat like a functionyou define roughly what the query will do, without actually passing it any values, then later you "call" the query and pass it the values to use.

Prepared statements are easy to use and eliminate much of the fuss of SQL, because you no longer need long and complicated queries to achieve your goals. Most importantly, you don't need to worry about escaping quotes and the like.

A prepared statement looks something like this:

     INSERT INTO people VALUES (?, ?); 

Once you have the prepared statement ready, it can be called later by providing the values previously filled with question marks:

     include_once('DB.php');     $db = DB::connect("mysql://phpuser:alm65z@localhost/phpdb");     if (DB::isError($db)) {             print $db->getMessage( );             exit;     } else {             $data = array(                     array("Gabor", 25),                     array("Elisabeth", 39),                     array("Vicky", 19)             );             $prep = $db->prepare("INSERT INTO people VALUES (?, ?);");             while(list($var, $val) = each($data)) {                     print "Adding element $var\n";                     $db->execute($prep, $val);             }     }     $db->disconnect( ); 

The $data array has three elements, each arrays in their own right. Look down to the line $db->execute( )this function takes two parameters: the prepared statement to execute and the array of values to pass to it. When PEAR::DB fills in the question marks in the prepared statement passed in parameter one of execute( ), it iterates through the array passed as parameter twoelement zero of the array is used for the first question mark, element one is used for the second, etc.

Going back to the $data array, you should now realize that the reason it is an array of arrays is because each child array holds one complete set of values for the prepared statement, ready to be passed into $db->execute( ) later on. The first set of values is "Gabor" and 25, which will be turned into this:

     INSERT INTO people VALUES ('Gabor', 25); 

The $db->prepare( ) function is what actually sets up the prepared statement. It takes the SQL statement to use as its parameter, with question marks being used wherever values need to be provided later. You can mix hard-coded values and question marks freely, and you should take advantage of this so that you need to do as little work as possible.

Calling prepare( ) returns the index number of the prepared statement to use, which is an integer. This needs to be stored away in a variable so that you can specify which prepared statement you want to use when you call execute( ).

The actual execution of the prepared statement is inside a while loop. The loop iterates through each element in the $data array, extracting its key and value into $var and $val, respectively; each time we have an element, we call execute( ). This takes two parameters: the prepared statement to execute and the values to pass to it. In the example code above, the return value from the $db->prepare( ) line is used as parameter one, and the $val value extracted from the $data array is sent in as parameter two. That will execute the prepared statement three times, as we have three sets of data to be inserted.