Page List / Popular Pages

Permalink 6 users found helpful
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

View Replies: View Best Answer
okhayat replied on at Permalink Best Answer Reply
okhayat
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()) {
cannonf700 replied on at Permalink Reply
cannonf700
Way cool!
Thanks for sharing this.
synlag replied on at Permalink Reply
synlag
Thanks okhayat for sharing.
djatomicdog replied on at 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 at Permalink Reply
okhayat
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 at Permalink Reply
That's brilliant many thanks!

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

Thanks

Tim
okhayat replied on at Permalink Reply
okhayat
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 at Permalink Reply
cannonf700
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 at Permalink Reply
c5mix
okhayat,
Thanks so much for this! Very handy.
How could I limit results to just pages of a certain page type?
lexbi replied on at 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 at Permalink Reply
MathiasB
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 at 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 at 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 at Permalink Reply
goesredy
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();
rudrum replied on at 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 at Permalink Reply
goesredy
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();
drumrby replied on at Permalink Reply
drumrby
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 at Permalink Reply
MathiasB
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 at Permalink Reply
JohntheFish
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 at Permalink Reply
goesredy
You can change value in SQL code, look at these :
cParentID > 0

Change to this :
cParentID = $cParentID