php畢業(yè)設(shè)計(jì)外文翻譯--通過php訪問mysql

1、<p><b>　　原文： </b></p><p>　　Getting PHP to Talk to MySQl</p><p>　　Now that you’re comfortable using the MySQL client tools to manipulate data in the database, you can begin

2、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

3、 Extension and Application Repository (PEAR) database</p><p>　　functions that provide the ability to use the same functions to access any supported database. This type of flexibility comes from a process cal

4、led abstraction. In programming interfaces, abstraction simplifies a complex interaction. It works by</p><p>　　removing any nonessential parts of the interaction, allowing you to concentrate on the important

5、 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 ot

6、her 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-specifi</p><p>　　mysql_connect

7、($db_host, $db_username, $db_password);</p><p>　　versus PEAR’s DB connect function:</p><p>　　$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");</

8、p><p>　　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 discu

9、ss both connection methods in detail.</p><p>　　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 informat

10、ion to the user.</p><p>　　1 The Process</p><p>　　The basic steps of performing a query, whether using the mysql command-line tool or PHP, are the same:</p><p>　　? Connect to the da

11、tabase.</p><p>　　? Select the database to use.</p><p>　　? Build a SELECT statement.</p><p>　　? Perform the query.</p><p>　　? Display the results.</p><p>　　

12、We’ll walk through each of these steps for both plain PHP and PEAR functions.</p><p>　　2 Resources</p><p>　　When connecting to a MySQL database, you will use two new resources. The first is the

13、 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

14、an active database query’s result set. You’ll be creating and assigning both resources in this chapter.</p><p>　　3 Querying the Database with PHP Functions</p><p>　　In this section, we introduc

15、e 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 serv

16、er 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.</p><p>　　As in Chapter

17、8, we’ll need the same pieces of information to connect to the database:</p><p>　　? The IP address of the database server</p><p>　　? The name of the database</p><p>　　? The username

18、</p><p>　　? The password</p><p>　　Before moving on, make sure you can log into your database using the MySQL command-line client.</p><p>　　4 Including Database Login Details</p&

19、gt;<p>　　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 th

20、at 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

21、by itself, is processed as a PHP file and returns a blank page. </p><p>　　5 Troubleshooting connection errors</p><p>　　One error you may get is:</p><p>　　Fatal error: Call to

22、undefined function </p><p>　　mysql_connect( ) in C:\Program Files\Apache</p><p>　　Software Foundation\Apache2.2\htdocs\db_test.php on line 4</p><p>　　This error occurs because PHP 5

23、.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.</p>

24、<p>　　Make sure there are two lines that are not commented out by a semicolon (;) at the beginning of the line like these:</p><p>　　extension_dir = "c:/PHP/ext/"</p><p>　　extensio

25、n=php_mysql.dll</p><p>　　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

26、the lines are already there and just need to be uncommented, or whether they need to be added completely. </p><p>　　You’ll need to restart Apache, and then MySQL support will be enabled.</p><

27、p>　　6 Selecting the Database</p><p>　　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, optiona

28、lly, the database connection. If you don’t specify the database connection, the default is the connection from the last mysql_connect:</p><p>　　// Select the database</p><p>　　$db_select=mysql_s

29、elect_db($db_database);</p><p>　　if (!$db_select)</p><p><b>　　{</b></p><p>　　die ("Could not select the database: <br />". mysql_error( ));</p>&l

30、t;p><b>　　}</b></p><p>　　Again, it’s good practice to check for an error and display it every time you access the database.</p><p>　　Now that you’ve got a good database connection,

31、 you’re ready to execute your SQL query.</p><p>　　7 Building the SQL SELECT Query</p><p>　　Building a SQL query is as easy as setting a variable to the string that is your SQL query. Of course,

32、 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 variabl

33、e name. The SQL query in this example is SELECT * FROM books. </p><p>　　8 Executing the Query</p><p>　　To have the database execute the query, use the mysql_query function. It takes two pa

34、rameters—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

35、to make sure that there were no errors in the query string or the database connection by verifying that $result is not FALSE:When the database executes the query, all of the results forma result </p><p>　　9

36、 Fetching and Displaying</p><p>　　Use mysql_fetch_row to get the rows from the result set. Its syntax is:</p><p>　　array mysql_fetch_row ( resource $result);</p><p>　　It takes the r

37、esult 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

38、 define some code to display each row:</p><p>　　The columns of the result row are stored in the array and can be accessed one at a time. The variable $result_row accesses the second attribute (as defined in

39、the query’s column order or the column order of the table if SELECT * is used) in the result row.</p><p>　　10 Fetch types</p><p>　　This is not the only way to fetch the results. Using mysql_fet

