Home  >   Forum Home  >  ExpressionEngine Community Forums  >  "How to"  >  Thread
   
 
How Do I Create a List of Authors with Published Articles
 
Matthew Anderson
Posted: 21 November 2007 03:30 PM   [ Ignore ]  
Summer Student
Total Posts:  7
Joined  03-26-2007

I am trying to build a list of all authors who have posted articles in my weblog. I tried:

<ul>
      {exp:weblog:entries weblog="{my_weblog}"}
      <li class="author"><a href="{path="/authors"}{username}">{author}</a></li>
      {/exp:weblog:entries}
</ul>

This pulls the correct type of information, but displays several duplicates of each author. I also tried:

<ul>
      {exp:query sql="SELECT screen_name FROM exp_members"}
      <li class="tag"><a href="{path="/authors"}{username}">{screen_name}</a></li>
      {/exp:query}
</ul>

However, this displays ALL members (including admins), not just those who have posted to the weblog.

How do I pull only published authors from my weblog and limit each author to a single appearance in the list?

Profile
 
 
Shawn Blower
Posted: 06 January 2008 10:22 AM   [ Ignore ]   [ # 1 ]  
Grad Student
Avatar
Rank
Total Posts:  80
Joined  12-29-2005

I searched for this as well and came up empty handed. So I tried accomplishing it myself. This is my “solution” of sorts. It works, but it’s not as beautiful as I would want.

{exp:query sql="SELECT author_id FROM exp_weblog_titles WHERE weblog_id = '2'"}
{exp:query sql="SELECT screen_name FROM exp_members WHERE member_id = '{author_id}'"}
{/exp:query}

<ul>
<li>{screen_name}</li>
</ul>

{/exp:query}

It scans all the weblog entries and gets the author ID, then I query again to reverse the author ID to their screen name. To get a more understanding you should check out the weblog_titles database table. Now, if anyone can come up with a better solution for changing the author id to the screen name I’m all ears.

Profile
 
 
Robin Sowell
Posted: 06 January 2008 11:51 AM   [ Ignore ]   [ # 2 ]  
Moderator
Avatar
RankRankRankRankRankRankRank
Total Posts:  24513
Joined  05-20-2002

Yep- a query is the way to go.  I’d do it with one query rather than nesting them- so filter on something like total_entries in the members table:

<ul>
      {exp:query sql="SELECT screen_name FROM exp_members WHERE total_entries > 0"}
      <li class="tag"><a href="{path="/authors"}{username}">{screen_name}</a></li>
      {/exp:query}
</ul>

Shawn’s example is more complex- but if you join the member table on the weblog_titles table, you’d be able to restrict it to folks publishing to a given weblog- or only ones with open entries- stuff like that.  I’d avoid nesting- do something like:

{exp:query sql="SELECT author_id, screen_name FROM exp_weblog_titles t, exp_members m WHERE weblog_id = '2' AND t.author_id = m.member_id"}

But either approach should work.

 Signature 

AKA rob1

Help Request Tips *  Pro Network

Profile
 
 
Shawn Blower
Posted: 06 January 2008 12:32 PM   [ Ignore ]   [ # 3 ]  
Grad Student
Avatar
Rank
Total Posts:  80
Joined  12-29-2005

I’m not very familiar with databases and the query module, so it’s great to find out that you can search two tables with one query. Thanks for the head’s up.

Profile
 
 
Ian Ebden
Posted: 31 March 2008 09:22 AM   [ Ignore ]   [ # 4 ]  
Lab Assistant
Avatar
RankRank
Total Posts:  127
Joined  03-17-2006

Hi guys - this is exactly what I was looking for, only Robin’s combined query is throwing up an error in my EE Database Query Form:

MySQL ERROR:
Error Number: 1052
Description: Column 'weblog_id' in where clause is ambiguous
Query: SELECT SQL_CALC_FOUND_ROWS author_id, screen_name FROM exp_weblog_titles t, exp_members m WHERE weblog_id = '6' AND t.author_id = m.member_id LIMIT 0, 100

Any idea where I’m going wrong?

Profile
 
 
Mark Bowen
Posted: 31 March 2008 10:12 AM   [ Ignore ]   [ # 5 ]  
Research Scientist
Avatar
RankRankRankRankRankRank
Total Posts:  9249
Joined  04-15-2006

Hi lanebden,

I think that you will need to make the query like this instead :

SELECT author_id, screen_name FROM exp_weblog_titles t, exp_members m WHERE t.weblog_id = '6' AND t.author_id = m.member_id;

Note the t.weblog_id instead of just weblog_id.

Is that correct Robin?

Best wishes,

Mark

 Signature 

Shopping Cart Plugin
Full list of add-ons
———————————————————-
Buy me a drink, or two if you like!!

Profile
 
 
Ingmar Greil
Posted: 31 March 2008 10:13 AM   [ Ignore ]   [ # 6 ]  
Moderator
Avatar
RankRankRankRankRankRankRank
Total Posts:  21121
Joined  05-15-2004

You should probably JOIN the two tables, but in any case use

t.weblog_id = '6'

instead of simply weblog_id so it’s no longer ambiguous.

EDIT: What Marks says.

 Signature 

Everything will be good in the end. If it’s not good, it’s not the end.

Profile
MSG
 
 
Mark Bowen
Posted: 31 March 2008 10:14 AM   [ Ignore ]   [ # 7 ]  
Research Scientist
Avatar
RankRankRankRankRankRank
Total Posts:  9249
Joined  04-15-2006

Yep Ingmar is right on the JOIN but t.weblog_id is definitely what will get this working for you.

Best wishes,

Mark

 Signature 

Shopping Cart Plugin
Full list of add-ons
———————————————————-
Buy me a drink, or two if you like!!

Profile
 
 
Ian Ebden
Posted: 31 March 2008 10:55 AM   [ Ignore ]   [ # 8 ]  
Lab Assistant
Avatar
RankRank
Total Posts:  127
Joined  03-17-2006

Thanks guys - I did try that, but it’s repeating authors rather than just listing them once. Make sense? In other words I want a list of all contributors (authors) in a blog, but at the moment I’m getting a repeated output. To recap, here’s my code so far:

<ul>
                    {exp:query sql="SELECT author_id, screen_name FROM exp_weblog_titles t, exp_members m WHERE t.weblog_id = '6' AND t.author_id = m.member_id"}
                    <li><a href="{profile_path=member}">{screen_name}</a></li>
                    {/exp:query}
                </ul>

Help much appreciated.

Profile
 
 
Ingmar Greil
Posted: 31 March 2008 11:21 AM   [ Ignore ]   [ # 9 ]  
Moderator
Avatar
RankRankRankRankRankRankRank
Total Posts:  21121
Joined  05-15-2004
SELECT author_id, screen_name
FROM `exp_weblog_titles` JOIN `exp_members` ON exp_weblog_titles.author_id = exp_members.member_id
WHERE exp_weblog_titles.weblog_id = '6'
GROUP BY author_id
 Signature 

Everything will be good in the end. If it’s not good, it’s not the end.

Profile
MSG
 
 
Ian Ebden
Posted: 31 March 2008 11:25 AM   [ Ignore ]   [ # 10 ]  
Lab Assistant
Avatar
RankRank
Total Posts:  127
Joined  03-17-2006

Fantastic. Thanks Ingmar. I need to get some SQL skills.

Profile
 
 
Mark Bowen
Posted: 31 March 2008 11:25 AM   [ Ignore ]   [ # 11 ]  
Research Scientist
Avatar
RankRankRankRankRankRank
Total Posts:  9249
Joined  04-15-2006

Is this what you are looking for?

SELECT DISTINCT author_id, screen_name FROM exp_weblog_titles t, exp_members m WHERE t.weblog_id = '6' AND t.author_id = m.member_id;

Note the use of DISTINCT near the start so any repeated items aren’t spat out.

Hope that helps?

Best wishes,

Mark

 Signature 

Shopping Cart Plugin
Full list of add-ons
———————————————————-
Buy me a drink, or two if you like!!

Profile
 
 
Mark Bowen
Posted: 31 March 2008 11:27 AM   [ Ignore ]   [ # 12 ]  
Research Scientist
Avatar
RankRankRankRankRankRank
Total Posts:  9249
Joined  04-15-2006

Ah just noticed that Ingmar had posted the better version with the JOIN in it. The DISTINCT does do the same thing but the JOIN is much fancier and better all round.

Best wishes,

Mark

 Signature 

Shopping Cart Plugin
Full list of add-ons
———————————————————-
Buy me a drink, or two if you like!!

Profile
 
 
Ingmar Greil
Posted: 31 March 2008 11:29 AM   [ Ignore ]   [ # 13 ]  
Moderator
Avatar
RankRankRankRankRankRankRank
Total Posts:  21121
Joined  05-15-2004

My pleasure. Mark is correct in that you could’ve used SELECT DISTINCT also, there’s very little actual difference.

 Signature 

Everything will be good in the end. If it’s not good, it’s not the end.

Profile
MSG
 
 
   
 
 
‹‹ Spell Check- compiling pspell support....      One template to show all entries from all weblogs ››
Post Marker Legend
New Topic New posts Hot Topic Hot Topic with new posts New Poll New Poll Moved Topic Moved Topic Sticky Topic Sticky topic
Old Topic No new posts Hot Old Topic Hot Topic with no new posts Old Poll Old Poll Closed Topic Closed Topic Announcement Announcements
Theme
Change Theme
Visitor Statistics
The most visitors ever was 1149, on July 16, 2007 09:33 AM
Total Registered Members: 77531 Total Logged-in Users: 39
Total Topics: 101540 Total Anonymous Users: 24
Total Replies: 544312 Total Guests: 312
Total Posts: 645852    
Members ( View Memberlist )
Newest Members:  jodevizes,  mkmrgn30,  top10review722,  AlanK3818,  gemmortgages,  cdlabel,  ttapan,  gradozero,  sagaasu44,  bovinecolostrum
Active Members:    3000,  BridgingUnit,  BWare,  carlos.pedro,  Compass360,  Crucial,  ellimondo,  Flatulent Badger,  Florian Fangohr,  Forrest Anderson,  Georgi Veznev,  hearsay,  impdesigns,  jbcobbs,  jodevizes,  JohnD,  jsks16,  kenny2,  kshaw124,  ladams1949,  Lisa Wess,  Marni Melrose,  MattJeans,  mkmrgn30,  Paul Bryan,  pcmeissner,  rao,  russlipton,  shartie,  ShawnStringfield,  silenz,  Stephen (tmoswift),  Steve Hammer,  tgriesser10,  Tim Griffiths,  timprint,  Werner Strydom,  zamson
Powered By ExpressionEngine
ExpressionEngine Discussion Forum - Version 2.1.1 (20081028)
Script Executed in 0.4192 seconds
Atom Feed   RSS 2.0