Tracking entry referrers with PHP in MT

In: Blogging|Web development

16 Aug 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.

  1. 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) .
  2. 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.

  3. 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).

  4. Login to your MovableType installation and Manage your weblog. Go to Manage -> Templates -> Archive-Related Templates -> Individual Entry Archive.
  5. 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.

  6. 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>
  7. 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.

  8. 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’;?>
  9. Do the same for the other archive templates (Category Archive, Date-Based Archive, any other templates you have created that displays blog entries).
  10. 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

81 Responses to Tracking entry referrers with PHP in MT

Avatar

lovelinks

August 17th, 2003 at 8pm

entry referrers
redemption in a blog: Tracking entry referrers with PHP in MT – uses MySQL database, and looks much more elegant…

Avatar

kadyellebee

August 18th, 2003 at 6am

linkyness
I’m finding such good links while catching up on news reads that I just had to share some of them….

Avatar

Links

August 19th, 2003 at 12am

http://ko.offroadpakistan.com/links/cat_mt.html#000481
Tracking entry referrers with PHP in MT…

Avatar

Considering...

August 19th, 2003 at 3am

A better wheel. Maybe.
Today, I tried to install a bit of support for tracking entry referrers. As you can see, I haven’t had much luck getting it to work. I’ll probably undo that work in a little while unless I find out how…

Avatar

unchained thoughts

August 19th, 2003 at 4pm

focus
Thank you for everyone’s thoughtful and helpful suggestions about my wedding woes. I am feeling better about it now, trying to think about what we want and I will reconcile the whole dress thing in the months to come. Much…

Avatar

Live in the Delirious Cool

August 22nd, 2003 at 2am

Posted elswhere, counted here.
Two more excellent advanced widgets for tracking what’s said and seen here and elswhere – and a plug for my favorite webring.

Avatar

scriptygoddess.com

August 22nd, 2003 at 1pm

Referrer script and entry hit tracker
Donna posted a few neat little tidbitds – but the one I was most interested in was the referrer script that also tracks hits on an entry… Like “read 100 times” etc. Here’s the script from redemption in a blog….

Avatar

kiesows.de

August 27th, 2003 at 12am

referrers
meiner einer hatte mal wieder lust etwas am blog zu verändern, und da ich neulich über Tracking entry referrers with…

Avatar

Pinging Knight

August 27th, 2003 at 10pm

Minor Blog Update
Made a few minor updates to the design and workings of my blog: Comments are now numbered. Your email will…

Avatar

Cookie Crumbs

August 31st, 2003 at 2am

Tracking entry referrers with PHP in MT
Tracking entry referrers with PHP in MT Want to know who is coming to see your entries? Track them!…

Avatar

Geeky Bookmarks

September 6th, 2003 at 9am

Tracking entry referrers with PHP in MT
redemption in a blog: Tracking entry referrers with PHP in MT via Christine’s Cookie Crumbs…

Avatar

JayAllen - The Daily Journey

September 6th, 2003 at 5pm

Tracking entry referrers in MT

Avatar

The Blog of the Century of the Week

September 14th, 2003 at 3am

Changing some scripts
I’m junking a bunch of CGI scripts and going to PHP. I’m doing this because it’s much easier to put PHP data in a page, since you just set your variables at the top and then you can call them…

Avatar

The Blog of the Century of the Week

September 21st, 2003 at 8pm

PHP and MySQL: How to collect referrer data
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…

Avatar

//gtmcknight

October 6th, 2003 at 5am

Random Updates…
First, and foremost Radiohead was one of the best concerts I’ve ever been to. They are simply amazing, and the outdoor amphitheatre made it alot of fun as well. Thom Yorke’s performance was moving, funny, exciting, and hypnotic all at…

Avatar

meowy's Got Stuff to Say

October 20th, 2003 at 6am

Counts! Project for tomorrow
Yay! Thanks to Violet at the MT forums, I think I’ve got just what I need. :) Her site is…

Avatar

distant, early morning

October 26th, 2003 at 6pm

Entry hit counter and referrers
I was asking about some stuff over on the movable type support forums, which a wonderful gal named Violet answered. Because she was so helpful I was curious and popped over to her weblog. I noticed that she had hit…

Avatar

highlyoverrated.info - do you need more?

November 9th, 2003 at 7am

