1、英文原文名 Database Handling With PHP/MySQL中文譯名 數據庫處理PHP/MySQL 英文原文版出處：（美）Chris Shiflett， Marcus Baker PHP in Action M.2010-01-01:361-430譯文成績： 指導教師（導師組長）簽名： 譯文：與數據庫通信不同其他腳本語言的Web頁面開發，即ASP ，PHP是開源的，跨平臺的，并提供良好的連接，今天的大多數常見的數據庫，包括Oracle ， Sybase的Microsoft SQL Server ，MySQL和PostgreSQL ， ODBC 。 PHP還提供了集成各種外部庫，

2、使開發人員能夠生成PDF文檔，訪問安全的支付服務，并產生圖形輸出以解析XML 。回頭看看第一原理在我們的飛躍的同時要簡短的回顧一下，去提醒我們正在努力朝著自己的目標。我們有兩個強大的，新的工具的在我們的面前：PHP腳本語言和MySQL數據庫引擎。重要的是要了解這兩個怎么會結合在一起。一個數據庫驅動的Web站點的整個想法是讓網站的內容駐留在數據庫中，并從數據庫中動態地抽取數據來創建網頁，來讓人用一個普通的web瀏覽器查看。因此，在系統的一端你有一個訪問者到你的網站使用Web瀏覽器來加載 ，期望看到一個standardHTML網頁。在另一端你有你的網站，其中一部分在一個MySQL數據庫中的一個或以

3、上的表，只有懂得何為SQL查詢（命令）才能作出回應的內容。   PHP腳本語言是穿針引線于兩種語言之間，它處理頁面請求，并獲取從MySQL數據庫中的數據，然后動態地得出瀏覽器期望得到的格式化的HTML頁面。使用PHP，你可以寫網站（花哨的圖形和頁面布局）像常規HTML的 “模板” 。其中內容屬于這些模板，你可以使用一些PHP代碼來連接MySQL數據庫和 - 使用SQL查詢檢索并顯示在它地方的一些內容。當有人訪問我們的數據庫驅動的Web站點的頁面會發生什么這是很清楚和明顯的：使用標準的URL訪問者的Web瀏覽器請求的網頁 Web服務器軟件（ Apache的，IIS或WH at

4、ever ）認識到所需的文件是一個PHP腳本，所以使用它的PHP插件，響應頁面請求之前，服務器解釋該文件。某些PHP命令（我們還沒有學會）連接到MySQL數據庫，并要求所屬在Web頁面的內容。 MySQL數據庫通過發送所請求的內容到PHP腳本。 PHP腳本存儲內容到一個或多個PHP變量，然后使用現在，熟悉的echo函數來輸出內容作為網頁的一部分。 PHP的插件，通過分發它創造到Web服務器的HTML副本結束了。 Web服務器發送的HTML到Web瀏覽器，因為它會一個純HTML文件，不同之處在于，而不是直接從HTML文件來了，該網頁是由PHP插件提供的輸出。用PHP 連接到MySQL在

5、你可以得到的內容用來提供載入網頁的MySQL數據庫之前，你必須知道如何從一個PHP腳本中建立連接到MySQL，用于連接到MySQL 的支持是內置在PHP語言中。下面的PHP函數調用建立連接：mysql_connect(server_name, username, password); 在這里，地址是IP地址或者電腦的主機名在MySQL服務器運行（ “localhost”如果是在同一臺計算機上運行的Web服務器軟件）用戶名和密碼是MySQL的用戶名和密碼。當被使用時，PHP函數通常會返回（輸出）一個值時，他們被稱為價值。除了做一些有用的東西時，當被使用時，大多數函數會輸出一個值，這個值可能存儲在

