A Flash Developer Resource Site

Page 1 of 3 123 LastLast
Results 1 to 20 of 46

Thread: 'Tutorial on How to use PHP/mySQL interactions

  1. #1
    A Senior Newbie - How Odd ForumNewbie's Avatar
    Join Date
    Mar 2003
    Posts
    590

    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.
    Last edited by ForumNewbie; 09-03-2006 at 03:55 PM. Reason: Update with new version of 3DFA
    Please note that my domain has changed to http://www.morganmultinational.com

  2. #2
    A Senior Newbie - How Odd ForumNewbie's Avatar
    Join Date
    Mar 2003
    Posts
    590

    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:
    Attached Files Attached Files
    Last edited by ForumNewbie; 09-03-2006 at 04:06 PM. Reason: Added the files from the other thread
    Please note that my domain has changed to http://www.morganmultinational.com

  3. #3
    A Senior Newbie - How Odd ForumNewbie's Avatar
    Join Date
    Mar 2003
    Posts
    590

    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.
    Last edited by ForumNewbie; 10-10-2005 at 07:31 PM.
    Please note that my domain has changed to http://www.morganmultinational.com

  4. #4
    A Senior Newbie - How Odd ForumNewbie's Avatar
    Join Date
    Mar 2003
    Posts
    590

    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";
            }    
        
    $_datedate("Ymd");
        
    $_timedate("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.
    Last edited by ForumNewbie; 10-10-2005 at 07:36 PM.
    Please note that my domain has changed to http://www.morganmultinational.com

  5. #5
    A Senior Newbie - How Odd ForumNewbie's Avatar
    Join Date
    Mar 2003
    Posts
    590

    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.
    Last edited by ForumNewbie; 10-10-2005 at 07:37 PM.
    Please note that my domain has changed to http://www.morganmultinational.com

  6. #6
    KoolMoves Moderator blanius's Avatar
    Join Date
    Jul 2001
    Location
    Atlanta GA
    Posts
    5,244
    Thanks Morgan, good stuff. I made it sticky!

  7. #7
    A Senior Newbie - How Odd ForumNewbie's Avatar
    Join Date
    Mar 2003
    Posts
    590
    Out of interest... Has anyone got this to work yet? It's gone awful quiet
    Please note that my domain has changed to http://www.morganmultinational.com

  8. #8
    Game Master ConnELITE's Avatar
    Join Date
    Apr 2005
    Location
    United States, DC
    Posts
    474

    Yeh

    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?
    BC

  9. #9
    A Senior Newbie - How Odd ForumNewbie's Avatar
    Join Date
    Mar 2003
    Posts
    590
    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.
    Please note that my domain has changed to http://www.morganmultinational.com

  10. #10
    Game Master ConnELITE's Avatar
    Join Date
    Apr 2005
    Location
    United States, DC
    Posts
    474
    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.
    BC

  11. #11
    Junior Member
    Join Date
    Apr 2005
    Location
    The Netherlands
    Posts
    15
    I was looking for this tutorial, could'nt find it..
    Now i found it, it is even in the 3DFA board
    N00b flasheR
    -_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_

  12. #12
    Game Master ConnELITE's Avatar
    Join Date
    Apr 2005
    Location
    United States, DC
    Posts
    474
    i fianlly have time *sits back with hot chocolate and begins*. Yay!
    BC

  13. #13
    Game Master ConnELITE's Avatar
    Join Date
    Apr 2005
    Location
    United States, DC
    Posts
    474
    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.
    Last edited by ConnELITE; 11-08-2005 at 07:56 PM.
    BC

  14. #14
    A Senior Newbie - How Odd ForumNewbie's Avatar
    Join Date
    Mar 2003
    Posts
    590
    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/...l_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.
    Last edited by ForumNewbie; 11-09-2005 at 06:41 AM.
    Please note that my domain has changed to http://www.morganmultinational.com

  15. #15
    A Senior Newbie - How Odd ForumNewbie's Avatar
    Join Date
    Mar 2003
    Posts
    590
    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.
    Please note that my domain has changed to http://www.morganmultinational.com

  16. #16
    Game Master ConnELITE's Avatar
    Join Date
    Apr 2005
    Location
    United States, DC
    Posts
    474
    Yeh I didnt even kno I had to change the path, thanks Ill go try it.
    BC

  17. #17
    Game Master ConnELITE's Avatar
    Join Date
    Apr 2005
    Location
    United States, DC
    Posts
    474
    yeh it works now.
    BC

  18. #18
    Game Master ConnELITE's Avatar
    Join Date
    Apr 2005
    Location
    United States, DC
    Posts
    474
    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.
    BC

  19. #19
    A Senior Newbie - How Odd ForumNewbie's Avatar
    Join Date
    Mar 2003
    Posts
    590
    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.
    Please note that my domain has changed to http://www.morganmultinational.com

  20. #20
    Game Master ConnELITE's Avatar
    Join Date
    Apr 2005
    Location
    United States, DC
    Posts
    474
    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?
    BC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  




Click Here to Expand Forum to Full Width

HTML5 Development Center