This article helps explain how to use Macromedia Flash 5 in conjunction with PHP and a MySQL database. This demonstration will show you how to setup a simple login and registration form, then allow users to save movie clip positions and various properties in the database. When they log back in the movie clips are in the same position.

For this tutorial you will have to have some basic knowledge of PHP and MySQL, hopefully you won't need much though. It is also assumed that you have a decent understanding of Flash 5 Actionscript. This tutorial shows you how to set up a table in a MySQL database, then using PHP manipulate that table and interact with Flash. The first part of the tutorial demonstrates how to create a unique user ID in the database; in this case it will be the person's name. The unique user ID is required because in the next part we'll be saving the x and y coordinates of movie clips for each user. In this way a user can return to the (your) site at anytime and the movie clips will be in the exact same position that they left them in. I also added in a place for the user to add in a comment, which is also saved.

For a working example of this tutorial please goto http://www.flash-db.com/Ex1/Ex1_SaveMovie.html. All of the PHP code was kept as simple as possible. Also this is meant as an introductory or example of using Flash and PHP together. There are a lot more useful things you can do -- this was just a simple example I choose to describe. It's helpful to follow the scripts and Fla source included, in addition to viewing the working example at the same time.

You must have a MySQL server set up by your host or on your local machine. It is assumed that yourself or your server administrator has already created a database that you can use. Make sure to read all the documentation your host has available on using MySQL on their servers. I'll attempt to explain the easiest way to set up a table with your shell account. If you server doesn't allow the use of telnet they will have other methods available to you to create your table. You can also just use PHP to create your table once your database is set up.

Once your database has been set up. Open up your shell account. From the telnet command prompt type in "mysql -p YourDatabaseName". Then you will be prompted for a password. Type in your database password. You'll see some text appear after this, then the command prompt changes to read mysql>; (then Type) use Database Name. We will name the table "saveMovie" for this example. Then type in the following create table syntax as it appears below:

You can then use the describe table command to see what the table looks like.

Here are a couple other MySQL commands you may find useful from the shell.

(Note: if the data that you have entered into one of these definitions is larger then the amount you specified in the table it will be cut off.)

The PHP scripts will be better explained by reading the comments in the attached files. Just open up the PHP script with notepad or any other text editor to make changes.

The first thing we have to do is connect to the database. You will need to know your database name, account username, and database password. In order to keep this as simple as possible use an include file to list these variables. They will be the same for each one of your scripts. This is what is contained in the file Include.inc. One thing to note is that sometimes the $DBhost variable is slightly different then "localhost" -- sometimes it can be "localhost.yourHost.com" or "mysql.yourHost.com".

You will notice that at the top of each PHP script there is a line of code something like this: include ('Include.inc'); All this does is to include those variables in the script. It's just an easier way of keeping track of everything.

Next you want to make a connection to the Database:

You can see how the variables in the Include File are used here. I'm only going to go over in detail the SQL query part of each script. The rest you should be able to figure out by reading through the comments in the File. Also note that for simplicity some basic security precautions where left out. These are not necessary but you might want to include them if you have any type of sensitive data.

This will create a new entry in the database for a new user. The only value we have to worry about for now is the Name variable which will be set to the Name entered in the Flash Movie in the text field Named RegName. All other variables will be set later. The name column was defined as being unique when we created the MySQL table so there can only be one User with that Name. If someone else tries to register with that name they will not be able to. The second line of the above code is what actually performs the query. The only thing that's returned to the Flash movie at this point is a message to the user proclaiming that registration was successful or failed if the Name already exists in the database. You can see how this is done in the actual script.

All that is required in Flash at this point is to have one text field named RegName. Then either on a button or Frame command include this code.

I have an extra text field named Status, but this is optional. Notice on the LoadVariablesNum command I specifically pass the variable RegName to the PHP script. This is not necessary, adding "Post" will do the same thing. It's just sometimes easier to keep track of what's passed to a script in this way. After a user has entered their Name and hit the register button the LoadVariablesNum Command will call the PHP script, pass whatever was entered in the RegName Field to it and execute the statements.