40、ch_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 index

41、ed 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 mysq&l

42、t;/p><p>　　11 Closing the Connection</p><p>　　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 an

43、d MySQL that you no longer will be using the connection, and will free any resources and memory allocated to it:</p><p>　　mysql_close($connection) </p><p>　　12 Installing</p><p&

44、gt;　　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 alr

45、eady 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 Packag

46、e Manager, you’re all set.</p><p><b>　　譯文：</b></p><p>　　通過PHP訪問MySQL</p><p>　　現(xiàn)在你已經(jīng)可以熟練地使用MySQL客戶端軟件來操作數(shù)據(jù)庫(kù)里的數(shù)據(jù)，我們也可以開始學(xué)習(xí)如何使用PHP來顯示和修改數(shù)據(jù)庫(kù)里的數(shù)據(jù)了。PHP有標(biāo)準(zhǔn)的函數(shù)用來操作數(shù)據(jù)庫(kù)。</p>

47、<p>　　我們首先學(xué)習(xí)PHP內(nèi)建的數(shù)據(jù)庫(kù)函數(shù)，然后會(huì)學(xué)習(xí)PHP擴(kuò)展和應(yīng)用程序庫(kù)（PEAR，PHP Extension and Application Repository ）中的數(shù)據(jù)庫(kù)函數(shù)，我們可以使用這些函數(shù)操作所有支持的數(shù)據(jù)庫(kù)。這種靈活性源自于抽象。對(duì)于編程接口而言，抽象簡(jiǎn)化了復(fù)雜的交互過程。它將交互過程中無關(guān)緊要的部分屏蔽起來，讓你關(guān)注于重要的部分。PEAR的DB類就是這樣一種數(shù)據(jù)庫(kù)接口的抽象。你登錄一個(gè)數(shù)據(jù)庫(kù)所需

48、要提供的信息被減少到最少。這種標(biāo)準(zhǔn)的格式可以通過同一個(gè)函數(shù)來訪問MySQL以及其他的數(shù)據(jù)庫(kù)。同樣，一些MySQL特定的函數(shù)被更一般的、可以用在很多數(shù)據(jù)庫(kù)上的函數(shù)所替代。比如，MySQL特定的連接函數(shù)是：</p><p>　　mysql_connect($db_host, $db_username, $db_password);</p><p>　　而PEAR的DB提供的連接函數(shù)是：<

49、/p><p>　　$connection= DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");</p><p>　　兩個(gè)命令都提供了同樣的基本信息，但是PEAR的函數(shù)中還指定了要連接的數(shù)據(jù)庫(kù)的類型。你可以連接到MySQL或者其他支持的數(shù)據(jù)庫(kù)。我們會(huì)詳細(xì)討論這兩種連接方式

50、。 </p><p><b>　　1 步驟</b></p><p>　　無論是通過MySQL命令行工具，還是通過PHP，執(zhí)行一個(gè)查詢的基本步驟都是一樣的：</p><p><b>　　? 連接到數(shù)據(jù)庫(kù)</b></p><p>　　? 選擇要使用的數(shù)據(jù)庫(kù)</p><p&

51、gt;　　? 創(chuàng)建SELECT語(yǔ)句</p><p><b>　　? 執(zhí)行查詢</b></p><p><b>　　? 顯示結(jié)果</b></p><p>　　我們將逐一介紹如何用PHP和PEAR的函數(shù)完成上面的每一步。</p><p><b>　　2 資源</b></p&

52、gt;<p>　　當(dāng)連接到MySQL數(shù)據(jù)庫(kù)的時(shí)候，你會(huì)使用到兩個(gè)新的資源。第一個(gè)是連接的標(biāo)識(shí)符，它記錄了一個(gè)活動(dòng)連接用來連接到數(shù)據(jù)庫(kù)所必需的所有信息。另外一個(gè)資源是結(jié)果資源，它包含了用來從一個(gè)有效的數(shù)據(jù)庫(kù)查詢結(jié)果中取出結(jié)果所需要的所有信息。本章中我們會(huì)創(chuàng)建并使用這兩種資源。</p><p>　　3 使用PHP函數(shù)查詢數(shù)據(jù)庫(kù)</p><p>　　我們會(huì)介紹如何使用PHP連接M

