Using MySQL and PHP

Developer Newsletter

Tutorials
AJAX
ASP
CGI & Perl
CSS
Flash
HTML
Illustrator
Java
JavaScript
Linux
MySQL
PHP
Photoshop
Python
Wireless
XML
Miscellaneous

Scripts Directory
AJAX Scripts
ASP Scripts
ASP.NET Scripts
CGI & Perl Scripts
Flash Scripts
Java Scripts
JavaScript Scripts
PHP Scripts
Python Scripts
Remotely Hosted Scripts
Tools & Utilities Scripts
XML Scripts

Web Hosting Directory
ASP.NET
Budget
Dedicated Servers
Ecommerce
Linux
Resellers
Shared
Small Business
Windows

Developer Manuals
Learn HTML
Learn PHP
Learn CSS
Learn AJAX
Learn JavaScript
Learn Pear
Free White Papers

Developer Resources
Developer Tools
Developer Content
Survey Software
Dedicated Servers

By Neil Williams
2007-11-10

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.

Tutorial Pages:
» Creating dynamic pages from online databases
» Connecting to the database
» Getting and displaying results
» Adding and updating records

Copyright © Neil Williams

| Write For Us

Related Tutorials:
» Web Database Access from Desktop Applications
» CubeCart 3.0 Installation and Configuration
» PHP Site Search Made Easy
» Installing and Configuring Drupal 6.1
» Desktop Application Development with PHP-GTK
» Installing PHP on Windows