Doing it the PEAR way
Out with the old, and in with the new – how the hell do we do that with PEAR ? Here’s our MySQL example above, rewritten using PEAR DB.
<?php
include('dbinfo.php');
require_once( 'DB.php' );
$db = DB::connect( "mysql://$dbuser:$dbpass@$dbhost/$dbname" );
$sql = 'SELECT * FROM demo';
$demoResult = $db->query($sql);
while ($demoRow = $demoResult->fetchRow()) {
echo $demoRow[2] . '<br>';
}
$db->disconnect();
?>
OK, compare that to what we saw with MySQL. The process is pretty much the same (connect, query, loop through result, disconnect), but the way that we do each step has changed. Let’s go through it line by line and I’ll explain what’s going on.
Our first line is as it was in our original script. I’m including my
dbinfo.php so that I don’t have to hard code my database usernames and passwords. The next line require_once( "DB.php" ); is require’ing the file DB.php. The _once part makes sure that requires of files that contain duplicate variable or function declarations do not break your script.
The file
DB.php can be found in the /pear subdirectory of the PHP4 distribution. If the installation process didn’t do it, make sure that the directory that contains the PEAR files is included in your include_path in the php.ini.
Inside
DB.php a class of type DB is defined. One of the functions of the DB class is the connect() function, and this is what we are using here:
<?php
$db
= DB::connect( "mysql://$dbuser:$dbpass@$dbhost/$dbname" );
?>
The
connect() function creates a new database object and connects to the database as specified in the string that you pass it. The string takes the format of:
phptype://username:password@protocol+hostspec/database
phptype is the type of database that you wish to connect to. Valid types are ibase, msql, mssql, mysql, oci8, odbc, pgsql and sybase.username, password and hostspec are self explanatory. protocol is the protocol to connect on (eg tcp) and database is the database that you want to use. The only thing in that string that is required is the phptype. Everything else is optional, but in most cases you are going to want to specify a username, password and database.
If I wasn’t using the
dbinfo.php include file the string I passed the connect function would look something like:"mysql://theuser:thepassword@localhost/phptst"
Assuming that the
connect() function succeeded, $db is now an object that not only contains methods for accessing your database but also contains all sorts of information about the conection you have just created.
What is important for this example though, is that the object has a function called
query() that will execute an SQL statement on the database. It returns a result object with it’s own properties and methods that we can use to get at the data.
$demoResult = $db->query($sql);
says “in the object
$db is a function called query(). Run it with the argument $sql and stick the results you get back from it in $demoResult“.
One of the functions in
$demoResult is fetchRow(). fetchRow() will return a row of data as an array, much like mysql_fetch_row() will.
while ($demoRow = $demoResult->fetchRow()) {
fetchRow() is returning an array indexed numerically. What if you wanted to get an associative array back like you can with mysql_fetch_assoc() ? The fetchRow() function has an optional argument that specifies the type of array that will be returned by the function. If no argument is specified then DB_FETCHMODE_DEFAULT is assumed. It then returns whatever the efault has been set to. If you wanted to explicitly specify each one, then you would use DB_FETCHMODE_ORDERED for an array index numerically, or DB_FETCHMODE_ASSOC for an array index by field name (associative). DB_FETCHMODE_DEFAULT, DB_FETCHMODE_ORDERED and DB_FETCHMODE_ASSOC are all constants that are defined in DB.php.
So if we wanted to use an associative array, we would rewrite our code as follows:
<?php
while ($demoRow = $demoResult->fetchRow(DB_FETCHMODE_ASSOC)) {
echo $demoRow['demo_text'] . '<br>';
}
?>
Once we have finished with our connection to the database we can disconnect with
$db->disconnect();.
