File Under: Databases, Multimedia

Build a Website With Flash and MySQL – Lesson 1

Overview

There’s always been somewhat of a disconnect between designer-types and backend-types. And we’re not just talking about hairstyles, eyewear, and the contents of their bookshelves — designers and programmers approach problems in different ways, and their individual plans of attack don’t always jibe. Of course, you want your site to be sleek, fast, and bleeding-edge, but are you willing to compromise stability, scalability, and compatibility just so the users can ooh and aah at a few pretty pictures? Web design has always been a balancing act, and the ideologies of each camp often clash.

But Webmonkey’s here to say that it doesn’t have to be that way. It is possible to create a site that has a lightweight Flash frontend, a dreamy interface, and a scalable, secure, and dynamic backend.

Webmonkey Scott has found the balance between eye-catching design and backend database wizardry. Lucky for us, he’s agreed to share this knowledge in a two-day tutorial. He’s even put together a hands-on project that shows you how to build an easy-to-update blog using Flash. That’s right, a Flash-based blog — it sounds a little loony, but this blog pulls content from a MySQL database and feeds it into the dynamic Flash frontend using a few lines of PHP code.

In day one, Scott talks you through the construction of an open source MySQL database using both the phpMyAdmin tool and mysql on the command line. You’ll learn how to add blog entries to the database and then you’ll learn how to run queries in PHP. Topics such as basic database organization and the behavior of variables are also covered in this lesson.

Day two shows you how to design and build the blog’s Flash frontend. You’ll use common Flash ActionScript objects to pull the content from your MySQL database to the different areas of your blog’s user interface. You’ll also learn some common workarounds to keep all of your content flowing smoothly.

Whether you are designer or a programmer, it’s time to roll up those sleeves and get ready to see how the other half lives. Even if those of you who don’t feel a particular loyalty to either side of the fence will still discover that there’s plenty of hands-on knowledge to be gained.

We’re not promising that designers will sell their $500 pencil sharpeners, start marrying UNIX geeks, and honeymooning in Cancun before breeding programmer/designer children, but we will insist that they sit in a room alone together until they can build a useful website that everyone can agree on. Hey, stranger things have happened. Like Carrot Top.


Contents

  1. Building with Flash and MySQL
  2. PHP Basics
  3. Using MySQL
  4. Creating the Database
  5. Entering Data
  6. Add a Dash of PHP
  7. Strings, Sealing Wax, Fancy Stuff

Building with Flash and MySQL

From the beginning, the database has been the sole province of programming geeks. For designers, usually geeks themselves, but with backgrounds in color theory or animation rather than code architecture, the jargon surrounding databases is often confusing, overwhelming, and intimidating. Even so, the irony is that designers are sometimes the very people who can benefit the most from using a database.

Many designers can create eye-popping sites full of life, but it’s rare to find a designer who can sustain that initial vibrancy as the site ages. How often have you seen an amazing-looking site that was created, hyped, then abandoned?

Database integration can propel a website from a well-designed but infrequently updated bore to a dynamically generated, updated-on-the-fly extravaganza for the mind. Connecting your design to content driven by a database gives you or your clients a site that can be updated easily without ever needing to touch the design elements themselves. Also, this separation of content and container will make your job as a designer much easier. With the continuous flow of timely content from your easy-to-update database, your site will stay fresh and youthful. It works better than any of those creams from France, anyway.

As a good designer, you’re capable of translating loosely held, often ill-conceived ideas into the sites we all use and enjoy. What’s more you make them look good. But perhaps dealing with raw code is not your forte. Not to worry, it’s not as difficult as you might think.

To get the full benefits a database offers, we’re going to feed our content into a Flash movie rather than an HTML page. Why would you want to do that, you ask? As a seasoned Web surfer, you’re probably familiar with the so-called “blink” of HTML. The process goes a little something like this:You click a link, the link then sends a command (often via PHP, which we’ll use here) to a database to retrieve whatever information you’re after. Once the database has retrieved your information, it sends that info back to the page that requested it. Now, if that page is HTML, your browser must weed through the results and shape them accordingly. But, before it can do any of that, the browser must first empty out the old page and create that momentary blank browser window – the blink of HTML.

This doesn’t happen with Flash. Flash can load the data into the very same place from which it was requested without a visible refresh. No blink. No redrawing of elements that haven’t changed. In short, a smoother, less distracting transition for your user which leads to a better, more enjoyable viewing experience.

