9.6 Working with a Database

PHP works with many
different databases, but the one most often associated with it is
MySQL. The following example assumes you have both PHP and MySQL set
up and running and that you have the Northwind
database set up with a user named
"northwind" and a password of
"northwind". The
Northwind database is provided by Microsoft as a
sample for SQL Server
(http://office.microsoft.com/downloads/2000/Nwind2K.aspx)
and can be translated into a MySQL database via the
mssql2mysql VBScript
(http://www.kofler.cc/mysql/mssql2mysql.html). A
MySQL version of the Northwind database is also
available at the online Code Depot cited in the Preface.

This example defines a custom PHP class,
CustomersAdmin, which is assumed to live inside
of a file named CustomersAdmin.php in your
services directory. The information about the
database (database name, hostname, username, and password) are all
stored as properties of the class. In addition, the connection to the
database is set up as a persistent connection in the
class's constructor; consequently, the class methods
don't have to worry about setting up that
connection. Example 9-4 shows the server-side PHP
code to access a database. Example 9-5 implements
the client-side ActionScript code that goes along with it.

Notice that the getCountries(
) method returns the exact resource that is
returned from the database, but the getContact(
) method pulls out a single row of a resource and sends it
back as an array. It's up to you to determine the
most appropriate kind of data to send back to Flash. Just keep in
mind that you probably don't want to return more
data than the client will actually use.

Now that the server-side code is set up, you must set up the
client-side interface for the example (or download the
.fla file from the online Code Depot), as shown
in Figure 9-1. The instance names of each interface
element, as indicated in Figure 9-1, should be set
using the Property inspector.

Figure 9-1. Client-side interface for a database access application

The interface operates as follows. The countries are listed in the
countries_lb listbox; once a country is selected,
the customers in that country are displayed in the
customers_lb listbox. Once a user selects a
customer, the customer's contact name, contact
title, phone number, and fax number are displayed. If the user wants
to update the information about the customer, he simply changes the
listbox selections or text field text and clicks the Update button.
The status_txt text field tells the user what is
going on at any given time.

Example 9-4 showed the server-side PHP code to
access the database on the server. The client-side ActionScript code
to implement the user interface, as listed in Example 9-5, lives entirely on the main timeline.