Thread

Thanks in advance for any help you can give me ...

So, I have a blog set up along with a Query asking me to list the author’s who have created a post and link to the listing of their posts. This all works great, accept for the fact that if the author has not made an entry yet, their page listing is all sorts of messed up.

This is what I have now:

{exp:query sql="SELECT username, screen_name FROM exp_members WHERE group_id = '1'"}
     <li><a href="http://{path=blog/author}/{username}">{screen_name}</a></li>
{/exp:query}

How can I list ONLY those authors that have made an entry in the particular channel and hide those who don’t have an entry yet?

Hi Fat Free Interactive,

Whilst this isn’t specifically a technical support question I’ll do my best to try and help you out wherever I can.

I am going to move this thread across to the Community Help forums too so that you can get more eyes on it in there as well.

When you say if the user doesn’t have a post yet their page listing is all messed up, what exactly do you mean by that?

Make sure that you have made good use of the {if no_results} and {redirect="template-group/template-name"} variables in order to catch these types of problems.

Does that help here at all?

Thanks,

Mark

You could use a subquery for this:

SELECT username, screen_name FROM exp_members WHERE group_id = 1 AND (SELECT COUNT(*) FROM exp_channel_titles WHERE exp_channel_titles.author_id = exp_members.member_id AND exp_channel_data.channel_id = X) > 0

Where X is the channel_id you are targeting.

Hi FFI,

Looking at the query you provided you are missing a lot of info to get what you want. You need to include your channel tables in order to check if a member posted something. To check if a member posted something you need to run something like the following:

{exp:query sql="SELECT m.member_id,cd.channel_id,ct.title
FROM exp_channels c, exp_channel_data cd, exp_channel_titles ct, exp_members m 
where c.channel_name = 'PutYourChannelNameHere' 
  and cd.channel_id = c.channel_id 
  and ct.channel_id = cd.channel_id 
  and ct.entry_id = cd.entry_id 
  and m.member_id = ct.author_id
  and ct.status = 'open'"}

{title}
...

{/exp:query}

There are a few things you need to do with the query above:

1) Check the channel table for the field ids where your data is getting stored and include them in your query if you want to use them on your html page. This part is a little tricky and you need to be familiar with mySql databases and tables to do this. I would use something like phpMyAdmin to make it easier for you.

2) Put your channel name where it says “PutYourChannelNameHere”

3) Change the status from “open” to what ever you are using if different in your situation.

4) If you need to check for a specific member to see if they posted anything you need to include the following in the query filter:

and m.memeber_id = {SomeID}

Replace the {SomeID} with the members ID you want to check. If you just need all members who posted then the query above will give you exactly that minus your custom fields you created.

5) Put an “order by” if you need to sort it a certain way.

I hope that helps.

Mike