Click Tracking with Various Time Frames

artnow

Hello all. I would like to track the number of clicks of each particular link over various time frames -- today, this week (last 7 days), this month and total clicks, and hopefully I want to be able to select specific date ranges.

The problem I have is sorting out the logic behind it. My thinking is that I need to log each click with the link's $id and the date/time of the click. As $id would not be unique but date/time would be. Then I would write a script to allow me to extract the click details by $id from the mysql database using date/time field to order based on the time variations.

The only problem is, I am not sure this a sound way to go. Any thoughts would be appreciated.

Thanks, Bob

ednark

if you use a datetime column in mysql i think you are allowed to use the BETWEEN statement... check first which column types BETWEEN works with and choose one of them

here is the basic idea:

http://www.mysql.com/doc/en/Comparison_Operators.html

select * from table where time BETWEEN min AND max

you could always so this also

select * from table where time < max AND time > min

artnow

Thanks for the response. So this is what I have come up with. It redirects based on $id (link format http://www.blah.com/link.php?id=1234) properly, but does not update the click_count table. Any ideas as to why not?

<?
include("functions.php");

if ($id == "") {
    header("location:$site");
}
else {

mysql_connect($host,$user,$pass); 

$result=mysql_db_query("$db", "SELECT url FROM $table WHERE ID='$id'") or die ("Couldn't open \"$db\" database. Please inform $email: ".mysql_error());
$row = mysql_fetch_array($result);
$url = $row["url"];

   mysql_query("INSERT INTO click_count (day,id,raw,uni,time,ipnum) VALUES (NOW(),'$id','1','1',now(),'$REMOTE_ADDR'");

}
?>

<html>
<head>
<meta http-equiv="refresh" content="0;URL=<? echo "$url"; ?>">
</head>
<body bgcolor="#FFFFFF" link=#999999 vlink=#666666 alink=#000000 >
</body>
</html>

ednark

mysql_db_query

Note:
This function has been deprecated since PHP 4.0.6. Do not use this function. Use mysql_select_db() and mysql_query() instead.

try doing what is suggested there in that manual quote.

also i wouldn't use the <meta tag> refresh. instead, just place another header('Location:') after the sql query.

trying a

mysql_query( 'INSERT' ) or die( mysql_error() );

might give you more information