Square Wheels Posted December 14, 2015 Share Posted December 14, 2015 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 Link to comment Share on other sites More sharing options...
Kevin Carwile Posted December 14, 2015 Share Posted December 14, 2015 Its stored in the language system. Are you doing direct database queries are are you using IPS4 api methods? Link to comment Share on other sites More sharing options...
Square Wheels Posted December 14, 2015 Author Share Posted December 14, 2015 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> Link to comment Share on other sites More sharing options...
Kevin Carwile Posted December 14, 2015 Share Posted December 14, 2015 You'll need to join the core_sys_lang_words table in that case. You'll find language strings for your forums titles. Link to comment Share on other sites More sharing options...
Square Wheels Posted December 15, 2015 Author Share Posted December 15, 2015 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. Link to comment Share on other sites More sharing options...
Kevin Carwile Posted December 15, 2015 Share Posted December 15, 2015 By matching the language key. What is the language key for one of your forums title? Link to comment Share on other sites More sharing options...
Square Wheels Posted December 15, 2015 Author Share Posted December 15, 2015 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. Link to comment Share on other sites More sharing options...
Kevin Carwile Posted December 15, 2015 Share Posted December 15, 2015 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 Link to comment Share on other sites More sharing options...
Marcher Technologies Posted December 15, 2015 Share Posted December 15, 2015 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 Link to comment Share on other sites More sharing options...
Square Wheels Posted December 15, 2015 Author Share Posted December 15, 2015 I love both of you!!!! Thank you so much! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.