Tracking your users with Ajax, PHP and MySQL

  • 11 favourites

Index

Stats

5,119 visits, 14,855 views

Translations

This tutorial hasn't been translated.

Tools

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:

    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    /[i]!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT [/i]/;
    /[i]!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS [/i]/;
    /[i]!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION [/i]/;
    /[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`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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.

  • 0 Comments

  • Order by
Want to leave a comment? Login or Register an account!