Introduction

Now that we've covered the basics of getting your databases up and running with MySQL, we can concentrate on how we can use them within Dreamweaver.

Creating Recordsets

Before we can display the data in the database we need to first create a Recordset. Within the dmxzone site that we created in the last article, create a new Dynamic PHP page and save it as countries.php.  Then click the + button in the Bindings tabs of the Application Panel and select Recordset (Query).

You are then presented with the Recordset dialog box.  The screenshot below is in simple view: we've named our recordset rstCountries and pointed it at the dmxzone connection that we created in the previous article.  We select the table that we want to select our data from and then  choose to either retrieve all of the columns, or just selected ones.  We'll return all of the columns.

You can then click on Test to see if the query works, when you are happy with the results, click OK to create the recordset. The new recordset will appear in the Bindings tab and can be expanded to see the columns that have been returned by the query.

Displaying data

There are a number of ways in which to display the data in our page.  The easiest way is to simply drag the column that you wish to display into your page in  this works in both Design and Code view. 

This is just a shortcut to inserting a Dynamic Text Server Behavior.  If you select the Server Behaviors tab in the Application panel you will see that there are now two Server Behaviors listed  the one for the Recordset we created and now a Dynamic Text displaying the country_name field from the rsCountries Recordset.

Instead of dragging the field into the page you could have placed the cursor at the point in the page where you wanted the data to appear, and then chosen to insert a Dynamic Text Server Behavior.

The Dynamic Text dialog gives you formatting options for the text that you wish to insert  capitalizing letters, changing the case to upper or lower case and trimming off unwanted spaces.

Select the field you wish to display, the formatting that you want to apply to it and click OK  If you now save the file and view it in a web browser you should see the United Kingdom listed twice  first as we inserted it with phpMyAdmin and secondly in upper case:

This is fine when we are only expecting to get a single record of information in our recordset, for example if we were retrieving a single clients information, but in this case we are retrieving all of the records in the table.  In order to display all of the records we will need to use a Repeat Region Server Behavior.

First select both of the Dynamic Text Server Behaviors that are already in the page, either by selecting them in the Server Behavior tab in the Application Panel and clicking the  button, or by selecting them in Design view and hitting Delete.

We'll want to display the data in a table, so insert a table with 4 columns and 2 rows.  In the first 2 columns of the first row add the text Country Name and Country Code, then in the last 2 columns of the second row, add the text Edit and Delete.

Then into the first 2 columns of the second row add Dynamic Text Server Behaviors displaying the country_name and country_code fields from the rsCountries recordset:

Then, select the entire second row

and apply a Repeat Region Server Behavior to it.  Select the rsCountries recordset and choose to repeat all records.

Click OK and you will see the Repeat Region Server Behavior shown in Design View

If you save the file and preview it in a browser you will see both records that we added when we set the database up initially.

The Edit and Delete links we will come to in a short while. First we will look at how we can insert new records into the database.

Allan Kent

Allan comes from Cape Town, South Africa. He has been implicated in writing for several WROX, glasshaus, Wiley and Apress publications, generally in the 'cool stuff that PHP can do' sections.

You can catch up with him at his website http://www.mediafrenzy.co.za.

See All Postings From Allan Kent >>

User Reviews