Top referers!

[harmonic]

here is a top referers script that i wrote... it kind of has directions in it, but if you need help just ask me...

it is alot more efficient than others i have seen, because it keeps one row for each url, not one for each hit... and it deletes those URL's that have not had any hits in after x days (you specify)


<?

/* PHREF, CREATED BY DAN - HTTP://WWW.VEXDEV.COM/ */

/* RUN THIS QUERY IN MYSQL:

CREATE TABLE phref (
url varchar(255) NOT NULL,
count int(5) DEFAULT '0' NOT NULL,
time varchar(30) NOT NULL,
UNIQUE url (url)
);

*/

/* EDIT THESE */

$website = "yourdomain.com";
//YOUR DOMAIN, NO WWW. OR HTTP://

$server = "localhost";
//MYSQL SERVER

$username = "user";
//MYSQL USERNAME

$password = "password";
//MYSQL PASSWORD

$database = "db";
//MYSQL DATABASE

$days = "5";
// DAYS TO KEEP IN RECORD IF UNACTIVE

$num = "5";
// TOP # TO DISPLAY

$block = array("$website", 'bob.com', 'foo.com');
// BLOCKED URLS, SEPERATE THEM WITH COMMAS, DO NOT INCLUDE "http://" or "www."

/* STOP EDITING */

/* YOU'RE DONE, TO USE THIS YOU NEED TO HAVE A PHP FILE AND USE <? include('phref.php'); ?> */


mysql_connect("$server", "$username", "$password");
mysql_select_db("$database");

$c_time = time();


function checkblock($block_array, $url){
foreach($block_array as $key){
if($key == $url)
return TRUE;
}
}

if(!empty($HTTP_SERVER_VARS['HTTP_REFERER'])){
$ref = $HTTP_SERVER_VARS['HTTP_REFERER'];
$ref = str_replace('http://www.', '', $ref);
$ref = str_replace('http://', '', $ref);
$ref = explode('/', $ref);
$ref = $ref[0];
if(!checkblock($block, $ref)){
$qry = mysql_query("SELECT * FROM phref WHERE url = '$ref'");
if(mysql_num_rows($qry) == "0"){
mysql_query("INSERT INTO phref (url,time,count) values ('$ref','$c_time','1')");
} else {
mysql_query("UPDATE phref SET time = '$c_time' where url = '$ref'");
mysql_query("UPDATE phref SET count=count+1 where url = '$ref'");
}



$time = time()-(60*60*24*$days);
mysql_query("DELETE FROM phref WHERE time < $time");
}
unset($qry);
unset($ref);
}

$qry = mysql_query("SELECT url,count FROM phref group by url order by count desc limit $num");
echo "<table>";
echo "<tr><td>URL:</td><td>Hits in:</td></tr>";

while($row = mysql_fetch_array($qry, MYSQL_ASSOC)){
echo "<tr><td>".$row['url']."</td><td>".$row['count']."</td></tr>";
}

echo "</table>";

?>


just put this in a file named "phref.php"