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:

1.	Create a new database and name it addressBook.
2.	Create a new table in this database and name it contacts.
3.	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.

Table 22.1. Sample Data Table

Firstname	Lastname	Phonenum	email
Tom	Hanks	[phone]	[e-mail]
Dale	Gordon	[phone]	[e-mail]
Christopher	Walken	[phone]	[e-mail]
Julia	Roberts	[phone]	[e-mail]
Ashley	Judd	[phone]	[e-mail]

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:

1.	Create a new Flash document and save it as addressBook.fla.
2.	Create a new layer in the timeline. Rename the top layer actions and rename the bottom layer content.
3.	In the Content layer, drag an instance of the List component onto the stage and place it at X 20, Y 20. Set its size to 175x200. Give it an instance name of contacts_list.
4.	In the first frame of the Actions layer, paste the following code into the Actions panel: 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.

Figure 22.6. Your List component populated with the contact names.

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.

Figure 22.7. The requested contact's data written to the browser.

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

1.	Open up addressBook.fla.
2.	Create four text fields on the Content layer with the following properties: Type Dynamic Show border around text true Font size 14
3.	Give these text fields the following instance names: firstName_txt, lastName_txt, phoneNum_txt, and email_txt. You can place and label them however you want, or refer to Figure 22.8 to see how we placed them. Figure 22.8. Our example now gets all the info for the selected contact.
4.	Place the following code in the first frame of the Actions layer below the code already there: [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: