Retrieving Query Results


In the preceding section of this chapter I discussed and demonstrated how to execute simple queries on a MySQL database. A simple query, as I'm calling it, could be defined as one that begins with INSERT, UPDATE, DELETE, or ALTER. What all four of these have in common is that they return no data, just an indication of their success. Conversely, a SELECT query generates information (i.e., it will return rows of records) that has to be handled by other PHP functions.

The primary tool for handling SELECT query results is mysql_fetch_array(), which takes the query result variable (that I've been calling $result) and returns one row of data at a time, in an array format. You'll want to use this function within a loop that will continue to access every returned row as long as there are more to be read. The basic construction for reading every record from a query is

 while ($row = mysql_fetch_array   ($result)) {     // Do something with $row. } 

The mysql_fetch_array() function takes an optional parameter specifying what type of array is returned: associative, indexed, or both. An associative array allows you to refer to column values by name, whereas an indexed array requires you to use only numbers (starting at 0 for the first column returned). Each parameter is defined by a constant listed in Table 7.1. The MYSQL_NUM setting is marginally faster (and uses less memory) than the other options. Conversely, MYSQL_ASSOC is more specific ($row['column'] rather than $row[3]) and will continue to work even if the table structure or query changes.

Table 7.1. Adding one of these constants as an optional parameter to the mysql_fetch_array() function dictates how you can access the values returned. The default setting of the function is MYSQL_BOTH.

mysql_fetch_array() Constants

CONSTANT

EXAMPLE

MYSQL_ASSOC

$row['column']

MYSQL_NUM

$row[0]

MYSQL_BOTH

$row[0] or $row['column']


An optional step you can take when using mysql_fetch_array() would be to free up the query result resources once you are done using them:

 mysql_free_result ($result); 

This line removes the overhead (memory) taken by $result. It's an optional step, since PHP will automatically free up the resources at the end of a script, butlike using mysql_close()it does make for good programming form.

To demonstrate how to handle results returned by a query, I will create a script for viewing all of the currently registered users.

To retrieve query results

1.

Create a new PHP document in your text editor (Script 7.4).

 <?php # Script 7.4 - view_users.php $page_title = 'View the Current   Users; include ('./includes/header.html'); echo '<h1 >Registered   Users</h1>'; 

Script 7.4. The view_users.php script runs a static query on the database and prints all of the returned rows.


2.

Connect to and query the database.

 require_once ('../mysql_connect.   php); $query = "SELECT CONCAT(last_name,   ', ', first_name) AS name,   DATE_FORMAT(registration_date,   '%M %d, %Y) AS dr FROM users ORDER   BY registration_date ASC; $result = @mysql_query ($query); 

The query here will return two columns: the users' names (formatted as Last Name, First Name) and the date they registered (formatted as Month DD, YYYY). Because both columns are formatted using MySQL functions, aliases are given to the returned results (name and dr, accordingly). See Chapter 4 if you are confused by any of this syntax.

3.

Display the query results.

 if ($result) {  echo '<table align="center"    cellspacing="0 cellpadding="5">  <tr><td align="left"><b>Name   </b></td><td align="left>   <b>Date Registered</b></td></tr> ';  while ($row = mysql_fetch_array   ($result, MYSQL_ASSOC)) {   echo '<tr><td align="left">'  . $row['name] . '</td>   <td align="left>' .   $row['dr] . '</td></tr>   ';  }  echo '</table>'; 

To display the results, I first make a table and header row in HTML. Then I loop through the results using mysql_fetch_array() and print each subsequent row. Finally, I close the table.

Notice that within the while loop, I refer to each returned value using the proper alias: $row['name'] and $row['dr']. I could not refer to $row['first_name'] or $row['date_registered'] because no such field name was returned (you can confirm this by running the same query in the mysql client).

4.

Free up the query resources.

 mysql_free_result ($result); 

Again, this is an optional step but a good one to take.

5.

Complete the conditional.

 } else {   echo '<p >The current     users could not be retrieved.     We apologize for any     inconvenience.</p>';   echo '<p>' . mysql_error() .     '<br /><br />Query: ' . $query .     '</p>'; } 

As in the previous example, there are two error messages here. The first is a generic message, the kind you'd show in a live site. The second is much more detailed, printing both the MySQL error and the query, and is critical for debugging purposes.

6.

Close the database connection and finish the page.

 mysql_close(); include ('./includes/footer.html'); ?> 

7.

Save the file as view_users.php, upload to your Web server, and test in your browser (Figure 7.9).

Figure 7.9. All of the user records are retrieved from the database and displayed in the Web browser.


Tips

  • The function mysql_fetch_row() (which you might run across) is the equivalent of mysql_fetch_array ($result, MYSQL_NUM);

  • The function mysql_fetch_assoc() is the equivalent of mysql_fetch_array ($result, MYSQL_ASSOC);

  • As with any array, when you retrieve records from the database, you must refer to the columns exactly as they are defined in the database if using the array associatively (that is to say, the keys are case-sensitive).

  • If you are in a situation where you need to run a second query inside of your while loop, be certain to use different variable names for it ($result2 and $row2 instead of $result and $row), or else you'll encounter logical errors.

  • I frequently see beginning PHP developers muddle the process of fetching query results. Remember that you must execute the query using mysql_query(), then use mysql_fetch_array() to retrieve a single row of information. If you have multiple rows to retrieve, use a while loop (not for or foreach).




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net