create table tbl_activity_log ( fld_date int, //ex. 19990101 fld_hours text, //Which hour of the day? fld_remote_ip text, //IP of visitor fld_action int, //page view, click, etc fld_special text, //which page or category? fld_affil_num int //unique for each web site );
Someone who really knows what they're doing would have used a timestamp field rather than int fld_date, but this works for me. You could also add another column to hold the $PATH_INFO, but I decided the Special column gives me what I want. I also defined these indexes, as I knew I would be querying on any of these fields:
The next step was getting the info into the tables. It would be a perfect world if all pages were served through PHP and all of your various web sites existed on one box. Unfortunately, I have an array of servers scattered all over the country, and I want to collect this info from every server for every page view 24 hours a day.
So that required me to use the 1x1 pixel GIF trick. I have a GIF on every page that looks like this:
gif11.php is a simple script that resides on my central server. I have included the source on the next page. Since the gif is on each page, and is forced to load due to the random number at the end (the b=xxxx), a request is sent back to the central server for each and every page view.
If all of your pages and the database reside on one server, you don't need to use the GIF trick - you can insert the logging code into the header of your page. The same logging system can be used to track click-thrus on ad banners: