File Under: Databases, Programming

Build a Website With Flash and MySQL – Lesson 2

In Build a Website With Flash and MySQL – Lesson 1, we successfully created a MySQL database, filled it with blog entries, and learned how to query it. Now we’re going to move on to the fun stuff:creating a Flash container to display our blog entries as we pull them out of the database. I’m going to help you build something along the lines of what you’ll encounter at my own Flash blog site, Luxagraf.

Fire up Flash and create a new document. The first thing we need is a nice big text field to display our entries. You could create a text field in ActionScript if you like, using the createTextField() method. I don’t know about you, but my stomach for code is still full from yesterday, so I’ve just drawn a text field using the cursor tool and given it an instance name of entries_txt. In the Properties Inspector, set the text to be dynamic, multi-line, and HTML formatted. For safety’s sake, select the character option and click “embed all characters.” Flash has some issues with dynamic textfields that don’t have embedded characters, especially if you put your text under a mask.

I’ve also attached a scrollbar to the text field and given it an instance name of sb.

Flash_MySQL_shot1.jpg

My design is minimalist, but you can load up your movie with cool graphics, nifty background animation, menus, and more. And you can rest assured that while Flash is retrieving your data, your design will keep your user otherwise enthralled until the data loads. You might even include a loading progress bar if you want to give modem users some feedback so they know that something is happening. Creating a progress bar for external data is a little more complex than one for an external .swf, but it’s not too bad. Colin Moock, author of ActionScript:The Definitive Guide, has a sample available.

To show our list of archived entries, we’ll need a second, smaller text field, which I’ve named archive_txt. I’ve attached another scrollbar to the archive text field and given it instance name sb2. Set the archive text field up the same way:dynamic, multiline and HTML. You don’t need to embed the font again since it can use the characters from the first text field.

That’s it for the graphics. Now let’s get back to code!

Contents

  1. Linking Flash to the Database
  2. Loading Up on Content
  3. Publishing the Finished Product
  4. Allow Me to Extrapolate

Linking Flash to the Database

Create a new layer on top of the last one and name it “actions” or something similar. Now open up the Actions panel (F9) and paste in the following code. It should display correctly below, but if your browser refuses to wrap text properly, use the plain text or HTML version of the raw code.

//function to load external data using the loadVars() object

//l=name of loadVars object

//n=name of text field

//t=trigger to decide whether to show all entries or just one.

//e= entry number to display (number)

//f=file to load from (string)

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

	sb.setSize(null, 200);

	sb2.setSize(null, 50);

	//create a new loadVars object if one doesn't already exist

	//if it does, use it

	if (l == undefined) {

		l = new LoadVars();

		l.onLoad = function() {

			var i;

			//clear out any text that might already be there

			n.htmlText = "";

			//to show a single entry at a time we use the

			//following code

			if (t == undefined) {

				n.htmlText += "<b>"+this["title"+e]+"  -

				"+this["date"+e]+"</b><br><br>";

				n.htmlText += this["entry"+e];

			} else {

				//cycle through and show all entries

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

					n.htmlText += "<u>

					<a href='asfunction:_root.loadArc,

					"+this["id"+i]+"'>"+this["title"+i]+"  -

					"+this["date"+i]+"</a></u><br>";

				}

			}

			sb.update();

			sb2.update();

		};

	}

	l.load(f);

}



What we have here is a reasonably generic function that you can use to load external data in any Flash movie provided you know how it works and how to modify it. I’ll walk you through it line by line and hopefully you can see where you might have to change things to suit your needs.

First off, let’s define our single letter variables in a little more detail. First we have “l”, which will become a new instance of the loadVars() object. The object needs to have an instance name so we’ll give it one when we call the function. Next we have “n” which will be the name of the text field. If you’re following along with my example, we’re going to use the instance name of the text fields we created earlier (entries_txt and archive_txt). By making this a variable that’s passed to the function, we can create as many textboxes as we want and we don’t have to rework our main loadVars function. “t” is for trigger (like the horse, but smaller). This variable may be unnecessary for your needs, but I’ve put it in there so that I can use the same function to load both the main text and archives. For the main textfield, I’ll pass a value of undefined because I only want to show the latest entry. For the archive textfield, we can pass any value other than undefined. In my own work I tend to pass the string “cycle” so I know what it is I’m doing without looking back at the actual function, but you could pass it the string “webmonkey rocks” and it would do the same thing.

The next variable is “e,” which we won’t be using initially, but which will help us when we get to the archive section by allowing us to select entries by number. Last we have “f”, which is short for file, as in the .php file we’re calling. In the last section, use whatever name you used when you saved your .php file. I chose blog.php.