53、ySQL數(shù)據(jù)庫(kù)。這非常簡(jiǎn)單，我們會(huì)用一些例子說明。但是之前我們應(yīng)該稍微了解一下幕后發(fā)生的事情。當(dāng)你試圖連接一個(gè)MySQL數(shù)據(jù)庫(kù)的時(shí)候，MySQL服務(wù)器會(huì)根據(jù)你的用戶名和密碼進(jìn)行身份認(rèn)證。PHP為你建立數(shù)據(jù)庫(kù)的連接，你可以立即開始查詢并得到結(jié)果。</p><p>　　我們需要同樣的信息來連接數(shù)據(jù)庫(kù)：</p><p>　　? 數(shù)據(jù)庫(kù)服務(wù)器的IP地址</p><p>&l

54、t;b>　　? 數(shù)據(jù)庫(kù)的名字</b></p><p><b>　　? 用戶名</b></p><p><b>　　? 密碼</b></p><p>　　在開始之前，首先使用MySQL的命令行客戶端確認(rèn)你登錄到數(shù)據(jù)庫(kù)。顯示了數(shù)據(jù)庫(kù)交互過程的各個(gè)步驟和兩種類型資源之間的關(guān)系。創(chuàng)建SELECT語(yǔ)句發(fā)生在第三個(gè)函

55、數(shù)調(diào)用之前，但是在圖中沒有顯示出來。它是通過普通的PHP代碼，而不是MySQL特定的PHP函數(shù)完成的。</p><p>　　4 包含數(shù)據(jù)庫(kù)登錄細(xì)節(jié)</p><p>　　我們先創(chuàng)建一個(gè)文件，用來保存登錄MySQL所用到的信息。我們建議你把這些信息放在單獨(dú)的文件里然后通過include來使用這個(gè)文件。這樣一來如果你修改了數(shù)據(jù)庫(kù)的密碼。無論有多少個(gè)PHP文件訪問數(shù)據(jù)庫(kù)，你只需要修改這一個(gè)文件。

56、</p><p><b>　　5 連接到數(shù)據(jù)庫(kù)</b></p><p>　　我們需要做的頭一件事情是連接數(shù)據(jù)庫(kù)，并且檢查連接是否確實(shí)建立起來。通過include包含連接信息的文件，我們可以在調(diào)用mysql_connect函數(shù)的時(shí)候使用這些變量而不是將這些值寫死在代碼中。我們使用一個(gè)叫做db_test.php的文件，往其中增加這些代碼段。</p><

57、;p><b>　　6 診斷連接錯(cuò)誤</b></p><p>　　你可能遇到的一個(gè)錯(cuò)誤是：</p><p>　　Fatal error: Call to undefined function mysql_connect( ) in C:\Program Files\ApacheSoftware Foundation\Apache2.2\htdocs\db_tes

58、t.php on line 4</p><p>　　這個(gè)錯(cuò)誤發(fā)生的原因是下載安裝的PHP5.x默認(rèn)沒有包括對(duì)MySQL的支持。解決這個(gè)問題需要將php_mysql.dll文件從PHP壓縮包例的ext/目錄復(fù)制到C:/php，并修改C:\WINDOWS\php.ini文件，確保下面兩行沒有被注釋掉（注釋的方法在行首使用分號(hào)）。</p><p>　　extension_dir = "

59、c:/PHP/ext/"</p><p>　　extension=php_mysql.dll</p><p>　　這樣PHP擴(kuò)展的目錄就被設(shè)為C:\PHP,MySQL的擴(kuò)展也會(huì)被使用。在編輯php.ini文件的時(shí)候，你可以使用編輯器的搜索功能來檢查這兩行是否已經(jīng)存在，只是需要去掉注釋，并且需要重新輸入。</p><p>　　重新啟動(dòng)Apache，這樣MyS

60、QL的支持就會(huì)被打開了。</p><p><b>　　7 選擇數(shù)據(jù)庫(kù)</b></p><p>　　建立連接之后，下一步就是使用mysql_select_db來選擇我們要用的數(shù)據(jù)庫(kù)。它的參數(shù)有兩個(gè)：數(shù)據(jù)庫(kù)名和可選的數(shù)據(jù)庫(kù)連接。如果不指定數(shù)據(jù)庫(kù)連接，默認(rèn)使用上一條mysql_connect所建立的連接。</p><p>　　// Select t

