dcsimg

Handling SELECT Result Sets - Page 4

January 24, 2002

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 SELECT 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 contains 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 JokeText FROM Jokes");
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 jokes stored in the Jokes table into the variable $result. As there's no practical limit on the number of jokes in the database, that result set can be pretty big.

We mentioned before that the while loop is a useful control structure for dealing with large amounts of data. Here's an outline of the code to process the rows in a result set one at a time:

while ( $row = mysql_fetch_array($result) ) {
  // process the row...
}

The condition for the while loop probably doesn't much resemble the conditions you're used to, so let me explain how it works. Consider the condition as a statement all by itself:

$row = mysql_fetch_array($result);

The mysql_fetch_array function accepts a result set as a parameter (stored in the $result variable in this case), and returns the next row in the result set as an array. If you're not familiar with the concept of arrays, don't worry: we'll discuss it in a moment. When there are no more rows in the result set, mysql_fetch_array instead returns false.

Now, the above statement assigns a value to the $row variable, but at the same time the whole statement itself takes on that same value. This is what lets us use the statement as a condition in our while loop. Since while loops keep looping until their condition evaluates to false, the loop will occur as many times as there are rows in the result set, with $row taking on the value of the next row each time the loop executes. All that's left is to figure out how to get the values out of the $row variable each time the loop runs.

Rows of a result set are represented as arrays. An array is a special kind of variable that contains multiple values. If you think of a variable as a box that contains a value, then an array can be thought of as a box with compartments, where each compartment is able to store an individual value. In the case of our database row, the compartments are named after the table columns in our result set. If $row is a row in our result set, then $row["JokeText"] is the value in the JokeText column of that row. So here's what our while loop should look like if we want to print the text of all the jokes in our database:

while ( $row = mysql_fetch_array($result) ) {
  echo("<p>" . $row["JokeText"] . "</p>");
}

To summarize, here's the complete code of a PHP Web page that will connect to our database, fetch the text of all the jokes in the database, and display them in HTML paragraphs. The code of this example is available as jokelist.php in the code archive.

<html>
<head>
<title> Our List of Jokes </title>
<head>
<body>
<?php
  // Connect to the database server
  $dbcnx = @mysql_connect("localhost", "root", "mypasswd");
  if (!$dbcnx) {
    echo( "<p>Unable to connect to the " .
"database server at this time.</p>" );
    exit();
  }
  // Select the jokes database
  if (! @mysql_select_db("jokes") ) {
    echo( "<p>Unable to locate the joke " .
"database at this time.</p>" );
    exit();
  }
?>
<p> Here are all the jokes in our database: </p>
<blockquote>
<?php

   // Request the text of all the jokes
  $result = @mysql_query("SELECT JokeText FROM Jokes");
  if (!$result) {
    echo("<p>Error performing query: " . mysql_error() . "</p>");
    exit();
  }
  // Display the text of each joke in a paragraph
  while ( $row = mysql_fetch_array($result) ) {
    echo("<p>" . $row["JokeText"] . "</p>");
  }
?>
</blockquote>
</body>
</html>







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers