Mark Discussion as Helpful Reply

Editing (v7+)

Page List / Popular Pages

Permalink 6 users found helpful June 17, 2010 at 4:43 PM

Hi guys,

Is there anyway in C5 to create a list of the most viewed pages – like a list of popular pages which updates itself by the amount of views they get? Then maybe display say a top 5?

Could you do this with the page list block?

If anyone knows how to do this that would be a great help!

Thanks

Tim

okhayat replied on Jun 18, 2010 at 9:51 am Permalink Best Answer Reply

I used the following custom SQL as a Page List block template to achieve it. Just save it to a file, like most_read.php to your /blocks/page_list/templates/most_read.php and choose it as a custom template from the page list you're using.
It will by default display the top ten, unless you choose the number of pages you want to display.

<?php
defined('C5_EXECUTE') or die(_("Access Denied."));
$limit = $num ? $num : 10;
$mrSql = "SELECT cv.cID, COUNT(*) Visits
          FROM CollectionVersions cv
          JOIN Pages p ON cv.cID = p.cID
          JOIN PageStatistics ps ON cv.cID = ps.cID
          WHERE cParentID = $cParentID AND cChildren = 0
          GROUP BY ps.cID
          ORDER BY Visits DESC
          LIMIT $num";
Loader::model('page_list');
$db = Loader::db();
$pageList = $db->execute($mrSql);
while ($pg = $pageList->fetchRow()) {

Viewing 15 lines of 21 lines. View entire code block.

 
<?php
defined('C5_EXECUTE') or die(_("Access Denied."));
$limit = $num ? $num : 10;
$mrSql = "SELECT cv.cID, COUNT(*) Visits
          FROM CollectionVersions cv
          JOIN Pages p ON cv.cID = p.cID
          JOIN PageStatistics ps ON cv.cID = ps.cID
          WHERE cParentID = $cParentID AND cChildren = 0
          GROUP BY ps.cID
          ORDER BY Visits DESC
          LIMIT $num";
Loader::model('page_list');
$db = Loader::db();
$pageList = $db->execute($mrSql);
while ($pg = $pageList->fetchRow()) {
  $curPage = Page::getByID($pg[cID]);
?>
  <a href="<?=$this->url($curPage->getCollectionPath())?>">
    <?=$curPage->getCollectionName()?>
    </a>
<? } ?>

cannonf700 replied on Jun 18, 2010 at 11:52 am Permalink Reply

Way cool!
Thanks for sharing this.

synlag replied on Jun 18, 2010 at 12:03 pm Permalink Reply

Thanks okhayat for sharing.

djatomicdog replied on Jun 21, 2010 at 5:45 am Permalink Reply

Thanks okhayat!

Can I just check – when I implement the custom template my page list disappears completely... is this because none of the pages have been viewed yet to make them the most popular? and the list will populate once some pages have been viewed?

Hope that makes sense.

Thanks

Tim

okhayat replied on Jun 21, 2010 at 5:10 pm Permalink Reply

Yes, that's exactly the case.
EDIT: Also, the count doesn't include pages that have sub pages. You can remove 'AND cChildren = 0' from the query to include those.

djatomicdog replied on Jun 21, 2010 at 5:15 pm Permalink Reply

That's brilliant many thanks!

Do you know how long it takes until the popular pages appear?

Thanks

Tim

okhayat replied on Jun 21, 2010 at 5:24 pm Permalink Reply

Well, it might seem stupid, but what I usually do for the first use, is to simply visit some of the pages I'm counting as popular till the list is populated.
You may want to add a condition to show a simple message instead of an empty block.

cannonf700 replied on Jun 22, 2011 at 2:51 am Permalink Reply

I am getting this error message when using this as a custom template:

Parse error: syntax error, unexpected $end in C:\wamp\www\my_site\blocks\page_list\templates\most_read.php on line 21

when I try to use this... any ideas?

c5mix replied on Dec 16, 2011 at 10:25 am Permalink Reply

okhayat,
Thanks so much for this! Very handy.
How could I limit results to just pages of a certain page type?

lexbi replied on Jun 17, 2013 at 7:27 pm Permalink Reply

Don't suppose yourself or someone else could help with adding in an if dependant on page type, for example I presumed something like this would work:

WHERE cParentID > 0 AND cChildren = 0 AND pt.ctIsInternal = 0 AND pt.ctID = 4 AND p.cIsActive = 1

Can someone identify what the correct WHERE line would be?!

Thanks in advance,
Joe

MathiasB replied on Nov 28, 2013 at 12:56 pm Permalink Reply

Dear Okhayat,
I also get the error:

Parse error: syntax error, unexpected $end in C:\wamp\www\my_site\blocks\page_list\templates\most_read.php on line 21

I tried almost everything to kill that...
Have you got an idea?

Thanks
Mathias

pixilated44 replied on Jul 26, 2011 at 2:12 pm Permalink Reply

I tried this bit of code and had no success. Not sure why, but nothing would display - even after days of useage. A little bit of toying around with the db showed me what the issue was and so I thought to post this here just in case it might help others in the future.

My issue was with:

WHERE cParentID = $cParentID and cChildren = 0

seems SQL didn't like the variable $cParent. I used Firebug and poked around the DOM and found my homepage ID was 0. I replaced the variable with the ID# and it worked. I then decided that the homepage would get a lot of hits anyway and I wanted it removed from the list. My homepage ID was 0, so I set the line to read as follows:

WHERE cParentID > 0 and cChildren = 0

This fixed my issue. Just an idea to try for others having problems, no promises, but I figured someone might benefit from it somewhere along the line.

luan replied on Mar 31, 2012 at 11:42 am Permalink Reply

How do you exclude pages such as the dashboard, login, register, stacks, drafts. I guess all the backend pages. Well it think it would be better if I can just include just one parent page such as the blog. So to show the most view blog pages. Any idea? I think you can use the parentId, but how to do you find the Id of those interior pages?

goesredy replied on Jan 18, 2013 at 10:57 pm Permalink Reply

Thanks okhayat.
I just modify okhayat codes for not display internal pages and trashed page.
Page limit come from page limit on page list edit.

<?php
defined('C5_EXECUTE') or die(_("Access Denied."));
$num    = count($cArray);
$limit = $num ? $num : 10;
$mrSql = "SELECT cv.cID, COUNT(*) Visits
          FROM CollectionVersions cv
          JOIN Pages p ON cv.cID = p.cID
          JOIN PageStatistics ps ON cv.cID = ps.cID
          JOIN PageTypes pt ON cv.ctID = pt.ctID
          WHERE cParentID > 0 AND cChildren = 0 AND pt.ctIsInternal = 0 AND p.cIsActive = 1
          GROUP BY ps.cID
          ORDER BY Visits DESC
          LIMIT $num";
Loader::model('page_list');
$db = Loader::db();

Viewing 15 lines of 22 lines. View entire code block.

 
<?php
defined('C5_EXECUTE') or die(_("Access Denied."));
$num    = count($cArray);
$limit = $num ? $num : 10;
$mrSql = "SELECT cv.cID, COUNT(*) Visits
          FROM CollectionVersions cv
          JOIN Pages p ON cv.cID = p.cID
          JOIN PageStatistics ps ON cv.cID = ps.cID
          JOIN PageTypes pt ON cv.ctID = pt.ctID
          WHERE cParentID > 0 AND cChildren = 0 AND pt.ctIsInternal = 0 AND p.cIsActive = 1
          GROUP BY ps.cID
          ORDER BY Visits DESC
          LIMIT $num";
Loader::model('page_list');
$db = Loader::db();
$pageList = $db->execute($mrSql);
?>
<ul>
<?php while ($pg = $pageList->fetchRow()) {
  $curPage = Page::getByID($pg[cID]);
?><li><a href="<?=$this->url($curPage->getCollectionPath())?>"><?=$curPage->getCollectionName()?></a></li><? } ?>
</ul>

rudrum replied on Mar 27, 2013 at 11:00 am Permalink Reply

Thanks @okhayat and also @goesredy I am a php novice, and I was trying to add a thumbnail image to each of the Popular list item, I followed the how-to on adding thumbnails to the pagelist but the code from the default pagelist view.php and the template provided by @okhayat seems different here's how i was trying and i see from the firebug that the <img> tags are outputted but they fail to load the images.

<?php
defined('C5_EXECUTE') or die(_("Access Denied."));
$num = count($cArray);
$limit = $num ? $num : 10;
$mrSql = "SELECT cv.cID, COUNT(*) Visits
FROM CollectionVersions cv
JOIN Pages p ON cv.cID = p.cID
JOIN PageStatistics ps ON cv.cID = ps.cID
JOIN PageTypes pt ON cv.ctID = pt.ctID
WHERE cParentID > 0 AND cChildren = 0 AND pt.ctIsInternal = 0 AND p.cIsActive = 1
GROUP BY ps.cID
ORDER BY Visits DESC
LIMIT $num";
Loader::model('page_list');
$ih = Loader::helper('image');
$db = Loader::db();
$pageList = $db->execute($mrSql);
?>
<div class="ccm-page-list">
<ul >
<?php while ($pg = $pageList->fetchRow()) {

$curPage = Page::getByID($pg[cID]);

$img = $page->getAttribute('thumbnail');
$thumb = $ih->getThumbnail($img, 60, 60, false);
?>

<li>
<img src="<?php echo $thumb->src ?>" width="<?php echo $thumb->width ?>" height="<?php echo $thumb->height ?>" alt="" />

<a href="<?=$this->url($curPage->getCollectionPath())?>"><?=$curPage->getCollectionName()?></a></li><? } ?>

</ul>
</div>

goesredy replied on Jun 24, 2013 at 3:55 am Permalink Reply

Remember to get the block that provide thumbnail image first, I think that's not an attribute but a block. Try this code below :

<?php
defined('C5_EXECUTE') or die(_("Access Denied."));
$num    = count($cArray);
$limit = $num ? $num : 10;
$mrSql = "SELECT cv.cID, COUNT(*) Visits
          FROM CollectionVersions cv
          JOIN Pages p ON cv.cID = p.cID
          JOIN PageStatistics ps ON cv.cID = ps.cID
          JOIN PageTypes pt ON cv.ctID = pt.ctID
          WHERE cParentID > 0 AND cChildren = 0 AND pt.ctIsInternal = 0 AND p.cIsActive = 1
          GROUP BY ps.cID
          ORDER BY Visits DESC
          LIMIT $num";
Loader::model('page_list');
$db = Loader::db();

Viewing 15 lines of 33 lines. View entire code block.

 
<?php
defined('C5_EXECUTE') or die(_("Access Denied."));
$num    = count($cArray);
$limit = $num ? $num : 10;
$mrSql = "SELECT cv.cID, COUNT(*) Visits
          FROM CollectionVersions cv
          JOIN Pages p ON cv.cID = p.cID
          JOIN PageStatistics ps ON cv.cID = ps.cID
          JOIN PageTypes pt ON cv.ctID = pt.ctID
          WHERE cParentID > 0 AND cChildren = 0 AND pt.ctIsInternal = 0 AND p.cIsActive = 1
          GROUP BY ps.cID
          ORDER BY Visits DESC
          LIMIT $num";
Loader::model('page_list');
$db = Loader::db();
$pageList = $db->execute($mrSql);
$imgHelper = Loader::Helper('image');
?>
<ul>
<?php while ($pg = $pageList->fetchRow()) { ?>
   <li>
   <?php
   $curPage = Page::getByID($pg[cID]);
   $ts = $curPage->getBlocks('Thumbnail Image');
      if (is_object($ts[0])) {
         $tsb = $ts[0]->getInstance();
         $thumb = $tsb->getFileObject();
         if($thumb){
            $imgHelper->outputThumbnail($thumb, 60, 60, $title);
         }
      }
?><a href="<?=$this->url($curPage->getCollectionPath())?>"><?=$curPage->getCollectionName()?></a></li><? } ?>
</ul>

drumrby replied on Dec 9, 2013 at 6:47 pm Permalink Reply

Is there a way to select the location of the website you want to pull pages from? For instance, only display pages beneath the blog page? I've tried to select the page, that I want to display it's children, when I add the block, but once I add the custom template it just pulls site wide.

MathiasB replied on Jan 8, 2014 at 3:46 am Permalink Reply

Hi Drumr!
Does that code work for you?
I cannot get it work. Have you got an idea, what I am doing wrong?

Best Mathias

JohntheFish replied on Jan 8, 2014 at 6:52 am Permalink Reply

I have not looked at the above in detail, but the posts above are 4 years old.

The code could be way out of step with the schema for the database in current c5 versions.

So have a look inside the core page list class for the current version of c5 and see if that provides clues about changes you would need to make to the example code.

You should also consider using the core page list class and extending it with just the snippet you need for popular pages. That approach would be considerably more future proof.

You may also be able to do this using my Uber List and Magic Data addons.
http://www.concrete5.org/marketplace/addons/uber-list/...
http://www.concrete5.org/marketplace/addons/magic-data/...

goesredy replied on Feb 1, 2014 at 1:45 am Permalink Reply

You can change value in SQL code, look at these :