Showing the top domain referrals to your site

This tutorial will show you how to make a list of the top referring domains to your site. This list requires no signup from users at all, they just have to link to your site and they will show up. We will go through the basic creating the mysql tables in this tutorial and how to parse URLs into just the domain.

So first we set up the mysql table, we only need one, we will call it ref_domains with the following fields:
domainID - Primary,bigint, and autoincrement
domainname - varchar of length 255
hitsin - bigint

domainID is just an autogenerated ID that increments so there will not be two domains with the same ID
domainname is the actual url of the domain
hitsin is the number of hits that domain was given your site

Now we go on to the code:

</p> <p><?php</p> <p>$db = mysql_connect(”localhost”, “username”, “password”) or die(”Could not connect.”);</p> <p>if(!$db)</p> <p>die(”no db”);</p> <p>if(!mysql_select_db(”database_name”,$db))</p> <p>die(”No database selected.”);</p> <p>$ref = getenv(”HTTP_REFERER”); //gets referrer</p> <p>$dom=explode(”/”,$ref);</p> <p>$domainstr=array($dom[0],$dom[1],$dom[2]);</p> <p>$domainstring=implode(”/”,$domainstr); //builds just the domain</p> <p>if(strlen($domainstring)>5) //make sure the domain referrer isn’t blank</p> <p>{</p> <p>$isref=”SELECT domainID from ref_domains where domainname=’$domainstring’”; //gets the ID of the domain and checks to see if it exists</p> <p>$isref=mysql_query($isref) or die(”Could not query”);</p> <p>$numrows=mysql_num_rows($isref); //count number of rows</p> <p>if($numrows==0) //the referring domain is not in db already</p> <p>{</p> <p>$newref=”INSERT into ref_domains (domainname,hitsin) values(’$domainstring’,'1′)”;</p> <p>mysql_query($newref) or die(”Could not insert new domain”);</p> <p>}</p> <p>else //increment hit by 1</p> <p>{</p> <p>$updatedomain=”Update ref_domains set hitsin=hitsin+1 where domainname=’$domainstring’”;</p> <p>mysql_query($updatedomain) or die(”Could not update domain”);</p> <p>}</p> <p>}</p> <p>//now we display them</p> <p>$getdomains=”SELECT * from ref_domains order by hitsin DESC”;</p> <p>$getdomains2=mysql_query($getdomains) or die(”Could not get domains”);</p> <p>print “<table border=’1′><tr><td>Referrer</td><td>Hits in</td></tr>”;</p> <p>while($getdomains3=mysql_fetch_array($getdomains2))</p> <p>{</p> <p>print “<tr><td><A href=’$getdomains3[domainname]‘>$getdomains3[domainname]</a></td><td>$getdomains3[hitsin]</td></tr>”;</p> <p>}</p> <p>print “</table>”;</p> <p>?><br />

Lets go through it slowly. The first part:

</p> <p>$db = mysql_connect(”localhost”, “username”, “password”) or die(”Could not connect.”);</p> <p>if(!$db)</p> <p>die(”no db”);</p> <p>if(!mysql_select_db(”database_name”,$db))</p> <p>die(”No database selected.”);</p> <p>

Is just some basic connections to mySQL functions. Simply put your mysql username, password, and mysql database name where indicated. These are basic connection functions/code and can be used in any script with PHP/MYSQL.

</p> <p>$ref = getenv(”HTTP_REFERER”); //gets referrer</p> <p>$dom=explode(”/”,$ref);</p> <p>$domainstr=array($dom[0],$dom[1],$dom[2]);</p> <p>$domainstring=implode(”/”,$domainstr); //builds just the domain</p> <p>

This gets the referring URL and extracts the domain from it. First getenv(”HTTP_REFERER”) gets the referring url. The next line with explode we separate the
URL into an array, based on where the “/” is. The part if the URL before the first “/” is $dom[0], between the first and second “/” is $dom[1] and so on. Next, we create a variable $domainstr and have it be an array of just the first three pieces of our domain array. Why? Because the first three pieces will always contain the domain name only. In a URL “http:” always comes before the first “/”, between the first and second “/” , there is nothing so it is empty and the www.whatever.com is always between the 2nd and 3rd “/”. Therefore imploding this $domainstr togehter with the “/” as the separator in the next line will always give us the full domain.

Now that we’ve extracted the root domain from the URL, we need to determine if that domain is already in our database or if it is a new referrer and we need code to handle each case, so we need the code below:

</p> <p>if(strlen($domainstring)>5) //make sure the domain referrer isn’t blank</p> <p>{</p> <p>$isref=”SELECT domainID from ref_domains where domainname=’$domainstring’”; //gets the ID of the domain and checks to see if it exists</p> <p>$isref=mysql_query($isref) or die(”Could not query”);</p> <p>$numrows=mysql_num_rows($isref); //count number of rows</p> <p>if($numrows==0) //the referring domain is not in db already</p> <p>{</p> <p>$newref=”INSERT into ref_domains (domainname,hitsin) values(’$domainstring’,'1′)”;</p> <p>mysql_query($newref) or die(”Could not insert new domain”);</p> <p>}</p> <p>else //increment hit by 1</p> <p>{</p> <p>$updatedomain=”Update ref_domains set hitsin=hitsin+1 where domainname=’$domainstring’”;</p> <p>mysql_query($updatedomain) or die(”Could not update domain”);</p> <p>}</p> <p>}<br />

So first we check if the referring domain is at least 5 characters, since a domain must have at least “http:” in it, it has to have at least 5 characters or it is not a valid referring domain. If it is, then we select the ID’s of the records where the URL is the domain that referred us. It counts the number of rows with that URL(basically it sees if the URL is already in our table or not). If it isn’t, then it does an INSERT query to insert the new URL in and give the new entry a hitsin value of 1. If it detects that the URL is already in our database(the else case), the it does an UPDATE query and increments hitsin by 1.

Now the last step is to query and display the information:

</p> <p>$getdomains=”SELECT * from ref_domains order by hitsin DESC”;</p> <p>$getdomains2=mysql_query($getdomains) or die(”Could not get domains”);</p> <p>print “<table border=’1′><tr><td>Referrer</td><td>Hits in</td></tr>”;</p> <p>while($getdomains3=mysql_fetch_array($getdomains2))</p> <p>{</p> <p>print “<tr><td><A href=’$getdomains3[domainname]‘>$getdomains3[domainname]</a></td><td>$getdomains3[hitsin]</td></tr>”;</p> <p>}</p> <p>print “</table>”;</p> <p>

This just selects all the information in the database and orders it by the number of hits sent in, in descending order. Then it throws the query into an array and loops through the array and displays a hyperlink URL to the referring domain and the number of hits the domain has sent in in table format.

Copyright @ Chipmunk 2007