How to show forum name of parent id?

Discussion forum for MOD Writers regarding MOD Development.
Locked
miniatures12
Registered User
Posts: 9
Joined: Wed Aug 21, 2013 1:40 pm

How to show forum name of parent id?

Post by miniatures12 » Wed Oct 16, 2013 9:22 pm

I've been breaking my head over this for days now.

With the current tutorial (Example #4: Display posts from anywhere) I succesfully loaded a number of topics that last seen activity on an external page.

With some extra code from my side I added the name of the forum in which it is posted:

Code: Select all

'FORUM_TITLE'		=> $posts_row['forum_name'],
And with some more extra code I extracted the parent id that goes with that forum:

Code: Select all

'FORUM_PARENT'		=> $posts_row['parent_id'],
Now all of this works like a charm.

The thing is that in stead of the parent id, I'd like to show the name of that parent, not just the number. I just started to understand a little how PHP works and was quite happy I got the FORUM_TITLE and FORUM_PARENT to work, but can't find a way how to show the forum name in stead of the number.

My question is:
How can I show the forum name of the parent forum, in stead of just the number of the parent id?

I'd like to have them seperate because I only need to show 1 parent of the forum in which a topic is posted, and have both the parent forum as normal forum as separate clickable links.

I tried to use parts of this code written by AmigoJack but I can't get it to work (no worries, the code works, I just can't get the forum name of the parent id to show with pieces of that code, probably because I do something wrong). Maybe someone else can find something useful in there to help me. :D

Thanks in advance!

User avatar
EXreaction
Former Team Member
Posts: 5666
Joined: Sun Aug 21, 2005 9:31 pm
Location: Wisconsin, U.S.
Name: Nathan

Re: How to show forum name of parent id?

Post by EXreaction » Thu Oct 17, 2013 3:39 am

I believe this should take care of it:

Code: Select all

$posts_ary = array(
        'SELECT'    => 'p.*, t.*, u.username, u.user_colour, parent.forum_name AS parent_forum_name',
    
        'FROM'      => array(
            POSTS_TABLE     => 'p',
        ),
    
        'LEFT_JOIN' => array(
            array(
                'FROM'  => array(USERS_TABLE => 'u'),
                'ON'    => 'u.user_id = p.poster_id'
            ),
            array(
                'FROM'  => array(TOPICS_TABLE => 't'),
                'ON'    => 'p.topic_id = t.topic_id'
            ),
            array(
                'FROM'  => array(FORUMS_TABLE=> 'f'),
                'ON'    => 'f.forum_id = t.forum_id'
            ),
            array(
                'FROM'  => array(FORUMS_TABLE=> 'parent'),
                'ON'    => 'parent.forum_id = f.parent_id'
            ),
        ),
    
        'WHERE'     => $db->sql_in_set('t.forum_id', array_keys($auth->acl_getf('f_read', true))) . '
                        AND t.topic_status <> ' . ITEM_MOVED . '
                         AND t.topic_approved = 1',
    
        'ORDER_BY'  => 'p.post_id DESC',
    );
Get the name from: $posts_row['parent_forum_name']

miniatures12
Registered User
Posts: 9
Joined: Wed Aug 21, 2013 1:40 pm

Re: How to show forum name of parent id?

Post by miniatures12 » Thu Oct 17, 2013 8:51 am

Hi Nathan,

Thanks for your reply. I tried your code but I got this:
SQL ERROR [ mysql4 ]

Unknown column 'parent.forum_name' in 'field list' [1054]
I had an idea of saying that the parent id is the same as the forum id, so I can extract the forum_name field from that forum id (which is originaly the parent id). Hope that makes sense. :D An other solution would be welcome too.

As long as the name of the parent forum is displayed and not just the number. :D

User avatar
EXreaction
Former Team Member
Posts: 5666
Joined: Sun Aug 21, 2005 9:31 pm
Location: Wisconsin, U.S.
Name: Nathan

Re: How to show forum name of parent id?

Post by EXreaction » Thu Oct 17, 2013 1:26 pm

Could you post all of the code you're trying to use?

miniatures12
Registered User
Posts: 9
Joined: Wed Aug 21, 2013 1:40 pm

Re: How to show forum name of parent id?

Post by miniatures12 » Thu Oct 17, 2013 1:35 pm

From the php file

Code: Select all

/* create_where_clauses( int[] gen_id, String type )
* This function outputs an SQL WHERE statement for use when grabbing 
* posts and topics */