Everybody convinced? Then let’s get started.

PHP Basics

If you’ve worked with databases before, even if you’ve always fed the content into HTML pages, you’ll have a head start here. I’ll be assuming that you have MySQL and PHP installed and running on your server. If you’re going to be doing this through your Web hosting company, check to see if they already have the necessary software installed. If you find that you need to install either program, check out PHP and MySQL Tutorial, which will also be a helpful read for those of you who’ve never worked with PHP since it goes into a little more detail on the syntax. The homebase for MySQL has a full manual available for download, as does the official PHP site. Take advantage of these free resources. Many hardworking volunteers have created them for you, and answers to most nitty-gritty questions can be found on one of those sites.

If your website is hosted by someone other than yourself, you will have to ask your hosting company how to connect and interact with your database. Most good hosting companies will have you interact with your database via your Web browser and the phpMyAdmin program. The application will give you a nice graphical interface for creating tables and entering information. Also, phpMyAdmin shows you the actual MySQL syntax for your actions, which will let you get a better feel for how MySQL works. Really good hosting companies will let you connect directly via a secure shell or some other graphical program of your choice.

There are many other graphical ways to interact with MySQL, but for the purpose of this tutorial, I will be giving examples from a terminal interface using the mysql client program, which comes with the MySQL package.

Why use a terminal when GUIs of all shapes and sizes are available? Graphical interfaces may allow you to interact and manipulate data, but they often do so without letting you understand exactly what it is you’re doing. Typing the commands in a terminal session will help you not only learn the SQL syntax but understand how and why you’re doing what you’re doing. Knowledge of the SQL syntax will give you a better understanding of how to use PHP to manipulate SQL data. And what are you going to do when you land that dream project you spent months pitching only to learn you aren’t going to have access to a GUI? Over-reliance on GUIs is not the monkey way. Don’t let the command line intimidate you.

To take full advantage of the organization a database offers, it helps to sit down ahead of time with a pen and piece of paper (made from trees … or even better, recycled material … like in high school) and plan out your database. Databases are simply means of organizing information into tables. Tables are made up of columns and rows. Typically each vertical column is some category of information and each horizontal row is an individual instance of that information. Too abstract? Think of your monthly bank statement. If it’s anything like mine, it’s a simple table (full of zeros). Across the top are column definitions (check number, payee, amount, et cetera). Reading horizontally, each line contains values that are given meaning by the column they’re in. MySQL databases work very much the same way. We create tables, define columns, and enter rows of data. The main difference is that our information will be searchable by any criteria we define.

Unfortunately, those MySQL searches cannot be run directly from your browser. Thus, while we can enter the commands and search in our terminal (or GUI bad monkey, no banana), we can’t write some HTML code and duplicate those search results. But that’s OK, because there are other languages that can duplicate them. The two main languages used in a database search, or query, are PHP and Perl. For our purposes, which will be to deliver Web content to Flash, we’re going to use PHP. PHP was developed to specialize in database connections on the Web.

Chances are many of the sites you visit everyday are generated using PHP. You’ve been seeing the benefits without ever knowing what was going on behind the nicely formatted information you receive. How did PHP give you your information without you even knowing it was doing so? If you view the source of a PHP page you will only see HTML. Where the heck is the PHP and how does it do what it does? We’ll get to that part a little later. Right now let’s dive into MySQL and feed it some information.

Using MySQL

Databases are serious stuff my friends, and databases call for serious examples. When I think of seriousness and integrity on the Web, the first thing that comes to mind is the weblog. Ahem.

Anyway let’s suppose you’re one of the two people who don’t yet have a blog. Or even better, let’s suppose you want to create a weblog that’s a little different from the endlessly repeated templates you see everyday. Let’s suppose for a moment that you want to create your very own Flash-based weblog to tell the world about your ever-growing collection of florescent feather boas or your serialized, Charles Dickens-style Great American Novel (Great British Novel just doesn’t have the same ring, sorry). Whatever it is, you plan on telling the world something about it each and every day.

Well then, let’s think about this for a moment. Your user interface will be a Flash .swf file that will display content from your MySQL database. How do you want to organize your data? That’s really up to you. The simplest approach is the date-based model that most weblogs out there function on:one date, one title, one entry. Our simplest case calls for a table that contains the following information:

Flash_MySQL_chart1.gif

Before we run off to create this table, let’s first look at some potential problems with our mock-up (remember: measure twice, cut once). While this table meets our most basic needs, we might want to include a few other things. Let’s say for instance that down road, when we get more sophisticated with our site, we want to be able to search according to subject. Well, right this minute we aren’t going to bother with a subject column, but in the future we might want to add a whole separate table to contain metadata of this sort. To link a new table to this one we’d need an index column. So our mock-up should include an ID column which will cover us if we’d ever like to add to our database. We’ll also be using the ID column to simplify the process of finding the newest entry.

Flash_MySQL_chart2.gif

Alright, we’ve done all of our paperwork. Our database-to-be looks pretty robust, so let’s go ahead and create it.

Creating the Database

For this example, I’m going to assume you’re using the mysql client (note the all lowercase mysql, this denotes the client program that comes free with MySQL) in the command line. To fire up the mysql client, start up a new terminal window and login to your server using the format:/Path/To/mysql/ -h yourHostname -u yourUser -p. Enter your password when prompted. Now we’re going to issue the following commands (and don’t forget your semicolons):

mysql> CREATE DATABASE blog_db;

This command tells MySQL to create a new database named blog_db (you can give it whatever name you like, just adjust everything accordingly). Now switch to the new database.

mysql> USE blog_db;



You can verify that you’re actually using the blog_db by issuing a SELECT DATABASE() command which will tell basic information about our newly created blog_db database, namely, that it contains nothing. An empty database is a useless database, so let’s create a table. Your input will look like this:

CREATE TABLE blog_entries (



 id INT NOT NULL AUTO_INCREMENT ,



 date DATETIME NOT NULL ,



 title VARCHAR (30 )NOT NULL ,



 entry LONGTEXT NOT NULL ,



 PRIMARY KEY (id)



 );

OK what the hell is all that gibberish? Let’s walk through it one statement at a time.

The CREATE statement should be obvious:It creates a new table by the name of blog_entries. Next, we fill out our table. But we don’t want just a table, we want to define our four columns that we’re going to use to organize our data. So, “id” is the first column and will be an integer, defined by INT(). The NOT NULL statement simply means that each row must have a value for this column. As you can see, I’ve set the table up to require values for all the columns. If you need some rows of a table left blank, simply change the statement to NULL which will allow blanks, or “null values” in database speak. The AUTO_INCREMENT statement tells MySQL to assign a new value for each row and to increment that number each time we enter another row. In other words, the first entry will get a value of 1, the second 2, and so on. This way, we don’t have to keep track of what entry number we’re on; we let MySQL do it for us. This will allow us to always find the most recent entry by querying for the largest number. This information will come in handy when we start writing queries in PHP.

Next is DATETIME, a predefined format that will have MySQL stamp each entry with the current date and time. VARCHAR, on the next line, is short for variable character. This means that entries in the title column will be filled with characters of varying length. I chose to cut them off at 30 characters, but you can specify any number you’d like (up to 255). The last datatype we’re using is LONGTEXT. LONGTEXT allows up to 4,294,967,295 characters. That’s over one million words per entry — possibly overkill for our purposes, but I thought we’d play it safe. You could also use MEDIUMTEXT if you’re feeling less wordy than me. PRIMARY KEY is somewhat more complex, and I’m not going to explain it here, other than to say that if a column is AUTO_INCREMENTed it must be a primary key.

Whew! Alright, just to make sure that MySQL created things the way we expect, issue the command DESCRIBE blog_entries and you should see the following:

Flash_MySQL_chart3.gif

Notice the date format is year-month-day. Putting the year first takes some getting used to, but this is how MySQL handles dates — you’re just going to have to keep that in mind. We’re good monkeys, flexible and capable of adaptation.

Entering Data

Okay, we have our table all set up. Now we just need to enter some data. Entering information into MySQL can be very laborious when done by hand. For this reason, extensions were written to read from tab-delimited text files. However, since our table is pretty simple, and it’s unlikely that we’ll be entering more than one row at a time, I’m going to go ahead and enter things the old fashioned way. When your data gets more complex, you’ll want to investigate how to enter from external files. A good starting point would be the MySQL site.

 INSERT INTO blog_entries VALUES



 (



 '',NOW(),'my first blog entry','your text here'



 );



