Find out who the top 5, 10 or 15 etc... referral sites are that link to your site. Site's are listed in a MySQL database and then listed out using 1 PHP file. You can find out all about who is linking to you, and even link back to them to give credit, where credit is due!

Alright, this is going to be a non-aesthetic script, so assume that no visuals are provided with the code. We are just going to list out out the top 10 referrers for you, then you can just include it into a file for easier management.

We just need to quickly create our database, run this query in your PHPMyAdmin or whatever database program you use.

PLAIN TEXT
MySQL:
  1. CREATE TABLE `referers` (
  2.   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  3.   `website` VARCHAR(255) NOT NULL,
  4.   `hits` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  5.   PRIMARY KEY  (`id`)
  6. )

So, as always we create our database connection script, we will include this into our referer script so that we can keep our code cleaner and just use an include instead of writing out all of our database connection info directly into the referer page.

PLAIN TEXT
PHP:
  1. <?php
  2. DEFINE ('DB_USER', 'USERNAME'); // The username for your database.
  3. DEFINE ('DB_PASSWORD', 'PASSWORD'); // The password for your database.
  4. DEFINE ('DB_HOST', 'localhost'); // The host for your database (stays the same 99% of the time)
  5. DEFINE ('DB_NAME', 'NAME'); // The actual name of the database its self.
  6.  
  7. $dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error());
  8.  
  9. @mysql_select_db (DB_NAME) OR die('Could not select the database: ' . mysql_error() ); 
  10. ?>

The code has been commented for you to know where to put your database information. Now we create our referer script, the full code is shown at the bottom.

PLAIN TEXT
PHP:
  1. <?php
  2. require('mysql_connect.php');
  3.  
  4. // Find out or base pure referer.
  5. $ref = $_SERVER['HTTP_REFERER'];

This is where we get our referer, except this is the whole referral URL, we don't want that. What if the same site links to you from www.website.com/page.html and www.website.com/page2.html. We want just the www.website.com part of the URL, so that is what we do next.

PLAIN TEXT
PHP:
  1. // Split our URL into bits.
  2. $url = explode("/",$ref);
  3.  
  4. // Take the 3rd part of the url (the main site)
  5. $surl = $url[2];
  6.  
  7. // Check to see if it contains the www. at the begining
  8. $found = ereg('\www.',$surl);

This is where we select just the www.website.com part of our URL, we explode our URL and separate it at the slashes (/) and assign it to $url.

Next we need to select the part of the URL that we want, so we take the 3rd part, since we used explode, this exploded our URL into an array, and to access an array you just write what you want, ($url[0] corresponds to the first object in the array, $url[1] corresponds to the second part in the array, so on and so forth), the 3rd part of our array ($url[2] which equals the 3rd part) is the www.website.com part of the URL.

Now we use some simple regular expressions to check if the our URL so far contains the www. at the beginning, since when you link to a site you can either put, website.com and leave out the www. or www.website.com and keep it in. So we just use a simple regular expression to check if the URL has it or not.

PLAIN TEXT
PHP:
  1. if ($found) {
  2.  
  3.     $url = $surl;
  4.  
  5. } else {
  6.  
  7.     $url = 'www.'.$surl;
  8.  
  9. }

Now, this is where we put the www. on the beginning of the URL, so we check if our variable $found is true. By true I mean, if our regular expression turned of true or false. If it is true, just continue on, if it didn't pass the test then that means there is no www. at the beginning and we need to add it on manually.

PLAIN TEXT
PHP:
  1. $query = "SELECT website, hits FROM referers WHERE website = '$url'";
  2. $result = mysql_query($query);
  3. $num = mysql_num_rows($result);

Now we create our query to check if the URL is already submitted into the database, so we make our query, execute it then use mysql_num_rows() to check how many rows are selected.