The first two lines of this code performs the query on the database. It selects the record where the Name entered in the Flash movie is equal to a unique name found in the database. The next line of code (excluding the comment) checks to see if a record was returned. If a record is returned, then the Login is a success. Then it prints out the results back to flash. I also pass back another variable to tell the movie if the Login was successful. In this case, I named it CheckLog. The movie will keep looping the second and third frame until it successfully tests that the variable CheckLog is not equal to nothing, i.e. it's now equal to 1.

The most important thing to remember when retrieving any type of data from a php script to a flash movie is that it must return a file with nothing on it except text in variable/value format. For example, if the file you are returning has any type of html formatting it will cause an error. "<html><head></head>Status=Success&CheckLog=1" will not work, but "Status=Success&CheckLog=1" will. A good way to check this and to make sure the script is working is to type the url to the script in the browser window and review what is printed to the screen.

Also note that there are better ways to return variables to a specific level in a flash movie, printing out _root before the variable name works in this case but may not in others. It's important to remember that variables are returned to the movie clip that the request was sent from unless specified otherwise.

This is the actionscript on the Login Button. Initially the movie stops at the first frame of the root of the movie. After you hit the Login button it goes to frame 2 and continues to loop until it finds that there is a value for CheckLog. The Check part looks like the following.

That's it. After a user has successfully logged in we can load the variables for the first time. At first they won't have any values. So they come to rest at the default location.

The load variables part is not important the first time a user enters the room. It does become important when a user moves the objects then returns to the room at a later time. This is what loads them all into the same place as when the user left. It also loads a comment that the user can optionally leave. Here's what the basic script looks like. Again it is strongly recommended to follow along by also reading the php script. They are commented in much more detail.

The first line of code just selects the record in the database where the Name that the person logged in as matches the name in the database. After this, each column in the result record set is set to a variable. This basically just extracts the result you got from the query and puts it into variables that you can use. The if statement is necessary to make sure that the Comment variable contains some value. This will be explained later. The last line of code prints the results back to Flash. Notice how Object1, Object2, and Object3 are not separated by the & symbol. This is because the & symbols already exist in the database -- when you update the database these are included. Not necessary but just makes it easier.

In the 4th frame of the movie the following actionscript is attached to a frame:

What this does is to load the variables from the database and stick them into the movie. The variables that get loaded are the x and y coordinates for each of the three objects in the movie. After it makes the call to the script it goes onto Play Frame 6 of the Movie. We let the script play for a couple frames then check to see if the variables have been loaded. If they have been loaded we continue on in the movie. If not we continue to wait until they are. We do not need to check if they've all been loaded so we just check to see if the variable Comment has been loaded from the Database. Here's the script for the second check. Which brings us to the final stage of the tutorial.

Notice how that unless Comment has a value it will loop back to Frame Number 6. If Comment is equal to a value then it will set the x and y positions of the movie clips then continue and Play Frame number 19.

The last part of this tutorial involves updating the variables x and y position in the database as well as the user entered comment. First we need to use actionscript to get the x and y positions of the movie clips as they are moved around by the user.

This is done simply by using the getProperty function to return the x and y position of each movie clip. I also use the math.floor function so that the x and y positions are rounded to the nearest integer. This is not necessary but why have all those extra numbers in your database when no one will ever no the difference. This is repeated twice. Then on the last frame it continues to loop back to the first instance of this code so that they postitions are continually updated.

This part of the script updates and sets the variables from the movie to the database. Basically this takes the values that you obtained with the getProperty function and sticks them in the database. The print statement just lets you know that everything went successfully.

This part is probably easier accomplished by just using the Post option of the loadVariablesNum function -- which will post all the variables in the movie to the script. But I just attached them all onto the end of the url to the php script. Mostly because it's easier to test and you know exactly what is being passed.

Hopefully by reading over the source code and the php files you'll be able to better understand this tutorial. From these basic functions shown in this tutorial you should be able to manipulate the files and database into anything you want using Flash as the front-end. The possibilities are limitless and it is my hope that by sharing this with the rest of the Flash world that we'll start to see Flash take over in the interactive areas of the web that html once ruled unchallenged -- until now. (Ok -- I apologize for that last remark.)

-Jeffrey F. Hill

Don't forget to follow along in the working tutorial at: http://www.flash-db.com/Ex1/Ex1_SaveMovie.html. Also you can see more complex examples of this and everything else at www.flash-db.com.