Browse by title articles:

Logging with PHP

by 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, flexible enough to simultaneously support multiple sites, and multiple actions (page view, ad click, click-in, click-out, plus more if needed).

I sat down and came up with the following table:

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:

INDEXES
idx_logger_date
idx_logger_hour
idx_logger_ip
idx_logger_action
idx_logger_special
idx_logger_affil_num

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:

<IMG SRC="http://www.yourserver.com/util/gif11.php?c=4&s=phpbuildercom&b=77" height=1 width=1>

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:

<A HREF="http://www.yourserver.com/util/adclick.php?c=4&goto= * >

Just replace * with the URL you want to redirect to. Again here, the $c variable should be changed for each web site.

Reporting all this information is easy with PHP - just write up some SQL, execute it and use the ShowResults class to display it.

So now the system is in place and I can run a report at any time and get statistics up to the minute for any given site.

Here's a sample report from Friday on PHPBuilder.com:

Overall, I'm pretty impressed with this system. There's really no limit to its flexibility - in fact, I am expanding it to keep track of ad banners, and I may write up an article on that later.

Please use the sample code on the next page and tell me how you used it, or if you added anything interesting to it.

<!--

//gif11.php accepts these parameters:
$c; //correlates to fld_affil_num in the database -
//unique for each site
$s; //correlates to fld_special in the database
$b; //random number - forces the gif to load, even if cached
//generated by PHP and discarded
-->

<?php

//gif11.php
//don't have any extra spaces outside the <? ?>
// or you could have a broken gif come back

Header( "Content-type: image/gif");
passthru( "cat clear.gif" ); //send a 1x1 gif

$logger_action=5; //impression
$logger_special=$s; //move the $s param for the logger
$fld_affil_num=$c; //ditto for the $c param

include('connect.inc'); //connect to the DB SERVER
include('logger.inc'); //Exanded below

?>

PHP Code to generate the GIF URL:

<?php

srand((double)microtime()*1000000);
$random_num=rand(5,95);
?>
<P>
<IMG SRC="http://www.yourserver.com/util/gif11.php?c=4&s=phpbuildercom&b=<?
echo $random_num; ?>" height=1 width=1>

connect.inc:

<?php

//
//Connect to the PostgreSQL database
//

$conn = pg_pconnect("user=myname dbname=my_db");

//I use a persistent connection instead of opening
//and closing the connection tens of thousands
//of times/day

if (!$conn) {
echo "An error occured.\n"; //amateur error detection
exit;
}

?>

logger.inc:

<?php

//
//ACTIONS - 1=get 2=click-thru 3=clickin 4=jump2zip 5=impression
//

if (!$logger_action) {echo "error in logger"; exit;}
if (!$logger_special) {echo "error in logger"; exit;}

//
//The following should be on one long line...
//

$logger_sql="insert into tbl_activity_log values (".date("Ymd", mktime()).", ". //PHP method to build date
"'".date("H", mktime())."', ". //PHP method to get hour
"'$REMOTE_ADDR', ". //Get the IP address of client
"$logger_action, ' ". //action as set up in gif11.php
"$logger_special', ". //ditto
"$fld_affil_num);"; //which web site

$res_logger = pg_Exec ($conn, $logger_sql);

if (!$res_logger) {
echo "An error occured in the logger.\n";
echo pg_ErrorMessage();
exit;
}

?>

adclick.php:

<?php

$goto_location="Location: ".$goto."&b=".$b;

Header($goto_location);

$logger_action=2; //adclick
$logger_special="adclick";
$fld_affil_num=$c;

include('connect.inc');
include('logger.inc');

?>

reporter.php:

<?php

//
//This is just a tiny sample of the reporting you can do
//

Function ShowResults($result) {

//I apologize to the author of this code -
//I believe I found it in the code exchange, but I
//can't find it now! --Tim

if ($result) {
$rows = pg_NumRows($result);
$cols = pg_NumFields($result);

echo( "<table border=1>\n");
/* Create the headers */
echo( "<tr>\n");
for($i = 0; $i < $cols; $i++) {
printf( "<th>%s</th>\n", pg_FieldName($result, $i));
}
echo( "</tr>");
for($j = 0; $j < $rows; $j++) {
echo( "<tr>\n");
for($i = 0; $i < $cols; $i++) {
printf( "<td>%s</td>\n", pg_result($result, $j, $i));
}
echo( "</tr>");
}
echo( "</table>");
} else {
echo(pg_errormessage());
}

//
//The following should be one long line
//

$report_sql="SELECT fld_date, fld_special, count(*) AS IMPRESSIONS ".
"FROM tbl_activity_log ".
"WHERE fld_action=5 AND fld_affil_num=$affil ".
"GROUP BY fld_date, fld_special;";

$res_click_report = pg_Exec ($conn, $report_sql);

if ((!$res_click_report) || (pg_NumRows($res_click_report) < 1)) {
echo "An error occured.\n";
exit;
}

ShowResults($res_click_report);

}

?>

Please feel free to play around with this code, add your own features, change it how you like, but please come back and tell us what you did with it.

Tim Perdue at Perdue Inc

Tim is available for consulting and programming on database architecture, java, web applications, and Open Source Software.