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.
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.
Thanks.Your blog is useful.
Thanks.Your blog is useful.
Thank you very much
Hi,
Really this save my time, Nice work.
Keep it up.
Thank you once again
PHP Developer
RSS Subscribers in Drupal
Hi,
Excellent Post...
Given information is very useful.
Thanks for Post,
Palak Bhatt.
Innacurate number
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
Re: Innacurate number
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.
Correct statement
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 logYou will still count dial up accounts or users with changing ip address more than once...
Re: Correct statement
Hi Frank, thanks for your improvement of this SQL.
There's a module for that
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.