Tracking your users with Ajax, PHP and MySQL

  • 16 favourites



12,588 visits, 35,036 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.

Creating the MySQL table

First, let's create a MySQL table to track your users. Here's a series of MySQL commands to create that table:

    /[i]!40101 SET NAMES utf8 [/i]/;
    -- Database: `yourdatabase`
    -- --------------------------------------------------------
    -- Table structure for table `yourtable`
    DROP TABLE IF EXISTS `yourtable`;
    CREATE TABLE IF NOT EXISTS `yourtable` (
      `ID` int(11) NOT NULL auto_increment,
      `Name` varchar(255) NOT NULL,
      `Version` varchar(255) NOT NULL,
      `IP` varchar(20) NOT NULL,
      `UAgent` varchar(150) NOT NULL,
      `DateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
      `Referer` varchar(100) NOT NULL,
      PRIMARY KEY  (`ID`)

Select the code above and paste it into a program like Notepad. Next, you'll have some changes to make. Change the 'yourdatabase' to the exact same name as the database you created in your hosts control panel. Next, change every instance of 'yourtable' to the name you want this table to be, for example; MyAppTracking.

Once you have your SQL commands edited, log into your phpMyAdmin. At the top, you'll see several tabs or buttons depending on which version your host has installed. The one you're looking for is SQL. Click on it and a text box should appear with the title: "Run SQL query/queries on database yourdatabase." Copy the SQL commands from your Notepad into this text box. Make sure to erase anything that might be in there first then click the "Go" or execute button to run the SQL commands.

If everything went right, in the hierarchy tree on the left, you should see a new table with the name that matches the 'yourtable' from the SQL commands. Click on this table and it should take you to the table's 'Structure' tab. You should now be looking at something like this:

These are all the fields we're going to track.

Now, save the SQL commands in your Notepad just in case you need them again. But one thing to take note of is the line: " DROP TABLE IF EXISTS `yourtable`;" This line tells MySQL to completely erase the table and all of it's data. If you have records in the table and you run this command, they'll all vanish without even asking you.

For now, we're done with phpMyAdmin, but we'll be coming back to it.


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