Thread

Hi all,

Just wondering if anyone had any tips on getting output of number of entries per author per week?

What I’m after is results like this:

Week 2007 07 02

- Joe Smith (7)
- Jane Doe (2)
- Steven Brown (5)

Week 2007 06 25

- Jane Doe (4)
- Joe Smith (3)
- Steven Brown (1)

etc…

Appreciate any suggestions 😊

A query. Really no other way, I am afraid, and even that’s going to be complicated. Let’s see:

SELECT COUNT(entry_id) FROM `exp_weblog_titles` WHERE YEARWEEK(FROM_UNIXTIME(entry_date)) = '200429' AND author_id = '1'

That would give you the number of entries for author_id 1 in week# 29 of 2004. Makes sense?

How to get the ISO 8601 weeknumber, I hear you ask? EE to the rescue:

{entry_date format="%W"}

Thanks Ingmar, I can see what you mean, its a good start but a bit static for what I’m trying to achieve. Given it’s possible to spit out entries by week, eg

John Smith - Entry 1
John Smith - Another Entry 2
John Smith - Yet Another Entry

..do you think it’s possible to collate those into just “John Smith (3)”?

I was thinking it might be possible to do that in PHP, but that would be very inefficient as you would have to spit out all the entries first, rather than just select them directly. I was also thinking about using a query (my sql-fu is non-existent) within {exp:weblog:entries display_by="week"} but that would be executing the query for every entry returned… hmm tricky one!

Try this:

{exp:query sql="
SELECT screen_name, COUNT(entry_id) AS the_count FROM `exp_weblog_titles` 
INNER JOIN `exp_members` ON exp_weblog_titles.author_id = exp_members.member_id 
WHERE WEEKOFYEAR(FROM_UNIXTIME(entry_date)) = WEEKOFYEAR('2007-06-25') GROUP BY author_id"}
{screen_name}: {the_count} 

{/exp:query}

Where’s a :notworthy: smiley when you need one? 😉 Thanks heaps Ingmar, I think that query, maybe with the YEARWEEK idea too, should do the job, cheers :D

Actually, that should be even closer, should only need a little more tweaking:

<?php $my_number = ""; ?>

{exp:query sql="SELECT WEEKOFYEAR(FROM_UNIXTIME(entry_date)) AS week_number, 
screen_name, COUNT(entry_id) AS the_count FROM `exp_weblog_titles` 
INNER JOIN `exp_members` ON exp_weblog_titles.author_id = exp_members.member_id 
WHERE year = '2006' GROUP BY week_number, author_id"}

<?php if ($my_number != '{week_number}') { echo '{week_number}'; $my_number = '{week_number}'; } ?>

{screen_name}: {the_count} 


{/exp:query}

Ah that’s brilliant Ingmar, just what I was after, thanks. A super extra bonus point for difficulty if you can get the output to sort on the_count within those results (I mucked around with it but couldn’t :\ ), but that’s purely the uhh.. sprinkles on the icing, so to speak, so don’t worry if it’s a prob 😊

To claim my bonus:

... GROUP BY week_number, author_id ORDER BY week_number ASC, the_count DESC

P e r f e c t, thanks again 😊