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

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

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?

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.

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.

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:

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:

But either approach should work.

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.

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:

Any idea where I’m going wrong?

Hi lanebden,

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

Note the t.weblog_id instead of just weblog_id.

Is that correct Robin?

Best wishes,

Mark

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

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

EDIT: What Marks says.

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

Best wishes,

Mark

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:

Help much appreciated.

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

Is this what you are looking for?

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

Hope that helps?

Best wishes,

Mark

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

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