-top: 0px; margin-bottom: 0px; -ms-text-justify: inter-ideograph;">12 $query = "SELECT * FROM books NATURAL JOIN authors";
13 $result = $connection->query($query);
14
15 if (DB::isError($result)){
16 die("Could not query the database:<br />$query ".DB::errorMessage($result));
17 }
18
19 echo('<table border="1">');
20 echo '<tr><th>Title</th><th>Author</th><th>Pages</th></tr>';
21
22 while ($result_row = $result->fetchRow( )) {
23 echo "<tr><td>";
24 echo $result_row[1] . '</td><td>';
25 echo $result_row[4] . '</td><td>';
26 echo $result_row[2] . '</td></tr>';
27 }
28
29 echo("</table>");
30 $connection->disconnect( );
31
32 ?>
Example 9-7 displays the screen shown in Figure 9-7.
Figure 9-7. Switching to the PEAR DB functions didn’t change the output
Notice that Figure 9-7 is identical to the output in Figure 9-4.
Line 3 includes your database login information and remains unchanged:
include('db_login.php');
Line 4 has a new require statement:
require_once( "DB.php" );
This requires the file DB.php, which provides the PEAR DB functions. The require_once function stops your code fromexecuting and returns an error if the DB.php fileis not found. It also will not include the file if it has been incorporated already. And,this would cause an error.
The file DB.php is found in the /pear subdirectory of the PHP distribution.The PEAR install should have added that directory to the include_path in the php.ini file. If this file is not found, verify thatPEAR DB is installed and that the paths are set up correctly.
Creating a connect instance
The DB.php file defines a class of type DB. Refer to Chapter 5 for more information on working with classes and objects. We’ll principally be calling the methods in the class. The DB class has a connect method, which we’ll use instead of our old connect function, mysql_connect. The double colons (::) indicate that we’re calling that function from the class in line 4:
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
When you call the connect function, it creates a new database connection that is stored in the variable $connection. The connect function attempts to connect to the database based on the connect string you passed to it.
Connect string
The connect string uses this new format to represent the login information that you already supplied in separate fields:
dbtype://username:password@host/database
This format may look familiar to you, as it’s very similar to the connect string for a Windows file share. The first part of the string is what really sets the PEAR functions apart fromthe plain PHP. The phptype field specifies the type of database to connect. Supported databases include ibase, msql, mssql, mysql, oci8, odbc, pgsql, and sybase. All that’s required for your PHP page to work with a different type of database is changing the phptype!
The username, password, host, and database should be familiar from the basic PHP connect. Only the type of connection is required. However, you’ll usually want to specify all fields.
After the values from db_login.php are included, the connect string looks like the following:
"mysql://test:test@localhost/test"
If the connect method on line 6 was successful, a DB object is created. It contains the methods to access the database as well as all of the information about the state of that database connection.
Querying
One of the methods it contains is called query. The query method works just like PHP’s query function in that it takes a SQL statement. The difference is that the arrow syntax (->) is used to call it fromthe object. It also returns the results as another object instead of a result set:
$query = "SELECT * FROM books"
$result = $connection->query($query);
Based on the SQL query, this code calls the query function fromthe connection
object and returns a result object named $result.
Fetching
Line 22 uses the result object to call the fetchRow method. It returns the rows one at a time, similar to mysql_fetch_row:
while ($result_row = $result->fetchRow( )) {
echo 'Title: '.$result_row[1] . '<br />';
echo 'Author: '.$result_row[4] . '<br /> ';
echo 'Pages: '.$result_row[2] . '<br /><br />';
}
Use another while loop to go through each row from fetchRow until it returns FALSE. The code in the loop hasn’t changed from the non-PEAR example.
Closing
In line 30, you’re finished with the database connection, so close it using the object method disconnect:
$connection->disconnect( );
PEAR error reporting
The function DB::isError will check to see whether the result that’s been returned to you is an error. If it is an error, you can use DB::errorMessage to return a text description of the error that was generated. You need to pass DB::errorMessage, the return value from your function, as an argument.
Here you rewrite the PEAR code to use error checking:
<?php
if ( DB::isError( $demoResult = $db->query( $sql)))
{
echo DB::errorMessage($demoResult);
} else
{
while ($demoRow = $demoResult->fetchRow( ))
{
echo $demoRow[2] . '<br />';
}
}
?>
There’s also a new version of the PEAR database interface called PEAR::MDB2. To rewrite our example using the MDB2 version, see Example 9-8.
<?php
include('db_login.php');
require_once('MDB2.php');
//Translate our database login information into an array.
$dsn = array(
'phptype' => 'mysql',
'username' => $username,
'password' => $password,
'hostspec' => $host,
'database' => $database
);
//Create the connection as an MDB2 instance.
$mdb2 = MDB2::factory($dsn);
if (PEAR::isError($mdb2)) {
die($mdb2->getMessage( ));
}
//Set the fetchmode to field associative.
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$query = "SELECT * FROM books NATURAL JOIN authors";
$result =$mdb2->query($query);
if (PEAR::isError($result)){
die("Could not query the database:<br />$query ".$result->getMessage( ));
}
//Display the results.
echo('<table border="1">');
echo '<tr><th>Title</th><th>Author</th><th>Pages</th></tr>';
//Loop through the result set.
while ($row = $result->fetchRow( )) {
echo "<tr><td>";
echo htmlentities($row['title']) . '</td><td>';
echo htmlentities($row['author ']) . '</td><td>';
echo htmlentities($row['pages']) . '</td></tr>';
}
echo("</table>");
//Close the connection.
$result->free( );
?>
The same results display, but there are more functions available in this version of the PEAR database abstraction layer.
Now that you have a good handle on connecting to the database and the various functions of PEAR。
, Getting PHP to Talk to MySQl
Now that you’re comfortable using the MySQL client tools to manipulate data in the database, you can begin using PHP to display and modify data from the database. PHP has standard functions for working with the database.First, we’re going to discuss PHP’s built-in database functions. We’ll also show you how to use the The PHP Extension and Application Repository (PEAR) database
functions that provide the ability to use the same functions to access any supported database. This type of flexibility comes from a process called abstraction. In programming interfaces, abstraction simplifies a complex interaction. It works by
removing any nonessential parts of the interaction, allowing you to concentrate on the important parts. PEAR’s DB classes are one such database interface abstraction. The information you need to log into a database is reduced to the bare minimum. This standard format allows you to interact with MySQL, as well as other databases using the same functions. Similarly, other MySQL-specific functions are replaced with generic ones that know how to talk to many databases. For example, the MySQL-specific connect function is:
mysql_connect($db_host, $db_username, $db_password);
versus PEAR’s DB connect function:
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
The same basic information is present in both commands, but the PEAR function also specifies the type of databases to which to connect. You can connect to MySQL or other supported databases. We’ll discuss both connection methods in detail.
In this chapter, you’ll learn how to connect to a MySQL server fromPHP, how to use PHP to access and retrieve stored data, and how to correctly display information to the user.
The Process
The basic steps of performing a query, whether using the mysql command-line tool or PHP, are the same:
• Connect to the database.
• Select the database to use.
• Build a SELECT statement.
• Perform the query.
• Display the results.
We’ll walk through each of these steps for both plain PHP and PEAR functions.
Resources
When connecting to a MySQL database, you will use two new resources. The first is the link identifier that holds all of the information necessary to connect to the database for an active connection. The other resource is the results resource. It contains all information required to retrieve results from an active database query’s result set. You’ll be creating and assigning both resources in this chapter.
Querying the Database with PHP Functions
In this section, we introduce how to connect to a MySQL database with PHP. It’s quite simple, and we’ll begin shortly with examples, but we should talk briefly about what actually happens. When you try connecting to a MySQL database, the MySQL server authenticates you based on your username and password. PHP handles connecting
to the database for you, and it allows you to start performing queries and gathering data immediately.
As in Chapter 8, we’ll need the same pieces of information to connect to the database:
• The IP address of the database server
• The name of the database
• The username
• The password
Before moving on, make sure you can log into your database using the MySQL command-line client.
Figure 9-1 shows how the steps of the database interaction relate to the two types of resources. Building the SELECT statement happens before the third function call, but it is not shown. It’s done with plain PHP code, not a MySQL-specific PHP function.
Figure 9-1. The interaction between functions and resources when using the database
Including Database Login Details
You’re going to create a file to hold the information for logging into MySQL. Storing this information in a file you include is recommended. If you change the database password, there is only one place that you need to change it, regardless of how many
PHP files you have that access the database.
You don’t have to worry about anyone directly viewing the file and getting your database login details. The file, if requested by itself, is processed as a PHP file and returns a blank page.
Let’s call this file db_login.php and place it in the same directory as your other PHP files. The file is represented in Example 9-1.
Example 9-1. A template for setting database login settings
<?php
$db_host='hostname of database server';
$db_database='database name';
$db_username='username';
$db_password='password';
?>
In Example 9-2, we create this file to use a database on the same machine as the web server. We assign it a database name, username, and password.
<?php
$db_host='localhost';
$db_database='test';
$db_username='test';
$db_password='yourpass';
?>
Figure 9-2 illustrates how you’re going to use this file with other PHP files. You’regoing to continue using the database that you started to set up in Chapter 7.
Figure 9-2. Reusing the login details in multiple files
Example 9-3. The SQL to recreate the test objects (continued)
DROP TABLE IF EXISTS books;
CREATE TABLE books (
title_id int(11) NOT NULL auto_increment,
title varchar(150) default NULL,
pages int(11) default NULL,
PRIMARY KEY (title_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table books
--
INSERT INTO books VALUES (1,'Linux in a Nutshell',476),(2,'Classic Shell Scripting',256);
--
-- Table structure for table purchases
--
DROP TABLE IF EXISTS purchases;
CREATE TABLE purchases (
id int(11) NOT NULL auto_increment,
user varchar(10) default NULL,
title varchar(150) default NULL,
day date default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table purchases
--
LOCK TABLES purchases WRITE;
INSERT INTO purchases VALUES (1,'Mdavis','Regular Expression Pocket Reference','2005-02-15'),(2,'Mdavis','JavaScript & DHTML Cookbook','2005-02-10');
If you didn’t create the tables in Chapter 8, the code in Example 9-3 can be saved as backup.sql and run from the command prompt with the following syntax:
mysql -u username -ppassword -D database_name < backup_file_name.sql
Using the values from the examples, it becomes:
mysql -u test -pyourpass -D test < backup.sql
The database is called test, and it consists of three tables called books, authors, and purchases. Each table has a few sample rows. That’s enough to get us started querying from PHP.
Connecting to the Database
The first thing you need to do is connect to the database and check to make sure there’s a connection. Including the file that you set up to store your connection information allows you to use the variables instead of hardcoded values when you call the mysql_connect function, as shown in Example 9-4. We’re assembling one file, db_test.php, by adding these code snippets.
Example 9-4. Including the connection values and calling mysql_connect in db_test.php
// Include our login information
include('db_login.php');
// Connect
$connection = mysql_connect($db_host, $db_username, $db_password);
if (!$connection){
die ("Could not connect to the database: <br />". mysql_error( ));
}
The mysql_connect function takes the database host, username, and password as parameters. If the connection is successful, a link to a database is returned. FALSE is returned if a connection can’t be made. Check the return value from the function to make sure there’s a connection. If there’s a problem, such as an incorrect password, print out a polite warning and the reason for the error using mysql_error.
Instead of simply echoing an error message, die( ) displays the error and stops the program. Not being able to access the database makes most database-driven pages fairly useless and prevents the user from seeing numerous errors.
Notice that we didn’t specify the database name yet.
Troubleshooting connection errors
One error you may get is:
Fatal error: Call to undefined function mysql_connect( ) in C:\Program Files\Apache
Software Foundation\Apache2.2\htdocs\db_test.php on line 4
This error occurs because PHP 5.x for Windows was downloaded, and MySQL support was not included by default. To fix this error, copy the php_mysql.dll file from the ext/ directory of the PHP ZIP file to C:\php, and then C:\WINDOWS\php.ini.
Make sure there are two lines that are not commented out by a semicolon (;) at the beginning of the line like these:
extension_dir = "c:/PHP/ext/"
extension=php_mysql.dll
This will change the extension to include the directory to C:/php and include the MySQL extension, respectively. You can use the Search function of your text editor to check whether the lines are already there and just need to be uncommented, or whether they need to be added completely.
You’ll need to restart Apache, and then MySQL support will be enabled.
Selecting the Database
Now that you’re connected, the next step is to select which database to use with the mysql_select_db command. It takes two parameters: the database name and, optionally, the database connection. If you don’t specify the database connection, the default is the connection from the last mysql_connect:
// Select the database
$db_select=mysql_select_db($db_database);
if (!$db_select)
{
die ("Could not select the database: <br />". mysql_error( ));
}
Again, it’s good practice to check for an error and display it every time you access the database.
While it’s possible to call mysql_select_db multiple times within the same script, it’s not considered good practice.
Now that you’ve got a good database connection, you’re ready to execute your SQL query.
Building the SQL SELECT Query
Building a SQL query is as easy as setting a variable to the string that is your SQL query. Of course, you’ll need to use a valid SQL query, or MySQL returns with an error when you execute the query. The variable name $query is used since the name reflects its purpose, but you can choose anything you’d like for a variable name. The SQL query in this example is SELECT * FROM books.
Unlike when you used the mysql command-line client, the query does
not have a semicolon at the end.
You can build up your query in parts using the string concatenate (.) operator:
// Assign the query
$select = ' SELECT ';
$column = ' * ';
$from = ' FROM ';
$tables = ' books ';
$where = ' NATURAL JOIN authors';
$query = $select.$column.$from.$tables.$where;
This code is a more flexible version of the following:
// Assign the query
$query = "SELECT * FROM books NATURAL JOIN authors";
The query string could also use a variable in the WHERE clause to limit which rows are returned based on user information or another query.
Now that you have your query assigned to a variable, you can execute it.
Executing the Query
To have the database execute the query, use the mysql_query function. It takes two parameters—the query and, optionally, the database link—and returns the result. Save a link to the results in a variable called, you guessed it, $result! This is also a good place to check the return code from mysql_query to make sure that there were no errors in the query string or the database connection by verifying that $result is not FALSE:
// Execute the query
$result = mysql_query( $query );
if (!$result){
die ("Could not query the database: <br />". mysql_error( ));
}
When the database executes the query, all of the results forma result set. These results correspond to the rows that you saw upon doing a query using the mysql command-line client. To display them, you process each row, one at a time.
Fetching and Displaying
Use mysql_fetch_row to get the rows from the result set. Its syntax is:
array mysql_fetch_row ( resource $result);
It takes the result you stored in $result fromthe query as a parameter. It returns one row at a time from the query until there are no more rows, and then it returns FALSE. Therefore, you do a loop on the result of mysql_fetch_row and define some code to display each row:
// Fetch and display the results
while ($result_row = mysql_fetch_row(($result))){
echo 'Title: '.$result_row[1] . '<br />';
echo 'Author: '.$result_row[4] . '<br /> ';
echo 'Pages: '.$result_row[2] . '<br /><br />';
}
The columns of the result row are stored in the array and can be accessed one at a time. The variable $result_row[2] accesses the second attribute (as defined in the query’s column order or the column order of the table if SELECT * is used) in the result row.
Fetch types
This is not the only way to fetch the results. Using mysql_fetch_array, PHP can place the results into an array in one step. It takes a result as its first parameter, and the way to bind the results as an optional second parameter. If MYSQL_ASSOC is specified, the results are indexed in an array based on their column names in the query. If MYSQL_NUM is specified, then the number starting at zero accesses the results. The default value, MYSQL_BOTH, returns a result array with both types. The mysql_fetch_
assoc is an alternative to supplying the MYSQL_ASSOC argument.
If you rewrote the code shown previously to use mysql_fetch_array with an associative indexed array, it would look like this:
// Fetch and display the results
while ($result_row = mysql_fetch_array($result, MYSQL_ASSOC)){
echo 'Title: '.$result_row['title'] . '<br />';
echo 'Author: '.$result_row['author'] . '<br /> ';
echo 'Pages: '.$result_row['pages'] . '<br /><br />';
}
Closing the Connection
As a rule of thumb, you always want to close a connection to a database when you’redone using it. Closing a database with mysql_close will tell PHP and MySQL that you no longer will be using the connection, and will free any resources and memory allocated to it:
mysql_close($connection)
Using PEAR
PEAR is a framework and distribution system for reusable PHP components, creating a collection of add-on functionalities for PHP development. There are many modules available to handle everything fromsession management to shopping cart functionality. Categories of modules that are currently available are listed in Table 9-1.
Table 9-1. PEAR modules categories
Authentication HTML Processing
Benchmarking HTTP Science
Caching Images Semantic Web
Configuration Internationalization Streams
Console Logging Structures
Database Mail System
Date/Time Math Test
Encryption Networking Tools and utilities
Event Numbers Validate
File formats Payment Web services
File system PEAR XML
GTK components PHP
Our list is not complete. Visit http://pear.php.net to find out all of the modules thatare available for download.
Installing
PEAR uses a Package Manager that oversees which PEAR features you install.
Whether you need to install the Package Manager depends on which version of PHP you installed. If you’re running PHP 4.3.0 or newer, it’s already installed. If you’rerunning PHP 5.0, PEAR has been split out into a separate package. The DB package that you’re interested in is optional but installed by default with the Package Manager. So if you have the Package Manager, you’re all set.
Unix
You can install the Package Manager on a Unix systemby executing the following
from the shell (command-line) prompt:
lynx -source http://go-pear.org/ | php
This takes the output of the go-pear.org site (which is actually the source PHP code) to install PEAR and passes it along to the php command for execution.
Windows
The PHP 5 installation includes the PEAR installation script as C:\php\go-pear.bat. In case you didn’t install all the files in Chapter 2, go ahead and extract all the PHP files to C:/php from the command prompt, and execute the .bat file.
If you installed PHP fromthe MSI installer, you may need to execute
the following instead of the go-pear.bat file:
php go-pear.phar
If the PEAR directory does not exists at all you’ll need to re-run the
PHP MSI installer, select the Change option, and set Extensions and
Extras to “Will be installed on local drive” before running go-pear.phar.
Figure 9-5 shows the initial screen after executing the PEAR installer.
Figure 9-5. The go-pear.bat install script
You’ll be asked a set of questions about paths. You can accept the defaults for all of them. The base path should be C:\php.
The php.exe file must be in your path. Verify by typing php.exe froma
command prompt. If it is not found, you’ll need to add it to your PATH
variable. To access your systempath, navigate to Start ➝Control Panel ➝System ➝Environment, and add an entry to the end of the path with C:\php.
The PEAR installer creates a file called C:\php\PEAR_ENV.reg. You need to doubleclick to set up the PEAR paths in the registry. This file is contingent on which PEAR version you installed. When the dialog appears to verify your information, you will add this to the registry and click OK.
You may have to edit the php.ini file after running this .bat file to add the PEAR directory to the include path. Line 447 of php.ini now looks like this:
include_path = ".;c:\php\includes;c:\php\PEAR"
Apache must be restarted before the DB package can be used.
Hosted ISP
Most ISPs have PEAR DB installed. Ask your ISP to install it if they haven’t already. You can tell whether PEAR DB has been installed by trying the PHP code in Example 9-8 to see whether the require_once ('DB.php'); line causes an error when the script is executed.
Adding Additional Packages
Once that’s complete, you can access the PEAR Package Manager by entering pear at the command prompt. Adding new modules is as easy as executing pear packagename.
You won’t need to do anything because the DB package was installed along with the install by default.
However, if you’re running Windows XP Home, you’ll need to take these steps to install the PEAR DB:
C:\>cd c:\php
C:\>pear install DB
C:\>pear list
To find out which versions of PEAR packages are installed, execute pear list. That returns a listing such as the one shown in Figure 9-6.
Figure 9-6. A listing of installed PEAR packages and versions
Once you’ve got PEAR installed, you’re ready to try it out.
Rewriting the Books Example with PEAR
When using the PEAR DB package, you follow the same steps. However, the function syntax is slightly different. We’ll go line by line and explain the differences as they appear in Example 9-7.
Example 9-7. Displaying the books table with PEAR DB
1 <?php
2
3 include('db_login.php');
4 require_once('DB.php');
5
6 $connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
7
8 if (DB::isError($connection)){
9 die("Could not connect to the database: <br />".DB::errorMessage($connection));
10 }
11
全套毕业设计论文现成成品资料请咨询