function create_where_clauses($gen_id, $type)
{
global $db, $auth;

    $size_gen_id = sizeof($gen_id);

        switch($type)
        {
            case 'forum':
                $type = 'forum_id';
                break;
            case 'topic':
                $type = 'topic_id';
                break;
            default:
                trigger_error('No type defined');
        }

    // Set $out_where to nothing, this will be used of the gen_id
    // size is empty, in other words "grab from anywhere" with
    // no restrictions
    $out_where = '';

    if( $size_gen_id > 0 )
    {
    // Get a list of all forums the user has permissions to read
    $auth_f_read = array_keys($auth->acl_getf('f_read', true));

        if( $type == 'topic_id' )
        {
            $sql     = 'SELECT topic_id FROM ' . TOPICS_TABLE . '
                        WHERE ' .  $db->sql_in_set('topic_id', $gen_id) . '
                        AND ' .  $db->sql_in_set('forum_id', $auth_f_read);

            $result     = $db->sql_query($sql);

                while( $row = $db->sql_fetchrow($result) )
                {
                        // Create an array with all acceptable topic ids
                        $topic_id_list[] = $row['topic_id'];
                }

            unset($gen_id);

            $gen_id = $topic_id_list;
            $size_gen_id = sizeof($gen_id);
        }

    $j = 0;    

        for( $i = 0; $i < $size_gen_id; $i++ )
        {
        $id_check = (int) $gen_id[$i];

            // If the type is topic, all checks have been made and the query can start to be built
            if( $type == 'topic_id' )
            {
                $out_where .= ($j == 0) ? 'WHERE ' . $type . ' = ' . $id_check . ' ' : 'OR ' . $type . ' = ' . $id_check . ' ';
            }

            // If the type is forum, do the check to make sure the user has read permissions
            else if( $type == 'forum_id' && $auth->acl_get('f_read', $id_check) )
            {
                $out_where .= ($j == 0) ? 'WHERE ' . $type . ' = ' . $id_check . ' ' : 'OR ' . $type . ' = ' . $id_check . ' ';
            }    

        $j++;
        }
    }

    if( $out_where == '' && $size_gen_id > 0 )
    {
        trigger_error('A list of topics/forums has not been created');
    }

    return $out_where;
}

$search_limit = 20;

    $forum_id = array();
    $forum_id_where = create_where_clauses($forum_id, 'forum');

    $topic_id = array(20, 50);
    $topic_id_where = create_where_clauses($topic_id, 'topic');
	
$posts_ary = array(
        'SELECT'    => 'p.*, t.*, f.forum_name, f.forum_id, f.parent_id, u.username, u.user_colour', 
    
        'FROM'      => array(
            POSTS_TABLE     => 'p',
        ),
    
        'LEFT_JOIN' => array(
            array(
                'FROM'  => array(USERS_TABLE => 'u'),
                'ON'    => 'u.user_id = p.poster_id'
            ),
            array(
                'FROM'  => array(TOPICS_TABLE => 't'),
                 'ON'    => 'p.post_id = t.topic_last_post_id' 
            ),
			array( 
               'FROM'  => array(FORUMS_TABLE => 'f'), 
               'ON'    => 'p.forum_id = f.forum_id' 
            ),
        ),
    
        'WHERE'     => $db->sql_in_set('t.forum_id', array_keys($auth->acl_getf('f_read', true))) . '
                        AND t.topic_status <> ' . ITEM_MOVED . '
                         AND t.topic_approved = 1',
    
        'ORDER_BY'  => 'p.post_id DESC',
    );
    
    $posts = $db->sql_build_query('SELECT', $posts_ary);

   $posts_result = $db->sql_query_limit($posts, $search_limit);
   
      while( $posts_row = $db->sql_fetchrow($posts_result) )
      {
         $topic_title       = $posts_row['topic_title'];
         $post_author       = get_username_string('full', $posts_row['poster_id'], $posts_row['username'], $posts_row['user_colour']);
         $post_date          = $user->format_date($posts_row['post_time']);
         $post_link       = append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $posts_row['forum_id'] . '&t=' . $posts_row['topic_id'] . '&p=' . $posts_row['post_id']) . '#p' . $posts_row['post_id'];
		 $topic_forum_id	= $posts_row['forum_id'];
		 $forum_parent_id = $posts_row['parent_id'];
         $post_text = nl2br($posts_row['post_text']);

         $bbcode = new bbcode(base64_encode($bbcode_bitfield));         
         $bbcode->bbcode_second_pass($post_text, $posts_row['bbcode_uid'], $posts_row['bbcode_bitfield']);

         $post_text = smiley_text($post_text);

         $template->assign_block_vars('latest_topics', array(
         'TOPIC_TITLE'       => censor_text($topic_title),
         'POST_AUTHOR'       => $post_author,
         'POST_DATE'       => $post_date,
         'POST_LINK'       => $post_link,
		 'FORUM_TITLE'		=> $posts_row['forum_name'],
		 'FORUM_PARENT'		=> $forum_parent_id,
		 'TOPIC_FORUM_LINK'	 => append_sid("{$phpbb_root_path}viewforum.{$phpEx}", "f=" . $posts_row['forum_id']),
		 'PARENT_FORUM_LINK'	 => append_sid("{$phpbb_root_path}viewforum.{$phpEx}", "f=" . $posts_row['parent_id']),
         ));
      }	
