xlab » Popular Posts

Popular Posts

Now that I’m Minted, I decided to whip up a quick function to display the most popular pages on this site. You can see this info about half way down the side bar on the home page. Looking at the structure of the SQL tables installed by Mint, it’s quite straightforward to do this.

The PHP function basically scans the visits SQL table and returns a multi-dimensional array which can then be manipulated into some HTML (or whatever).


function getPopularPosts() {
		$resultsArray = array();
	$i = 0;
	$limit = 10; // how many results to return
	// an array of resources to ignore in search
	$ignore = array(
		"http://xlab.co.uk/",
		"http://xlab.co.uk/extras/",
		"http://xlab.co.uk/extras/desktops/",
		"http://xlab.co.uk/about/",
		"http://xlab.co.uk/photos/",
		"http://xlab.co.uk/macosx/",
	);
		$visits_table = "mint_visit"; // the name of the visits SQL table
	$ignoreString = "'".implode($ignore, "','")."'";
		// build up SQL query string
	$sql = "SELECT resource, resource_title, COUNT(resource) AS c ".
	$sql .= "FROM ".$visits_table." WHERE resource NOT IN (".$ignoreString.") ".
	$sql .= "GROUP BY resource ORDER BY c DESC LIMIT ".$limit.";";
		$res = mysql_query($sql) or die(mysql_error());
		while ($row = mysql_fetch_row($res)) {
			$resultsArray[$i]['url'] = $row[0];
		// clean up title
		// (a custom line which strips out the initial part of the page title (i.e. xlab »)
		$resource_title = str_replace("xlab » ", "", trim(stripslashes($row[1])));
		$resultsArray[$i]['title'] = $resource_title;
		$resultsArray[$i]['count'] = $row[2];
		$i++;
	}
		return $resultsArray;
	}

The getPopularPosts() function then returns a multi-dimensional array which looks a bit like this :


Array
(
    [0] => Array
        (
            [url] => http://xlab.co.uk/photos/read/53
            [title] => The Vampire Rabbit
            [count] => 89
        )
	    [1] => Array
        (
            [url] => http://xlab.co.uk/weblog/629
            [title] => Future of Web Apps Summit : A Review
            [count] => 70
        )
	    [2] => Array
        (
            [url] => http://xlab.co.uk/weblog/resident-evil-4.html
            [title] => Resident Evil 4
            [count] => 61
        )
    ....
)

To print the result as HTML, you’d simply use some PHP to loop through the array and print some markup e.g. :


<?php
$posts = getPopularPosts();
if (count($posts) > 0) {
	print "<ol>\n";
	for ($i = 0; $i < count($posts); $i++) {
		$postUrl = $posts[$i]['url'];
		$postTitle = $posts[$i]['title'];
		print "<li><a href=\"".$postUrl."\">".$postTitle."</a></li>\n";
	}
	print "</ol>\n";
}
?>

Posted 3 years, 11 months ago

Good stuff, but just a heads-up ... you’ve missed a closing [/a] tag somewhere, so your "Flickr" section is showing as a link when you hover over the last item in the list.

print "[li][a href=\"".$postUrl."\"]".$postTitle."[/li]\n";
should be:
print "[li][a href=\"".$postUrl."\"]".$postTitle."[/a][/li]\n";

obviously turn the braces into < / > etc

Steven Woods · www · 3 years, 11 months ago

Aha, you spotted the deliberate mistake ;-) Cheers for the heads-up – I’ve updated the post (and the function).

Phil · www · 3 years, 11 months ago

This is cool, thanks!

Brajeshwar · www · 3 years, 11 months ago

This is cool, and I was going to use it, but it sure increases page generation time.

On my (wordpress) site generation time (for the front page) is averaging .24 seconds. When I use this function it bumps up to .4 seconds average - a .16 second increase. Unfortunately I can’t justify that.

I am sure it could be extended to cache the results to a file - but I am too lazy to do that, at least right now.

Thanks for sharing the code anyway.

Cody · www · 3 years, 11 months ago

Unrelated, but your news-feeds should use absolute links instead of relative links, so that those people using feed-readers don’t have to frickin-load-yer-page-and-follow-the-link-there! Grr! ;)

Andrew · www · 3 years, 11 months ago

Actually, guess I’m not lazy tonight. Just added caching to this:

http://www.projectarcanum.com/archive/2006/02/16/popular-posts-with-mint/

Cody · www · 3 years, 11 months ago

An extra .14 seconds page load time? Scandalous!
*rolls eyes*

Steve Woods · www · 3 years, 11 months ago

Eh, don’t use it then. Personally, I’d rather not almost DOUBLE my page generation time for such a thing, and thought others interested in this function might feel the same way.

Cody · www · 3 years, 11 months ago

Hi Cody - good work with the caching. I slapped together the function quite quickly and didn’t really think about sites with heavy traffic, so caching the output makes sense.

Phil · www · 3 years, 11 months ago

Andrew - added absolute paths to the news feed. Happy now!?!? ;-)

Phil · www · 3 years, 11 months ago

Is there a way to ignore parts of the site with a wildcard url? Maybe to filter out all pages from the photos section, or something like that? And, is it possible to format the link output to include "www."?

Jake · 3 years, 11 months ago

Hi Jake,

You could use a REGEXP or LIKE expression in the SQL statement instead of NOT IN, though pattern matching might will slow down the search (particularly with large datasets).

Something like this might work :
'....WHERE resource NOT LIKE "http://yourdomain/photos/%"....'

More info on REGEXP and LIKE here -
http://dev.mysql.com/doc/refman/4.1/en/pattern-matching.html

To format the link to include the 'www' part of the address, change
$resultsArray[$i]['url'] = $row[0]; to
$resultsArray[$i]['url'] = str_replace("http://", "http://www", $row[0]);

Phil · www · 3 years, 11 months ago

Hey Phil, you left out a period. You meant:

$resultsArray[$i]['url'] = str_replace("http://", "http://www.", $row[0]);

instead of

$resultsArray[$i]['url'] = str_replace("http://", "http://www", $row[0]);

But its a nice addition to the script.

Cody · www · 3 years, 11 months ago

Thanks! This inspired me to write a Ruby model (for use with Ruby on Rails)

http://nubyonrails.com/articles/2006/02/16/activerecord-model-for-mint

topfunky · www · 3 years, 11 months ago

Thanks Phil, that worked great. Would it be possible to set up another ignore string and filter out a wildcard url string AND specific url strings (the default)? I’m not sure how to go about that. Thanks for your help.

Jake · 3 years, 11 months ago

This works marvelously, thank you for the great script. :)

Matt Burris · www · 3 years, 11 months ago

Thanks for the great algorithm. I have packaged the algorithm into a plugin (http://www.ellisweb.net/2006/03/wp-mint-popular-posts/), including some extra customizability options for what info to output.

Yaakov Ellis · www · 3 years, 10 months ago

No problem Yaakov. Good work on the WordPress plug-in :-)

Phil · www · 3 years, 10 months ago

Commenting on this post has been disabled.

My name is Phil Lindsay, a web guy from Scotland, now living in Newcastle upon Tyne in England. I also run a web design company called Presence. You can email me at phil@xlab.co.uk, read more ramblings on my Twitter stream and view my photos on Flickr. You can grab the RSS feed for this weblog here.

xlab : homepage

Popular Posts