Macromedia Flash Professional 8 UNLEASHED [Electronic resources] نسخه متنی

Incorporating a MySQL Database

If you have made it this far and are still up for morecongrats! You have learned how to load data from your server-side PHP scripts and how to send and receive data back from them. Up to this point, you have provided the data for our examples, either through hard coding or manually entering it in Flash.

Example 6Address Book

In the following example, we will create a very simple address book and store our data in a MySQL database.

Building the Database

Before we get into our example, we will need to build the database that is going to hold our content. You can download the SQL file from the book's website or build it on your own. If you want to build it yourself, follow these steps:

Add the following fields to the new table:

• 
  

  
  contactId
  This field is a unique identifier of the record in the database. This field should have the following properties:

  
  

  • 
    

    
    Type :
    INT

    
    

  • 
    

    
    Length :
    11

    
    

  • 
    

    
    Extra :
    auto_increment

    
    

  • 
    

    
    Primary Key :
    true

    
    

• 
  

  
  firstName
  The contact's first name. This field should have the following properties:

  
  

  • 
    

    
    Type :
    VARCHAR

    
    

  • 
    

    
    Length :
    255

    
    

• 
  

  
  lastName
  The contact's last name. This field should have the following properties:

  
  

  • 
    

    
    Type :
    VARCHAR

    
    

  • 
    

    
    Length :
    255

    
    

• 
  

  
  phoneNum
  The contact's phone number. This field should have the following properties:

  
  

  • 
    

    
    Type :
    VARCHAR

    
    

  • 
    

    
    Length :
    255

    
    

• 
  

  
  email
  The contact's email address. This field should have the following properties:

  
  

  • 
    

    
    Type :
    VARCHAR

    
    

  • 
    

    
    Length :
    255

    
    

If you built the database on your own, you will also have to populate it with some records. Table 22.1 is a table of the data we will be using for this example.

Getting the Data from the Database

Now that our database is created, and populated with some test data, let's move on to creating the PHP script that will grab this data and pass it to Flash. Create a new PHP file, save it as

contactList.php , and paste the following script inside. Don't forget to put in your username and password:

<?php
//Database Information
$dbHost = "localhost";
$dbUser = "YOUR_USERNAME";
$dbPass = "YOUR_PASSWORD";
$dbName = "addressBook";
//Open database connection
function openConn() {
global $dbHost;
global $dbUser;
global $dbPass;
global $dbName;
$curConn = mysql_connect($dbHost, $dbUser, $dbPass);
if (!$curConn) {
echo "<p>Could not connect to the database.</p>";
}
if (!@mysql_select_db($dbName)) {
echo "<p>Couldn't find database $dbName.</p>";
}
return $curConn;
}
openConn();
//Open database connection, and request the data
$tempConn = openConn();
$listQ = "SELECT contactId, firstName, lastName FROM contacts";
$listR =  mysql_query($listQ);
$listN = mysql_num_rows($listR);
//Write the returned data to the screen
if ($listN != 0){
for ($count = 1; $count <= $listN; $count++){
$aResult = mysql_fetch_array($listR);
$allContacts .= $aResult["lastName"] . ", " . $aResult["firstName"] . "~";
$allIds .= $aResult["contactId"] . "~";
}
echo "contacts=" . $allContacts . "&ids=" . $allIds;
}else{
echo "No records were returned, sorry.";
}
//Close the database connection
mysql_close($tempConn);
?>

This file will connect to the database, grab the contactId field, the firstName field, the lastName field, and write them to the browser in the name/value pair format that Flash can understand.

Now let's move on to the Flash file:

function getAllContacts(){
var getAll_lv:LoadVars = new LoadVars();
getAll_lv.onLoad = function (success){
if(success){
//get the data being returned
var name_array = this.contacts.split("~");
var ids_array = this.ids.split("~");
//temporary array to hold the data before it goes to the list
var temp_array = new Array();
//because of the extra "~", there is an extra element
//so we only need every element, but the last one
var tempLength = name_array.length - 1;
var i = 0;
while(i < tempLength){
temp_array.push({label: name_array[i], data: ids_array[i]});
i++;
}
//set the list component
contacts_list.dataProvider = temp_array;
}else{
trace("An error occurred with the connection");
}
}
//get the data
getAll_lv.load("http://PATH_TO_YOUR_SERVER/contactList.php");
}
//call the function
getAllContacts();

