'Tutorial on How to use PHP/mySQL interactions

[ForumNewbie]

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.

[ForumNewbie]

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 (http://www.flashkit.com/board/showthread.php?t=515869&highlight=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) (http://www.morgan-multinational.com/SQLTOOL/sql_console.html) or this (For the new version in 3dfa version 4.6). (http://www.morgan-multinational.com/SQLTOOL/sql_console_4_6.html)

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:

[ForumNewbie]

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.

[ForumNewbie]

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
$_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.

[ForumNewbie]

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.

[blanius]

Thanks Morgan, good stuff. I made it sticky!

[ForumNewbie]

Out of interest... Has anyone got this to work yet? It's gone awful quiet :)

[ConnELITE]

My computer crashed so I just started, uhh yeh where you say

"Firstly, save the SQL console swf, the html file and the generic PHP script onto your PHP/mySQL enabled server. "

wat do you mean by generic php script?

[ForumNewbie]

I amended the text to make it more clear. The 'SQL Tool Script.php' script was described as a 'generic script' as it can do anything for any flash file, and isn't specific to the SQL Tool movie, but the full filename is probably easier to follow.

I'll stand by for further questions ;)

M.

[ConnELITE]

yeh I had to go away for the weekend so I'll probaly finish this by sat I hope, Thanks so much btw! Ill post if I have questions.

[purplemadness]

I was looking for this tutorial, could'nt find it..
Now i found it, it is even in the 3DFA board :D

[ConnELITE]

i fianlly have time *sits back with hot chocolate and begins*. Yay!

[ConnELITE]

um yeh mone didnt work, i have mysql and everything...hmmmm how can i find out if my server allows that stuff? and is there a way i can make it allow it? here check it out http://www.deerg.com/sql_console.html

I talked to my host and hes convinced it does allow it, where should i save the files btw? I just saved them under public html. does that mater?

oh and i dont own the server i just have an account on it.

[ForumNewbie]

Hi Brent,

I took a look, and since you're getting no responses appearing from the PHP script at all, we don't have many clues to work from.

(Compare by going to your console and mine http://www.morgan-multinational.com/sqltool/sql_console.html in 2 windows, and simply typing in 'show databases' in the command box. I get a load of error messages indicating password problems, you get no response).

I tried to call your PHP script directly, by typing in the URL I would expect to find it at: http://www.deerg.com/SQL Tool Script.php

It is giving responses, which suggests that the PHP script is running and the PHP server is functioning. Check that in your .movie file, the path for the php script matches where you've saved it.

Next thing to check: Does your PHP server have a setting to turn off error messages? If you can turn these on, we may be able to see what the error is. My guess is that it's something to do with the user ID, password or hostname at the top of the console, but without an error message, we're a bit short of things to go on.

I'll have a look at a couple of other things, but am away from home at the moment, so it might take me a little while to investigate.

M.

[ForumNewbie]

I did some more testing.

If you compare my console to yours, the following test gives different results:

Open the 2 SQL consoles in 2 windows (Yours and mine).
In the command box, simply type 'user test' and hit enter.

In my version, the user field at the top left corner is updated. On yours, it isn't. The presence of a working mySQL connection would make no difference to this test, so I conclude that the swf file is not talking properly to the PHP script. As mentioned before, check the paths in the movie to ensure it is looking at the right filename in the right path for the php file. Perhaps I made an error in the file I posted. I'll check when I get home in a couple of days.

Let me know if this helps.

M.

[ConnELITE]

Yeh I didnt even kno I had to change the path, thanks Ill go try it.

[ConnELITE]

yeh it works now.

[ConnELITE]

Hmm ok now when I use my user name and password (which works when I sign in to cpannelx) isnt working for the mysql database. It gives me the ()mysql connet access denied deerg@localhost and then a bunch of other stuff. I tried typing the "create database" or watever and it came up "queary failed". Any help? all I can think of is that my password or username is wrong but It works when i go to the cpannelx.

[ForumNewbie]

Check that there aren't any strange characters in the password or anything.
(Colons, comma's apostrophes etc could cause problems. If so, change your password to something with just numbers and letters in it)

I know it seems obvious, but check whether you're entering in CAPS.

Also, if you type the password directly into the box at the top of the screen, do NOT type enter in that box. It gets treated as a carriage return, and therefore a part of the password. That might be why it thinks the password is wrong. If in doubt, highlight the whole box first then delete everything in it before typing in just the password, and no carriage return.

To test further, I'm afraid I'd need a user ID and password on your mySQL server. If you feel comfortable, you could PM these and change them after I've taken a look. If not, then you're probably better off just experimenting with the password. It's likely to be something really straightforward.

M.

[ConnELITE]

hmm I'm not sure what I did but it works now. I think I was pressing enter after I typed the password or something. But now when I type create database usertrack it says query failed. I tried changing the name to create database cool but it still said query failed. Any ideas?

[ForumNewbie]

I wonder whether your server allows you to create databases, or whether you're tied to the ones already there? Try the command 'show databases' and see what databases already exist. Try to create a table within an existing database as a test.

If you use an existing one, the only thing is that you'd have to change some references to 'usertrack' in the PHP script above.

M.

[ConnELITE]

hey thanks for the tutorial, I completed most of it, (had to make database using cpannel though). Now Im experimenting with some stuff. First of all I made a php script that send info to the database. it has the $data=$_POST['data] in it and then some other stuff. So I made a flash with a variable called data. Now how do I execute the php script? Do I just say root.load.'phpscript' or watever? Please help.

[ForumNewbie]

Well. What do you know !

I just rebuilt another PC as a PHP and mySQL server, so decided to test the tutorial again, and apart from PHP/mySQL installation problems, the tutorial worked OK. Now that I've tested it, has anyone else had any luck, or does anyone need help?

M.

[ForumNewbie]

hey thanks for the tutorial, I completed most of it, (had to make database using cpannel though). Now Im experimenting with some stuff. First of all I made a php script that send info to the database. it has the $data=$_POST['data] in it and then some other stuff. So I made a flash with a variable called data. Now how do I execute the php script? Do I just say root.load.'phpscript' or watever? Please help.

Hi,

The syntax in 3DFA is :

root.loadVariables ("phpsript.php","POST");

This will post the existing variables from swf into the PHP script, and a few seconds later, the PHP script will return any variables back into the swf file.

Be careful however:
Depending on how your PHP server is set up, it may not allow you to access the variables directly. (If Global variables are turned off on the PHP server as a security precaution). In this case, your PHP script needs to start off with: $variablenameinPHP = $_POST['variablenamefromswf'];

To bring the variables back from PHP, have the PHP write some text at the end like: echo "&variablenameiwanttoseebackinswf=".$variablenamefromPHP."&";

A few seconds after the loadVariables command is executed, you'll then get a value in the swf for 'variablenameiwanttoseebackinswf'.

:)

Morgan.

[ForumNewbie]

All,

Just a note to let you know that this tutorial does not currently work in the new version 4.6 beta 3. If anyone can work out why, then I'll be eternally grateful !

If it helps, then I think it has something to do with variables being handled differently.

Cheers,

Morgan.

[ConnELITE]

I haven't tried this specific tutorial in the new version, but all of my games with sql/php interaction still work. It doesn't work in the preview menu if that's what you mean. Also, sql was recently updated and you might have to ask your host to take off the extra protection they added.

[ForumNewbie]

Hiya Brent,

This kind of movie never works in the preview mode, so that isn't really the issue. The old versions still work on the same server, and I haven't updated mySQL, so I know it has to be something to do with the new version. There are a couple of other indicators that suggest it's something to do with handling of variables though, so I'll keep working on it. I generally hold all my variables as root.variable but it seems this doesn't work quite the same as it used to.

I guess I must have used sloppy programming originally, and I'm suffering from it now :)

A simple example of the problem might be the fact that the help button at the top right no longer works. (ie. When you click the help button, no text appears). Also, the scroll bars don't seem to work anymore. Taking them off seems to help some issues, but not the actually loadVariables one. I'll keep working on it though, and hopefully provide an updated version shortly.

Cheers,

Morgan

[ConnELITE]

oh yeh, now that you mention it, I noticed that I could'nt get one of my old movies to work. I haven't noticed anything wrong with the root. It may have something to do with your functions Ian. After you taught me enough, I kind of developed my own style and have removed the waiting and sending functions and replaced it with scripts that depend on the php more then the flash. Maybe check those.

Also, do your movies work when exported in the old 3dfa? Maybe it has something to do with the flashplayer update.

[ForumNewbie]

Thanks for the pointer. I'll take a look at the functions later although the problem with the helptext shouldn't depend on any functions, so I still think it's a variable handling issue.

Although the movie does grind very slowly in the new player, if I export with the old version, it does still work in the new player, so although it may be related, I don't think that's the whole problem.

On a positive note, the new functionality with loadVars where you can monitor whether the variables have been loaded yet, should make this kind of movie much more efficient and more robust in the long run.

Fingers crossed I'll work it out soon.

Cheers,

Morgan.

[Seas_Comander]

Suppose I had the variable score and topscore in a movie.
If score > topscore, can I POST the topscore to a file?

[ForumNewbie]

You certainly can. That's exactly the principle in my 10 pin bowling game and darts games. It's just a question of carefully writing the SQL statements you want to run.

If you manage to get through the tutorial and have understood the principles, then give me a shout if you need some help with the fine details.

Cheers,

Morgan.

[ForumNewbie]

Hiya Folks,

Just a note to let you know that the SQL Console has now been rewritten to work with the latest version of 3DFA. (Version 4.6)

I've made a few enhancements to it as well, so that it works a bit more efficiently, and added a 'debug' button for looking into errors more easily whilst getting things started. There is a new php script present in the zip file attached to this thread, so be sure to use this with the new version.

Also, I have worked out how to set SQL security permissions now, so I have set up a dedicated user ID on my SQL server in case anyone wants to play with my hosted example movie. The user ID is called 3dfa, the password is 3dfa and the only database it is allowed to access is also called 3dfa. You should have access rights to do anything in this except change user permissions.

Feel free to try this, and let me know if you experience any problems.

If anyone needs the old version for any reason, drop me a PM.

Best regards,

Morgan.

[ForumNewbie]

Out of interest, has anyone tried this tutorial in any of the new versions?

I've been a bit busy recently, but plan to check and update this tutorial soon.

Cheers,

M.

[ConnELITE]

I haven't tried this specific tutorial but I was able to get php connections working fine in 4.9.1 I think it was. I would really doubt that the movie in this forum would work though. I have had really bad compatibility issues with old php movies but I oculd never really isolate why. Are you having any troubles? I'm pretty busy with xml socket, but otherwise I would test it.

[phillong]

I am curious if anybody had any luck with getting data from an sql database, into a flash movie, via a php script, with the latest version of 3D Flash?

My php script does return my information as can be seen by clicking the below link.

http://www.carbonst.com/yahtzee1.php

What would be the syntax to load these into a movie?

The latest version's helpfile shows:

my_vars = new LoadVars;
my_vars.load ("url");

Then below that example it shows:

load (url)

The top example shows "" around the url and the bottom example does not. The top example shows a ; after the command, but the bottom example does not have the ;

I have tried both examples with no luck. But that does not mean that I am not doing something else wrong. :-)

I was just curious if anybody has gotten the variables into a movie with the latest version and if so, with what syntax.

Thanks in advance,
Phil

[ConnELITE]

Try using loadVariables(url, method). The syntax for that is something like...

loadVariables ("http://www.carbonst.com/yahtzee1.php", "POST");

That's the only one I've ever used or gotten to work, but I'll experiment with the LoadVars object a little more. If you're still having problems, I recommend starting a new thread so we don't crowd this one with post.

[ForumNewbie]

Please note that due to financial problems at my old Domain Registrar, my domain name of morgan-multinational.com has been poached. I am in the process of migrating all data to www.morganmultinational.com (Without the hyphen).

This may take some time, but I will have this data working again soon.

Best regards,

Morgan.

[man_id_unknown]

Using the LoadVars object, I've been able to send data to a PHP file. I've simply included the variables within the URL. However, how do I retrieve data from a PHP file?

[man_id_unknown]

Inspired by ForumNewbie and through experimentation, I've learned how to send and recieve data from PHP using 3DFA.

3DFA -> PHP

//Variables
loadvars_php = new LoadVars;
url = "http://www...";

//Setup loadvars_php
//User-defined PHP variables
loadvars_php.var1 = "any text";
loadvars_php.var2 = "any text";
loadvars_php.var3 = "any text";
loadvars_php.result1 = "";
loadvars_php.result2 = "";

//Callback function
loadvars_php.onLoad = function ()
{
//Variables
buffer_result1 = "";
buffer_result2 = "";
...

//Retrieve PHP result
buffer_result1 = this.result1;
buffer_result2 = this.result2;
...

}

//Execute PHP
loadvars_php.sendAndLoad(url, loadvars_php, "POST");



PHP -> 3DFA

<?php
//Retrieve 3DFA variables
$buffer_var1 = $_POST['var1'];
$buffer_var2 = $_POST['var2'];
$buffer_var3 = $_POST['var3'];

//Return result
echo("&result1=any text&result2=any text...");

?>

[man_id_unknown]

Keep in mind, (in my limited experience) I have learned that 3DFA requires PHP to precede its returned variables with an "&". As shown in the above example. However, KoolMoves, (and perhaps other Flash IDEs) requires PHP to not precede its returned variables with an "&".

[gerard_pp]

wow, thanks for this weasome post!!!

ok, here's my question: knowing that i can send and retrive information (variables) from flash to php and vic. how can i stablish a dinamic comunication between flash and php where i can see an element movement that everyone logged in can see it too?

this is the way i think i can do it (in fact it works, but very very slow):


onClipEvent(load){
function positioning(){
element._x= recivedata.newposx;
element._y= recivedata.newposy;
}
}

onClipEvent(enterframe){
senddata.positionx = element._x;
senddata.positiony = element._y;

senddata.loadAndSend("myphp.php", recivedata, "POST");
recivedata.onLoad = positioning();
}


please help...

[man_id_unknown]

Yes, that would work slowly, because you are attempting to use LoadVars() as a socket connection, (which it is not). LoadVars connections close once theire tasks are complete. Each use of LoadVars opens a new connection. The lack of a constant connection is what is causing the lack of speed. However, as of now, I'm not certain how to use sockets in 3DFA.

[gerard_pp]

im triying to make an rpg multiplayer game on flash, and i want to know. is there a way to make this, conecting to a data base using only php/mysql and loadVars()?

i was reading on a site (cant remember where) that i can make it work if i reduce at minimum the amount of information requests to the server.

in theory, it sounds it sounds very logical, but when apliying to practice i ended up with tons of commands... :(

so, recalling... is there a way to make it work with this method?

[man_id_unknown]

Flash ActionScript offers other types of connections. Such as "local connection", "net connection", etc... I have only been using flash for a few months, and never have I used it for an online game. Therefore, I have little experience with connections and Flash. Nonetheless, I have programed sockets before, and so I'm confident your problem is with loadvars. It just isn't intended for a constant connection.

What you are attempting to do is equivalent to trying to create a cartoon with each frame in a different html file. Each frame would have to be loaded. Which would obviously be slow. If no one on this forum has more info for you, try googleing "flash sockets",r "flash connections" , "actionscript connections", etc... I know they exist.

[gerard_pp]

thanks bro!!!!

[ForumNewbie]

Hi,

Glad to see this thread's still being useful, even though my server's still waiting for me to find time to rebuild the tutorial !

The solution to multiplayer games in 3DFA is definitely to use a socket server.

There is another thread on this Forum where I was able to use 3DFA to build a movie where I could move the mouse on one Flash movie on one client, causing a pointer to move on a Flash movie running on another client. The performance was actually really impressive. Unfortunately, until my server is reconfigured, it won't work, but you should be able to find the thread and download the source and make it work on your own machine. If you find the thread and the link is to my server at morgan-multinational.com, just change the address to morganmultinational.com. (My hosting company went bust, and the site got hijacked by some site-sitters from Jamaica so I'm having to move everything). The content has moved from a Windows box to a Linux box, so the SQL data and XML socket server might not be present/installed yet.

Hope this helps a little,

Morgan.