Building a Database-Driven Web Site Using PHP and MySQL

Handling SELECT Result Sets

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 processing a SELECT query, mysql_query returns a number that identifies a "result set", containing a list of all the rows (entries) returned from the query. False is still returned if the query fails for whatever reason.

$result = mysql_query("SELECT JokeText FROM Jokes");
if (!$result) {
  echo("<P>Error performing query: " .
       mysql_error() . "</P>");
  exit();
}

Assuming no error was encountered in processing the query, the above code will place a result set containing the text of all the jokes stored in the Jokes table into the variable $result. Since there is 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 seeing 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 through the loop. All that's left is to figure out how to get the values out of the $row variable each time through the loop.

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 containing a value, then an array can be thought of as a box with compartments, with each compartment 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:

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

Inserting Data into the Database

SitePoint.com is a fast growing Web Developer Community. Kevin Yank is the Editor of the SitePoint TechTimes, a fresh, technically oriented newsletter for the serious Webmaster.

Related Pages:

Building a Database-Driven Web Site Using PHP and MySQL