First we create a function called getAllContacts. This function holds all the data-loading code. Inside, we create an instance of the LoadVars object, set the onLoad event to take the returned data and put it into arrays, and after we have the data in Flash, we hand it over to the dataProvider object of the List component.

NOTE

As you can see, we like to make just about anything a function. In the Address Book example we did not have to do this, but it is a good practice to get into. Having the code run on the frame puts you in a position of having to line everything up right on the timeline. If you put your code into function, it gives you the capability to call it when and wherever you need it, giving you a little bit more freedom in how you build your projects.

If everything went well, when you test your movie you should be presented with the List component, populated with the Last Name, First Name of every contact in your database, as you can see in Figure 22.6.

Now this is great, but what happened to the rest of the data? All that information is still in the database, so we have to do some more work to get it displayed in Flash. Let's start with the PHP page that we will use to request the rest of that data for a specific contact. Create a new PHP file, save it as

contactInfo.php , and paste the following script inside. Don't forget to put in your username and password:

<?php
// Database Information
///////////////////////
$dbHost = "localhost";
$dbUser = "YOUR_USERNAME";
$dbPass = "YOUR_PASSWORD";
$dbName = "addressBook";
// Open database connection
///////////////////////////
function openConn() {
global $dbHost;
global $dbUser;
global $dbPass;
global $dbName;
$curConn = mysql_connect($dbHost, $dbUser, $dbPass);
if (!$curConn) {
echo "<p>Could not connect to the database.</p>";
}
if (!@mysql_select_db($dbName)) {
echo "<p>Couldn't find database $dbName.</p>";
}
return $curConn;
}
openConn();
//Connect to the database
$tempConn = openConn();
//Make our request, store it in an array, and count it
$listQ = "SELECT * FROM contacts where contactId = '" $_REQUEST["contactId"] . "'";
$listR =  mysql_query($listQ);
$listN = mysql_num_rows($listR);
//Write the returned data to the screen
if ($listN != 0){
$aResult = mysql_fetch_array($listR);
$contactInfo = "firstName=" . $aResult["firstName"];
$contactInfo .= "&lastName=" . $aResult["lastName"];
$contactInfo .= "&phoneNum=" . $aResult["phoneNum"];
$contactInfo .= "&email=" . $aResult["email"];
echo $contactInfo;
}else{
echo "No records were returned, sorry.";
}
//Close the database connection
mysql_close($tempConn);
?>

contactInfo.php is very similar to contactList.php, but there are three differences. First of all, in this page we are expecting a variable from Flash, contactId. This variable will tell us what contact's data to grab. We use that variable in the SQL query to grab all the fields (*) from the record whose contactId field is equal to the contactId variable this page received. After we get the information, we create a string that has all the contact's data in the name/value pair format and write it to the browser. You can test this page in your browser by passing it a variable in the URL like so:

http://PATH_TO_YOUR_SERVER/contactInfo.php?contactId=1

You should be presented with a page that looks like Figure 22.7.

Now we need to get back to the Flash file and set it up to display the data that contactInfo.php will return.

[View full width]

//The listener object for when a user selects a contact
var contact_obj:Object = new Object();
contact_obj.change = function(){
var contactId = contacts_list.value;
var contactInfo_lv:LoadVars = new LoadVars();
contactInfo_lv.onLoad = function(success){
if(success){
if(!this.error){
firstName_txt.text = this.firstName;
lastName_txt.text = this.lastName;
phoneNum_txt.text = this.phoneNum;
email_txt.text = this.email;
}else{
trace(this.error);
}
}else{
trace("An error occurred with the connection");
}
}
contactInfo_lv.contactId = contactId;
//send and receive the data
contactInfo_lv.sendAndLoad( "http://localhost/phpChapter/example6_addressBook
/contactInfo.php", contactInfo_lv, "POST");
}
//add the event listener to the List component
contacts_list.addEventListener("change", contact_obj);

Test your movie and select a movie in the list. You should see something similar to Figure 22.8: