Now that we have covered the basics of PHP, we get to the good stuff. PHP provides built-in functions to connect to and query a MySQL database. This is one of the main benefits of using PHP. Many Web programmers turn to PHP for a seamless interface between their web pages and MySQL. 12.7.1 MySQL Functions, Part 1We'll start with the basic PHP functions necessary to connect to the MySQL server, execute a simple database query, and display the result. Most of these functions have default values for the arguments. One such default argument is the empty string for a password. That's just plain silly! We will not discuss the arguments in detail ”if you are interested in all the details, check out www.php.net/manual/en/ref.mysql.php. mysql_connect() ”Connect to the MySQL ServerThis function returns a MySQL link identifier on success or an error message on failure: <? $mysql = mysql_connect("localhost", "apache", "LampIsCool") or die("could not connect to mysql"); ?> mysql_close() ”Close a MySQL Server ConnectionThis functions closes the connection to the MySQL server: <? mysql_close($mysql); ?> mysql_db_query() ”Execute a QueryThis function executes a query for the specified database. It returns a result identifier on success or returns false on error. The result identifier is used to retrieve the result from the query: $result = mysql_db_query($db, $query) mysql_num_rows() ”Return the Number of Rows SelectedThis function returns the number of rows selected by a query: <? echo mysql_num_rows($result); ?> mysql_fetch_row() ”Fetch a Row as an Enumerated ArrayThis function returns the next row as an enumerated array. Each column is stored as an array element, indexing starting with 0: <? $array = mysql_fetch_row($result); echo "First column: $array[0]"; echo "Second column: $array[1]"; ?> mysql_errno() and mysql_error() ”Return MySQL ErrorsThe mysql_errno() function returns the error number of the most recent function call or 0 if there was no error. The mysql_error() function returns the error text of the most recent function call or the empty string if there was no error: <? $result = mysql_db_query($db, $query) or die("query failed-".mysql_errno() . ": " .mysql_error()); ?> Putting It All TogetherLet's put all these functions together and create a simple example. To demonstrate connecting to a database and executing basic queries, we use the existing "books" database from Chapter 8. This example connects to the MySQL server ( mysql_connect() ), queries the database ( mysql_db_query() ), reports any errors ( mysql_errno() and mysql_error() ), shows the number of rows that are returned by the query ( mysql_num_rows() ), and returns each row ( mysql_fetch_row() ). When the query is finished, the program closes the connection ( mysql_close() ). <? // connect to the mysql server on localhost $mysql = mysql_connect("localhost", "apache", "LampIsCool") or die("could not connect to mysql"); // execute the MySQL query, grab the result in $result $result = mysql_db_query("books", "SELECT * FROM book_information") or die("query failed - " . mysql_errno() . ": " . mysql_error()); ?> <html> <head> <title>PHP and MySQL</title> </head> <body bgcolor="#ffffff"> We executed: <b>SELECT * FROM book_information</b> <hr> We found <b><? echo mysql_num_rows($result); ?></b> rows. <h3>Query result</h3> <? //loop through each row while ($array = mysql_fetch_row($result)) { // foreach column in the row foreach ($array as $f) { print "$f :: "; } print "<hr>"; } ?> </body> </html> <? // we are all done, so close the MySQL connection mysql_close($mysql); ?> To see the result of this program, go to either http://localhost/php/mysql1.php or www.opensourcewebbook.com/php/mysql1.php. The result can be seen in Figure 12.10. Figure 12.10. PHP mysql example 1
The output in mysql1.php is displayed, after a fashion, but we can do better by putting it in a table. We show only the ISBN, the author, and the title by indexing into the array. Here are the contents of /var/www/html/php/mysql2.php . <? // connect to the mysql server on localhost $mysql = mysql_connect("localhost", "apache", "LampIsCool") or die("could not connect to mysql"); // execute the MySQL query, grab the result in $result $result = mysql_db_query("books", "SELECT * FROM book_information") or die("query failed - " . mysql_errno() . ": " . mysql_error()); ?> <html> <head> <title>PHP and MySQL - Example 2</title> </head> <body bgcolor="#ffffff"> <table border="1"> <tr> <th>ISBN</th> <th>Author(s)</th> <th>Title</th> </tr> <? while ($array = mysql_fetch_row($result)) : ?> <tr> <td><? echo $array[0]; ?></td> <td><? echo $array[1]; ?></td> <td><? echo $array[2]; ?></td> </tr> <? endwhile; ?> </table> </body> </html> <? // we are all done, so close the MySQL connection mysql_close($mysql); ?> To view the result of this program, go to either http://localhost/php/mysql2.php or www.opensourcewebbook.com/php/mysql2.php. The result can be seen in Figure 12.11. Figure 12.11. PHP mysql example 2
12.7.2 MySQL Functions, Part 2mysql_select_db() ”Select a DatabaseOne might grow weary of specifying the database each time it's called; luckily the developers of PHP did also. This function selects a database that is used by all subsequent mysql_query() function calls. The function returns true if successful, false on error: mysql_select_db("books") or die("select failed - " . mysql_errno() . ": " . mysql_error()); mysql_query() ”Query a Selected DatabaseThis function queries the currently selected database and returns true if successful, false on error: mysql_query("SELECT * FROM books") or die("query failed - " . mysql_errno() . ": " . mysql_error()); mysql_fetch_array() ”Fetch a Row as an Associative ArrayThis function returns the next row as an associative array with the table field names as the array keys and the table values as the array values. It returns the fetched row or false if there are no more rows. Each column is stored as an array element, where the field name is used as the key: <? $array = mysql_fetch_array($result) echo "ISBN: " . $array["isbn"]; echo "Title: " . $array["title"]; ?> mysql_affected_rows() ”Return the Number of Affected RowsThis function returns the number of affected rows from the last INSERT , UPDATE , or DELETE MySQL query: <? $result = mysql_query("DELETE FROM books WHERE isbn = 0596001320 "); echo "This should be 1: " . mysql_affected_rows($mysql); ?> mysql_free_result() ”Free the Result MemoryThis function frees the memory used by the result. If you do not call this function, all the memory used by the result is deleted when the script finishes running. This function is needed only if you are concerned about memory as your script is running: <? mysql_free_result($result); ?> Putting It All Together, V2.0As a third example of MySQL functions, we query the book database again. This example selects only three fields: isbn , title , and price . The result of the query is obtained by calling mysql_fetch_array() , which returns an associative array of data. The elements in the array are accessed by indexing with the key, as in $array["isbn"] . The code can be found in /var/www/html/php/mysql3.php file or online at http://localhost/php/mysql3.php or www.opensourcewebbook.com/php/mysql3.php. This example is a bit long, so we talk about it in chunks . The code is first, followed by an explanation. <? // connect to the mysql server on localhost $mysql = mysql_connect("localhost", "apache", "LampIsCool") or die("could not connect to mysql"); // select the "books" database mysql_select_db("books") or die("select failed - " . mysql_errno() . ": " . mysql_error()); // execute the MySQL query, grab the result in $result $result = mysql_query("SELECT isbn,title,price FROM book_information") or die("query failed - " . mysql_errno() . ": " . mysql_error()); ?> At the top of the file, the PHP code connects to the database, selects the proper database and makes our query (starts with // execute . . . ). <html> <head> <title>PHP and MySQL - Example 3</title> </head> <body bgcolor="#ffffff"> <table border="1"> <tr> <th>ISBN</th> <th>Title</th> <th>Price</th> </tr> That is the top of the HTML (pretty boring stuff), but this is more interesting: <? // a different way to build the HTML, using print() // function calls within a while loop (echo could have // been used // $array is indexed as an associative array while ($array = mysql_fetch_array($result)) { print " <tr>"; print " <td>" . $array["isbn"] . "</td>"; print " <td>" . $array["title"] . "</td>"; print " <td>" . $array["price"] . "</td>"; print " </tr>"; } // free memory mysql_free_result($result); // we are all done, so close the MySQL connection mysql_close($mysql); ?> The preceding PHP code loops through the result of the SQL query and prints the HTML for each row of data. After each row is printed, the memory is freed and the MySQL connection is closed. And finally, here is the ending HTML: </table> </body> </html> To see the result of this code, go to either this URL, http://localhost/php/mysql3.php , or www.opensourcewebbook.com/php/mysql3.php. The result can be seen in Figure 12.12. Figure 12.12. PHP mysql example 3
12.7.3 More PHP MySQL FunctionsThere are more MySQL functions that we did not talk about (see the docs for a complete list). Here are a few to pique your curiosity ”see the documentation for the proper arguments.
|