Thread

Hi,

I`m not good with sql queries, and I`ve been battling this for weeks without getting much of a result..
When a weblog post is posted, a forum post is automatically created with a module I found here on the forum.
What I want is to show the last x forum posts in the forum thread associated with the weblog article.
I need the post date, author and body.

I have managed to pull out the body with this:

{exp:query sql="SELECT body AS body1, author_id FROM exp_forum_posts WHERE topic_id = '{forum_topic_id}' ORDER BY post_date"}
{body1}
{/exp:query}

Now I need help to get the post date and author name?

Anyone?

I guess these tables has what I need:

SELECT * FROM exp_forum_posts
author_id
body
post_date

SELECT * FROM exp_members
member_id
screen_name

I need a query which gets the body, postdate and screen_name based on the forum post author_id which I assume is the same as member_id in exp_members.

This query:

{exp:query sql="SELECT exp_forum_posts.body AS body1, exp_forum_posts.post_date, exp_members.screen_name FROM exp_forum_posts JOIN exp_members ON exp_members.member_id = exp_forum_posts.author_id WHERE topic_id = '{forum_topic_id}' ORDER BY post_date"}


{body1}
{screen_name}


{/exp:query}

Gives me exactly what I need when ran through Admin ›  Utilities ›  SQL Manager ›  Database Query Form ›  Query Result.
But for some reason the when used in the weblog template it shows wrong screen name. (shows the same screen name for all posts)

Ah, got it working by using this instead:

{exp:query sql="SELECT exp_forum_posts.body AS body1, exp_forum_posts.post_date, exp_members.screen_name AS author1 FROM exp_forum_posts JOIN exp_members ON exp_members.member_id = exp_forum_posts.author_id WHERE topic_id = '{forum_topic_id}' ORDER BY post_date"}


{body1}
{author1}


{/exp:query}

Now I need to figure out how to show the post_date in a human readable way, any tips?