It’s worth asking why I didn’t use more descriptive variable names in the function. Well, that’s partially due to laziness, but it’s also because using single letters seems to make things happen a bit faster.

Loading Up on Content

Okay, now that we’ve spelled out our variables, we’ll move on to the function and its inner workings. To load our database information into Flash we’re going to be using the loadVars() object. As with most Flash objects, the first step is to create a new instance of the object. In this function we’ve started with an if statement.


 if (l == undefined) {



 		l = new LoadVars();


The if statement checks to see whether we have a loadVars object by the name of “l” and, if not, it creates one. Now, since “l” will be defined in our function call, we can create loadVars objects left and right if we want to. On the other side of the coin, if we already have a loadVars object by the name we’ll call, we don’t have to waste time creating a new one. The next thing we need to do is define the onLoad method of our loadVars() object. Flash needs to know what to do with the information it’s about to get. So we define the function to process our data. Here’s the section of the code from page two that we’re dealing with:

l.onLoad = function() {

			var i;

			n.htmlText = "";

			//to show a single entry at a time we use the

			//following code

			if (t == undefined) {

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

					-  "+this["date"+e]+"</b><br><br>";

					n.htmlText += this["entry"+e];

			} else {

				//cycle through and show all entries

				for (i=0; i" +this["title"+i]+"

					-  "+this["date"+i]+"</a></u><br>";

				}

			}

The first thing in our function is a variable i. I should point out here that, just as we used $i in our PHP code, we’ll call this variable i as well. Using i for incremented variables is just a programming convention. If there is a good reason for it, I’m not aware of it. You can ignore convention and use whatever you like, just don’t use any of the letters we’ve already assigned. The key thing to note is the use of var to define the variable. In Flash, var creates a local variable; that is, the variable i is only defined in this function. Outside this function you can use i again and it won’t mess things up. Local variables, as this practice is called, use up less memory since they get used and wiped away immediately afterward. Next we empty out our textfield:

n.htmlText = "";

Now we’re going to set up two possible ways of handling our data using our “t” variable as a selector. The code reads “if the variable t is undefined then do the first chunk of code, else do the second chunk of code.” The first chunk of code is just formatting for the information that was passed from MySQL. Our variable n, which will be the name of our text field, gets its htmlText property defined.

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



 n.htmlText += this["entry"+e];

Here we’re concatenating a string again like we did in PHP, except in Flash we concatenate strings with the + operator. The first element is an HTML bold tag to highlight the title. The title itself comes from the array we defined in PHP this["title"+e]. “This” refers to the function we’re in, which is the onLoad handler, meaning anything with the “this” designation uses the PHP passed data. So, this.["title"+e] refers to the title column in our MySQL table plus our variable e. We could have just said this.["title"+0], but then we wouldn’t be able to use this function in our archives section. Next we have a dash to separate the title and date. Then, we join the rest of the string which is the same code, but this time we use the “date” element of our PHP array. Lastly, we close the bold tag and add some
tags to put a little space between the title and the entry itself. The entry itself is written on a separate line so it will be displayed below the date and title. Once again we use an element of the PHP array, this time the “entry” element, which contains the main text.

But what about our other case? Can we display a list of past entries listed by title and date without text? Of course, but first let’s look at how our function will get to this point. If we define the variable t, the if statement we just wrote evaluates to false so Flash jumps to the else clause. The else clause then tells Flash to execute a loop so all of our entries will be displayed at once. Remember for loops? Well this one should look familiar from the PHP section. It’s essentially the same loop with one subtle difference:the second condition, iThe way we’re handling the data in the archives textbox is a little different, too. We’ve set our text to underline using the HTML tag <u> and wrapped everything in an HTML link (note that this should all be on one line with no hard returns).

 n.htmlText += "<u><a href='asfunction:_root.loadArc,"+this["id"+i]+"'>"



 +this["title"+i]+"  -  "+this["date"+i]+"</a></u><br>";

Our href tag might look a little strange to veteran HTML coders. Flash has a little-known feature that allows you to call a function from within an HTML tag. The syntax is asfunction:location/nameOfFunction,variable. The limiting thing is that you can pass only one variable using this method. The quickest way around that problem is to pass a variable as long string and then break it apart in your function, but that won’t be an issue for us since we have only one variable to pass.

The last line our onLoad function uses the variable “s” to update our scrollbar. The lines after our onLoad function call the .load method of the loadVars object and pass the name of our .php file. This tells our loadVars object to go ahead and read the .php file. It might seem backwards to define the onLoad handler before calling the load function, but you must define the onload handler first.

Publishing the Finished Product

Alright! We’re so close, I can taste it. Now that we’ve written our main function, let’s handle that asfunction we’re calling to, but haven’t yet defined.

Copy this code into your Flash movie:

 function loadArc(passed) {



 	arcNum = passed-1;



 	lv(blog_lv, entries_txt, undefined, arcNum, "blog.php");



 }

All this function does is take the entry number passed from our lv function, subtract 1, and then call the lv function again with the new number. Why subtract one? Remember that our PHP loop starts counting at zero, but our MySQL auto increment starts at one. This means we’re always one number behind. Simple solution to the problem:subtract one.

More copy and paste:

 //for the large entry textfield



 lv(blog_lv, entries_txt, undefined, 0, "blog.php");



 //for the archives text field



 lv(archive_lv, archive_txt, "cycle", null, "blog.php");

These lines call the function lv with all of our variables defined. When this code gets executed Flash will jump up and read our lv() function and then look for our PHP file which will query our database, extract and format our information and send it back to Flash where it will be displayed. I know it sounds like an incredibly long and complex process, but it will actually happen in fractions of a second and your user won’t have to sit idly waiting for the entire page to redraw itself.

OK, publish the movie and upload it to your site. You’re done! Now, when a user comes to the site and loads the Flash piece, they’ll see the latest entry in the big text box and a list of past entries in the smaller one. Try clicking on the links in the archive list. You should see the Flash movie bring up past entries in the larger text box.

You can see a working version of this very system over at my own blog site, Luxagraf. While you’re poking around, click on the “Browse Archives” tab at the bottom of the Flash window and you’ll see my second textbox full of archived entries show up.

We did it! Primitive though it may be, we’ve created a Flash-based blog and learned a few things about Flash and database integration. Now let’s take a look at some places we can expand our code and make it more useful to a wider range of applications.

Allow Me to Extrapolate

My main goal with this tutorial was to give you, the designer/programmer (which you can now truthfully call yourself!) a starting point from which to extrapolate and expand. You’ve made it this far, and for that you should be proud, but you probably have some questions. With this in mind, I wanted to address a few things.

First off, we only scratched the surface of MySQL and PHP. To start thinking in more general terms, I suggest visiting the developer’s sites for the two technologies. Perhaps you should also join some mailing lists and see what others are doing with MySQl and PHP. Since there is no way to sit back and anticipate what a particular project may require, it’s a good idea to look around and see how other people are using these tools.

Some examples that spring to mind for PHP/MySQL projects are a discussion board, or perhaps a backend application that lets your client update content without having to access MySQL directly. Once you have a better idea of what you can do with a database and have polished up your PHP knowledge, you will start to see some holes in our example. For instance, what if your want your PHP query to be dynamic? Suppose you want to return a query based on user input (i.e. some sort of search criteria defined by the user)? It’s not hard to do that at all. From the Flash end, you simply need to use the sendAndLoad() method of the LoadVars() class rather than the simple load() method (look it up in the ActionScript Dictionary) and then write your PHP query string with a POST variable. PHP and Flash are both very flexible tools, and you’ll find that the functionality you can achieve by using them is close to unlimited.

The final concern I wanted to address is Macromedia’s release of Flash MX 2004. Aside from the fact that software manufacturers seem intent on coming up with ever more confusing product names and pricing schemes, the more pertinent question is “Does our example still work?”

The short answer is yes. Judging from my own experience with Flash MX 2004 Professional and the Flash Player 7, the behaviors associated with loadVars() remain the same. There is, however, a new set of data components in Flash MX 2004. I have not explored these new tools in great depth, but it’s very possible they may simplify the code necessary to access external data sources. In my readings, the new components seem to be geared toward moving data between component sets. However, there is a fair price to be paid. Just dragging the new dataGrid component onto the Flash stage is going to bump your file size up by 58Kb. In comparison, the code we’ve written is only as large as the scrollbars you use. It’s also worth noting that none of these components can query MySQL directly – several rather expensive server technologies are supported, but not the free, open source solution.

This raises yet another issue:By no means is Flash limited to PHP and MySQL interaction. Everything we’ve done can be done with ASP or other proprietary software so long as you know how to get the returned data into a URL encoded string. Then there is XML data which is also not hard to feed into Flash…

Hopefully this tutorial can serve as a kind of gateway, providing an entrance into the world of dynamic content for Flash movies. How much of it you use is always going to depend on your own projects and needs. If nothing else, you have left the five percent nation of non-weblog creators and joined the thronging masses. Now take this project and see if you can adapt it to your own creative needs.

From the series

Build a Website With Flash and MySQL – Lesson 1
Build a Website With Flash and MySQL – Lesson 2