Tracking your users with Ajax, PHP and MySQL

  • 15 favourites



10,071 visits, 29,362 views



This tutorial hasn't been translated.


This tutorial is licensed under CC BY 4.0. Please refer to the license text if you wish to reuse, share or remix the content contained within this tutorial.

The PHP script

Next, either erase everything in your Notepad or open a new one and paste the following code into it:

        header('Access-Control-Allow-Origin: *');
        $user_name = "yourusername";
        $database  = "yourdatabase";
        $password = "yourpassword";
        $server    = "yourserverurl";
        $tablename = "yourtablename";
        //[b][i][/b] don't change anything below here if you don't know what you're doing [b][/i][/b][b][i][/b][/i]//
        $mysqli = new mysqli($server, $user_name, $password, $database);
        if (mysqli_connect_errno()) 
            die(file_put_contents('error.dat', "Connect failed: \n". mysqli_connect_error()));
        if (isset($_POST["PV"]) && isset($_POST["N"]))
            $PV=filter_input(INPUT_POST, "PV", FILTER_SANITIZE_SPECIAL_CHARS);
            $N=filter_input(INPUT_POST, "N", FILTER_SANITIZE_SPECIAL_CHARS);        
            $SQL="Insert into $tablename (Name,Version,IP,UAgent,Referer) VALUES (
            $mysqli->query($SQL) or die(file_put_contents('error.dat', $mysqli->error));

When you created your MySQL database, you had to enter a user name and a password. Change the 'yourusername' and 'yourpassword' to those. Dont worry, PHP is run on the server side, these will never be visible to anyone who stumbles upon this script. Next, change the 'yourdatabase' to match the same database you use in the SQL commands. Next, change the 'yourserverurl' to match the one for your MySQL server that your host gave you. If you're in phpMyAdmin you'll also see it at the top of the window. It'll look something like this: Server: If you don't have a url, an IP address will work just as well. Finally, change the 'yourtablename' to the table name you entered in the SQL commands.

Now, save this .php script to a file. Give it a name like "tracking.php" and FTP it up to your server. Remember where you put it, you'll need that for your Construct Ajax plugin.

So, what does this script do?

The first line tells the Construct Ajax plugin that it's ok to talk to this server. When Ajax contacts a server, only allowed clients are able to talk to it. This tells your Ajax plugin that it'll accept connections from anyone, anywhere.

First, it tries to connect to the MySQL server. If it fails, it creates a file in the same directory as the script called error.dat that tells you why it's failed to connect. Then it looks for two POST variables, PV an N to be sent to it. If it doesn't find them, it just ends and does nothing. If it does find them, it builds them into an SQL query and sends it to your database telling it to create a new database entry. Along with those two variables, it also grabs the IP address of of the game/app that's contacting it, it grabs their user agent, which should be something like: "Mozilla/5.0" which is the user agent for Chrome, and then it grabs the referring url. This will let you know where the app is being run from if there's a url involved. When you run your game locally, it'll be: "http://localhost:50000/". If it runs from your server, it'll be that address.

Two other things happen automatically whenever a new record is added to the database. First, it assigns an ID number. These are handy to keep track of the connections based on the order they happen. The ID number is automatically incremented each time a record is added. Second, is a timestamp. Each time a record is added, the database automatically adds the current date and time to that record. Once you gain some skill in php and MySQL, you can even create charts of your game's connections based on day and hour.


  • Order by
Want to leave a comment? Login or Register an account!
  • I have GoDaddy to host my website..

    Is it possible to create a BLANK MySQL database, then just copy it when I have a new user?

    I want each user to have their own MySQL database to save ALOT of data in.

  • Thanks a lot - VERY helpful! :)

    Just to note that lines 2-5 in your MySQL codearen't recognized as correct SQL code.

    Are they important? can you update how should they look now?