I am creating a newsletter that will query the database for the last 10 posts.  I want to make it look similar to the Activity Stream.

I can get Member x replied to Member Y's topic in ____________.

When I look in the forums_forums table, all I see if the name_seo.  Is the raw name stored somewhere else?

Thanks

Its stored in the language system. Are you doing direct database queries are are you using IPS4 api methods?

Just now, Kevin Carwile said:

Its stored in the language system. Are you doing direct database queries are are you using IPS4 api methods?

Direct

<cfquery datasource="#DSN5#" name="NewPosts" maxrows="10">
SELECT
    tid,
    title AS ThreadTitle,
    last_poster_name,
    last_post,
    title_seo,
    pp_main_photo,
    starter_name,
    last_poster_name,
    forums_topics.posts AS PostCount
FROM
    forums_topics
INNER JOIN
core_members
ON
forums_topics.last_poster_id=member_id

WHERE
    forum_id <> '29' AND
    forum_id <> '31' AND
    forum_id <> '27' AND
    forum_id <> '30'
ORDER BY
    last_post DESC
</cfquery>

You'll need to join the core_sys_lang_words table in that case. You'll find language strings for your forums titles.

3 minutes ago, Kevin Carwile said:

You'll need to join the core_sys_lang_words table in that case. You'll find language strings for your forums titles.

I see it in that table, but how do I link it to the forums table?

Oh, and thank you.

By matching the language key. What is the language key for one of your forums title?

I don't see that, I see a word_key in the core_sys_lang_words table, forums_forum_3 that looks like it matches the id in the forums_forums table.  I don't know if I'm clever enough to figure that join out.

JOIN core_sys_lang_words ON CONCAT( 'forums_forum_', forums_forum.forum_id )=core_sys_lang_words.word_key AND core_sys_lang_words.lang_id=1

SELECT
    tid,
    title AS ThreadTitle,
    last_poster_name,
    last_post,
    title_seo,
    pp_main_photo,
    starter_name,
    last_poster_name,
    forums_topics.posts AS PostCount,
    CASE WHEN core_sys_lang_words.word_custom IS NULL THEN core_sys_lang_words.word_default ELSE core_sys_lang_words.word_custom END AS ForumTitle
FROM
    forums_topics
INNER JOIN
core_members
ON
forums_topics.last_poster_id=core_members.member_id
INNER JOIN
core_sys_lang_words
ON
core_members.language=core_sys_lang_words.lang_id AND core_sys_lang_words.word_key=CONCAT('forums_forum_', forums_topics.forum_id)
WHERE
    forum_id NOT IN(29, 31, 27, 30)
ORDER BY
    last_post DESC

 

I love both of you!!!!

Thank you so much!