Implementing Individual Entry Views Feature..
Posted : November 10, 2004 at 4:45 pm [America/Los_Angeles]
For quite some time now, I had been putting off implementing the following nifty little enhancement:
Capture and display the number of times a blog entry is viewed!
Well, since I seem to be on some kinda roll, I decided to knock this one off my to-do list today.
Listed below is my best attempt at sharing what I did to enable this feature. Enjoy.
Assumptions
- MT 2.x Blog backended by MySQL running on a Unix/Linux platform
- All MT pages (especially, the main Index page and the Individual Entry pages) are PHP-driven, not static HTML
- Optional: Perl 5.x installed
- Optional: DBI, DBD::MySQL Perl modules available
- Optional: Access to Apache access log file (this article assumes the filename to be access_log). Also, the format of this file must be set in Apache’s httpd.conf as follows:
LogFormat “%h %l %u %t \”%r\” %>s %b \”%{Referer}i\” \”%{User-Agent}i\”" combined - Optional: Your individual blog entry filename looks like 000800.php (for example), where “800″ is MT blog entry ID
- All steps on Unix/Linux were performed in /tmp folder. Substitute /tmp with your own folder location, if necessary
Steps
1. Create a MySQL table called mt_entryhits using the script below:
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,
ip_addr VARCHAR(255) NOT NULL,
entry_created_on DATETIME NOT NULL
);
2a. If you’re launching a brand new MT blog, you’re in for a surprise. You don’t have to muck around with Perl and Apache access log stuff detailed in steps 2a thru 2c. Skip to Step 3. However, I did not have this luxury since this web site has been live for almost 6 months and has over 500 blog entries! So, my first challenge was to write a “migration” script which would populate the mt_entryhits table using the “hits” information captured in my Apache’s (web server) access log file. After a few trial and error, I came up with the following script which worked like a charm on my 65 MB access_log file! If you’re in the same boat as myself, copy and paste this code into a file called run.pl and save it in /tmp folder. Do not forget to update this file with your own MySQL database settings!
#!/usr/bin/perl
use DBI;
use URI::URL;
use strict;
use vars qw($dbh);
# Flush STDOUT
$|=1;
# Business logic
&initialize();
processApacheLog(“input.txt”);
# Cleanup
&cleanup();
######################################################################
# Initialize DB
#######################################################################
sub initialize() {
# Initialize connection to MySQL’s mt database
$dbh = DBI->connect(“DBI:mysql:host=localhost;database=<your-db-name>”,
“<your-user-id>”,“<your-password>”,
{PrintError=>0,
RaiseError=>1,
AutoCommit=>1});
}
######################################################################
# Process Apache Log file
#######################################################################
sub processApacheLog() {
my ($access_log) = @_ if @_;
open(HANDLE, “$access_log”) || die “Can’t open file:$!n”;
my @lines = <HANDLE>;
close(HANDLE);
open (HANDLE, “>output.txt”) || die “Can’t open file:$!n”;
my $hits = 0;
my $miss = 0;
foreach my $line (@lines) {
chomp($line);
my ($host,$date,$url_with_method,$status,$size,$referrer,$agent) = $line =~
m/^(S+) - - [(S+ -d{4})] “(S+ S+ [^”]+)” (d{3}) (d+|-) “(.*?)” “([^“]+)”$/;
my ($method, $url, $http) = split /s+/, $url_with_method;
$url =~ s/?(.*)//;
my $newurl = new URI::URL(“$url”);
my @path = $newurl->path_components;
my $filename = $path[$#path];
if($filename =~ m/(d+).php/) {
my ($entry_id) = $filename =~ m/(d+).php/;
my $entry_id = scalar($entry_id) + 0;
&insertEntries($entry_id, $referrer, $host);
print HANDLE “[HIT]: $linen”;
$hits++;
}
else {
print HANDLE “[MISS]: $linen”;
$miss++;
}
}
close(HANDLE);
print “Hits = $hitsn”;
print “Miss = $missn”;
print “TOTAL = “ . ($hits + $miss) . “n”;
}
######################################################################
# Insert into mt_entryhits
#######################################################################
sub insertEntries() {
my ($entry_id, $referrer, $ip_address) = @_ if @_;
my $sth;
$sth = $dbh->prepare(“insert into mt_entryhits values (?,?,?,NOW())”);
my @bindvars = ($entry_id, $referrer, $ip_address);
$sth->execute(@bindvars);
# Release the statement handle
$sth->finish;
}
#######################################################################################
# Perform DB cleanups
#######################################################################################
sub cleanup {
if(defined($dbh)) {
$dbh->disconnect();
}
}
2b. Run the following steps:
(unix-prompt)>cp <apache-root>/logs/access_log /tmp/input.txt (unix-prompt)>chmod 755 run.pl (see step 2a. above) (unix-prompt)>./run.pl
2c. Check the mt_entryhits table (using client tools like MySQL Query Browser or TOAD MySQL) to make sure that appropriate inserts were performed
3. Create a file in your <MT_ROOT_FOLDER>/ called variables.php as shown below. Do not forget to update this file with your own MySQL database settings:
<?
$host = ‘yadiyada.com’; // database server hostname
$user = ‘foo’; // database username
$password = ‘foobar’; // database password
$database = ‘blahblah’; // MT database
?>
4. Edit “Individual Entry Archive” MT Template as follows:
a. Somewhere at the top of the template:
<?
// include the variables
include ‘<MT_ROOT_FOLDER>/variables.php’
// Hits
mysql_connect( $host, $user, $password );
mysql_select_db( $database );
// insert into mt_entryhits table
$referer = ”;
if (isset($_SERVER[‘HTTP_REFERER’])) {
$referer = $_SERVER[‘HTTP_REFERER’];
}
else {
$referer = ‘-’;
}
// insert into mt_entryhits table
$ip_addr = ”;
if (isset($_SERVER[‘REMOTE_ADDR’])) {
$ip_addr = $_SERVER[‘REMOTE_ADDR’];
}
else {
$ip_addr = ‘-’;
}
mysql_query(“INSERT INTO mt_entryhits (entry_id, referer, ip_addr, entry_created_on)
VALUES (<$MTEntryID$>, ‘$referer’, ‘$ip_addr’, NOW())” );
// 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];
?>
b. Wherever you want to display the “Individual Entry Views” number:
Viewed: <b><?=$hits?></b> time<? if($hits != 1) echo ’s’;?>
5. Edit “Main Index” MT Template as follows:
a. Somewhere at the top of the template:
<?
// include the variables
include ‘<MT_ROOT_FOLDER>/variables.php’
// Hits
mysql_connect( $host, $user, $password );
mysql_select_db( $database );
?>
b. Immediately after <MTEntries> tag:
<? // 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]; ?>
c. Wherever you want to display the “Individual Entry Views” number:
Viewed: <b><?=$hits?></b> time<? if($hits != 1) echo ’s’;?>
6. Rebuild Movable Type.
7. Enjoy!
Note:
- I could not have done all this in a day without the help of this extraordinary article at Chu Yeow’s Redemption in a blog. Thanks a bunch.
- In case you were wondering, as of today, the most “viewed” blog entry across all categories is “Do I smell a “Gmail Hacks” book here?” with 1381 views!
- In the “US Politics” category, the most “viewed” blog entry is “I challenge every PATRIOTIC American to watch this. NOW!” with 334 views! Nice.
Note (to myself):
Need to create a section on the web site which lists the most “viewed” blog entries. Until I do that, here’s the query that I can use to get the same information:
SELECT a.entry_id, entry_title, category_label, count(a.entry_id) FROM mt_entryhits a, mt_entry b, mt_category c, mt_placement d WHERE a.entry_id = b.entry_id AND b.entry_id = d.placement_entry_id AND c.category_id = d.placement_category_id GROUP BY a.entry_id, entry_title, category_label ORDER BY 4 desc
- Anand
Category: Application Development