Revisited: Logging With PHP

Tim Perdue picture of Tim Perdue

Figuring out where your hits are coming from and which pages are being viewed is not too difficult if you use a good log analyzer like Analog or something similar.

But if you want to pull up reports on the fly at any time, you need to take a different route.

For about six months, I was importing my Apache logs into an SQL database and then running ad-hoc queries against that.

Unfortunately, that requires me to go out and manually grab those log files, tweak the format a bit, then import. I wanted something better - something that was continually updated, gave me information on what content was being viewed, browser and platforms stats, and trends over time.

The solution I came up with in my original version of this article has served me well for a couple years now. I enhanced it somewhat when we built SourceForge and all logging and tracking for the entire SourceForge.net web site pipes through this system, including page views on the main site and page views for each of the 12,000 projects that use the site.

Basically, what I do is add a row to a table in a database for each action I want to log (page views in this example, but you can log banner add views, clicks, click-ins from other sites, etc).

Here is the table structure I've been using on SourceForge:

create table activity_log (
  day integer DEFAULT '0' NOT NULL,
  hour integer DEFAULT '0' NOT NULL,
  group_id integer DEFAULT '0' NOT NULL,
  browser varchar(8) DEFAULT 'OTHER' NOT NULL,
  ver float(10) DEFAULT '0.00' NOT NULL,
  platform varchar(8) DEFAULT 'OTHER' NOT NULL,
  time integer DEFAULT '0' NOT NULL,
  page text,
  type integer DEFAULT '0' NOT NULL
);

The group_id column can be used if you have multiple web sites you are trying to track. Just assign a different group_id to each site and pass it in the URL (shown below).

type is unused in these examples, but you can use it to track different types of actions, like ad clicks, clickins from other sites, etc.

browser, ver, platform are all obtained from the $HTTP_USER_AGENT variable which is passed by your browser to the web server. I have a browser detection library which we call to fill in this info. The code for that is available elsewhere in this article.

page is any arbitrary information that you want to log - I generally log $PHP_SELF.

[ Next Page ]

Comments:
RE: opentracker	Mayukh	04/30/05 05:46
RE: Some fixes to SourceForges browser.php	David Neff	12/06/02 20:29
Cache Problem(On Indirect Ip's)	Shrirang Kulkarni	11/06/02 03:43
Some fixes to SourceForges browser.php	morten hansen	10/29/02 09:49
RE: Referrer checking?	tony	06/28/02 23:32
Use timestamp from random num	nicole	05/16/02 16:50
RE: Sample SQL to run reports sorting	Chris Peterson	04/22/02 04:14
RE: Sample SQL to run reports sorting	Brandon	04/05/02 18:19
Sample SQL to run reports sorting	Luis Oliveira	03/25/02 04:48
Problem using 1x1 pixel GIF image	Anders Kronquist	10/18/01 12:03
Referrer, uniques and standards	Frans	03/08/01 19:46
Memory effective Database	Kenneth	02/08/01 12:21
RE: Apache log => mysql	Martin	01/16/01 06:16
Java referrer	david	01/01/01 20:35
here's some code I use on a large system	Stephen VanDyke	12/28/00 14:45
RE: Referrer checking?	david	12/27/00 20:55
RE: A more optimized solution	Richard Bendelow	12/22/00 08:41
RE: Badly normalized - Both?	llong	12/20/00 18:36
RE: opentracker	llong	12/20/00 18:21
Error but no error echoed???	Charles	12/19/00 15:36
Apache log => mysql	James	12/19/00 10:05
RE: Badly normalized	Jeppe Salvesen	12/15/00 20:12
Simple way to convert Day integer back to dat	Patrick	12/14/00 21:27
mod_log_mysql is the way to go	Nathan Cassano	12/14/00 11:54
!the real solution: webalizer.com/sample !!!	Igor	12/13/00 13:40
RE: Badly normalized	Beth J.	12/13/00 11:08
RE: Disabled images	Tim Perdue, PHPBuilder.com	12/13/00 10:32
Disabled images	Joe Clarke	12/12/00 19:47
A more optimized solution	Stallion	12/12/00 14:57
RE: Real user tracking	Ed Rahn	12/12/00 14:20
RE: Not the fastest solution	Viking	12/12/00 11:37
RE: Badly normalized	Martijn	12/12/00 11:29
opentracker	edmz	12/12/00 11:12
RE: Referrer checking?	Martin Joergensen	12/12/00 10:25
RE: Badly normalized	Tim Perdue, PHPBuilder.com	12/12/00 09:44
Another alternative	George Schlossnagle	12/12/00 08:58
RE: Referrer checking?	Grant Petersen	12/12/00 01:54
RE: Not the fastest solution	Hendrik Mans	12/11/00 20:13
Badly normalized	Jeppe Salvesen	12/11/00 19:04
HTML-based e-mail	Chris Thompson	12/11/00 17:42
Not the fastest solution	Viking	12/11/00 16:08
RE: Real user tracking	Sean Clark	12/11/00 14:07
RE: Real user tracking	luke chasteen	12/11/00 12:53
RE: Referrer checking?	Pavel Prishivalko	12/11/00 10:55
RE: Referrer checking?	Micheal O Shea	12/11/00 09:33
RE: Real user tracking	Tim Perdue, PHPBuilder.com	12/11/00 09:30
RE: Referrer checking?	John	12/11/00 08:24
Real user tracking	NightOwl	12/11/00 07:38
cache problem if using host without PHP	SaS	12/11/00 07:08
RE: Referrer vs. Referer foo	philip olson	12/10/00 21:59
RE: Referrer checking?	Tim Perdue, PHPBuilder.com	12/10/00 21:30
Referrer checking?	Hendrik Mans	12/10/00 20:44
If you are looking for help, please post on the appropriate forum here. Your questions will be answered much more quickly. Add A Comment: Name: Email: Subject: Message: To reduce spam posts, messages are now manually approved You are not [logged in]. That means your account will not get credit for this post.

Find a programming school near you

Add A Comment: