The analysis of attendance of a site referrals

The small introduction.

The referral is url from which the visitor comes on your site. For example, on page http://www.site.com/links.html there is a link to your site. If the person will press it{her} he will get on your site. Then url http://www.site.com/links.html will be your referral.

I do not argue, that at any respecting CMS (content management system, the control system of the maintenance{contents}) is modules (bots) which are responsible for gathering statistics. For the same at whom on a site does not cost CMS, there is an opportunity to practise a spelling and konfigurirovanii own mini-module of statistics.

It{He} can be realized in a small script which will trace pressing links, to write down them URL'HH in a database (in our example it MySQL) and to generate statistics as the schedule. And will help to understand, as all this works actually, not digging in modules of foreign developers.

It is required to us three files:

?         referer.sql (search to a DB on creation of the table where the statistics will be stored{kept});

?         referer.php (a script);

?         viewreferer.php (a script for viewing statistics).

For creation of the table referer it is necessary to execute search referer.sql. his  maintenance{contents}:

CREATE TABLE referer (

       url varchar (100) NOT NULL,

       hits int (10) DEFAULT '0' NOT NULL,

       PRIMARY KEY (url)

);

Now we shall engage in a file referer.php. We shall set values to variables:

$hostname = "localhost"; // it is given yours khosterom. In most cases it - localhost (sometimes it happens 127.0.0.1)

$username = "your-username"; // a login name - your login for connection to a database

$password = "your-password"; // you should receive it  from the hosting - provider

$userstable = "referer"; // the table in which results of job of our script will be stored{kept}

$dbName = "your-db-name"; // a name of a DB which contains the table referer

In a variable $ref we bring the information on the link - referere:

$ref = strtolower ($HTTP_REFERER);

With the help of function strtolower we translate value of a variable $ref in the bottom register. It is done{made} that links such as www.site.com, www.Site.com or WWW.SITE.COM were the identical register.

If a variable $ref not empty,

if (! empty ($ref)) {

That we are connected to a DB:

mysql_connect ($hostname, $username, $password) or die (mysql_error ());

mysql_select_db ("$dbName") or die (mysql_error ());

We make sample of a column url which value coincides with $ref

$query = " select * from $userstable where url = '$ref' ";

The variable $rows contains quantity{amount} of concurrences

$result = mysql_query ($query);

$rows = mysql_num_rows ($result);

If it is not found any recording (t.e c such url'a yet there were no referrals)

if ($rows == 0) {

We establish{install} value of a variable $hits in "1"

$hits = "1";

And further simply we carry out an insert url'b and a variable $hits in our table (referer):

$query1 = " insert into $userstable (url, hits) values ('$ref', ' $hits') ";

We carry out search:

mysql_query ($query1);

}

If recordings on given url (t.e c it url'a referrals already came) have been found,

else {

$hitquery = " select hits from $userstable where url = '$ref' ";

$result2 = mysql_query ($hitquery);

$row = mysql_fetch_array ($result2);

$hits = $row ["hits"];

Inkrementiruem value of a variable $hits (it is increased on "1")

$query2 = " update $userstable set hits = hits+1 where url = '$ref' ";

mysql_query ($query2);>

}

}

In that page of a site for which the statistics on referrals (as a rule is necessary is main page, index.php), it is necessary to insert the link to a script referer.php. It is supposed, that files referer.php and index.php are in one folder.

include ("referer.php");

On it contents of a script referer.php also come to an end. But, as I have written above, is as well graphic statistics. It will be our third file - viewreferer.php.

We set variables for connection to a DB:

$hostname = "localhost";

$username = "your-username";

$password = "your-password";

$userstable = "referer";

$dbName = "your-db-name";

As this script generates the table with the schedule, that is the sense for the certain ranges of values is hit to appoint the certain colors.

$color10 = "*FFFF00";

$color20 = "*FF0000";

$color50 = "*008000";

$color1k = "*0000FF";

$color2k = "*0000A0";

$color5k = "*000040";

We are again connected to a DB:

mysql_connect ($hostname, $username, $password) or die (mysql_error ());

mysql_select_db ("$dbName") or die (mysql_error ());

We do{make} sample of a DB and we order her  on decrease (desc) quantities{amounts} it is hit (a column hits).

$query = " select * from $userstable order by hits desc ";

$result = mysql_query ($query);

We count up quantity{amount} of lines with unique referrals (it simply number of lines in our table referer).

$number = mysql_num_rows ($result) or die (mysql_error ());

$i = 0;

If it is not found any recording (the table is empty) we deduce{remove} the message:

if ($number == 0) {

echo " <center> <p> <b> Data on referrals it is not revealed! </b> </center> ";

}

If referrals are:

elseif ($number> = 1) {

while ($i <$number) {

$row = mysql_fetch_array ($result);

$hits = $row ["hits"];

$ref = $row ["url"];

We choose color for the current value hits:

if ($hits <10) {

$color = "$color10";

}

elseif (($hits> = 10)

and ($hits <20)) {

$color = "$color20";

}

elseif (($hits> = 20)

and ($hits <50)) {

$color = "$color50";

}

elseif (($hits> = 50)

and ($hits <100)) {

$color = "$color1k";

}

elseif (($hits> = 100)

and ($hits <200)) {

$color = "$color2k";

}

elseif ($hits> = 200) {

$color = "$color5k";

}

The tablet with the schedule is under construction as result of MySQL-search. The first column contains the name url'b, and in the second - quantity{amount} is hit, and in thirds - a color strip.

echo " <div align = "left"> ";

echo " <table cols = "3" border = "0" width = " 100 % "> <tr> <td align = "left" width = "400"> <b> <a href = " $ ref "> $ref </a> </b> </td> ";

echo " <td align = "right" width = "60"> <b> $hits </b> </td> ";

echo " <td align = "left" width = " $ hits " bgcolor = " $ color "> </td> </tr> ";

$i ++;

echo " </table> n ";

}

}

So, all is ready! Now it is possible to cause periodically a script viewreferer.php and to look graphic statistics of transitions to your site.