Tutorial on How to use PHP/mySQL interactions - NOW UPDATED FOR VERSION 4.6
Hi,
For interested parties, a few folks have asked recently about a tutorial for creating interactions with databases.
Although I don't have time to do a full one at the moment, I'll share a few comments that I was going to make to Connelite, so that hopefully everyone can benefit.
The steps that are required to perform database interactions:
a. You need a server that will allow server side scripting, such as PHP.
b. You need a server that will allow database interactions. (eg. A mySQL server)
c. You will need to pass variables from your SWF file into the PHP script, so it can interact with the mySQL database.
d. You will need to interpret responses from the PHP script back into your swf, so that you can manipulate them to show what you like on screen.
I am assuming here that steps a and b are already complete, since these tend to be the tricky bits. Please don't ask me if you are unable to do these parts, since the forums for PHP and mySQL can answer better than I can.
Any questions about this later stuff, feel free to PM me. I'll make amendments to the thread as people point out what doesn't work quite right.
1 Attachment(s)
Step 1: Testing Connectivity with the SQL Console
Simple tutorial for how to do simple 'user tracking' into a mySQL database
In this example, we want to post a record to a database whenever a user views our Flash animation.
We need a table to post the records into.
We need a PHP script to write records to the table.
We need a SWF file to prompt the PHP script to write records to the table.
We need some way of looking at the records within the table.
Rather than performing the first and last steps manually, I suggest using my home made SQL Console. This is a simple front end for maintenance of your mySQL databases. I have attached the files to this post, and also posted the files for this in the thread here: SQL Console
Firstly, save the SQL console swf, the html file and the 'SQL Tool Script.php' file onto your PHP/mySQL enabled server. Open the html page in your browser using the full web address. It should look something like this (For the old version in 3dfa version 4.5) or this (For the new version in 3dfa version 4.6).
Enter your user name and password for the mySQL server in the 2 boxes at the top left respectively and enter a databasename for a database that you are allowed access to.
In the 'Current statement' box at the foot of the screen, type 'show databases' and hit enter. You should see a list of the databases already existing on the mySQL server. If not, then there is something wrong with the mySQL server set-up, and you may have to return to the beginning to work out why this isn't working.
PLEASE NOTE: If you want to test how this should look on my hosted server, use the user ID '3dfa', the password '3dfa', the databasename '3dfa' then use the 'show databases' command. It should show the 3dfa database and a system database called information_schema.
Assuming you got at least 1 database returned (Depending on your access rights, probably the system databases like 'information_schema' or 'mysql') then we are OK to continue:
Step 2 : Creating your database on the mySQL server
In the box at the bottom of the SQL console, type 'create database usertrack' and hit enter in the bottom box. It should return a result of 'Create Result 1' to show it has created 1 database.
Check it has worked by typing 'Show Databases' in the box at the bottom of the SQL console. Your new database should now be listed.
To select that you want to use the new database, type in 'use usertrack' in the box at the bottom of the SQL console. The database usertrack will now appear in the fourth box from the left at the top of the screen to indicate it is the active database.
To see any tables in the database, type 'Show tables' in the box at the foot of the SQL console and hit enter. You will see a blank screen as there are no tables created yet in this database.
In the box at the foot of the console, type 'create table usertrack (_ip VARCHAR (30), _date VARCHAR (30), _time VARCHAR(30), _page VARCHAR(255), _host VARCHAR(255))' and hit enter. You will see a result of 'Create result: 1' to indicate that the table has been created. This shows that you have created a table with 5 fields for capturing IP address, time of visit, date of visit, page visited, and the hostname of the visitor.
To prove that it has been created successfully, type 'show tables' in the box at the bottom of the screen and hit enter. Your new table should now be listed.
Prove that there are no records in the table by typing in the box at the foot of the screen 'select * from usertrack'. A blank screen will indicate that there are no records in the table yet.
You now have a database and a table ready to be populated, and we're ready for the next stage.
Step 3: Posting to your database upon each user viewing your Flash file
The next step is to have a record written to the database each time a user enters your website.
To do this you will need a PHP script to post the records to the database, and you will need a SWF file to call that PHP script.
First, create a PHP file on your server called 'trackuser.php' containing the following PHP commands:
PHP Code:
<?php
$_page = $_POST['_page'];
$_ip="$REMOTE_ADDR";
$_host=gethostbyaddr($_ip);
if ($_page == null)
{
$_page="$HTTP_REFERER";
}
$_date= date("Ymd");
$_time= date("His");
$dbconnection=@mysql_connect("localhost","user","password");
echo ($dbconnection);
if (!$dbconnection)
{
echo("No SQL SERVER AVAILABLE");
exit();
}
mysql_select_db("usertrack",$dbconnection);
if (! @mysql_select_db("usertrack") )
{
echo( "NO DATABASE AVAILABLE" );
exit();
}
$page="index";
$sql="insert into usertrack values ('$_ip', '$_date', '$_time', '$_page', '$_host')";
echo ($sql);
if ( mysql_query($sql) );
{
echo("<P>Update affected " . mysql_affected_rows() . " rows.</P>");
}
?>
NOTE THAT YOU MUST REPLACE 'USER' and 'PASSWORD' WITH YOUR USER ID AND PASSWORD ON THE mySQL SERVER !!!!
Now that the PHP script is complete, go back to 3DFA, and create a swf file that has the following text in the startup script:
urlstring="http://domain/trackuser.php"
_page="The name of this flash file"
loadVariables (urlstring,"POST");
Where the variable 'urlstring' is the full path for where the trackuser.php script is to be found.
Save this swf and html file to the server.
Open the html file from your browser, using the full web URL.
This will open the swf file. The swf file will use the starting script to run the PHP script. The PHP script will then take the variable '_page' and use it, along with a few other variables it will create, and post these to the usertrack table in the usertrack database.
Hey presto, you have logged your first visitor.
Step 4: Proving it has worked, and seeing who has been visiting your site!
Now that we have created a table, and posted a record to it, let's have a look and see who's been to see us !
Open the SQL console again on your website.
Enter your user ID and password in the top 2 boxes as before. Enter 'usertrack' in the fourth box.
Now type in the bottom box, 'select * from usertrack' and hit enter.
This will send an SQL statement to another PHP script in the background, retrieve all the records in the table and return them to your screen in a format so you can read who has been visiting your site.
If this has worked, then you're 80% there on mastering PHP/mySQL interactions and a good step forward to creating multiplayer games and such like. Once you've got this far, the only limit is your imagination!
So - I wrote this late at night working from memory - So let me know if anyone gets this to work. If it doesn't work, drop me a PM and I'll see if I can work out what I've written down wrongly.
Best of luck,
Morgan.
