An Introduction to PEAR - The PEAR::DB class
(Page 3 of 4 )
The most popular PEAR package is PEAR::DB, which allows us to write code and query several different types of databases with ease. For example, we could tell the PEAR::DB class that we want to query a MySQL database, perform that query, and then come back, change one variable, and have that same query run against an Oracle database (assuming that we have configured PHP with Oracle support).
The PEAR::DB package contains a class called DB. The DB class contains a method called connect, which we can use to connect to a database. The signature of the connect function looks like this:
function &connect($dsn, $options = false)It accepts two parameters. The first is a DSN (data source name), and must be passed in as either an array or a string. The DSN array must be in the following format:
$dsn = array(
'phptype' => "mysql",
'hostspec' => "localhost",
'database' => "mydatabase",
'username' => "admin",
'password' => "password"
);Note that the array is associative, and each of the keys must be present in order for the connect method to successfully execute. The second option is to pass the DSN as a string. The format of a DSN string must be:
phptype://username:password@protocol+hostspec/database
So a valid DSN string might be:
mysql://admin:password@localhost/mydatabase
or
oracle://admin:password@localhost/mydatabase
or
pgsql://admin:password@localhost/mydatabase
In both the array and string DSN types, the phptype value specifies the type of database that we're requesting a connection to. It can be ibase, msql, mssql, mysql, oci8, odbc, pgsql or sybase. The username and password values should correspond to those that you use to connect to your database server. The hostspec value should be the name/I.P. address of your database host, and obviously the database value will be the name of the database you'd like to connect to.
The second parameter of the connect function is $options, which can optionally be an associative array that we can use to specify debugging options, etc. It may also be a Boolean value for backwards compatibility. If it's set to true, then the connection to the database will be persistent.
The simplest way to test PEAR's PEAR::DB class is to mock up a simple PHP script. Create a new PHP script called testdb.php and enter the following code into it:
<?php
require_once("DB.php");
$dbType = "mysql";
$dbUser = "admin";
$dbPass = "password";
$dbServer = "localhost";
$dbName = "mysql";
$db = DB::connect("$dbType://$dbUser:$dbPass@$dbServer/$dbName");
if(DB::isError($db))
{
die("Couldn't connect to database");
}
else
{
$query = "SELECT host, user FROM user";
$uResult = $db->query($query);
while ($uRow = $uResult->fetchRow())
{
echo $uRow[0] . " => " . $uRow[1] . "<br>";
}
$db->disconnect();
}
?>Save testdb.php into a directory that Apache/IIS can process and load it into your web browser. Here's the output from testdb.php on my computer:
Let's now run through the code in our PEAR::DB example above.
require_once("DB.php");Firstly, we use PHP's require_once() function to include PEARs DB package, which resides inside of the DB.php script in the PEAR subdirectory of where you installed PHP. By using require_once(), we're making sure that PHP will only every include DB.php once during the execution of a script.
$dbType = "mysql";
$dbUser = "admin";
$dbPass = "password";
$dbServer = "localhost";
$dbName = "mysql";
$db = DB::connect("$dbType://$dbUser:$dbPass@$dbServer/$dbName");Next, we define the connection variables for our MySQL database. On my machine, MySQL is installed along with PHP, and I'm using the default user account.
The PEAR::DB classes name is DB. Notice that we call the classes connect method statically with the scope resolution operator, "::" and don't actually instantiate the class. The connect method which we described earlier returns a database connection object if it succeeded, or a PEAR error object if it failed. PEAR::DB's isError() method is then used to check whether or not the connect attempt succeeded:
if(DB::isError($db))
{
die("Couldn't connect to database");
}If the connection succeeded, then we call PEAR::DB's query() method to run a query against our database. The query() method executes a query against our MySQL database and returns the result. We loop through this result using the fetchRow() method, which is similar to PHP's mysql_fetch_row() method:
else
{
$query = "SELECT host, user FROM user";
$uResult = $db->query($query);
while ($uRow = $uResult->fetchRow())
{
echo $uRow[0] . " => " . $uRow[1] . "<br>";
}Lastly, we close the connection to our database using PEAR::DB's disconnect() function:
$db->disconnect();That's really all there is to using PEAR::DB to connect to a database. Of course the PEAR::DB package exposes several other methods that we can use to work with database as well. The great thing about these methods is that they work with all of the databases that the PEAR::DB package supports (Interbase, mSQL, Microsoft SQL Server, MySQL, Oracle, ODBC, PostgreSQL or Sybase).
Here are some of the methods that we can use with PEAR::DB:
- DB::isWarning(): Tell whether or not a result code from a DB method is a warning.
- DB::quote(): Quotes a string so it can be safely used in a query.
- DB::execute(): Executes a prepared SQL query.
- DB::getOne(): Fetch the first column of the first row from a query.
- DB::getAll(): Fetch all the rows returned from a query.
- DB::nextId(): Returns the next free id of a sequence.
- DB::getListOf(): List internal DB info.
- DB_Result::fetchInto(): Fetch a row of data into an existing variable.
- DB_Result::nextResult(): Get the next result if a batch of queries was executed.
- DB_Result::tableInfo(): Returns meta data about the result set.
Next: Conclusion >>
More MySQL Articles
More By John Ferme
/ 7 