mt resources, die x-te
für mt gibt es alles und das auch noch tonnenweise ;) und das ist genial so: redemption in a blog…

Avatar

Fembat.Net :: Journal

November 24th, 2003 at 9pm

MT Individual Entry Referrals
I’ve always wanted a more precise method of tracking referrers for each entry, and finally after months of trying to come up with something myself I finally fell upon a terrific MT Hack at redemption in a blog. This also…

Avatar

pixelgraphix

March 7th, 2004 at 2am

Ressourcen für Movable Type
Wer auf der Suche nach einem Publishing-System für die eigene Internet-Präsenz ist, stößt früher oder später auf Movable Type. Ein Programm, das von Six Apart vor allem als Weblog-Werkzeug entwickelt und unterstützt …

Avatar

pixelgraphix

March 23rd, 2004 at 2am

Und sie validieren doch
Auf pixelgraphix verwende ich die Referrer und Hits Zählung, die von Cheah Chu Yeow auf redemption in a blog veröffentlicht wurde. Durch die Anzeige der Referrer und der in ihnen enthaltenen Sonderzeichen fielen die entsprechenden Seiten beim…

Avatar

pixelgraphix

March 23rd, 2004 at 2am

Und sie validieren doch
Auf pixelgraphix verwende ich die Referrer und Hits Zählung, die von Cheah Chu Yeow auf redemption in a blog veröffentlicht wurde. Durch die Anzeige der Referrer und der in ihnen enthaltenen Sonderzeichen fielen die entsprechenden Seiten beim…

Avatar

pixelgraphix

March 23rd, 2004 at 3am

Und sie validieren doch
Auf pixelgraphix verwende ich die Referrer und Hits Zählung, die von Cheah Chu Yeow auf redemption in a blog veröffentlicht wurde. Durch die Anzeige der Referrer und der in ihnen enthaltenen Sonderzeichen fielen die entsprechenden Seiten beim…

Avatar

pixelgraphix

March 23rd, 2004 at 9pm

Und sie validieren doch
Auf pixelgraphix verwende ich die Referrer und Hits Zählung, die von Cheah Chu Yeow auf redemption in a blog veröffentlicht wurde. Durch die Anzeige der Referrer und der in ihnen enthaltenen Sonderzeichen fielen die entsprechenden Seiten beim…

Avatar

shirtrat(dot)net

March 24th, 2004 at 7am

A few site changes
Long time no post. It’s been almost two weeks. Haven’t had much to say actually. Been working behind the scenes on a few things with my blog. I added a referrer to the bottom of each individual entry so you…

Avatar

hello-lovely

July 6th, 2004 at 11pm

More Blogsnobbery
Because I have nothing of importance to post (most of you probably already know that Kerry picked John Edwards as his running-mate), here’s another journey through Blogsnob Land, ended by a page without a blogsnob link! :( + Fembat blogs…

Avatar

Segmentation Fault: Core dumped..;-)

July 21st, 2004 at 2am

I am off..
..for a few days, that is. Will be taking some time off from active blogging starting this very moment and will be back in business by 07/20/2004 08/01/2004, if not sooner. Have a few personal, professional and some blog related housekeeping to do. Th…

Avatar

SSP

September 27th, 2004 at 2pm

How long i searched for a Hack like this!
Thank you very much, but i habe a mistake somewhere and my knowledge is to small to find it.

If i add this line:

$rs = mysql_query( ‘SELECT COUNT(*) FROM mt_entryhits WHERE entry_id=< $MTEntryID$>’ );

i get the following error:

Parse error: parse error, unexpected T_STRING in /www/htdocs/denkblog/index.php on line 73

(line 73 is the Line with $rs)

I would be so glad if you could give me a hint.
Thanks!

Avatar

Hongkong

November 5th, 2004 at 7am

Thank you once more for this great tutorial. It’s workling fine on my weblog, but I have another question:

Is it possible to display a top-10 list of the most read articels on the index site? I think this would be a great gimmick.

Cheers,

Chris

Avatar

FiReaNG3L

April 6th, 2005 at 8am

I would be highly interested in a “top 10” of the most read posts… I sadly use titles AND date (/archives/day/month/title) for my posts, and I can’t figure how to rebuild these links from my MT database (the URL isn’t stored).

Avatar

FW

March 9th, 2006 at 7am

Great tutorial, you can see the script in action on my site.