How do I get the Forum Name?

[Square Wheels]

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

[Kevin Carwile]

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

[Square Wheels]

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>

[Kevin Carwile]

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

[Square Wheels]

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.

[Kevin Carwile]

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

[Square Wheels]

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.

[Kevin Carwile]

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

[Marcher Technologies]

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

 

[Square Wheels]

I love both of you!!!!

Thank you so much!