The main thing to notice here is that the first value is blank. The value for the ID column tells MySQL to go ahead and insert the auto increment number, in this case, the integer one. The NOW() function gives us the current date and time. If you wanted to forge an older or newer date and time, you could enter them by hand in the format discussed earlier (YYYY-MM-DD 00:00:00). Double check to see that everything looks the way it should by issuing your first real query:

SELECT * FROM blog_entries;

and you should see:

Flash_MySQL_chart4.gif

I went ahead and inserted a few more entries to give us more to play with when we get to the Flash end of our blog. See, that wasn’t too bad was it? Alright, maybe a little, but take a deep breath and move on to the PHP anyway.

Add a Dash of PHP

When a user arrives at our blog, we want the following two things to happen:First, we want the latest entry to be displayed. Second, we want to display a list of past entries, which will be links to our older postings. I’m going to make the archives show both entry and date so you can see how both methods would work. The information we need to retrieve is stored in our table named “blog_entries”, which is part of the database named “blog_db”. The Flash piece we’re going to build will need to query the database for that information, and to do so, we will utilize a PHP file.

If you’re familiar with Flash ActionScript, PHP should look familiar to you. Like ActionScript, PHP is a scripting language and therefore has a similar syntax. The one big difference from ActionScript is that PHP variables must always begin with a $. Just like in Actionscript, we’re going to write some functions and use them to interact with our blog_db database. The reason I spent so much time on the MySQL section is that the commands we used there to see the entries in our database are the same as the ones we’ll use in our PHP scripts. For our purposes, PHP is going to act as a middleman, sending queries from Flash to MySQL and then returning results back from MySQL to Flash.

So, diving right in. Fire up your favorite text editor, create a new document and save it with the extension .php. Now enter in the following code. (Your browser will probably wrap this code, so try copying the raw source)


<?php

mysql_pconnect ("yourHost", "yourUserName", "yourPassword");



mysql_select_db ("blog_db");

$qResult = mysql_query ("SELECT * FROM blog_entries ORDER BY id DESC");



$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']."&"."&date".$i."=".$row['date']."&"."&entry".$i."=".$row['entry']."&";

}

echo $rString."&";



?>




Now let’s go through it line by line. The first few lines are PHP’s way of connecting to our database, just like what we did earlier in our terminal program. How does this work you ask? PHP, like ActionScript, has a bevy of built-in functions, and one of them is mysql_pconnect(). So then it’s just a matter of plugging in the right parameters for our database. The mysql_select_db() function is just like the USE function we typed earlier in our terminal. It tells MySQL which database we want to query. Then we have these lines:

$qResult = mysql_query ("SELECT * FROM blog_entries ORDER BY entry_id DESC");



 $nNows = mysql_num_rows($qResult);



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


Here we’ve created three variables (remember variables in PHP must begin with a $). The variable $qResult is our actual query to the database (qResult is short for Query Result — get it?). The parameter is a string which tells MySQL to select all the information in our blog_entries table and return them in an ordered list. The ORDER BY command will order our result by the id column, starting with the largest number and reading down in descending order. This means that the rows of data returned start with the newest entry and read down to the oldest. Now you can see why we used the auto increment feature when we set up our table.

The variable $nRows uses the function mysql_num_rows() to store the number of rows in our database (nRows is short for number of rows). We need to know how many rows we have so that we can write a loop to cycle through and read each row. Then we will write some code to format each row into a URL encoded string, which we will then pass on to our Flash movie. $rString is the variable we will use to pass the data to Flash (rString being my shorthand for Returned String). Next comes the loop to read all the data in our table.

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



 	$row = mysql_fetch_array($qResult);



 	$rString .="&id".$i."=".$row['id']."&"."&title".$i."=".$row['title']."&".



 	"&date".$i."=".$row['date']."&"."&file".$i."=".$row['entry']."&";



 }



 echo $rString."&";



“Oh my god!” you scream, “What is that string of nonsense?” Good question.

The first thing we’ve done is set up a for loop. This loop says:for a variable $i, which is initially equal to zero, and for as long as $i remains less than our variable $nRows, increment the variable $i by one each time you perform the following loop. We do this so that PHP will loop through each row of our MySQL table and give each row its own unique number. This way when we pass the information to Flash, we can separate each entry and send it to the appropriate place.

