Home arrow MySQL arrow Page 3 - An Introduction to PEAR
MYSQL

An Introduction to PEAR


There have been several add-ons developed for PHP in the last couple of years, but none have reached the popularity of PEAR. PEAR is a framework and distribution for reusable PHP components. In this article John introduces us to PEAR, and more specifically, its PEAR::DB package, which is a set of classes that allows us to create generic code to talk to a variety of databases with minimal code changes.

Author Info:
By: John Ferme
Rating: 4 stars4 stars4 stars4 stars4 stars / 12
March 03, 2002
TABLE OF CONTENTS:
  1. · An Introduction to PEAR
  2. · What is PEAR?
  3. · The PEAR::DB class
  4. · Conclusion

print this article
SEARCH DEVARTICLES

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:

Results from calling PEAR::DB

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.

blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials