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>
» 
Comment and Contribute
