Invision Community 4: SEO, prepare for v5 and dormant account notifications Matt November 11, 2024Nov 11
Posted December 14, 20159 yr 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
December 14, 20159 yr Its stored in the language system. Are you doing direct database queries are are you using IPS4 api methods?
December 14, 20159 yr Author 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>
December 14, 20159 yr You'll need to join the core_sys_lang_words table in that case. You'll find language strings for your forums titles.
December 15, 20159 yr Author 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.
December 15, 20159 yr By matching the language key. What is the language key for one of your forums title?
December 15, 20159 yr Author 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.
December 15, 20159 yr 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
December 15, 20159 yr 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
Archived
This topic is now archived and is closed to further replies.