php毕业设计外文翻译--通过PHP访问MySQL.doc

1、Getting PHP to Talk to MySQlNow 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 database f

2、unctions. Well also show you how to use the The PHP Extension and Application Repository (PEAR) databasefunctions 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, abstract

3、ion simplifies a complex interaction. It works byremoving 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 minimum. This

4、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_us

5、ername, $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 to MySQL or o

6、ther 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 ProcessThe basic steps of performing a query, wh

7、ether 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.ResourcesWhen connecting to a MySQL database

8、, 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 querys resu

9、lt set. Youll be creating and assigning both resources in this chapter.Querying the Database with PHP FunctionsIn 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 what actually happens. Wh

10、en you try connecting to a MySQL database, the MySQL server authenticates you based on your username and password. PHP handles connectingto the database for you, and it allows you to start performing queries and gathering data immediately.As in Chapter 8, well need the same pieces of information to

11、connect to the database: The IP address of the database server The name of the database The username The passwordBefore 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 resour

12、ces. 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 databaseIncluding Database Login DetailsYoure going to create a f

13、ile 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 manyPHP files you have that access the database.You dont have to worry about a

14、nyone 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 represented in Example 9-1.Example 9

15、-1. A template for setting database login settingsIn 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 PHP files. Youregoing to continue usi

16、ng the database that you started to set up in Chapter 7.Figure 9-2. Reusing the login details in multiple filesExample 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 in

17、t(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)

18、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 Referen

19、ce,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 username -ppassword -D database_name backup_file_name.sqlUsing the value

20、s from the examples, it becomes:mysql -u test -pyourpass -D test backup.sqlThe 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 started querying from PHP.Connecting to the DatabaseThe first thing y

21、ou 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 call the mysql_connect function, as shown in Example 9-4. Were assembling

22、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 informationinclude(db_login.php);/ Connect$connection = mysql_connect($db_host, $db_username, $db_password);if (!$connection)die (Could not co

23、nnect 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 value from the function to make sure t

24、heres a connection. If theres 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-dri

25、ven pages fairly useless and prevents the user from seeing numerous errors.Notice that we didnt specify the database name yet.Troubleshooting connection errorsOne error you may get is:Fatal error: Call to undefined function mysql_connect( ) in C:Program FilesApacheSoftware FoundationApache2.2htdocsd

26、b_test.php on line 4This 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:WINDOWSphp.ini.Make sure there are two lines that are not c

27、ommented out by a semicolon (;) at the beginning of the line like these:extension_dir = c:/PHP/ext/extension=php_mysql.dllThis 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 whet

28、her the lines are already there and just need to be uncommented, or whether they need to be added completely.Youll need to restart Apache, and then MySQL support will be enabled.Selecting the DatabaseNow that youre connected, the next step is to select which database to use with the mysql_select_db

29、command. It takes two parameters: the database name and, optionally, the database connection. If you dont 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

30、 database: . mysql_error( );Again, its good practice to check for an error and display it every time you access the database.While its possible to call mysql_select_db multiple times within the same script, its not considered good practice.Now that youve got a good database connection, youre ready t

31、o execute your SQL query.Building the SQL SELECT QueryBuilding a SQL query is as easy as setting a variable to the string that is your SQL query. Of course, youll 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

32、 reflects its purpose, but you can choose anything youd 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 doesnot have a semicolon at the end.You can build up your query in parts using the string concatenate (

33、.) 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

34、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 QueryTo have the database execute the query, use the mysql_query function. It takes t

35、wo parametersthe query and, optionally, the database linkand 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 co

36、nnection by verifying that $result is not FALSE:/ Execute the query$result = mysql_query( $query );if (!$result)die (Could not query the database: . 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

37、a query using the mysql command-line client. To display them, you process each row, one at a time.Fetching and DisplayingUse 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 param

38、eter. 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 resultswhile ($result_row = mysql_fetch_row($result)echo Title: .$resu

39、lt_row1 . ;echo Author: .$result_row4 . ;echo Pages: .$result_row2 . ;The columns of the result row are stored in the array and can be accessed one at a time. The variable $result_row2 accesses the second attribute (as defined in the querys column order or the column order of the table if SELECT * i

40、s used) in the result row.Fetch typesThis 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, th

41、e 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 ar

42、gument.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 resultswhile ($result_row = mysql_fetch_array($result, MYSQL_ASSOC)echo Title: .$result_rowtitle . ;echo Author: .$result_rowauthor . ;echo Page

43、s: .$result_rowpages . ;Closing the ConnectionAs a rule of thumb, you always want to close a connection to a database when youredone 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

44、 to it:mysql_close($connection)Using PEARPEAR 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. Categori

45、es of modules that are currently available are listed in Table 9-1.Table 9-1. PEAR modules categoriesAuthentication HTML ProcessingBenchmarking HTTP ScienceCaching Images Semantic WebConfiguration Internationalization StreamsConsole Logging StructuresDatabase Mail SystemDate/Time Math TestEncryption Networking Tools and utilitiesEvent Numbers ValidateFile formats Payment Web servicesFile system PEAR XMLGTK components PHPOur list is not complete. Visit to find out all of the modules thatare available for download.InstallingPEAR uses a Package Manager that oversees which