In the previous page, you created a flash animation that accessed a PHP file that retrieved data from a MySQL database. In this page, I will explain the code that helps connect all three separate sections into a final product.

Let's start with the MySQL code first:

CREATE TABLE `sites` (

`id` int(11) NOT NULL auto_increment,

`link` varchar(100) NOT NULL default '',

`title` varchar(100) NOT NULL default '',

PRIMARY KEY (`id`)

) TYPE=MyISAM AUTO_INCREMENT=19 ;

INSERT INTO `sites` VALUES (5, 'www.kirupa.com', 'Kirupa');

INSERT INTO `sites` VALUES (4, 'www.voetsjoeba.com', 'Voetsjoeba');

INSERT INTO `sites` VALUES (3, 'www.cannedlaughter.net', 'Canned Laughter');

INSERT INTO `sites` VALUES (6, 'www.spoono.com', 'Spoono');

INSERT INTO `sites` VALUES (7, 'www.readymademag.com', 'ReadyMadeMag');

INSERT INTO `sites` VALUES (9, 'www.weebl.jolt.co.uk', 'Weebl and Bob');

INSERT INTO `sites` VALUES (10, 'www.aamukaste.org');

INSERT INTO `sites` VALUES (12, 'www.flipflopflyin.com', 'Flip Flop Flyin''');

INSERT INTO `sites` VALUES (15, 'www.kirupaforum.com', 'KirupaForum');

INSERT INTO `sites` VALUES (16, 'www.razyr.com/blog', 'Razyr');

INSERT INTO `sites` VALUES (17, 'senocular.com', 'Senocular');

INSERT INTO `sites` VALUES (18, 'www.may1reboot.com', 'May 1st Reboot');

The code above is pretty much the straightforward format for adding values to a MySQL database. You first create the table, called sites, that will hold the various data "cells". If you want to see the above data in a more familiar, table-like format, it would look like this:

The above MySQL data is directly accessed by the PHP file. Let's take a look at the code in the PHP file:

mysql_pconnect ("HOST OF YOUR SQL SERVER", "YOUR SQL USERNAME", "YOUR PASSWORD");

 

mysql_select_db ("THE DATABASE YOU WANT TO USE");

Before gaining access to a MySQL database, you first need to create a connection to the database. For security reasons, you are required to input the hostname of your SQL server (usually 'localhost'), your username, and your password. Since you may have multiple databases, you use mysql_select_db to tell PHP the name of the database you are planning on using.

$qResult = mysql_query ("SELECT * FROM sites ORDER BY id ASC");

$nRows = mysql_num_rows($qResult);

$rString ="&n=".$nRows;

for ($i=0; $i< $nRows; $i++){

$row = mysql_fetch_array($qResult);

$rString .="&id".$i."=".$row['id']."&"."&title".$i."=".$row['title']."&".
"&link".$i."=http://".$row['link']."&";

}

echo $rString."&";

The code above looks confusing, but it's really simple. What I am trying to do, is extract the data from our MySQL table and display it for Flash to be able to read it.

I first use mysql_query to send a, well, query to the database we currently have a connection open to. If you are familiar with Flash, this is similar to instantiating an object prior to using it in Flash.

In order to retrieve the data from the table, an efficient way would be to use a loop - a for loop in our case. A for loop always requires a starting point, an ending point, and an increment value that will take you from your starting point to your ending point.

Our starting point is zero, as specified by $i =0. Our ending point should be the number of rows of data that are in our MySQL table. In order to retrieve the row information, you can use the mysql_num_rows(data) function that returns a number based on the 'data' passed to it:

$nRows = mysql_num_rows($qResult);

We simply pass in the value from our mysql_query as our data for mysql_num_rows in order to determine the number of rows of data we need to loop through:

Getting back to our loop, we now have our starting point, our ending point ($nRows), and the increment value: $i++. Now that our loop structure is done, we can simply retrieve the data from our database:

for ($i=0; $i< $nRows; $i++){

$row = mysql_fetch_array($qResult);

$rString .="&id".$i."=".$row['id']."&"."&title".$i."=".$row['title']."&".
"&link".$i."=http://".$row['link']."&";

}

echo $rString."&";

The above code is analogous to accessing data from an array. The value if $i increments from 0 to the number of rows, and that number acts as an index position value that concatenates (adds to) $rString with the id, title, and link from our database.

All of the above code returns a long string of data stored in the $rString variable. Flash will see the data from the $rString variable because that is all that is output by the browser. Speaking of which, let's talk about the last piece of the puzzle, the Flash code:

function lv(l, n, t, e, f) {

if (l == undefined) {

l = new LoadVars();

l.onLoad = function() {

var i;

n.htmlText = "";

if (t == undefined) {

n.htmlText += "<b>"+this["title"+e]+"</b><br>";

} else {

for (i=0; i<this.n; i++) {

n.htmlText += "<a href='"+this["link"+i]+"'>
"+this["title"+i]+"</a><br>";

}

}

};

}

l.load(f);

}

lv(sites_txt, "cycle", null, "sites.php");

The above code is very self-explanatory. The main noteworthy aspect you should get out of the above code is that I am taking all of the data and placing it in the text field I created in the previous page. Since I enabled HTML for the text field, I can use HTML formatting to give the text a URL value and make it, essentially, a hyperlink.

The loop structure is very similar to the loop structure I explained in the PHP code. The section on loadVars is addressed by Kirupa's tutorial on that very topic: Displaying Data from an External File.

You are now finished! I have provided the source file for the Flash and PHP portions of this tutorial

Download ZIP

So, hopefully you have learnt a little something about Flash and PHP and its possibilities, so go forth and make something wonderful!

If you have any questions, feel free to contact me or post on the forums!

Ben Smith a.k.a Ωmega