• Choose a category
• All
• Classic-Fiction

Processing the results

The results of an SQL query are usually data tables, which are a complex data structure. The next step when you work with a database is to get all the appropriate information from the $request object and display it in an XHTML output for the user to understand.

This process is a little involved because SQL results are normally composed of two-dimensional data. A query result typically consists of multiple records (information about a specific entity — sometimes also called a row). Each record consists of a number of fields (specific data about the current record).

I’m tossing a bunch of database terms at you here. Databases deserve (and have) a minibook of their own. If nothing in this chapter makes sense to you, build your own copy of the contact database following the instructions in Book VI and then come back here to have your program present that data through your Web site.

The $request variable has a lot of data packed into it. You get that data out by using a pair of nested loops:

1. The outer loop extracts a record at a time.

The first job is to get each record out of the request, one at a time.

2. Use another loop to extract each field from the record.

After you have a record, you need to extract each field from the record.

Here’s all the code for this segment; I explain it in detail in the following sections:

while($row = mysql_fetch_assoc($result)){

foreach ($row as $name => $value){

print “$name: $value
\n”;

} // end foreach

print “
\n”;

} // end while

Extracting the rows

The first task is to break the $result object into a series of variables that each represent one record (or row). Here’s the line that does the job:

while($row = mysql_fetch_assoc($result)){

To break a result into its constituent rows, follow these steps:

1. Begin a while loop.

This code will continue as long as more rows are available in the $result object:

while($row = mysql_fetch_assoc($result)){

2. Extract the next row as an associative array.

Every time through the loop, you’ll extract the next row from the result. There are several functions available for this task, but I use mysql_fetch_assoc() because I think it’s easiest to understand (see the sidebar “MySQL fetch options” for some other options and when you might choose them):

while($row = mysql_fetch_assoc($result)){

3. Pass the resulting object to a variable called $row.

The output of mysql_fetch_assoc is an array (specifically, an associative array). Copy that value to a variable called $row:

while($row = mysql_fetch_assoc($result)){

4. Continue as long as there are more rows to retrieve.

mysql_fetch_assoc() has an important side effect. In addition to extracting an associative array from $result, the function returns the value false if no more records are left. Because I’m using this statement inside a condition, the loop will continue as long as another row is available. When no rows are left, the assignment will evaluate to false, and the loop will exit.

while($row = mysql_fetch_assoc($result)){

MySQL fetch options

You can extract data from a MySQL result in four different ways:

mysql_fetch_row() creates an ordinary (numeric index) array from the current row.

mysql_fetch_assoc() creates an associative array from the current row, with the field name as the key and field value as the value in each key/value pair.

mysql_fetch_array() can be used to get numeric or associative arrays, based on a parameter.

mysql_fetch_object() returns a PHP object corresponding to the current row. Each field in the row is a property of the object.

In general, the mysql_fetch_assoc() provides the best combination of ease-of-use and information. Use mysql_fetch_array() when you don’t need the field names: for example, you’re using an XHTML table for output, and you’re getting the field names from the mysql_fetch_field() function. The mysql_fetch_object() technique is useful if you’re going to build a complete object based on a query