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.

Example 9-4. Server-side code for accessing a database

 <?PHP   class CustomersAdmin {     // Login information for the database     var $dbhost = "localhost";     var $dbname = "northwind";     var $dbuser = "northwind";     var $dbpass = "northwind";     // Constructor     function CustomersAdmin ( ) {       // Create the method table for AMFPHP       $this->methodTable = array(         "getCountries" => array(           "description" => "Retrieve the list of countries",           "access" => "remote",           "arguments" => array( )         ),         "getCustomersByCountry" => array(           "description" => "Retrieve a list of companies in a given country",           "access" => "remote",           "arguments" => array("country")         ),         "getContact" => array(           "description" => "Retrieve all of the information about a customer",           "access" => "remote",           "arguments" => array("customerID")         ),         "updateContact" => array(           "description" => "Update the contact information for a customer",           "access" => "remote",           "arguments" => array("customerID", "contactName", "contactTitle",                               "phone", "fax")         )       );       // Create the connection to the database server and select the database       $this->conn = mysql_pconnect($this->dbhost, $this->dbuser, $this->dbpass);       mysql_select_db($this->dbname);     }     // Get a list of the countries     function getCountries ( ) {       $result = mysql_query("SELECT Distinct(Country) FROM Customers                             ORDER BY Country");       return $result;     }     // Get the customers in a given country     function getCustomersByCountry ($country) {       $query = "SELECT CustomerID, CompanyName FROM Customers ";       if ($country != "All") {         $query .= "WHERE Country = '".$country."' ";       }       $query .= "ORDER BY CompanyName";       $result = mysql_query($query);       return $result;     }     // Get all of the information for a contact at a given customer     function getContact ($customerID) {       $customer = array( );       $result = mysql_query("SELECT * FROM Customers                             WHERE CustomerID = '".$customerID."'");       $customer = mysql_fetch_array($result);       return $customer;     }     // Update the contact at a given customer     function updateContact ($customerID, $contactName, $contactTitle, $phone, $fax) {       $query = "UPDATE Customers SET ";       $query .= "ContactName = '".$contactName ."', ";       $query .= "ContactTitle = '".$contactTitle ."', ";       $query .= "Phone = '".$phone."', ";       $query .= "Fax = '".$fax."' ";       $query .= "WHERE CustomerID = '".$customerID."'";       $result = mysql_query($query);       return $result;     }   } ?> 

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.

Example 9-5. Client-side code for user interface

 #include "NetServices.as" //------------------------------------------------------------------- // Define the URLs //------------------------------------------------------------------- gatewayURL = "http://localhost/frdg/gateway.php"; serviceURL = "CustomersAdmin"; //------------------------------------------------------------------- // General purpose functions //------------------------------------------------------------------- // Turn the entire interface on or off enableInterface = function (state) {   var textType = state ? "input" : "dynamic";   var textColor = state ? 0xFFFFFF : 0xBEBEBE;   countries_cb.setEnabled(state);   customers_cb.setEnabled(state);   update_pb.setEnabled(state);   contactName_txt.type = textType;   contactTitle_txt.type = textType;   phone_txt.type = textType;   fax_txt.type = textType;   contactName_txt.backgroundColor = textColor;   contactTitle_txt.backgroundColor = textColor;   phone_txt.backgroundColor = textColor;   fax_txt.backgroundColor = textColor; }; //------------------------------------------------------------------- // Event handlers for the components //------------------------------------------------------------------- onSelectCountry = function ( ) {   var country = countries_cb.getSelectedItem( ).label;   service.getCustomersByCountry(customerResponder, country);   customers_cb.removeAll( );   customers_cb.addItem("Loading...");   enableInterface(false);   status_txt.text = "Getting customers..."; }; onSelectCustomer = function ( ) {   customerID = customers_cb.getSelectedItem( ).data;   service.getContact(contactResponder, customerID);   contactName_txt.text = "";   contactTitle_txt.text = "";   phone_txt.text = "";   fax_txt.text = "";   enableInterface(false);   status_txt.text = "Loading contact..."; }; onUpdate = function ( ) {   var customerID = customers_cb.getSelectedItem( ).data;   var contactName = contactName_txt.text;   var contactTitle = contactTitle_txt.text;   var phone = phone_txt.text;   var fax = fax_txt.text;   service.updateContact(updateResponder, customerID, contactName,             contactTitle, phone, fax);   enableInterface(false);   status_txt.text = "Updating contact..."; }; //------------------------------------------------------------------- // Create the responder objects //------------------------------------------------------------------- // Responder for loading countries countryResponder = new Object( ); countryResponder.onResult = function (countries_rs) {   var max = countries_rs.getLength( );   countries_cb.setEnabled(true);   countries_cb.addItem("All", 0);   for (var i=0; i<max; ++i) {     countries_cb.addItem(countries_rs.getItemAt(i).Country, i+1);   }   countries_cb.setSelectedIndex(0); }; // Responder for loading customers customerResponder = new Object( ); customerResponder.onResult = function (customers_rs) {   var customer;   var max = customers_rs.getLength( );   customers_cb.setEnabled(true);   customers_cb.removeAll( );   for (var i=0; i<max; ++i) {     customer = customers_rs.getItemAt(i);     customers_cb.addItem(customer.CompanyName, customer.CustomerID);   }   customers_cb.setSelectedIndex(0); }; // Responder for loading a contact contactResponder = new Object( ); contactResponder.onResult = function (contact) {   enableInterface(true);   contactName_txt.text = contact.ContactName;   contactTitle_txt.text = contact.ContactTitle;   phone_txt.text = contact.Phone;   fax_txt.text = contact.fax;   status_txt.text = "Ready."; }; // Responder for updating a contact updateResponder = new Object( ); updateResponder.onResult = function ( ) {   enableInterface(true);   status_txt.text = "Ready."; }; //------------------------------------------------------------------- // Create the gateway and service objects //------------------------------------------------------------------- gateway = NetServices.createGatewayConnection(gatewayURL); service = gateway.getService(serviceURL); //------------------------------------------------------------------- // Set up the components' event handlers //------------------------------------------------------------------- countries_cb.setChangeHandler("onSelectCountry", this); customers_cb.setChangeHandler("onSelectCustomer", this); update_pb.setClickHandler("onUpdate", this); //------------------------------------------------------------------- // Set up the interface and load the countries //------------------------------------------------------------------- contactName_txt.borderColor = 0x666666; contactTitle_txt.borderColor = 0x666666; phone_txt.borderColor = 0x666666; fax_txt.borderColor = 0x666666; enableInterface(false); status_txt.text = "Loading countries..." service.getCountries(countryResponder);