Page 1 of 2

Latest topics code is fetching replies too

Posted: Sun Dec 01, 2013 9:39 pm
by AwesomeStyle
Hi! The following code also returns replies due to posts table being included in the MySQL query.
I need the posts table included because I need the topic text.

Code: Select all

	$announcement_topics = array();
	$announcement_forums = array(2);
			  
	$announcement_topics = 'SELECT * FROM ' . TOPICS_TABLE . ', ' . POSTS_TABLE . '
			  ' . $forum_id_where . '
			  WHERE ' . TOPICS_TABLE . '.topic_approved = 1 AND ' . $db->sql_in_set("" . POSTS_TABLE . ".forum_id", $announcement_forums) . '
			  ORDER BY ' . TOPICS_TABLE . '.topic_last_post_time DESC';

	$announcements_topics_result = $db->sql_query_limit($announcement_topics, TOPICS_LIMIT);

	while ($topics_row = $db->sql_fetchrow($announcements_topics_result))
	{
		$topic_title		= $topics_row['topic_title'];
		$topic_author		= get_username_string('full', $topics_row['topic_poster'], $topics_row['topic_first_poster_name'], $topics_row['topic_first_poster_colour']);
		$topic_date			= $user->format_date($topics_row['topic_time']);
		$topic_last_post	= append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $topics_row['forum_id'] . '&t=' . $topics_row['topic_id'] . '&p=' . $topics_row['topic_last_post_id']) . '#p' . $topics_row['topic_last_post_id'];
		$topic_last_author	= get_username_string('full', $topics_row['topic_last_poster_id'], $topics_row['topic_last_poster_name'], $topics_row['topic_last_poster_colour']);
		$topic_link			= append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $topics_row['forum_id'] . '&t=' . $topics_row['topic_id']);
		
		$post_text = $posts_row['post_text'];
		$post_text = smiley_text($post_text);
		
		$template->assign_block_vars('announcements', array(
			'TOPIC_TITLE'		=> censor_text($topic_title),
			'TOPIC_AUTHOR'		=> $topic_author,
			'TOPIC_DATE'		=> $topic_date,
			'TOPIC_LAST_POST'	=> $topic_last_post,
			'TOPIC_LAST_AUTHOR'	=> $topic_last_author,
			'TOPIC_LINK'		=> $topic_link,
			'TOPIC_TEXT'		=> censor_text($post_text),
		));
	}
How can I fix this? Thanks.

Re: Latest topics code is fetching replies too

Posted: Sun Dec 01, 2013 11:17 pm
by stevemaury
Where is this code from? Like, what file?

Re: Latest topics code is fetching replies too

Posted: Sun Dec 01, 2013 11:29 pm
by Brf
I am not seeing any join in that SQL statement. I am assuming you want to join the post_id with the topic_first_post.

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 8:03 am
by AwesomeStyle
I got it working, here is the current code:

Code: Select all

	$announcement_topics = 'SELECT * FROM ' . TOPICS_TABLE . '
							JOIN ' . POSTS_TABLE . ' ON ' . TOPICS_TABLE . '.topic_first_post_id = ' . POSTS_TABLE . '.post_id
							' . $forum_id_where . '
							WHERE ' . TOPICS_TABLE . '.topic_approved = 1 AND ' . $db->sql_in_set("" . POSTS_TABLE . ".forum_id", $announcement_forums) . '
							ORDER BY ' . TOPICS_TABLE . '.topic_last_post_time DESC';
but the $posts_row['post_text'] code won't work.

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 2:40 pm
by AwesomeStyle
bump

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 3:48 pm
by ViolaF
AwesomeStyle wrote:but the $posts_row['post_text'] code won't work.
what dont work? please Xplain..

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 4:03 pm
by AwesomeStyle
ViolaF wrote:
AwesomeStyle wrote:but the $posts_row['post_text'] code won't work.
what dont work? please Xplain..
The code for returning the first post TEXT (content) is returning an empty string.
Other functions that use topic table (phpbb_topics) are working (topic title, author, etc.).

I need a code that will fetch last 5 topics from an array of forums, assigning the specified template vars:
  • TOPIC_TITLE
  • TOPIC_AUTHOR
  • TOPIC_DATE
  • TOPIC_LAST_POST
  • TOPIC_LAST_AUTHOR
  • TOPIC_LINK
  • TOPIC_TEXT (the only var that is using POSTS_TABLE)

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 4:06 pm
by T0ny
Where are you creating the $posts_row array?

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 4:29 pm
by ViolaF
give me some minutes, i check it...

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 4:35 pm
by AwesomeStyle
T0ny wrote:Where are you creating the $posts_row array?
It is created in the included PHP code.

