How to count your Drupal website RSS subscribers?

Submitted by Jan Polzer on April 20, 2008 - 1:07pm

Website owners using Drupal have a very detailed log of access available directly in their admin pages. However there is no built-in functionality which could show how many RSS subscribers does the website have. You have all the neccessary information available in the log. So only thing you have to do is to find them and to show them. I will show you how to prepare a block that will inform you and your readers about current RSS subscribers count.

RSS subscribers block in Drupal

So at first, create a new block and set up its input format to the PHP code. You should input a few sentences to your readers about RSS. Then prepare the most important part of this work - SQL command:

<?php
$rssreaders = db_fetch_array(db_query("SELECT COUNT(DISTINCT(hostname)) AS hostname FROM {accesslog} WHERE path LIKE '%/feed' OR path LIKE 'rss.xml'"));
?>

This command will count all RSS hits to the URL's ending by /feed or to rss.xml page. The DISTINCT keyword will manage to count every IP in the accesslog only once. Then you have to print the value in $rssreaders['hostname'] somewhere:

<p>Follow other readers and <a href="/rss.xml">subscribe</a> to our <a href="/rss.xml">RSS feed</a>. Currently we have <b><?php print $rssreaders['hostname']; ?></b> RSS subscribers now.</p>

So the result content of your block should look like this:

<?php
$rssreaders = db_fetch_array(db_query("SELECT COUNT(DISTINCT(hostname)) AS hostname FROM {accesslog} WHERE path LIKE '%/feed' OR path LIKE 'rss.xml'"));
?>
<p>Follow other readers and <a href="/rss.xml">subscribe</a> to our <a href="/rss.xml">RSS feed</a>. Currently we have <b><?php print $rssreaders['hostname']; ?></b> RSS subscribers now.</p>

I have use this block on my websites Backup HowTo and Photo HowTo - take a look if you are interested.

Stories: 
Webdesign

Comments

Submitted by Jaako (not verified) on January 17, 2009 - 2:42pm.

This only counts unique hits to rss.xml (or other feeds) This could come from people randomly clicking on your feed, or even from google crawling your website. In reality, if someone was actually subscribed to your feed, they would have multiple hits in a fairly close amount of time (like from their RSS reader). A better way would be to filter out hostnames that only hit once, in favor of ones that look more like they're actually subscribed. Thoughts? KJ

Submitted by Jan Polzer on January 24, 2009 - 9:50pm.

Well, so this way the SQL query should have the GROUP BY and HAVING statements to filter out the one time visits. So something like this should be there: SELECT COUNT(DISTINCT(hostname)) AS hostname FROM {accesslog} WHERE path LIKE '%/feed' OR path LIKE 'rss.xml' GROUP BY hostname HAVING count(*) > 1 However I'm not good as I would like to be in SQL, so the statement above is not working correctly. Any idea? The problem could be eliminated by the time. The logs are regularly deleted, so the one time visitors go away from time to time.

Submitted by Frank (not verified) on April 25, 2010 - 10:56am.

I read your article as I want do have such a statistic, too. I played a bit with the SQL and I filtered all out, that just hit twice or less:

SELECT COUNT(hostname) AS hostname FROM 
       (SELECT hostname FROM `accesslog` 
               WHERE path LIKE '%/feed' OR path LIKE '%rss.xml' 
               GROUP BY hostname 
               HAVING COUNT(*) >2
        ) AS log

You will still count dial up accounts or users with changing ip address more than once...

Submitted by Anonymous (not verified) on June 15, 2010 - 2:23pm.

If you prefer to leave the PHP filter disabled for security reasons, or if you prefer to keep PHP code out of the database, you can always try out the Subscribers Counter module, which provides the same functionality.

Submitted by boykie (not verified) on December 27, 2010 - 1:37pm.

Thanks for the post. It was quite helpful (regardless of the comments regarding accuracy). I'm sure with time it will be further refined!