|Register--Login--Top 20 Posters--Search Topics|
Rank:Settler of Bobland
Group: Head Administrator
PM ID and RPS ID: 1
View Member Photo
|Last replied to on Sat May 05, 2012 02:23:17|
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:
Lets go through it slowly. The first part:
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.
Now, the next part of the code:
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, between the first and second "/" is $dom 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:
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:
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.