display top-five referral urls

romow

Hi,
I would like to display top-five referral URLs on the main page of my site dynamically,
so that I can automatically provide reciprocal links for those sites that bring us the most traffic.

Can anyone help me create a code snippet that does this?
Any help will be greatly appreciated.

Trs2988

If you already have the information about referrals being stored in a database, then it's real easy. But from your question, I'm assuming you don't.

So from the page that you expect people to link to, you would want to start recording $_SERVER['HTTP_REFERER']. Note that this could easily be abused if you don't check IP addresses, so you'll probably want to only record one referral per ip address.

So, assuming you had the table set up with columns 'ip' and 'referrer', you could do something like this...

$ip = $_SERVER['REMOTE_ADDR'];
$refer = mysql_real_escape_string($_SERVER['HTTP_REFER']);

$alreadyLogged = mysql_num_rows(mysql_query("select * from table_name where ip='$ip' and referrer='$referrer'"));

if(!$alreadyLogged) mysql_query("insert into table_name (ip,referrer) values ('$ip','$referrer'") or die(mysql_error());

Then, you should be able to pull out the top 5 referrers with a query like this:

select referrer,count(ip) as referrals from table_name group by referrer order by referrals desc

That should work. Let me know how it goes.

EDIT: Oh, by the way. You might also want to filter out referrals from your own website, from search engines, or other sites that you don't want to link back to. You can use ereg() for that.

romow

hi Trs2988,
You are awesomely helpful!

BTW, if $alreadyLogged is true (i.e., referral is not new), what sql code shall I put in the else clause?

Thanks much!

Snake_PHP

and this answer ..

http://www.phpbuilder.com/board/showpost.php?p=10672392&postcount=4

Trs2988

romow wrote:
BTW, if $alreadyLogged is true (i.e., referral is not new), what sql code shall I put in the else clause?

You shouldn't put anything in there, I don't think. If the referral is not new, that means the person either refreshed, went back and came again, or is trying to abuse your system. You should just discard that result, so nothing goes in the else clause.

Keep in mind - $alreadyLogged isn't checking whether the referral in and of itself is new. We're checking to see if that IP address has already come from that referral. If 100 people come from 1 referral, you'll have 100 rows in the database: all with the same referral, just different IPs. But if the same person comes from the same referral 100 times, it'll only record it once - and throw out the next 99.

Make sense?

romow

thanks SnakePHP.
but your script does not seem to be doing what I'm asking.
Your script just displays all the referral URLs. Doesn't it?

whereas I would like to display top-five referrals.
So I keep track of amount of traffic associated with each referral URL,
and display the top-five of them.

Snake PHP wrote:
and this answer ..

http://www.phpbuilder.com/board/showpost.php?p=10672392&postcount=4

romow

aha, that's what <$ip, $referer> tuple is for!
Now that makes sense.. 🙂
thanks alot for the clarification.
Will try your code..

Trs2988 wrote:
You shouldn't put anything in there, I don't think. If the referral is not new, that means the person either refreshed, went back and came again, or is trying to abuse your system. You should just discard that result, so nothing goes in the else clause.

Keep in mind - $alreadyLogged isn't checking whether the referral in and of itself is new. We're checking to see if that IP address has already come from that referral. If 100 people come from 1 referral, you'll have 100 rows in the database: all with the same referral, just different IPs. But if the same person comes from the same referral 100 times, it'll only record it once - and throw out the next 99.

Make sense?