Latest topics code is fetching replies too

Discussion forum for MOD Writers regarding MOD Development.
AwesomeStyle
Registered User
Posts: 64
Joined: Tue Jul 16, 2013 7:10 pm

Latest topics code is fetching replies too

Post by AwesomeStyle » Sun Dec 01, 2013 9:39 pm

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.
I will install MODs for your forum as cheap as 5-15$ in BitCoin depending on the MOD install level & time. Contact via PM.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50916
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Latest topics code is fetching replies too

Post by stevemaury » Sun Dec 01, 2013 11:17 pm

Where is this code from? Like, what file?
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51888
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Latest topics code is fetching replies too

Post by Brf » Sun Dec 01, 2013 11:29 pm

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.

AwesomeStyle
Registered User
Posts: 64
Joined: Tue Jul 16, 2013 7:10 pm

Re: Latest topics code is fetching replies too

Post by AwesomeStyle » Mon Dec 02, 2013 8:03 am

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.
I will install MODs for your forum as cheap as 5-15$ in BitCoin depending on the MOD install level & time. Contact via PM.

AwesomeStyle
Registered User
Posts: 64
Joined: Tue Jul 16, 2013 7:10 pm

Re: Latest topics code is fetching replies too

Post by AwesomeStyle » Mon Dec 02, 2013 2:40 pm

bump
I will install MODs for your forum as cheap as 5-15$ in BitCoin depending on the MOD install level & time. Contact via PM.

User avatar
ViolaF
Registered User
Posts: 1458
Joined: Tue Aug 14, 2012 11:52 pm

Re: Latest topics code is fetching replies too

Post by ViolaF » Mon Dec 02, 2013 3:48 pm

AwesomeStyle wrote:but the $posts_row['post_text'] code won't work.
what dont work? please Xplain..

AwesomeStyle
Registered User
Posts: 64
Joined: Tue Jul 16, 2013 7:10 pm

Re: Latest topics code is fetching replies too

Post by AwesomeStyle » Mon Dec 02, 2013 4:03 pm

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)
I will install MODs for your forum as cheap as 5-15$ in BitCoin depending on the MOD install level & time. Contact via PM.

User avatar
T0ny
Registered User
Posts: 1383
Joined: Sun Jan 29, 2006 8:42 pm
Location: Lancashire
Name: Tony

Re: Latest topics code is fetching replies too

Post by T0ny » Mon Dec 02, 2013 4:06 pm

Where are you creating the $posts_row array?

User avatar
ViolaF
Registered User
Posts: 1458
Joined: Tue Aug 14, 2012 11:52 pm

Re: Latest topics code is fetching replies too

Post by ViolaF » Mon Dec 02, 2013 4:29 pm

give me some minutes, i check it...

AwesomeStyle
Registered User
Posts: 64
Joined: Tue Jul 16, 2013 7:10 pm

Re: Latest topics code is fetching replies too

Post by AwesomeStyle » Mon Dec 02, 2013 4:35 pm

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
Last edited by AwesomeStyle on Mon Dec 02, 2013 6:28 pm, edited 1 time in total.
I will install MODs for your forum as cheap as 5-15$ in BitCoin depending on the MOD install level & time. Contact via PM.

User avatar
ViolaF
Registered User
Posts: 1458
Joined: Tue Aug 14, 2012 11:52 pm

Re: Latest topics code is fetching replies too

Post by ViolaF » Mon Dec 02, 2013 4:46 pm

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?

AwesomeStyle
Registered User
Posts: 64
Joined: Tue Jul 16, 2013 7:10 pm

Re: Latest topics code is fetching replies too

Post by AwesomeStyle » Mon Dec 02, 2013 4:50 pm

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?
I will install MODs for your forum as cheap as 5-15$ in BitCoin depending on the MOD install level & time. Contact via PM.

User avatar
ViolaF
Registered User
Posts: 1458
Joined: Tue Aug 14, 2012 11:52 pm

Re: Latest topics code is fetching replies too

Post by ViolaF » Mon Dec 02, 2013 5:56 pm

its always better, when a last-x-file works completely standalone ;)

AwesomeStyle
Registered User
Posts: 64
Joined: Tue Jul 16, 2013 7:10 pm

Re: Latest topics code is fetching replies too

Post by AwesomeStyle » Mon Dec 02, 2013 6:29 pm

ViolaF wrote:its always better, when a last-x-file works completely standalone ;)
What is a last-x-file?
I will install MODs for your forum as cheap as 5-15$ in BitCoin depending on the MOD install level & time. Contact via PM.

User avatar
ViolaF
Registered User
Posts: 1458
Joined: Tue Aug 14, 2012 11:52 pm

Re: Latest topics code is fetching replies too

Post by ViolaF » Mon Dec 02, 2013 7:07 pm

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

Locked

Return to “[3.0.x] MOD Writers Discussion”