Using MySQL and PHP - 3

Getting and displaying results.

After connecting to MySQL, selecting the database and constructing your first SQL query, you need to obtain, process and display the results. Use mysql_query to send the contents of the $sql statement over the existing $connection and store the results as an array. (Each record returned is one row in the array and each field returned is one column in the array.)

$result = @mysql_query($sql, $connection) 
or die ("Could not execute query");

In many cases, your query will result in more than one record being returned and often more than one field. PHP deals with this using a while loop and the mysql_fetch_array function:

while ($row = mysql_fetch_array( $result)) {
 // process each row in turn here 
 }

The $row variable contains a hash (a paired list of data names - called keys - and data values) where each value is identified by the field name. e.g. to set the value $id equal to the value of the id field in the current record, insert

$id = $row['id'];

into the {} brackets in the while loop above. To store the output before the loop moves on to the next record, store the value of $id in a string that can be output as HTML and use the .= operator (fullstop+equals) to add to existing contents of the string instead of clearing the string:

$htmloutput .= "<p>Id is set to $id</p>\n";

You can now use echo to display the results of the loop. The completed loop looks like:

while ($row = mysql_fetch_array( $result) {
 // process each row in turn here 
 $id = $row['id'];
$htmloutput .= "<p>Id is set to $id</p>\n";  }
echo "<html> <body>\n$htmloutput</body> </html>";

This should output HTML code to the browser along the lines of:

<html> <body> 
<p>Id is set to owner</p> 
<p>Id is set to guest</p> 
<p>Id is set to anonymous</p> 
</body> </html>

To extend the HTML and make a more usable page out of the output, create a new variable (e.g. $htmlblock), to contain large chunks of HTML code like meta tags, titles, navigation bar(s), images etc. You can either create two blocks - one above $htmloutput and one below - or create one block and refer to $htmloutput within the $htmlblock variable to push the output into the final page where you want it to appear. (Just like the echo statement above, you simply refer to $htmloutput within the $htmlblock string.) Do make sure you set the value for $htmloutput before you refer to it in $htmlblock.