1、科技外文文献 Getting PHP to Talk to MySQL Now that youre 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, were going to discuss PHPs built-in da
2、tabase functions. Well 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,
3、 abstraction simplifies a complex interaction. It works by removing any nonessential parts of the interaction, allowing you to concentrate on the important parts. PEARs DB classes are one such database interface abstraction. The information you need to log into a database is reduced to the bare mini
4、mum. 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_h
5、ost, $db_username, $db_password)； versus PEARs 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
6、 to MySQL or other supported databases. Well discuss both connection methods in detail. In this chapter, youll 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 perfo
7、rming 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. Well walk through each of these steps for both plain PHP and PEAR functions. Resources When connecting
8、 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
9、database querys result set. Youll 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. Its quite simple, and well begin shortly with examples, but we should talk briefly about wha
10、t 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, well need the same p
11、ieces 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
12、to the two types of resources. Building the SELECT statement happens before the third function call, but it is not shown. Its 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 Deta
13、ils Youre 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.
14、 You dont 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. Lets call this file db_login.php and place it in the same directory as your other PHP files. The file is repr
15、esented in Example 9-1. Example 9-1. A template for setting database login settings 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. Figure 9-2 illustrates how youre going to use this file with other PH
16、P files. Youregoing 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,
17、 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 I
18、F 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
19、INTO purchases VALUES (1,Mdavis,Regular Expression Pocket Reference,2005-02-15),(2,Mdavis,JavaScript & DHTML Cookbook,2005-02-10)； If you didnt 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 usern
20、ame -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. Thats enough to get us
21、 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 theres 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 ca
22、ll the mysql_connect function, as shown in Example 9-4. Were 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_con
23、nect($db_host, $db_username, $db_password)； if (!$connection) die (Could not connect to the database: . 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 cant be made. Check the return