HTML file

Code: Select all

           <!-- BEGIN latest_topics -->
           <div>
              <strong><a href="{latest_topics.POST_LINK}">{latest_topics.TOPIC_TITLE}</a></strong> in <a href="{latest_topics.TOPIC_FORUM_LINK}">{latest_topics.FORUM_TITLE}</a> + <a href="{latest_topics.PARENT_FORUM_LINK}">{latest_topics.FORUM_PARENT}</a> -----
 by {latest_topics.POST_AUTHOR} on {latest_topics.POST_DATE}
           </div>
           <!-- END latest_topics -->

User avatar
EXreaction
Former Team Member
Posts: 5666
Joined: Sun Aug 21, 2005 9:31 pm
Location: Wisconsin, U.S.
Name: Nathan

Re: How to show forum name of parent id?

Post by EXreaction » Thu Oct 17, 2013 6:55 pm

This works for me:

Code: Select all

$posts_ary = array(
        'SELECT'    => 'p.*, t.*, f.forum_name, f.forum_id, f.parent_id, u.username, u.user_colour, parent.forum_name AS parent_forum_name',

        'FROM'      => array(
            POSTS_TABLE     => 'p',
        ),

        'LEFT_JOIN' => array(
            array(
                'FROM'  => array(USERS_TABLE => 'u'),
                'ON'    => 'u.user_id = p.poster_id'
            ),
            array(
                'FROM'  => array(TOPICS_TABLE => 't'),
                 'ON'    => 'p.post_id = t.topic_last_post_id'
            ),
         array(
               'FROM'  => array(FORUMS_TABLE => 'f'),
               'ON'    => 'p.forum_id = f.forum_id'
            ),
         array(
               'FROM'  => array(FORUMS_TABLE => 'parent'),
               'ON'    => 'parent.forum_id = f.parent_id'
            ),
        ),

        'WHERE'     => $db->sql_in_set('t.forum_id', array_keys($auth->acl_getf('f_read', true))) . '
                        AND t.topic_status <> ' . ITEM_MOVED . '
                         AND t.topic_approved = 1',

        'ORDER_BY'  => 'p.post_id DESC',
    );

    $posts = $db->sql_build_query('SELECT', $posts_ary);

   $posts_result = $db->sql_query_limit($posts, $search_limit);

      while( $posts_row = $db->sql_fetchrow($posts_result) )
      {
         $topic_title       = $posts_row['topic_title'];
         $post_author       = get_username_string('full', $posts_row['poster_id'], $posts_row['username'], $posts_row['user_colour']);
         $post_date          = $user->format_date($posts_row['post_time']);
         $post_link       = append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $posts_row['forum_id'] . '&t=' . $posts_row['topic_id'] . '&p=' . $posts_row['post_id']) . '#p' . $posts_row['post_id'];
       $topic_forum_id   = $posts_row['forum_id'];
       $forum_parent_id = $posts_row['parent_id'];
         $post_text = nl2br($posts_row['post_text']);

         $bbcode = new bbcode(base64_encode($posts_row['bbcode_bitfield']));
         $bbcode->bbcode_second_pass($post_text, $posts_row['bbcode_uid'], $posts_row['bbcode_bitfield']);

         $post_text = smiley_text($post_text);

         var_dump(array(
         'TOPIC_TITLE'       => censor_text($topic_title),
         'POST_AUTHOR'       => $post_author,
         'POST_DATE'       => $post_date,
         'POST_LINK'       => $post_link,
       'FORUM_TITLE'      => $posts_row['forum_name'],
       'PARENT_FORUM_TITLE'      => $posts_row['parent_forum_name'],
       'FORUM_PARENT'      => $forum_parent_id,
       'TOPIC_FORUM_LINK'    => append_sid("{$phpbb_root_path}viewforum.{$phpEx}", "f=" . $posts_row['forum_id']),
       'PARENT_FORUM_LINK'    => append_sid("{$phpbb_root_path}viewforum.{$phpEx}", "f=" . $posts_row['parent_id']),
         ));
      }

miniatures12
Registered User
Posts: 9
Joined: Wed Aug 21, 2013 1:40 pm

Re: How to show forum name of parent id?

Post by miniatures12 » Thu Oct 17, 2013 11:10 pm

You sir, are my hero. It worked!

Image

Thanks for the help!


+++++++++++++++++++++++++++++++++++++++++++++
Final code for those who are interested:

HTML