6、一個變量中為等下使用。Mysql_connect 函數顯示以上內容，如，返回一個已經被建立的定義了內容的數字。既然我們想利用連接，我們應守住這個值，這里有一個例子說明我們怎樣連接我們的MySQL服務器。$ dbcnx = mysql_connect(“localhost”，“root” ， “mypassed”）;如上所述，這三個函數參數的值可能不同于你的MySQL服務器。重要的是值通過mysql_connect返回存儲在一個名為$dbcnx的變量中。由于MySQL服務器是一個完全獨立的一塊軟件，我們必須考慮服務器不可用或無法訪問由于網絡中斷，或因為您提供的用戶名/密碼組合是不被接受的服務器的

7、可能性。在這種情況下，mysql_connect函數沒有返回連接標識符（因為沒有建立連接）。相反，它返回false。這允許我們使用if語句來應對這樣的故障：$dbcnx = mysql_connect("localhost", "root", "mypasswd"); if (!$dbcnx) echo( "<p>Unable to connect to the database server at this time.</p>" ); exit(); 在上面的代碼片段有三個新方法。首先，我

8、們放置一個符號在mysql_connect函數的前面。很多函數，包括mysql_connect當他們失敗時自動顯示難看的錯誤消息。放置一個符號在函數名稱前面告訴函數靜靜的失敗，使我們能夠展示我們自己的，顯示友好的錯誤消息。接下來，我們把一個感嘆號加在$ dbcnx中變量前面的ifstatement的條件。感嘆號是PHP拒絕運算符，可以翻轉錯誤轉為真，或者真值設置為錯誤 。因此，如果連接失敗，并且mysql_connect返回錯誤，!$dbcnx將值為真 ，如果執行語句會導致我們的身體的語句。另外，如果做了一個連接，存儲在$ dbcnx中的連接標識符的值為真（任何非零數字被認為是“真”在PHP

9、） ，所以！ $ dbcnx中的計算結果為false，在if語句的語句將不被執行。最后的新方法是exit函數，這是我們所遇到的函數不帶參數的第一個例子。所有這個函數做的一切是使PHP停止閱讀的頁面在這一點上。這是一個失敗的數據庫連接的良好反應，因為在大多數情況下，頁面將無法顯示與連接有關的任何有用信息。下一步，一旦連接建立，則要選擇你要使用的數據庫。比方說，我們希望與一個銷售數據庫的工作。我們創建的數據庫名為“銷售” 。選擇該數據庫在PHP中只是調用一個函數。mysql_select_db("sales", $dbcnx); 請注意，我們使用數據庫連接標識符告訴要使用的數據

10、庫連接的函數$ dbcnx中的變量。這個參數實際上是可選的。當它被省略，該函數會自動連接鏈接標識符打開的最后一個連接。當函數返回真，說明成功，返回失敗，說明有錯誤。再次提醒審慎的做法是使用if語句來處理錯誤：if (! mysql_select_db( "sales") ) echo( "<p>Unable to locate the sales database at this time.</p>" ); exit(); 注意，這一次，不是分配函數的結果給一個變量然后檢查，如果變量是真的或是假的，我只是使用了函數調用自身的條件。

11、這看起來可能有些奇怪，但它是一個非常常用的快捷方式。要檢查，如果條件為真或假，PHP執行函數然后檢查它的返回值 這正是我們需要發生的情況。隨著連接建立和數據庫選擇，我們現在準備開始使用存儲在數據庫中的數據。用PHP發送SQL查詢在PHP中，提交查詢到數據庫，我們使用mysql_query函數。mysql_query （query,connection_id）;下面的查詢包含我們想要執行的SQL命令的字符串。與mysql_select_db ，連接標識符參數是可選的。函數返回什么將取決于被發送的查詢類型。對于大多數的SQL命令， mysql_query返回真或假，分別指示成功或失敗。考慮下面的例

12、子，它試圖創建客戶表：$sql = "CREATE TABLE Customers ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Surname Varchar(25), Firstname Varchar(25), Createdate Date )" if ( mysql_query($sql) ) echo("<p>Customers table successfully created!</p>"); else echo("<p>Error creatin

13、g Customers table: " mysql_error() . "</p>"); 同樣，我們使用方法去打壓請求mysql_query所產生的任何錯誤信息，而是打印出自己的友好的錯誤消息。mysql_error函數用來返回一個字符串文字，描述被送往MySQL服務器的最后一個錯誤消息。對于DELETE，INSERT和UPDATE查詢（它用來修改存儲的數據 ，MySQL也跟蹤行（條目）都受到了查詢的數量的影響。考慮下面的SQL命令來設置一個名為“布朗”到“史密斯”所有客戶的姓：$sql = "UPDATE Customers SET Su

14、rname='Smith' WHERE Surname = 'Brown'" When we execute this query, we can use the mysql_affected_rows function to view the number of rows that were affected by this update: if ( mysql_query($sql) ) echo("<p>Update affected " . mysql_affected_rows() " rows.&l

15、t;/p>" ); else echo("<p>Error performing update: " . mysql_error() "</p>" ); SELECT查詢的處理方式不同一點，因為他們可以獲取大量的數據，而PHP必須提供方法來處理這些信息。處理SELECT結果集對于大多數的SQL查詢，mysql_query函數返回true（成功）或false （失敗） 。對于SELECT查詢，這是不夠的。你會記得SELECT查詢被用來顯示數據庫中存儲的數據。除了指出查詢成功還是失敗，PHP也必須接收的查詢的結果。因此

16、，當它處理一個SELECT查詢，請求mysql_query返回一個數字，用于標識一個“結果集”，它包含了所有（條目）從查詢返回的行的列表。如果查詢失敗，仍返回以任何理由的虛假信息。 $result = mysql_query("SELECT Surname FROM Customers"); if (!$result ) echo("<p>Error performing query: " . mysql_error () "</p>"); exit(); 只要沒有錯誤在查詢時遇到，上面的代碼會放置一

17、個包含所有存儲在客戶表到變量$ result姓氏的文本結果集。由于有對姓氏的數據庫的數量沒有實際限制，該結果集可以是相當大的。我們之前提到的while循環是一個有用的控制結構來處理大量數據。下面就來處理行的結果集一次一個代碼的輪廓：while ( $row = mysql_fetch_array ($result ) ) / process the row.  $row = mysql_fetch_array($result); ） ;該mysql_fetch_array函數接受一個結果集作為一個參數（存儲在這種情況下，$ result變量）,并返回結果集作為數組的下一行。如果你不熟

18、悉數組的概念，不用擔心：我們將在稍后討論它。當結果集中沒有更多的行，而不是mysql_fetch_array返回false 。現在，上面的語句將一個值賦到$row變量，但在同一時間整個聲明本身需要對相同的值。這就是讓我們使用該語句在while循環的條件。由于while循環不斷循環，直到他們的條件計算為false ，循環將因為有在結果集中的行，以$行每執行一次循環，同時對下一行的值發生多次。所有剩下的就是要弄清楚如何每次得到的值了$ row變量的循環運行。結果集的行表示為數組，數組是一種特殊類型的變量，它包含多個值。如果你認為包含一個值的變量為一個箱子，那么數組可以認為是隔室，其中每個隔室能夠存

19、儲單個盒子值。在我們的數據庫運行的情況下，車廂被命名表中的列后，我們的結果集$row如果是在我們的結果集中的行，而$row “ JokeText ”是在JokeText該行的列。因此，我們的while循環在這里應該是這樣，如果我們想打印所有客戶的姓氏在我們的數據庫中：while ( $row = mysql_fetch_array ($result ) ) echo("<p>" . $row "Surname" "</p>"); 總之，這里是一個PHP的Web頁面的完整代碼，將連接到我們的數據庫，獲取數據庫中所

20、有客戶的姓氏，并以HTML段落顯示出來。 <html> <head> <title > Our List of Customers </title > <head> <body> <?php / Connect to the database server $dbcnx = mysql_connect( "localhost", "root", "mypasswd"); if (!$dbcnx ) echo( "<p>Unab

21、le to connect to the database server at this time.</p>" ); exit(); / Select the sales database if (! mysql_select_db("sales") ) echo( "<p>Unable to locate the sales database at this time.</p>" ); exit(); ?> <p> Here are all the customers in our dat

22、abase: </p> <blockquote> <?php / Request the surname of all the customers $result = mysql_query ( "SELECT Surname FROM Custom if (!$result) echo("<p>Error performing query: " . mysql_error() "</p>" ) exit(); / Display the surname of each customer i

23、n a paragraph while ( $row = mysql_fetch_array($result ) ) echo("<p>" . $row"Surname" "</p>"); ?> </blockquote> </body> </html> 原文: Database Handling with PHP/MySQL Communication with Databases Unlike other scripting languages for Web pa

24、ge development (i.e. ASP), PHP is open-source, cross-platform, and offers excellent connectivity to most of today's common databases including Oracle, Sybase, Microsoft SQL Server, MySQL, Postgresql, ODBC (and others). PHP also offers integration with various external libraries which enable the

25、developer to do anything from generating PDF documents, accessing secure payment services and producing graphic output, to parsing XML. A Look Back at First Principles Before we leap forward, it's worth a brief look back to remind ourselves of the goal we're working toward. We have two power

26、ful, new tool s at our disposal: the PHP scripting language, and the MySQL database engine. It's important to understand how these two will fit together. The whole idea of a database-driven Web site is to allow the content of the site to reside in a database, and for that content to be dynamical

27、ly pulled from the database to create Web pages for people to view with a regular Web browser. So on one end of the system you have a visitor to your site who uses a Web browser to load , and expects to view a standardHTML Web page. On the other end you have the content of your site, part of which s

28、its in one ormore tables in a MySQL database that only understands how to respond to SQL queries (commands). Tthe PHP scripting language is the go-between that speaks both languages. It processes the page request and fetches the data from the MySQL database, then spits it out dynamically as the nice

29、ly-formatted HTML page that the browser expects. With PHP, you can write the presen tation aspects of your site (the fancy graphics and page layouts) as "templates" in regular HTML. Where the content belongs in those templates, you use some PHP code to connect to the MySQL database and - u

30、sing SQL queries retrieve and display some content in its place. Just so it's clear and fresh in your mind, this is what will happen when someone visits a page on our database-driven Web site: · The visitor's Web browser requests the Web page using a standard URL · The Web server s

31、oftware (Apache, IIS, or wh atever) recognizes that the requested file is a PHP script, and so the server interprets the file using its PHP plug -in, before responding to the page request. · Certain PHP commands (which we have yet to learn) connect to the MySQL database and request the content

32、that belongs in the Web page. · The MySQL database responds by sending the requested content to the PHP script. · The PHP script stores the content into one or more PHP variables, and then uses the now-familiar echo function to output the content as part of the Web page. · The PHP plu

33、g-in finishes up by handing a copy of the HTML it has created to the Web server. · The Web server sends the HTML to the Web browser as it would a plain HTML file, except that instead of coming directly from an HTML file, the page is the output provided by the PHP plug-in. Connecting to MySQLwit

34、h PHP Before you can get content out of your MySQL database for inclusion in a Web page, you must first know how to establish a connection to MySQL from inside a PHP script. PHP support for connecting to MySQL is built right into the language. The following PHP function call establishes the connecti

35、on: mysql_connect(server_name, username, password); Here, address is the IP address or hostname of the computer on which the MySQL server software is running ("localhost" if it's running on the same computer as the Web server software), and username and password are the MySQL user name

36、 and password. Functions in PHP usually return (output) a value when they are called. In addition to doing something useful when they are called, most functions output a value, and that value may be stored in a variable for later use. The mysql_connect function shown above, for example, returns a nu

37、mber that identifies the connection that has been established. Since we intend to make use of the connection, we should hold onto this value. Here's an example of how we might connect to our MySQL server. $dbcnx = mysql_connect("localhost", "root", "mypasswd"); As d

38、escribed above, the values of the three function pa rameters may differ for your MySQL server. What's important to see here is that the value returned by mysql_connect (which we'll call a connection identifier) is stored in a variable named $dbcnx. Since the MySQL server is a completely sepa

39、rate piece of s oftware, we must consider the possibility that the server is unavailable, or inaccessible due to a network outage, or because the username/password combination you provided is not accepted by the server. In such cases, the mysql_connect function doesn't return a connection identi

40、fier (since no connection is established). Instead, it returns false. This allows us to react to such failures using an if statement: $dbcnx = mysql_connect("localhost", "root", "mypasswd"); if (!$dbcnx) echo( "<p>Unable to connect to the database server at t

41、his time.</p>" ); exit(); There are three new tricks in the above code fragment. First, we have placed an symbol in front of the mysql_connect function. Many functions, including mysql_connect, automatically display ugly error messages when they fail. Placing an symbol in front of the fun

42、ction name tells the function to fail silently, allowing us to display our own, friendlier error message. Next, we put an exclamation point in front of the $dbcnx variable in the condition of the ifstatement. The exclamation point is the PHP negation operator, which basically flips a false value to

43、true, or a true value to false. Thus, if the connection fails and mysql_connect returns false, !$dbcnx will evaluate to true, and cause the statements in the body of our if statement to be executed. Alternatively, if a connection was made, the connection identifier stored in $dbcnx will evaluate to

44、true (any number other than zero is considered "true" in PHP), so !$dbcnx will evaluate to false, and the statements in the if statement will not be executed. The last new trick is the exit function, which is the first example that we've encountered of a function that takes no paramete

45、rs. All this function does is cause PHP to stop reading the page at this point. This is a good response to a failed database connection, because in most cases the page will be unable to display any useful information without that connection. The next step, once a connection is established, is to sel

46、ect the database you want to work with. Let's say we want to work with a sales database. The database we created is called “sales”. Selecting that database in PHP is just a matter of another function call: mysql_select_db("sales", $dbcnx); Notice we use the $dbcnx variable that contain

47、s the database connection identifier to tell the function which database connection to use. This parameter is actually optional. When it's omitted, the function will automatically use the link identifier for the last connection opened. This function returns true when it's successful and fals

48、e if an error occurs. Once again, it's prudent to use an if statement to handle errors: if (! mysql_select_db( "sales") ) echo( "<p>Unable to locate the sales database at this time.</p>" ); exit(); Notice that this time, instead of assigning the result of the func

49、tion to a variable and then checking if the variable is true or false, I have simply used the function call itself as the condition. This may look a little strange, but it's a very commonly used shortcut. To check if the condition is true or false, PHP executes the function and then checks its r

50、eturn value - exactly what we need to happen. With a connection established and a database selected, we are now ready to begin using the data stored in the database. Sending SQL Queries with PHP In PHP, to submit a query to the d atabase, we use the mysql_query function. mysql_query(query, connectio

51、n_id ); Here query is a string that contains the SQL command we want to execute. As with mysql_select_db, the connection identifier parameter is optional. What this function returns will depend on the type of query being sent. For most SQL commands, mysql_query returns either true or false to indica

52、te success or failure respectively. Consider the following example, which attempt to create the customers table: $sql = "CREATE TABLE Customers ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Surname Varchar(25), Firstname Varchar(25), Createdate Date )" if ( mysql_query($sql) ) echo("&

53、lt;p>Customers table successfully created!</p>"); else echo("<p>Error creating Customers table: " mysql_error() . "</p>"); Again, we use the trick to suppress any error messages produced by mysql_query, and instead print out a friendlier error message of

54、 our own. The mysql_error function used here returns a string of text that describes the last error message that was sent by the MySQL server. For DELETE, INSERT, and UPDATE queries (which serve to modify stored data), MySQL also keeps track of the number of table rows (entries) that were affected b

55、y the query. Consider the SQL command below to set the surname of all customers named “Brown” to “Smith”: $sql = "UPDATE Customers SET Surname='Smith' WHERE Surname = 'Brown'" When we execute this query, we can use the mysql_affected_rows function to view the number of rows

56、 that were affected by this update: if ( mysql_query($sql) ) echo("<p>Update affected " . mysql_affected_rows() " rows.</p>" ); else echo("<p>Error performing update: " . mysql_error() "</p>" ); SELECT queries are treated a little diffe

57、rently, since they can retrieve a lot of data, and PHP must provide ways to handle that information.Handling SELECT Result Sets For most SQL queries, the mysql_query function returns either true (success) or false (failure). For SELECT queries this just isn't enough. You'll recall that SELEC

58、T queries are used to view stored data in the database. In addition to indicating whether the query succeeded or failed, PHP must also receive the results of the query. As a result, when it processes a SELECT query, mysql_query returns a number that identifies a "result set", which contain

59、s a list of all the rows (entries) returned from the query. False is still returned if the query fails for any reason. $result = mysql_query("SELECT Surname FROM Customers"); if (!$result ) echo("<p>Error performing query: " . mysql_error () "</p>"); exit(); Provided no error was encountered in processing the query, the above code will place a result set that contains the text of all the surnames sto