Vuzix Wrap 1200X10 Palm Pad ControllerASUS Transformer TF300 10inch 32GB Tablet

Tracking Visitors with PHP and MySQL

On February 10, 2011, in PHP, Programming, by LuCuS

PHP and MySQL LogosAlrighty, I just got finished showing you how to extend your IP tracking script so that it can log requests in a MySQL database. But, what else can we do with the script? Well, there are lots of things we can do with it. You can extend the script to track where visitors are coming from, what pages they visit on your site, how long they stay there, and a whole lot more. Use your imagination and I’m sure you’ll come up with some really cool stuff. For now, I’m going to give you a head start by showing you how to determine where visitors are coming from. It’s always nice to know how people got to your site. And, as an added bonus, I’ll also show you how to detect which pages they visit on your site. Before we begin, be sure to checkout this article as it plays a big role in what you’re about to learn here.

To get started, we’re gonna need to revisit our MySQL database and add a few columns to our “ip_tracker” table. The first column we’re gonna need to add is a field for storing the “referer”. This will be a varchar with a length of about 255. You can set the length to whatever you want, but it needs to be at least 255 characters. Otherwise it will be too short for storing longer web addresses. To add the referer column to the table, you’ll need to issue the following command:

# ALTER TABLE ip_tracker ADD COLUMN referer varchar(255);

The second column you’re gonna need to add to your table is a field called “requestUri”. This too will be a varchar, but the length doesn’t have to be 255 characters like the “referer” field. You can probably get away with something like 100 characters if you want. The command for adding the requestUri is the same as the one we used for the referer. But, I’m going to add a little extra piece to this command to show you how to tell MySQL where you want to new column to be added. For this example, I want to place the requestUri between the “address” and “date” fields. So, my command will look something like this:

# ALTER TABLE ip_tracker ADD COLUMN requestUri varchar(100) AFTER address;

I like to try to keep my database tables fairly organized and one of the things I like to do is to keep most of my timestamps near the end of the table. So, I would’ve included the “AFTER address” to the end of the command I used for adding the referer and would’ve changed “AFTER address” to “AFTER referer” for the requestUri command. But, this isn’t a requirement. I just wanted to show you that you could choose where new columns are added if you were curious.

Another field you might want to add to your table is “userAgent” for tracking what application / browser the visitor used to access your script, but I think we have enough fields for our demonstration.

Now it’s time to tell our script to add the variables for storing our referer and request URI. So, create a variable called “$referer” and assign “$_SERVER['HTTP_REFERER']” to it. Then, create a variable called “$requestUri” and assign “$_SERVER['REQUEST_URI']” to it. After you have those variables assigned, you will need to include them in your “insert” statement. After you’ve updated your script, it should look something like this:

<?php
$ip = $_SERVER['REMOTE_ADDR'];
echo $ip;

$db_host = "server.ip.goes.here";
$db_port = "3306";
$db_user = "your_username";
$db_pass = "your_password";
$db_name = "your_database_name";
$link = mysql_connect("$db_host:$db_port", "$db_user", "$db_pass");
mysql_select_db("$db_name", $link);

if($link)
{
  $referer = $_SERVER['HTTP_REFERER'];
  $requestUri = $_SERVER['REQUEST_URI'];

  $qstring = mysql_query("INSERT INTO ip_tracker (address, referer, requestUri) VALUES (\"" . $ip . "\", \"" . $referer . "\", \"" . $requestUri . "\")", $link);
}
else
{
  echo 'Unable to connect to database.';
}
?>

That’s it. Pretty cool huh? Well, now it’s time to put the script to work. There are a few different methods you can use for calling this script from your pages. For a quick example, we will use a “dummy” image. This way, we can call the script from any type of page that can render HTML. It can be HTML, JSP, ASP, PHP, etc… So, to use a “dummy” image, we will need to add an “image” tag to our HTML and assign it a height and width of zero for both. Then, we will need to tell the “src” to point to our “yourip.php” script. Here’s how we do that:

<img src="/yourip.php" width="0" height="0" />

As mentioned, we set the width & height of our image to zero so that it wouldn’t display on the page. That way, no one will know that we’re tracking them when they visit our page. Now when people visit our pages, we will have their IP address, the page they came from to get to our site, the page they’ve requested from our site, and the time & date they made the request.

For more information on PHP and MySQL, you should take a look at the following books from Amazon.com.

PHP and MySQL Web Development (4th Edition)

Learning PHP, MySQL, and JavaScript: A Step-By-Step Guide to Creating Dynamic Websites (Animal Guide)

PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide

PHP Solutions: Dynamic Web Design Made Easy

Head First PHP & MySQL

If you enjoyed this post, please consider making a donation.

Related Posts

Tagged with:  

Leave a Reply