Next, we’ve taken advantage of another built-in PHP function (don’t worry — it won’t sue). We’ve created the variable $row and assigned it the function mysql_fetch_array(). Our parameter is the variable $qResult which we defined earlier as our query. This function takes the long string of information that is our variable $qResult and breaks it into an array. The new array contains all our table columns and the rows of data. Using the $row array we can access each element by name. Let’s just pause for a moment and consider that there are brilliant programmers out there who made this possible with one simple line of code.

Done pausing? Great, let’s move on.

Strings, Sealing Wax, Fancy Stuff

OK now let’s concentrate on that really long and confusing line, the meat of our for loop. It turns out that Flash has to receive external information in the form of a URL-encoded string (actually, if you install Flash Remoting on your server, it can receive information as straight record sets, but Flash Remoting is beyond the scope of this article — visit the Macromedia site if you’re interested in learning more). The string our Flash movie will be looking for is a bunch of incremented variables squeezed between ampersands. If we include linebreaks to make it more readable it looks like this:

&id0=1&



 &title0=myfirstEntry&



 &date0=2003:10:10 03:30:02&



 &entry0=some text&

The variable $rString in our PHP code contains all the necessary formatting syntax to ensure that Flash gets this string. The PHP code looks confusing because we’re concatenating (joining together) strings (everything in quotes) with elements in our $row array.

Let’s break it down some more. In PHP we write (I’ve put extra spaces between each element to make it easier to follow):

 $rString.=  "&id".  $i.  "=".  $row['id'].  "&"  et cetera...




$rString is our PHP variable which will get passed to Flash. We’ve assigned it to a big long concatenated string of smaller strings and elements of the array $row. In PHP the . (dot) operator is used to concatenate elements of a string. “&id” starts our string off and gets joined with the variable $i. Now the variable $i is the incremented element in our loop, so it will be an integer. Because we set $i=0 in our loop, the first time through the loop it will be 0, the second time it will be 1, and so on. For this reason, each row in our MySQL table has a unique identifying number. We then join the $i variable with an equals sign. So far we have given Flash the following information:

 &id1=

Now we assign this newly created “id” variable an element in our $row array ($row[id]). Essentially we’re writing id=id, pretty simple right? The first number in our MySQL column was 1, so the first time through our loop we return a 1. We then tack on another string so that this element of our big string (the one being passed to Flash) is enclosed in ampersands the way we want it to be. All Flash is going to see is:

 &id1=1&

The rest of our variable $rString does the same formatting for the rest of our columns so that we can pass Flash our completed string which will look like this:

 &id0=1&&title0=myfirstEntry&&date0=2003:10:10 03:30:02&&entry0=some text&



Now for the final and really easy bit of code:

echo $rString."&";



 ?>

Since Flash is going to read the results of our PHP code, we have to make sure that we use the echo command, to tell PHP (when it gets done processing all the things we’ve told it to do) to display the completed string shown above. Echo is the same as print. In fact, you can use the command print if you so desire. The ?> simply closes our PHP code just like  /html¢ would close an HTML document.

To test and make sure everything is working, upload the .php file to the server and point your browser to its location. You should be greeted by a stark white page with unformatted black text that reads:

 &id0=1&&title0=myfirstEntry&&date0=2003:10:10 03:30:02&&entry0=some text&



Okay, you’re done. With PHP that is. Time for a break. Crack your knuckles, lean back, smoke ‘em if you got ‘em, and relax. That was some terribly complicated stuff you just waded through.

A quick note about security:Did it make you nervous to type your password in plain text at the beginning of our PHP file? It should. Granted, PHP is a server-side technology and there is no easy way to view this file as we’re seeing it. But that doesn’t mean it can’t be done. A more secure method of sending this information is to use an include statement. What we do is take the mysql_pconnect statement and move it to a separate file. Name it connect.inc, and now move it to a location on your server where the general public can’t access it. Then change the mysql_connect statement above to read include (“connect.inc”). Also, don’t forget to tell PHP where the include file is. We need to edit the PHP initialization file to point to the directory in which you just placed the include file. Typically, the initialization file lives in the /usr/local/apache/php directory. If your server isn’t running Apache, refer to your documentation. Not only is this method more secure, it saves you from having to type the whole connect statement every time you write a new PHP document for this database.

Now rest up and get ready for lesson 2!

From the series

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