Top 10 referrer tracking.

bluntster

Okay, what I want to do is track the 10 ten pages that link to my site and display them in order with a link back to that page.

I could probably figure this out but what I'm not sure about is how I should structure my mySQL table to hold the log, and how to compere the IP address so as not to repeatedly log the same referrer, and how to do all this efficiently so as not to slow my page down.

Has anyone already done something similar, that could give me the benefit of their experience?
Thanks,

Neil

[deleted]

There are two ways of doing this, the quick-and-dirty way, and the 'proper' way.

Qcuick and dirty may give faster responses, but the data you collect will only be quitable for the top-10 listing, nothing else.

The 'proper' will slow down slightly (milliseconds) as the table grows, but you can do regular maintenance to erase irrelevant data.

Quick and dirty:
The mysql table can be very simple, it only needs to hold the referer name and a 'hitcount'.

You will need to run at least two queries: one to find out wether the referer already exists, and one to update or insert the data.

The procudere is:
Query to see if the referer already exists in the database.
If yes, update the hitcount to hitcount+1
If no, insert the referer and set hitcount to 1

Advantages:
- takes very little space in the database
- is very fast the get the stats

Drawbacks:
- can lead to duplicate data in high-traffic environments
- generates very application-specific data

the Proper way:
Create a table that contains referer, remote-ip, date, and url.
With every visit, insert all this data into a new record.
You can get the stats by running queries like

SELECT referer, count(*) as hitcount
FROM table
GROUP BY referer
ORDER BY hitcount DESC
limit 10

Advantages:
- very detailed information, you can get a lot more data from it than just the top10
- Only one query required to insert new data
- No duplicate data in high-traffic sites.

Disadvantages:
- takes more space in the database
- will slow down a little as the table grows

Anon

cheers to vincent for a long and descriptive answer/

thanks

kalyan

bluntster

Okay, first Vincent, thanks for all your help. I've decided that the only way to do things is the proper way, so here goes my query to log visitors...

<?
$web_referrer = getenv("$HTTP_REFERER");
$remote_id = getenv("$REMOTE_ADDR");
$date = date("Y-m-d H:i:s",time());

$sql = "INSERT INTO referrer_log(referrer,IP,date) VALUES ('$web_referrer','$remote_id','$date')";
$result = mysql_query($sql,$db);
?>

The problem is getenv("$HTTP_REFERER") and getenv("$REMOTE_ADDR") return empty. I know that proxy servers and firewalls can sometimes strip these environmental variables but this is the case for EVERY visitor, so there must be a problem. Could it be because the script lives in an INCLUDE file and not in my index.php? (I thought it was only in functions that vars had to be declared global)...

[deleted]

"The problem is getenv("$HTTP_REFERER") and getenv("$REMOTE_ADDR")"

they return empty because the proper syntax is:

getenv("HTTP_REFERER")
getenv("REMOTE_ADDR")

or if you are accessing them inside a function:

$GLOBALS['HTTP_SERVER_VARS']['HTTP_REFERER']
$GLOBALS['HTTP_SERVER_VARS']['REMOTE_ADDR']