12.7 PHP and MySQL

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 1

We'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 Server

This 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 Connection

This functions closes the connection to the MySQL server:

 <?      mysql_close($mysql);  ?>

`mysql_db_query()` ”Execute a Query

This 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 Selected

This function returns the number of rows selected by a query:

 <? echo mysql_num_rows($result); ?>

`mysql_fetch_row()` ”Fetch a Row as an Enumerated Array

This 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 Errors

The 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 Together

Let'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

graphics/12fig10.jpg

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

graphics/12fig11.jpg

12.7.2 MySQL Functions, Part 2

`mysql_select_db()` ”Select a Database

One 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 Database

This 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 Array

This 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 Rows

This 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 Memory

This 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.0

As 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

graphics/12fig12.jpg

12.7.3 More PHP MySQL Functions

There 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.

mysql_change_user() ” Change the logged-in user .
mysql_create_db() ” Create a new database.
mysql_data_seek() ” Move the result pointer.
mysql_drop_db() ” Drop a database.
mysql_fetch_field() ” Get a field from a query result.
mysql_field_name() ” Get the name of a field.
mysql_field_table() ” Get the name of the table the field is in.
mysql_field_type() ” Get a field's type.
mysql_list_dbs() ” List the databases available on the server.
mysql_list_fields() ” List the fields in a result.
mysql_list_tables() ” List the tables in a database.

12.7.1 MySQL Functions, Part 1

mysql_connect() ”Connect to the MySQL Server

mysql_close() ”Close a MySQL Server Connection

mysql_db_query() ”Execute a Query

mysql_num_rows() ”Return the Number of Rows Selected

mysql_fetch_row() ”Fetch a Row as an Enumerated Array

mysql_errno() and mysql_error() ”Return MySQL Errors

Putting It All Together

Figure 12.10. PHP mysql example 1

Figure 12.11. PHP mysql example 2