ViolaF wrote:give me some minutes, i check it...
Current code: http://pastebin.com/raw.php?i=WqSr9Xiq

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 4:46 pm
by ViolaF
takes a time, bcs. the code is a bit crappy and must be re-written.
it goes normally more simple, so be a bit patient ;)

where is $forum_id_where defined?

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 4:50 pm
by AwesomeStyle
ViolaF wrote:takes a time, bcs. the code is a bit crappy and must be re-written.
it goes normally more simple, so be a bit patient ;)

where is $forum_id_where defined?
$forum_id_where is not defined, I guess that it is defined in the incuded phpBB files?

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 5:56 pm
by ViolaF
its always better, when a last-x-file works completely standalone ;)

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 6:29 pm
by AwesomeStyle
ViolaF wrote:its always better, when a last-x-file works completely standalone ;)
What is a last-x-file?

Re: Latest topics code is fetching replies too

Posted: Mon Dec 02, 2013 7:07 pm
by ViolaF
a last-x-file (php) is a last-whatever-file: the last x posts, the last x topics etc.,
like this
http://www.4seven.de/forum/6test/viewtopic.php?t=70
or this
http://www.4seven.de/forum/6test/viewtopic.php?t=96
or whatever kind of

anyway, here is your solution, it works.

just put it before the main template variable array in eg. viewforum ore where-ever

means, put it before the main assign vars array

Code: Select all

$template->assign_vars(array( 
and switch debug-mode on to see each error, which can appear, when you put it on the false place.
of course you can use the code standalone with the instruction for phpbb3 integrated sites.

Code: Select all

    #############################################
    $announcement_topicz = 'SELECT p.topic_id, p.post_text,
                                   t.topic_id, t.forum_id, topic_title, topic_poster, 
                                     topic_first_poster_name, topic_first_poster_colour, topic_last_poster_colour
                                     topic_time, topic_last_post_id, topic_last_poster_id, topic_last_poster_name 
                             FROM ' . POSTS_TABLE . ' p, ' . TOPICS_TABLE . ' t
                            WHERE p.topic_id = t.topic_id
                            AND post_id = topic_last_post_id
                            AND ' . $db->sql_in_set('t.forum_id', $announcement_forumz) . '
                            ORDER BY topic_last_post_time DESC';
                            
    $announcements_topicz_result = $db->sql_query_limit($announcement_topicz, 5);

    while ($topics_row = $db->sql_fetchrow($announcements_topicz_result))
    {
    
    
    
        $topic_title        = $topics_row['topic_title'];
        $topic_author        = get_username_string('full', $topics_row['topic_poster'], $topics_row['topic_first_poster_name'], $topics_row['topic_first_poster_colour']);
        $topic_date            = $user->format_date($topics_row['topic_time']);
        $topic_last_post    = append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $topics_row['forum_id'] . '&t=' . $topics_row['topic_id'] . '&p=' . $topics_row['topic_last_post_id']) . '#p' . $topics_row['topic_last_post_id'];
        $topic_last_author    = (isset($topics_row['topic_last_poster_colour'])) 
                              ? 
                              get_username_string('full', $topics_row['topic_last_poster_id'], $topics_row['topic_last_poster_name'], $topics_row['topic_last_poster_colour'])
                              :
                              get_username_string('full', $topics_row['topic_last_poster_id'], $topics_row['topic_last_poster_name'], false);
        $topic_link            = append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $topics_row['forum_id'] . '&t=' . $topics_row['topic_id']);
        
        $post_text = $topics_row['post_text'];
        $post_text = smiley_text($post_text);
        
        $template->assign_block_vars('announcements', array(
            'TOPIC_TITLE'        => censor_text($topic_title),
            'TOPIC_AUTHOR'        => $topic_author,
            'TOPIC_DATE'        => $topic_date,
            'TOPIC_LAST_POST'    => $topic_last_post,
            'TOPIC_LAST_AUTHOR'    => $topic_last_author,
            'TOPIC_LINK'        => $topic_link,
            'TOPIC_TEXT'        => censor_text($post_text),
        ));

            ###### just remove this echo after testings        
            echo censor_text($topic_title) . '<br>' . censor_text($post_text) . '<br><br>';       
    }
    
    $db->sql_freeresult($announcements_topicz_result);
    ###############################################################     
this code is tested