PLAIN TEXT
PHP:
  1. if ($num == 1) {
  2.     $site = mysql_fetch_array($result, MYSQL_ASSOC);
  3.    
  4.     if ($site['website'] != 'www.') {
  5.    
  6.         if ($site['website'] != 'www.tutorialcode.com') { // Make sure you put in YOUR website into this section.
  7.    
  8.             $hits = $site['hits'] + 1;
  9.             $website = $site['website'];
  10.            
  11.             $query = "UPDATE referers SET hits = $hits WHERE website = '$website'";
  12.             $result = mysql_query($query);
  13.        
  14.         }
  15.     }

Now, if the number of rows selected equals one, we only want to update our hits for that specific URL and not insert it into the database again.

Now, we also don't want to submit a blank into the database, so if we are just typing the URL directly into the address bar it will be a blank URL and will come out as www. (since we add it manually) so we make an if clause, saying if it does not equal just www. then continue, and also since we don't want to add your own website as a referral make sure you input the name of your website into the commented line.

Next since we used mysql_fetch_array() onto our query results, all of our results are in an array that we can access using a variable. So we want to add 1 to the number of hits the website has sent us, so we use $site['hits'] + 1 to add one to our hits.

We use an UPDATE query to just update our hits, pretty straight forward. Then we execute our query, and close our 2 if statements, now onto the else statement for our first if.

PLAIN TEXT
PHP:
  1. } else {
  2.  
  3.     if (!empty($surl)) {
  4.         $query = "INSERT INTO referers (website, hits) VALUES ('$url', '1')";
  5.         $result = mysql_query($query);
  6.     }
  7.  
  8. }

Now, this is where we would insert a new referer into the database, way up before we add the www. part to our URL we created a variable called $surl this was our selection of the 3rd part of our URL. So we check to make sure that it is not empty, so in other words if there actually is a referer, then we use use a simple INSERT query to insert our referer into the database.

PLAIN TEXT
PHP:
  1. $query = "SELECT * FROM referers ORDER BY hits DESC LIMIT 10"; // Change the limit to the top XX that you want to show.
  2. $result = mysql_query($query);
  3.  
  4. while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  5.     echo '<a href="http://'.$row['website'].'">'.$row['website'].'</a> - <b>'.$row['hits'].'</b><br />';
  6. }
  7. ?>

This is where we select our top 10 referers from the database, we write up our SELECT query, execute it then use a while() loop to loop out all the info, I just loop out a simple top 10 list, that shows the referer, a link back to the referer, and how many hits it has sent to your site.

Now I will just list out the full script for you -

PLAIN TEXT
PHP:
  1. <?php
  2. require('mysql_connect.php');
  3.  
  4. // Find out or base pure referer.
  5. $ref = $_SERVER['HTTP_REFERER'];
  6.  
  7. // Split our URL into bits.
  8. $url = explode("/",$ref);
  9.  
  10. // Take the 3rd part of the url (the main site)
  11. $surl = $url[2];
  12.  
  13. // Check to see if it contains the www. at the begining
  14. $found = ereg('\www.',$surl);
  15.  
  16. if ($found) {
  17.  
  18.     $url = $surl;
  19.  
  20. } else {
  21.  
  22.     $url = 'www.'.$surl;
  23.  
  24. }
  25.  
  26. $query = "SELECT website, hits FROM referers WHERE website = '$url'";
  27. $result = mysql_query($query);
  28. $num = mysql_num_rows($result);
  29.    
  30. if ($num == 1) {
  31.     $site = mysql_fetch_array($result, MYSQL_ASSOC);
  32.    
  33.     if ($site['website'] != 'www.') {
  34.    
  35.         if ($site['website'] != 'www.tutorialcode.com') {
  36.    
  37.             $hits = $site['hits'] + 1;
  38.             $website = $site['website'];
  39.            
  40.             $query = "UPDATE referers SET hits = $hits WHERE website = '$website'";
  41.             $result = mysql_query($query);
  42.        
  43.         }
  44.     }
  45.    
  46. } else {
  47.  
  48.     if (!empty($surl)) {
  49.         $query = "INSERT INTO referers (website, hits) VALUES ('$url', '1')";
  50.         $result = mysql_query($query);
  51.     }
  52.  
  53. }
  54.  
  55. $query = "SELECT * FROM referers ORDER BY hits DESC LIMIT 10"; // Change the limit to the top XX that you want to show.
  56. $result = mysql_query($query);
  57.  
  58. while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  59.     echo '<a href="http://'.$row['website'].'">'.$row['website'].'</a> - <b>'.$row['hits'].'</b><br />';
  60. }
  61. ?>

Just make sure you have your mysql_connect.php file in the same directory, if you change it then you must change the path to it in the require() at the top of the file.

And that's all! If you have any questions on how to use this, or don't quite understand something. Just make sure you leave a comment, I usually answer the same day!

Thanks,
Sean

Download Source Code
Referral Script Source