Tracking entry referrers with PHP in MT
August 16, 2003
I’ve always wanted to track referrers for each entry and at the same time be able to tell which entries are the more popular ones by keeping track of each entry’s hit count.
So I did the smart thing first by Googling to see if anyone else had done it. I found the AWStatsReferers MT plugin at the MT Plugin Directory but it requires your host to have AWStats installed. My host doesn’t, and I wasn’t going to get them to install AWStats just so I can track referrers for my blog. Refer is a referrer tracking script that turned up - it actually seems like it could do what I wanted with a little tweaking, but a look at the code left me thinking there should be a simpler way to achieve my more specific goals (i.e. track referers and, consequently, hit count for each entry).
So, hey, I decided to whip up a simple hack of sorts using PHP and a database table to store the referrers.
Tracking entry referrers with PHP in MT
Requirements: MovableType, webhost that supports PHP, database (MySQL in this tutorial)
A plain text version of this tutorial is also available here.
- First off, any page where you wish to display the referrers or hit count, including your archives and main index page, need to have a .php extension (or any other extension that will cause the file to be pre-processed by PHP). You can do so by renaming your Archive File Templates to have a .php extension (go to Weblog Config -> Archiving). The same goes for your main index page (go to Templates) .
- Create the database table. The SQL is:
CREATE TABLE `mt_entryhits` (
`entry_id` INT(11) NOT NULL
REFERENCES mt_entry(entry_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
`referer` VARCHAR(255) NOT NULL
);You can use either phpMyAdmin, the MySQL command-line client or whatever suits your boat.
- Create a file named db-config.php with the following code:
<?php
$host = ‘host’; // database server hostname
$user = ‘user’; // database username
$password = ‘password’; // database password
$database = ‘database’; // MT database
?>Replace the values of the variables as per your configuration. Upload it in your blog’s top level directory (i.e. the Local Site Path of your weblog, as specified in the Weblog Configuration screen).
- Login to your MovableType installation and Manage your weblog. Go to Manage -> Templates -> Archive-Related Templates -> Individual Entry Archive.
-
At the top of the template, add the following lines:
<?php
error_reporting(0);include( ‘<$MTBlogSitePath$>db-config.php’ );
mysql_connect( $host, $user, $password );
mysql_select_db( $database );// this is the list of referrers you don’t want to record
// use the following format within the parentheses:
// ‘http://domain1.com’, ‘http://domain2.com’, …
// leave it blank if you want to record all referrers
$ignore_list = array( ‘http://referrer.you.want.to.ignore’ );// check referrer string to determine if we want to record it
$ignore_referrer = false;
foreach( $ignore_list as $ignore ) {
if( stristr( $_SERVER[’HTTP_REFERER’], $ignore ) !== false ) {
$ignore_referrer = true;
break;
}
}// record the referer
if( !$ignore_referrer ) {
mysql_query( “INSERT INTO mt_entryhits(entry_id, referer) VALUES (<$MTEntryID$>, ‘{$_SERVER[’HTTP_REFERER’]}’)” );
}// 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];// get the list of referers
$sql =
‘SELECT referer, COUNT(*) AS count
FROM mt_entryhits
WHERE entry_id=<$MTEntryID$>
GROUP BY referer
ORDER BY count DESC’;$rs = mysql_query( $sql );
?>This code snippet is specific to MySQL - you should be able to change it easily for PostgreSQL or SQLite (if not, let me know and I’ll post code snippets for those RDBMSs too). Of course, you can plug in a database abstraction library like the excellent ADODB, but I leave out that portable solution in favor of database-specific built-in function calls because MovableType supports only MySQL, PostgreSQL, or SQLite.
-
In the same template (the Individual Entry Archive template), look for a good place to display the entry hit count and insert the following code:
Read <?=$hits?> time<?php if($hits != 1) echo ’s’;?>I place them in the “Posted by” bit at the end of each entry, which for the default MT template is
<span class=”posted”>Posted by <$MTEntryAuthor$> at <$MTEntryDate$>
<MTEntryIfAllowPings>
| <a href=”<$MTCGIPath$><$MTTrackbackScript$>?__mode=view&entry_id=<$MTEntryID$>” onclick=”OpenTrackback(this.href); return false”>TrackBack</a>
</MTEntryIfAllowPings>
| Read <?=$hits?> time<?php if($hits != 1) echo ’s’;?>
<br /></span> -
Now, it’s time to display the referrers. Again in the same template, add the following code where you wish to display the referrers.
<?php
// display the list of referers
echo ‘<div class=”sidetitle”>Referrers</div>’, “\n”;
echo ‘<div class=”side”>’, “\n”;
echo ‘<ul>’, “\n”;
while( $row = mysql_fetch_assoc( $rs ) ) {$referer = $row[’referer’];
$count = $row[’count’];echo ‘<li>’;
if( strpos( $referer, ‘http://’ ) !== false ) {
echo ‘<a href=”‘, $referer, ‘” target=”_blank”>’;// truncate URL if too long
if( strlen($referer) > 90 ) {
$referer = substr($referer, 0, 90) . ‘…’;
}
echo $referer, ‘(’, $count, ‘)’;
echo ‘</a>’;
}
else {
// truncate URL if too long
if( strlen($referer) > 90 ) {
$referer = substr($referer, 0, 90) . ‘…’;
}// handle cases where there is no referrer
if( $referer == ” ) {
$referer = ‘Direct hit or unknown referrer’;
}
echo $referer, ‘(’, $count, ‘)’;
}
echo ‘</li>’;
echo “\n”;
}
echo “\n”;
echo ‘</ul>’, “\n”;
echo ‘</div>’;
?>You can format the HTML to suit your current template.
-
Phew! The hardest part is now over. All that’s left is to display the number of times each entry has been read in the other archive pages and the main page (Main Index). I’ll only work through the changes to the Main Index template, since it will be similar for the other templates (Category Archive, Date-Based Archive, any other templates you have created that displays blog entries).
At the top of the template, add the following lines:
<?php
error_reporting(0);include( ‘<$MTBlogSitePath$>db-config.php’ );
mysql_connect( $host, $user, $password );
mysql_select_db( $database );
?>Look for the <MTEntries> tag in the template and add the following lines after it:
<?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 that we’ve the number of hits in the variable $hits, we can display the entry hit count as before by adding the following line of code within the <MTEntries> tag:
Read <?=$hits?> time<?php if($hits != 1) echo ’s’;?> - Do the same for the other archive templates (Category Archive, Date-Based Archive, any other templates you have created that displays blog entries).
- Rebuild your site and you’re done!
Thanks to everyone who gave comments and asked questions at the MovableType support forums and elsewhere, who pointed out things I overlooked.
Update: Matt Moore has an alternative (based on this very post! ;-)) that includes collection of the page title of the referrer, and also the search terms used to get to your blog entry. Check out his entry entitled “How to collect referrer data”
| Category: 
entry referrers
redemption in a blog: Tracking entry referrers with PHP in MT - uses MySQL database, and looks much more elegant…
Posted by: lovelinks on August 17, 2003 8pm