[johan]

I was thinking it'd be nice to have a 'top pages' feature.

As in top visited pages across a site, not top commented pages. Since we already have data in 'report viewers', if a time minus 24hr view of that data was 'spitoutable' somewhere on a non admindirectory URL with pagereference and href, in a table sorted by popularity, that could make the basis for quite a cool sidepanel 'most popular recent pages).

fwiw this all came about because you can do it with google APIs but it's such a royal pita that I thought why go through these hoops when commentics already has this data

[johan]

This is quite fun, I made it, have a look at any pages at extreme-macro.co.uk to see what it looks like. Scroll down to green box on bottom right. Fwiw, the crucial SQL is:

Code:

SELECT page_reference, count(page_reference) as total,page_url FROM `viewers` GROUP BY `page_reference` ORDER BY total DESC LIMIT 0, 10

[Steven]

Nice work! I'll add this to the Recent Comments add-on.

[johan]

Thanks! For anyone intending to use this, bear in mind that you have to have a suitable setting in Commentics > Settings > Viewers - I have mine set at 86400 which is 24hrs. That's enough time to have a range of values for my piddly site.

[johan]

Actually I've just thought of a small improvement? Rather than using a count of pages to sort, add the timestamps instead. You get the same order if the pagecounts all differ, but if there are any pages with the same pagecount, the sort brings more recently viewed pages to the top within that 'same page bracket' rather than the default alphabetical. Which is nicer as it's more granular and means the time setting in viewers can be smaller too. So this query works quite nicely:

Code:

mysql_query("SELECT page_reference, page_url, sum(timestamp) as timetotal FROM `viewers` GROUP BY `page_reference` ORDER BY timetotal DESC LIMIT 0, 10");

[marcparish]

Hey Johan, I know this is from 2014, but where do I put that mysql line?