Introduction

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 good understanding of Flash 5 Actionscript.   What this tutorial basically does is to show you how to set up a table in a mySQL database, then using PHP (or asp with a few minor changes) manipulate that table and interact with Flash.  The first part of the Tutorial shows you how to create a unique user ID in the database, In this case it will be the persons name.  With a few minor changes however you'll be able to make it a secure password protected user login.  The unique user ID is required because in the next part we'll be saving the position of a couple movie clips and some of their property's.  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.  With a couple modifications you can turn that into a guest book or anything else you can think of.  For a working example of this tutorial please goto http://www.snowvids.com/Ex1/Ex1_SaveMovie.html. This is a much more of a hands on tutorial so it's helpful if you follow the scripts and Fla source included, in addition to viewing the working example at the same time. 

It is recommended that you read over the PHP scripts first. These have all been extensively commented.  In PHP the # symbol is a comment.  Also it may be best to try to follow the code in the included Flash movie along as well.  

Let's Get Started - Part I - setting up the mySQL database and relevant SQL commands

You must have a mySQL server set up by your host or on your local machine.  I'll be using the most basic shell commands to set up the table. However their are GUI's out there to make this part easier.  Your host may already have one set up for you to use. Make sure to read all the documentation your host has available on using mySQL on their servers - some may be different.  But it should all basically be the same.

Once your database has been set up. Open up your shell account. In most cases you can use Telnet to get to your account.  From the telnet command prompt - type in mysql -p YourDatabaseName Then you will be prompted for a password. Type in your 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 exactly 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.

Change the Column Name:  mysql>alter table saveMovie change Name SomeotherName varchar(30);  (This just changes the name notice the syntax - after change enter the old Column Name followed directly after by the New column name and definition.  

Delete one of the Columns: mysql>alter table saveMovie drop Name; This just deletes the column that you had previously named "Name".  

Select and view everything in the Table: mysql>Select * from TableName;

Delete the whole table and start over: mysql>drop saveMovie;

Short List of Column types (not 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 cur off):

varchar (Number) - A variable text type - in my opinion the easiest and most flexible to use when first starting out. Maximum is 60 characters Long. 

char (number) - A fixed character type column.

int (number) - A fixed integer type column. Default is 14 if you leave it blank. 

text or blob - use either when you want to enter a large amount of text.  

Part II - Setting up the PHP 

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 often times the $DBhost variable is slightly different then "localhost" - Sometimes it can be "localhost.yourHost.com".

<?
$DBhost = "localhost";
$DBuser = "UserName";
$DBpass = "DatabasePassword";
$DBName = "DatabaseName";
$table = "saveMovie";
?>

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: 

mysql_connect($DBhost,$DBuser,$DBpass);
@mysql_select_db("$DBName");

 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 sercurity precautions where left out. These are not necessary but you might want to include them if you have any type of sensitive data.

User Registration

PHP used in the first Registration section

$query = "INSERT INTO $table (Name, Object1, Object2, Object3, Comment) VALUES ('$RegName' , '', '', '', 'Hello')";
$result = mysql_query($query);

 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 try's 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. 

Flash Actionscript Used in Registration

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 this Code. 

on (release) {
 Status = "Beginning registration Process... Please Hold";
 loadVariablesNum ("Register.php?RegName="+RegName, 0);
}

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. 

User Login

PHP used in Login (Login.php)

$query = "SELECT * FROM $table WHERE Name = '$Name'";
$result = mysql_query($query);
$numR = mysql_num_rows($result);
#If the number of rows is not equal to one then it prints out an error statement - Name not in Database. 
 if ($numR == 1) { 
 print "_root.Status=Success Login Complete&_root.CheckLog=1";
 }
 else {
 print "_root.Status=Failure - Your name was not found - Please register"; 
 }

The first 2 lines of this code preforms 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 ie it's now equal to 1.

Actionscript used in Flash for Login

on (release) {
 gotoAndPlay(2);
 Status = "Beginning Login Process.. Please Hold";
 loadVariablesNum ("Login.php?Name="+Name, "0");
}

 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.

if (CheckLog ne "") {
gotoAndPlay (4);
}else {
gotoAndPlay(2);
}

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. 

Loading the Variables

PHP used the Loading Variables Part (Load.php)

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. 

$result = mysql_query("SELECT * FROM $table WHERE Name = '$Name'");
 ##This sets the variables from the Database
 $Comment = mysql_result($result,0,"Comment");
 $Object1 = mysql_result($result,0,"Object1");
 $Object2 = mysql_result($result,0,"Object2");
 $Object3 = mysql_result($result,0,"Object3");
 if ($Comment == "") {
$Comment = "Hello $Name";
}
#This next bit prints out the value's of the Comment and the positions
#of the three objects whose properties you want to save. This is the line that Flash will read into the movie. 
print "Comment=$Comment&$Object1$Object2$Object3";

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.

Actionscript used to Load the Variables into the Movie

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

// Loads the Variables
loadVariablesNum ("Load.php?Name="+Name, 0);
gotoAndPlay (6);

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 3 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. 

if (Comment ne "") {
setProperty ("Object1", _x, xO1);
setProperty ("Object1", _y, yO1);
setProperty ("Object2", _x, xO2);
setProperty ("Object2", _y, yO2);
setProperty ("Object3", _x, xO3);
setProperty ("Object3", _y, yO3);
gotoAndPlay (19);
}
else {
gotoAndPlay (6);
}

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. 

Updating the Variables in the Database

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.

O1x = math.floor(getProperty(_root.Object1, _x));
O1y = math.floor(getProperty(_root.Object1, _y));
O2x = math.floor(getProperty(_root.Object2, _x));
O2y = math.floor(getProperty(_root.Object2, _y));
O3x = math.floor(getProperty(_root.Object3, _x));
O3y = math.floor(getProperty(_root.Object3, _y));

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 buy 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.

The PHP used in Updating the x and y positions along with the Comment (Update.php)

$query = "UPDATE saveMovie SET Object1='xO1=$O1x&yO1=$O1y&',  Object2='xO2=$O2x&yO2=$O2y&',
Object3='xO3=$O3x&yO3=$O3y' , Comment='$Comment'  WHERE Name='$Name'";
$result = mysql_query($query);
print "_root.UpdateStatus=Success Updated - It's saved $Name";

This part of the script updates and set's 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.

The Actionscript used for updating the variables

on (release) {
 loadVariablesNum ("Update.php?Name="+Name+"&Comment="+Comment+"&O1x="+O1x+"
 &O1y="+O1y+"&O2x="+O2x+"&O2y="+O2y+"&O3x="+O3x+"&O3y="+O3y, "0");
 _root.UpdateStatus = "Updating..";
}

This part is probably easier accomplished by just using the "Post" option of the loadVariablesNum function -which will load 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. 

Part III - Conclusion

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 area's of the web that html once ruled unchallenged - Until Now.

-Jeffrey F. Hill. aka Snowdude.

Don't forget to follow along in the working tutorial at:   http://www.snowvids.com/Ex1/Ex1_SaveMovie.html also you can see more complex examples of this and everything else at www.snowvids.com - I'll be doing tutorials on all the different parts of that site as giving it the much needed fix up.