Revisited: Logging With PHP

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.