Forums

Creating Most Read/ Commented List

Permalink 1 user found helpful
BACKGROUND:
I'm currently trying to pull a list of pages (say top 10) which by site statistics are most read/ commented by visitors.

QUESTION:
1. Is there a database table in C5 which compiles page visits?
2. Is there a function which can parse the raw access logs for visits per page?

NOTE:
I recently readhttp://www.concrete5.org/community/forums/usage/page-list-popular-p...
However, when looking in the C5 database I couldn't find the table "cv" (table CollectionVersions exists though).

3. Any ideas how to enable this table (cv) if possible?

I recon comments could be totalled using:

Loader::controller($PageObject)->getCommentCountString('%s')

Thanks in advance for any help!
 
cowland replied on at Permalink Reply
heh, never mind - pretty blind sometimes. theres a big fat table labled "PageStatistics" which I somehow missed!
cowland replied on at Permalink Reply
Think I've got it for most popular. The dashboard seems to rate a visit when "uID" from the table PageStatistics = 0. So I wrote for my query:

SELECT ps.cID, COUNT( * ) Visits
        FROM PageStatistics ps
        JOIN Pages p ON p.cID = ps.cID
        WHERE cParentID =$cParentID AND ps.uID = 0
        GROUP BY ps.cID
        ORDER BY Visits DESC
        LIMIT $num

On a modification from the earlier link.

Seems to work fine. If there's a glaring mistake someone tell me.
cowland replied on at Permalink Reply
Also for the comments list it's simply:

<?php defined('C5_EXECUTE') or die(_("Access Denied."));
$limit = $num ? $num : 5;
$mrSql = "SELECT cID, commentText, entryDate
        FROM btGuestBookEntries
        ORDER BY entryDate DESC
          LIMIT $num";
Loader::model('page_list');
$db = Loader::db();
$pageList = $db->execute($mrSql);
echo '<h2>Recent Comments</h2>';
while ($pg = $pageList->fetchRow()) {
  $curPage = Page::getByID($pg[cID]);
  echo '<a href="'.$this->url($curPage->getCollectionPath()).'">'.$pg[commentText].'</a><br/>';
}

This collects info from the btGuestBookEntries table. Data's already filtered only for blog posts in there.
fastcrash replied on at Permalink Reply
fastcrash
thanks cowland, this thread very helpfull to me.
thanks for sharing :)
Pritam replied on at Permalink Reply
I tried to pull up the Recent Comments by creating a template for the pagelist block using this but the comments don't seem to show up !!