Awave, Inc. - Using PHP and MySQL with NetObjects Fusion - Using PHP to Retrieve Records from MySQL


	Highly Recommended!


	Visit these sites where we use what we teach here:

The Edge

A commercial site where really cool tie-dye clothing is sold. Uses PHP generated links to Mal's Shopping Cart.

Scifisource

Directory of Science Fiction and Fantasy sites. Search and category results pages use PHP generated tables to display results.

Online PHP Resources

Zend
Their engine powers PHP. They own PHP.

PHPBuilder
Good general PHP tutorials and code library

HotScripts
Large library of PHP and other code snippets and programs


	Tutorial: Using PHP and MySQL with Fusion

	© 2000-2008 Awave, Inc.

	*Tutorial -* dynamic web sites using PHP and MySQL
		Contact the author

Lesson 5 - Using PHP to Retrieve Records from a MySQL Database

Test Environment -

In order to give you a "live" environment to work with during this and the following lessons, we've created a database on a test server that you can access with your test web pages - wherever they may be hosted.

To connect to the test database, we'll be using the following function:


	<? // connect to your database function db_connect() { $result = @mysql_pconnect("dns2.awave.net", "tutorialuser", "fusionmx"); if (!$result) return false; if (!@mysql_select_db("tutorial")) return false; return $result; } ?>


	Note: If you would like to try out HeidiSQL, these setting may also used to access the database from your PC.

Using the technique you learned in Lesson 4, save this to a file called 'dbconn.php' and FTP it to your web server into the directory where you place your test web pages. You'll be including this function on your test web page (assuming that you're following along and actually creating a test site in Fusion).

The Test Database -

As you can see in the function above, the test database is named 'tutorial' and is located on the MySQL server named dns2.awave.net. You can also see that we've set up a user account named 'tutorialuser' with a password of 'fusionmx'. The only privilege granted to this user is 'SELECT'. So... don't try to get ahead of us and try deleting or modifying the three records we've inserted into the 'names' table in the 'tutorial' database. IT WON'T WORK!

The following box shows you the structure of the database. It's simply one table (for now) with three fields. We'll be using two of these fields in the example.

The 'tutorial' database (one table for this lesson)

CREATE TABLE `names` (
`NameLast` char(20) default NULL,
`NameFirst` char(20) default NULL,
`FavSoftware` char(30) default NULL
) TYPE=MyISAM;

The Example -

The code below can be copied and pasted into the box on your web page. Just place the code into a text box or single table cell for now. We'll get into formatting and more advanced techniques later on. The output from this PHP script may be seen directly below the box containing the script. Get Code

<?
include('dbconn.php');
if (!db_connect())
{
echo "<p>Error: Could not connect to database.
Please try again later.</p>";
}
else
{
mysql_select_db("tutorial");
$query = "select * from names where NameFirst='Chuck'";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo "There is $num_results Chuck record.";
$row = mysql_fetch_assoc($result);
echo "<p>Chuck's favorite webdesign program is: ";
echo $row["FavSoftware"];
echo "</p>";
}
?>


		Error: Could not connect to database. Please try again later.

	Output from the script above. The script was inserted into a text box that was placed in a layout region with this color background. The output was centered using Text Properties (F3)

What the script does -

The query selects all records from the 'names' table that have 'Chuck' in the NameFirst. We already knew there was only one - but the call to the mysql_num_rows function confirms it.

When the query runs, the retrieved record is stored in the $result variable when $query is processed by the call to the mysql_query function. We display the number of records using the $num_results variable in the 'echo' statement

We use the mysql_fetch_assoc function to create an array in a variable named '$row'. There are other functions available that can retrieve a single row from $result, but we use mysql_fetch_assoc for consistency, since we will frequently be retrieving and using multiple records. We use an associative array rather than a numeric array so we can refer to columns by their name instead of a number.

Troubleshooting

If you get no output from the script when you place it in a page on your web server, the most likely problem is that the server is not configured to use MySQL with PHP.

The default PHP installation under Red Hat 7.2, for instance, does NOT enable MySQL-related functions. The /etc/php.ini file must be edited (the line enabling MySQL is there - but commented out by default). To see if the server has PHP-MySQL enabled, create a file called test.php containing the following: <? phpinfo(); ?>. FTP it to your web directory and use a web browser to access it. It will display information about the PHP installation on the server. If you don't see a section titled "MySQL", you will not be able to access MySQL databases from your web host.

What's next? -

Now that you know to connect to the test MySQL database and retrieve and display a single record from it, we'll move on to retrieving and displaying a resultset with multiple records.

Lesson 6 - Displaying Multiple Records with PHP

Last Updated: 01/27/08