Often there is a desire to see people come to your site to go there to read what they write about us, and zealously enter into polemics without departing from the ticket office. It would seem that such a popular thing as Google Analitycs should do it, but there is one problem - GET-parameters are cut off in the report, and if you see that the link is from a huge forum, then you still have to find the right topic, which takes time (it’s worth mentioning that Google Analitycs requires additional JS code on the pages, which also takes time and traffic).

We have 3 options:

1. Other JS-based systems, analogs of Google Analitycs, decided not to touch, because The idea of ​​additionally weighing up JS clients does not like
2. AwStats and other log analysis tools are a bulletproof solution (and probably the best) if you have full access to the server. Does not require code modification at all. It is a pity that I have shared-hosting, and there is no direct access to the logs.
3. Any means requiring php-instrumentation (since the code is called on each page).

Having rummaged in the internet, I found some quite good variants of the latter type (PHP), some were not so good in terms of functionality (in particular, it didn’t show referrer badly), eventually settled on TraceWatch , as it turned out to be Iranian-made. :-) Having started the installation, I was shocked - all the code was processed by the obfuscator, and the author in the license prohibits him from bringing to normal form. This, along with free of charge, immediately caused a great suspicion. After this crap failed to make money not in the default directory (the path to the inclusions was prescribed absolute and not relative), and attempts to fix it to the code, I realized that I would not wait for anything good from this craft.

Then I decided to write my Referrer tracker

The task was as follows: show referrals broken down by day. You need to count how many times you came from (with GET parameters), filter Google, nothing else is needed. No IP log, countries, counting visits, etc. (although it's easy enough to implement)

MySQL 5 database

CREATE TABLE ` ref ` (
`id` int (11) NOT NULL auto_increment,
`ref_url` varchar (950) NOT NULL ,
`ref_date` date NOT NULL ,
`ref_count` int (11) NOT NULL default '0' ,
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`ref_url`,`ref_date`),
KEY `ref_date` (`ref_date`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=138 ;



')
And here - the holy grail of MySQL 5 - stored procedures with error checking - guaranteed a maximum of 1 query to the database for 1 user. In this case, sometimes all the same it is possible (albeit with a very small probability) an error per unit when creating a record (since stored procedures are not atomic), but I thought that the risk / loss of speed from locking / unlocking the table is not worth this lost item .

DELIMITER $$
CREATE PROCEDURE `process_url`(new_url VARCHAR (900))
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION UPDATE ref SET ref_count=ref_count+1 WHERE ref_date=CURDATE() AND ref_url=new_url;
INSERT INTO ref (ref_url,ref_date,ref_count) VALUES (new_url,CURDATE(),1);
END $$

NB : INSERT INTO ref SET ref_url = '$ ref', ref_date = NOW () ON DUPLICATE KEY UPDATE ref_count = ref_count + 1 - thanks to the Habroyuser Nc_soft, we know that this code is simpler and faster 2-4 times: - )

We collect statistics

This should be inserted somewhere in the general file that is included everywhere. It is understood that the connection to the database has already been established, and the database is selected:

function process_referrer()
{
$referrer = $_SERVER[ "HTTP_REFERER" ];
if (strpos($referrer, "your_site_name_here" )===FALSE && strlen($referrer)>0)
{
//google support, remove redundant parameters & multiple records for the same query from different countries like google.nl
if (strpos($referrer, "www.google" )!==FALSE)
{
if (preg_match( "/[?&]q=([^&]+)/" , $referrer, $matches)) {
$referrer = "Google Search: " .htmlspecialchars(urldecode($matches[1]));
} else
$referrer = "Google Search: broken query, $referrer" ;
}
//and finally, store it :crazy:
sqlexec( 'CALL process_url("' .mysql_real_escape_string($referrer). '");' );
}
}


What we see here is not pulling the database if the user walks through our pages, or if he has typed the address with his hands (or a cunning firewall has cut it). Do not forget that the evil hacker could give us SQL Injection in the referrer, so mysql_real_escape_string is required, as always.

The main thing remains :-)

See the results

$max_date = sqlcount( "select MIN(ref_date) from (select DISTINCT ref_date from ref ORDER by ref_date DESC LIMIT 7) as tmp" );
$result = sqlexec( "select * from ref WHERE ref_date>='$max_date' order by ref_date DESC,ref_count DESC" );
$prev_date = "" ;
while ($row = mysql_fetch_array($result))
{
if ($row[ 'ref_date' ]!=$prev_date)
{
$articlesList .= "<h1>{$row['ref_date']}</h1>" ;
$prev_date = $row[ 'ref_date' ];
}
//Print $row data here with nice design
}

At the beginning, the last 7 dates in the database are determined (although it is possible to subtract 7 from the current date). Then we get all the records for the last 7 days and display them by day. Links can be immediately with target = "_ blank"

Auxiliary procedures

function sqlexec($sql)
{
if (!($result = mysql_query($sql))) showerror();
return ($result);
}
function sqlcount($sql)
{
$res = mysql_fetch_array(sqlexec($sql));
return ($res[0]);
}


Result

You can click on the links and immediately read what they write about you :-)


The original is here: http://3.14.by/ru/read/writing-php-referrer-tracker