Code: Select all

           <!-- BEGIN latest_topics -->
           <div>
              <strong><a href="{latest_topics.POST_LINK}">{latest_topics.TOPIC_TITLE}</a></strong> in <a href="{latest_topics.TOPIC_FORUM_LINK}">{latest_topics.FORUM_TITLE}</a> + <a href="{latest_topics.PARENT_FORUM_LINK}">{latest_topics.PARENT_FORUM_TITLE}</a> -----
 by {latest_topics.POST_AUTHOR} on {latest_topics.POST_DATE}
           </div>
           <!-- END latest_topics -->
PHP

Code: Select all

$posts_ary = array(
        'SELECT'    => 'p.*, t.*, f.forum_name, f.forum_id, f.parent_id, u.username, u.user_colour, parent.forum_name AS parent_forum_name', 
    
        'FROM'      => array(
            POSTS_TABLE     => 'p',
        ),
    
        'LEFT_JOIN' => array(
            array(
                'FROM'  => array(USERS_TABLE => 'u'),
                'ON'    => 'u.user_id = p.poster_id'
            ),
            array(
                'FROM'  => array(TOPICS_TABLE => 't'),
                 'ON'    => 'p.post_id = t.topic_last_post_id' 
            ),
			array( 
               'FROM'  => array(FORUMS_TABLE => 'f'), 
               'ON'    => 'p.forum_id = f.forum_id' 
            ),
         array(
               'FROM'  => array(FORUMS_TABLE => 'parent'),
               'ON'    => 'parent.forum_id = f.parent_id'
            ),
        ),
    
        'WHERE'     => $db->sql_in_set('t.forum_id', array_keys($auth->acl_getf('f_read', true))) . '
                        AND t.topic_status <> ' . ITEM_MOVED . '
                         AND t.topic_approved = 1',
    
        'ORDER_BY'  => 'p.post_id DESC',
    );
    
    $posts = $db->sql_build_query('SELECT', $posts_ary);

   $posts_result = $db->sql_query_limit($posts, $search_limit);
   
      while( $posts_row = $db->sql_fetchrow($posts_result) )
      {
         $topic_title       = $posts_row['topic_title'];
         $post_author       = get_username_string('full', $posts_row['poster_id'], $posts_row['username'], $posts_row['user_colour']);
         $post_date          = $user->format_date($posts_row['post_time']);
         $post_link       = append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $posts_row['forum_id'] . '&t=' . $posts_row['topic_id'] . '&p=' . $posts_row['post_id']) . '#p' . $posts_row['post_id'];
		 $topic_forum_id	= $posts_row['forum_id'];
		 $forum_parent_id = $posts_row['parent_id'];
         $post_text = nl2br($posts_row['post_text']);

         $bbcode = new bbcode(base64_encode($bbcode_bitfield));         
         $bbcode->bbcode_second_pass($post_text, $posts_row['bbcode_uid'], $posts_row['bbcode_bitfield']);

         $post_text = smiley_text($post_text);

         $template->assign_block_vars('latest_topics', array(
         'TOPIC_TITLE'       => censor_text($topic_title),
         'POST_AUTHOR'       => $post_author,
         'POST_DATE'       => $post_date,
         'POST_LINK'       => $post_link,
		 'FORUM_TITLE'      => $posts_row['forum_name'],
		 'PARENT_FORUM_TITLE'      => $posts_row['parent_forum_name'],
	 	 'FORUM_PARENT'      => $forum_parent_id,
		 'TOPIC_FORUM_LINK'	 => append_sid("{$phpbb_root_path}viewforum.{$phpEx}", "f=" . $posts_row['forum_id']),
		 'PARENT_FORUM_LINK'	 => append_sid("{$phpbb_root_path}viewforum.{$phpEx}", "f=" . $posts_row['parent_id']),
         ));
      }	

User avatar
irdem
Registered User
Posts: 172
Joined: Sat Oct 13, 2007 10:31 pm
Contact:

Re: How to show forum name of parent id?

Post by irdem » Fri Oct 18, 2013 8:48 am

@miniatures12,
very nice, thank you for sharing your code.

this line:

Code: Select all

		$bbcode = new bbcode(base64_encode($bbcode_bitfield));         
should be:

Code: Select all

		$bbcode = new bbcode(base64_encode($posts_row['bbcode_bitfield']));

miniatures12
Registered User
Posts: 9
Joined: Wed Aug 21, 2013 1:40 pm

Re: How to show forum name of parent id?

Post by miniatures12 » Fri Oct 18, 2013 9:27 am

Thanks for that Irdem!
irdem wrote:thank you for sharing your code.
You're welcome. I've been browsing these forums a lot, and so many times I see people just say "thanks!" or "I got it" without saying how they did it. I figured it would be nice to post the full solution for other people. :D

Thanks again guys!

Locked

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