61、he database</p><p>　　$db_select=mysql_select_db($db_database);</p><p>　　if (!$db_select)</p><p><b>　　{</b></p><p>　　die ("Could not select the database

62、: <br />". mysql_error( ));</p><p><b>　　}</b></p><p>　　同樣的，每次訪問數(shù)據(jù)庫(kù)的時(shí)候最好能檢查可能的錯(cuò)誤并且進(jìn)行顯示?，F(xiàn)在我們做好了一切準(zhǔn)備工作，可以開始執(zhí)行SQL查詢了。</p><p>　　8 構(gòu)建SQL SELECT查詢</p><p&

63、gt;　　構(gòu)建SQL查詢非常容易就是將一個(gè)字符串賦值給變量。這個(gè)字符串就是我們的SQL查詢，當(dāng)然我們要給出有效的SQL查詢，否則執(zhí)行這個(gè)查詢的時(shí)候MySQL會(huì)返回錯(cuò)誤。我們使用$query作為變量名，這個(gè)名字對(duì)應(yīng)其目的，你也可以選擇任何你喜歡的變量名。這個(gè)例子中的SQL查詢是”SELECT * FROM books”。你可以使用字符串連接操作符（.）來構(gòu)建查詢：。</p><p><b>　　9 執(zhí)行

64、查詢</b></p><p>　　使用mysql_query函數(shù)來告訴數(shù)據(jù)庫(kù)執(zhí)行查詢。它有兩個(gè)參數(shù)：查詢和可選的數(shù)據(jù)庫(kù)連接，返回值是查詢結(jié)果。我們將查詢結(jié)果保存在一個(gè)變量里，也許你已經(jīng)猜到我們要用變量名就是$result。這里同樣有必要檢查mysql_query的返回值不是FALSE來確保查詢字符串和數(shù)據(jù)庫(kù)連接都沒有問題。當(dāng)數(shù)據(jù)庫(kù)查詢的時(shí)候，所有的結(jié)果構(gòu)成一個(gè)結(jié)果集。這些結(jié)果跟使用mysql命令行客戶

65、端執(zhí)行同樣查詢所得到的行一致。要顯示這些結(jié)果，你需要依次處理這些行。</p><p>　　10 取結(jié)果并顯示</p><p>　　使用mysql_fetch_row從結(jié)果集中取出一行，它的用法如下：</p><p>　　array mysql_fetch_row ( resource $result);</p><p>　　它的參數(shù)是SQL

66、查詢返回的結(jié)果，我們將結(jié)果保存在$result中。每次調(diào)用它返回一行數(shù)據(jù)，直到?jīng)]有數(shù)據(jù)為止，這時(shí)候它返回FALSE。這樣，我們可以使用一個(gè)循環(huán)，在循環(huán)內(nèi)調(diào)用mysql_fetch_row并使用一些代碼來顯示每一行。結(jié)果行的所有列都保存在一個(gè)數(shù)組里，可以方便地進(jìn)行訪問。變量$result_row訪問結(jié)果行的第二個(gè)屬性（數(shù)組的順序是查詢是定義的列的順序，如果使用SELECE * ，那么數(shù)組順序就是表的列的順序）。</p>&l

67、t;p>　　11 取結(jié)果的方式</p><p>　　去結(jié)果的方式不止一種。使用mysql_fetch_arrry可以一次性將所有結(jié)果放在一個(gè)數(shù)組里。它的參數(shù)是查詢結(jié)果和一個(gè)可選的結(jié)果綁定方式。如果綁定方式指定為MYSQL_ASSOC，數(shù)組中的結(jié)果則使用查詢中列的名字進(jìn)行訪問。如果指定了MYSQL_NUM，那么就使用從0開始的數(shù)字來訪問結(jié)果。默認(rèn)使用的方式是MYSQL_BOTH，這樣返回的數(shù)組支持兩種類型的

68、訪問。Mysql_fetch_assoc是使用MYSQL_ASSOC取結(jié)果的另外一種方式。</p><p><b>　　12 關(guān)閉連接</b></p><p>　　絕大部分情況下，我們?cè)谑褂猛暌粋€(gè)數(shù)據(jù)庫(kù)之后要關(guān)閉到它的連接。使用mysql_close來關(guān)閉一個(gè)數(shù)據(jù)庫(kù)，它會(huì)告訴PHP和MySQL這個(gè)數(shù)據(jù)庫(kù)連接已經(jīng)不再使用，所使用的所有資源和內(nèi)存都可以釋放。</p

69、><p>　　mysql_close($connection)</p><p><b>　　13 安裝</b></p><p>　　PEAR使用包管理器來管理安裝PEAR模塊。是否需要安裝包管理取決于你所使用的PHP版本。如果你使用的版本是PHP4.4.0或者更新的版本，那么就已經(jīng)安裝了包管理器。如果你使用的是PHP5.0,則PEAR是一個(gè)單獨(dú)的