PHP and MySQL: How to collect referrer data
September 21st, 2003
Ok, here’s a special interest post: How to collect and display data on referrers, search terms, and number of reads on an individual post basis. You’ll need PHP, a MySQL database, and enough patience to work with your templates a bit.
You’ll have to read the extended entry, otherwise this makes my front page twice as long.
First, create a table in your MovableType MySQL database:
CREATE TABLE `mt_entryhits` (
`entry_id` INT(11) NOT NULL
REFERENCES mt_entry(entry_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
`referrer` VARCHAR(255) NOT NULL,
`time` INT(25) NOT NULL,
`search` INT(1) NOT NULL,
`title` VARCHAR(255) NOT NULL
);
Next, create a file called db-config.php:
<?php $host = 'localhost'; // database server hostname $user = 'user'; // database username $password = 'password'; // database password $database = 'database'; // MT database ?>
Replace all quoted fields with your own info. Put this file in your blog’s top level directory, or create a referrers subdir like I did. Next, put the following code in your templates so that it’s inbetween the <head>…</head> tags on all your pages:
<?php
error_reporting(0);
require '<$MTBlogSitePath$>referrers/URLHelper.php'; //replace with your path
include( '<$MTBlogSitePath$>referrers/db-config.php' ); //ditto
mysql_connect( $host, $user, $password );
mysql_select_db( $database );
$referrer = "{$_SERVER['HTTP_REFERER']}";
$time = time();
$my_ip = '10.1.1.1'; //don't register hits or referrals from my IP addresses
//replace with your own IP
$remote_ip = "{$_SERVER['REMOTE_ADDR']}";
$remote_agent = "{$_SERVER['HTTP_USER_AGENT']}";
if (strpos($referrer,'://www.') == 4) { $referrer = 'http://' . substr($referrer, 11); } //strip www.
if1 {
$referrer = $referrer . '/'; //add closing slash
}
//block self referrals from being registered
//replace mattmoore.ws and tbotcotw.com with your own domains
if (strpos($referrer, 'ttp://www.mattmoore.ws')) { $referrer = ''; }
if (strpos($referrer, 'ttp://mattmoore.ws')) { $referrer = ''; }
if (strpos($referrer, 'ttp://www.tbotcotw.com')) { $referrer = ''; }
if (strpos($referrer, 'ttp://tbotcotw.com')) { $referrer = ''; }
if (strpos($referrer, 'subhonker')) { $referrer = ''; }
// record the referrer
if2 || (strpos($remote_agent,'slurp')))) {
mysql_query( "INSERT INTO mt_entryhits(entry_id, referrer, title, time) VALUES (0, '$referrer', '', $time)" );
}
?>
And put this additional code in the top of your individual archive template:
<?php
// record the referrer
if ($my_ip !== $remote_ip) {
mysql_query( "INSERT INTO mt_entryhits(entry_id, referrer,title,time) VALUES (<$MTEntryID$>, '$referrer', '', $time)" );
}
// get the no. of times this entry has been read
$rs = mysql_query( 'SELECT COUNT(*) FROM mt_entryhits WHERE entry_id=<$MTEntryID$>' );
$row = mysql_fetch_row( $rs );
$hits = $row[0];
?>
Now download the code to pull the titles off of pages from here (registration required). Put this in the same directory as you db-config.php.
Then put the following code at the very bottom of your individual archive template. It needs to be at the bottom because the title resolution can take a few seconds the first time.
<?php
$rs = mysql_query( "SELECT * FROM mt_entryhits" );
$deletetime = time() - (60 * 60 * 24);
mysql_query( "DELETE FROM mt_entryhits WHERE time < $deletetime and entry_id = 0" );
while ($row = mysql_fetch_assoc($rs)) {
if ($referrer == $row['referrer']) {
$title = $row['title'];
}
}
if3 && ($referrer !== '')) {
if ($title1 = getquery($referrer)) {
$title = addslashes($title1);
$search = 1;
}
else { $search = 0; }
if (!($search)) { $title = addslashes(URLHelper::getTitle($referrer)); }
if (strpos($referrer,'encquery')) {
$title = addslashes(substr($title,26,-2));
$search = 1;
}
if (strpos($referrer,'info.dogpl')) {
$title = substr($title,21);
$search = 1;
}
if4 { $title = $referrer; }
if (!($search) && (strlen(trim($title)) > 45)) {$title = substr($title,0,42) . "..."; }
else if (trim(strlen($title)) > 52) { $title = substr($title,0,49) . "..."; }
}
mysql_query( "UPDATE mt_entryhits SET title='$title', search=$search WHERE referrer='$referrer'" );
function getquery($ref){
$queries = array("search=", "epq=", "p=", "qkw=", "q=", "query=", "va=", "qry=", "userQuery=", "searchfor=", "question=", "sb=");
while(list($key,$val)=each($queries)){
$pos = 0;//initialize
$pos = strpos($ref, $val);
if (strpos($ref,'encquery')) { $pos = 0; }
#$ret .= "($pos $val)";
if($pos > 0){
$endpos = strpos ($ref, "&", $pos);
if(trim($endpos) == ""){
$search = substr($ref, ($pos+strlen($val)), strlen($ref)-$pos);
}else{
$search = substr ($ref, ($pos+strlen($val)), ($endpos-$pos)-2);
}
$endpos = strpos ($search, "&");
if(trim($endpos)) {
$search = substr ($search, 0, $endpos);
}
$searched = trim($search);
$searched = str_replace("+", " ", $search);
$searched = str_replace("%2E", ".", $searched);
$searched = str_replace("%2B", "+", $searched);
$searched = str_replace("%2b", "+", $searched);
$searched = str_replace("%22", '"', $searched);
$searched = str_replace("%22", '"', $searched);
$searched = str_replace("%20", " ", $searched);
$searched = str_replace("%3F", "?", $searched);
$searched = str_replace("%28", "(", $searched);
$searched = str_replace("%27", "'", $searched);
$searched = str_replace("%3A", ":", $searched);
$searched = str_replace("%2C", ",", $searched);
$searched = str_replace("%24", "$", $searched);
$searched = str_replace("%21", "!", $searched);
$searched = str_replace("%5C", "\\", $searched);
$searched = str_replace("%2F", "/", $searched);
if(trim($searched) == ""){
$searched = substr($ref, $pos, strlen($ref)-$pos)."($endpos)";
}
}
}
if(trim($searched) !== "") {
return "$searched";
}else{
return false;
}
}// end function
?>
Now your table in your database should be filling up with info on referrers. The last step is printing the info. Put this code somewhere around the posted by stuff in your archive templates and main index:
<?php
if ($hits) {
print " | Read ";
echo "$hits";
print " time";
if($hits != 1) echo 's';
}
?>
In your main index and archive templates, other than individual, put this code just after <MTEntries>:
<?php // get the no. of times this entry has been read $rs = mysql_query( 'SELECT COUNT(*) FROM mt_entryhits WHERE entry_id=<$MTEntryID$>' ); $row = mysql_fetch_row( $rs ); $hits = $row[0]; ?>
Now put this code in your individual archive sidebar:
<?php
// display the list of referrers
echo '<div class="sidetitle">Referrers</div>', "\n";
echo '<div class="side">', "\n";
$sql = 'SELECT search, time, title, referrer, COUNT(*) AS count FROM mt_entryhits WHERE entry_id=<$MTEntryID$> GROUP BY referrer ORDER BY count DESC, title';
$rs = mysql_query( $sql );
$n = 0;
while( $row = mysql_fetch_assoc( $rs ) ) {
$referrer1 = $row['referrer'];
$title = $row['title'];
$count = $row['count'];
$search = $row['search'];
if5 {
$n++;
echo '<a href="', $referrer1, '" target="_blank">';
echo $title . "</a> [" . $count . "]<br />\n";
}
}
if (!($n)) {
echo "No referrals yet<br />";
}
else {
echo "<br />\n";
echo 'referrers [referrals]<br />';
}
echo '</div>';
echo "<div class='sidetitle'>Search Terms</div>\n";
echo "<div class='side'>";
$rs = mysql_query( $sql );
$n = 0;
while( $row = mysql_fetch_assoc( $rs ) ) {
$query = '';
$referrer1 = $row['referrer'];
$title = $row['title'];
$count = $row['count'];
$search = $row['search'];
if ($search == 1) {
$n++;
$query = "<a href='" . $referrer1 . "'>" . $title . "</a>";
echo $query . ' [' . $count . ']' . "<br />\n";
}
}
if (!($n)) { echo "No search terms yet<br />"; }
else { echo "<br />search terms [hits]"; }
echo "</div>";
?>
And, finally (whew!), put this slightly modified code in your main sidebar:
<?php
// display the list of referrers
echo '<div class="sidetitle">Referrers</div>', "\n";
echo '<div class="side">', "\n";
$deletetime = time() - (60 * 60 * 24);
$sql = 'SELECT search, time, title, referrer, COUNT(*) AS count FROM mt_entryhits WHERE entry_id=0 GROUP BY referrer ORDER BY count DESC, title';
$rs = mysql_query( $sql );
while( $row = mysql_fetch_assoc( $rs ) ) {
$referrer1 = $row['referrer'];
$title = $row['title'];
$count = $row['count'];
$search = $row['search'];
if6 {
echo '<a href="', $referrer1, '" target="_blank">';
echo $title, '</a> [', $count, "]<br />\n";
}
}
echo "<br />\n";
echo 'last 24 hours [referrals]<br />';
echo '</div>';
echo "<div class='sidetitle'>Search Terms</div>\n";
echo "<div class='side'>";
$sql = 'SELECT search, time, title, referrer, COUNT(*) AS count FROM mt_entryhits WHERE entry_id=0 GROUP BY referrer ORDER BY time DESC, count DESC';
$rs = mysql_query( $sql );
$n = 0;
while(($n < 40) && ($row = mysql_fetch_assoc( $rs))) {
$query = '';
$referrer1 = $row['referrer'];
$title = $row['title'];
$count = $row['count'];
$search = $row['search'];
if ($search == 1) {
$n++;
$query = "<a href='" . $referrer1 . "'>" . $title . "</a>";
echo $query . ' [' . $count . ']' . "<br />\n";
}
}
echo "<br />\n last 40 terms [hits]<br /></div>\n";
?>
Well, I think that was complicated and involved enough. I’m sure I made some mistakes, please correct me quickly.
(Original code via redemption in a blog, and search term parsing code via joeldg)
Popularity: 7% [?]
- substr($referrer,-4) == '.com') || (substr($referrer,-4) == '.net') || (substr($referrer,-4) == '.org') || (substr($referrer,-4) == '.gov') || (substr($referrer,-3) == '.ws') || (substr($referrer,-3) == '.gs' [↩]
- $my_ip !== $remote_ip) && !((strpos($remote_agent,'googlebot' [↩]
- $title == '') || (strpos($title,'ttp://' [↩]
- trim($title) == '-') || (trim($title) === '' [↩]
- strpos($referrer1,'ttp://') == 1) && ($search == 0) && (trim($title) !== '-') && (trim($title) !== '' [↩]
- strpos($referrer1,'ttp://') == 1) && ($search == 0) && (trim($title) !== '-') && (trim($title) !== '' [↩]
referer und read
ich habe mal das bisherige referer-skript auf das sch
Look Mom!
See what I’ve done? The side bars (just left for now) are collapsable, and not in the stupid way that the blogrolling is collapsable… It actually doesn’t transmit the data if it doesn’t need to, meaning an even quicker download…
Desværre ingen lingerie fra 2003 idag
Jeg har brugt aftenen på at få referers på min Movabletype Weblog. Tracking entry referers, er en god beskrivelse og Matt Moore har lavet en revideret version som også tracker søgeord samt referer title. Den vil jeg kigge…
Hi there, I have just implemented the Referer tracking script found at Redemption in a blog, and found yours as well.
But you should update the post because some of it is garbled (Tested with Internet Explorer and Mozilla) in the fourth box with code and includes additional comments to the coda and a link to download the URLHelper.php class.
And in the code view all the php start and end tags are faulty as well.
I’ll fix all that after dinner. Thanks for pointing out the errors!
Now it looks much better, I’ll try implementing tonight.
Strange no one saw the error before me.
I’m not really surprised, not many people follow this complicated a hack.
Don’t use these directions just yet, I’ll update with my latest code later today.
how do i register? where?
Register for what?
register for this:
Now download the code to pull the titles off of pages from here or, to get the absolute latest version, here (registration required). Put this in the same directory as you db-config.php.
Hi Matt Moore!
I tried it, and it works quite fine on my Inividual pages. But i cant get it working on the main-index. No errors, exactly the same code, but no results in the recordsets, thous the SQLs work fine within the database and generate enough lines.. any ideas? :p
Ninnghizidha.