Introduction to peardb

To get basic use out of pear::db, you need to learn how to connect to a database, run a SQL query, and work with the results. This is not a SQL tutorial, so we have assumed you are already familiar with the language. For the sake of this tutorial, we have also assumed you are working with a database called dentists and a table called patients that contains the following fields:

• ID The primary key, auto-incrementing integer for storing a number unique to each patient

• Name A varchar(255) field for storing a patient name

• Occupation A varchar(255) field for storing a patient occupation

Also for the sake of this tutorial, we will use a database server on IP address, running MySQL, with username ubuntu and password alm65z. You will need to replace these details with your ownuse localhost for connecting to the local server.

The first step to using pear::db is to include the standard pear::db file, DB.php. Your PHP will be configured to look inside the PEAR directory for include() files, so you do not need to provide any directory information.

pear::db is object oriented, and you specify your connection details at the same time as you create the initial DB object. This is done using a URL-like system that specifies the database server type, username, password, server, and database name all in one. After you have specified the database server here, everything else is abstracted, meaning you only need to change the connection line to port your code to another database server.

This first script connects to our server and prints a status message (see Listing 29.6).

Listing 29.6. Connecting to a Database Through pear::db

$dsn = "mysql://ubuntu:[email protected]/dentists"; $conn = DB::connect($dsn);

echo $conn->getMessage() . "\n"; } else {

echo "Connected successfully!\n";

You should be able to see how the connection string breaks down. It is server name first, then a username and password separated by a colon, then an @ symbol followed by the IP address to which to connect, and then a slash and the database name. Notice how the call to connect is DB::connect(), which calls pear::db directly and returns a database connection object for storage in $conn. The variable name $dsn was used for the connection details because it is a common acronym standing for data source name.

If DB::connect() successfully connects to a server, it returns a database object we can use to run SQL queries. If not, we get an error returned that we can query using functions such as getMessage(). In the previous script, we print the error message if we fail to connect, but we also just print a message if we succeed. Next, we will change that so we run an SQL query if we have a connection.

Running SQL queries is done through the query() function of our database connection, passing in the SQL we want to execute. This then returns a query result that can be used to get the data. This query result can be thought of as a multidimensional array because it has many rows of data, each with many columns of attributes. This is extracted using the fetchinto() function, which loops through the query result converting one row of data into an array that it sends back as its return value. You need to pass in two parameters to fetchinto() specifying where the data should be stored and how you want it stored. Unless you have unusual needs, specifying db_fetchmode_assoc for the second parameter is a smart move.

Listing 29.7 shows the new script.

Listing 29.7. Running a Query Through pear::db

$dsn = "mysql://ubuntu:[email protected]/dentists"; $conn = DB::connect($dsn);

echo $conn->getMessage() . "\n"; } else {

echo "Connected successfully!\n";

$result = $conn->query("SELECT ID, Name FROM patients;");

while ($result->fetchInto($row, DB_FETCHMODE_ASSOC)) { extract($row, EXTR_PREFIX_ALL, 'pat'); echo "$pat_ID is $pat_Name\n";

The first half is identical to the previous script, with all the new action happening if we get a successful connection.

Going along with the saying "never leave to PHP what you can clean up yourself," the current script has problems. We do not clean up the query result, and we do not close the database connection. If this code were being used in a longer script that ran for several minutes, this would be a huge waste of resources. Fortunately, we can free up the memory associated with these two by calling $result->free() and $conn->disconnect(). If we add those two function calls to the end of the script, it will be complete.


Was this article helpful?

0 0

Post a comment