For a new site I am keeping page count totals in a mySQL table:
create table PAGECOUNTS
(
PageName VARCHAR(25) NOT NULL,
Count MEDIUMINT ( 6 ),
PRIMARY KEY ( PageName )
);
and update it with php:
function GetPageCount ( $Page )
{
// Open the DB
$link = ConnectToDatabase ( );
// Get the count for the page
$query = "select Count from PAGECOUNTS where PageName = '$Page'";
$result = mysql_query ( $query, $link );
if ( !$result )
;
//error_message ( GetSQLError ( ) );
else
{
// record exists, get it
$QueryData = mysql_fetch_row ( $result );
$Count = $QueryData [ 0 ];
if ( $Count )
{
// add one to the page count
$query = "update PAGECOUNTS set Count = Count + 1 where PageName = '$Page'";
$result = mysql_query ( $query, $link );
}
else
{
// record doesn't exist, add it
$query = "insert into PAGECOUNTS VALUES ( '$Page', 2 )";
$result = mysql_query ( $query, $link );
$Count = 1;
}
}
return $Count;
}
I would like to to keep monthly data instead so I can see the site trends. I have thought about a table design like the following:
create table PAGECOUNTS
(
PageName VARCHAR(25) NOT NULL,
TotalCount MEDIUMINT ( 6 ),
Jan MEDIUMINT ( 6 ),
Feb MEDIUMINT ( 6 ),
Mar MEDIUMINT ( 6 ),
Apr MEDIUMINT ( 6 ),
May MEDIUMINT ( 6 ),
Jun MEDIUMINT ( 6 ),
Jul MEDIUMINT ( 6 ),
Aug MEDIUMINT ( 6 ),
Sep MEDIUMINT ( 6 ),
Oct MEDIUMINT ( 6 ),
Nov MEDIUMINT ( 6 ),
Dec MEDIUMINT ( 6 ),
PRIMARY KEY ( PageName )
);
The TotalCount would be a running total and the other columns (they could be 01 or something else rather than Jan) would keep the totals for each month. This is fine until I rollover into a new year.
Since mySQL doesn't currently support stored procedures or triggers I can't use them.
I could do manual maintenance to clear monthly totals at the end of each month but then I would have to remeber to do them (don't want to worry about Senior Moments). There needs to be a better way.
The only way I know of presently is for separate tables per year and a separate totals table. I could dynamically create a new table when it doesn't exist and update both the year and pagecount total table. That would work but I would like to see if anyone has any suggestions on a different dsign.
