Practical.Displaying posts and topics on external pages

It can be very useful to display the latest topics or posts on an external page, such as a website homepage. Fortunately, this is quite easy to do using the functions and classes provided in phpBB.

For the sake of this tutorial, we will be looking at displaying on a page called "home.php":


 * The latest topics (including from specified forums)
 * The first post of the latest topics (including from specified forums)
 * The latest posts from specified topics
 * The latest posts from the entire forum

Everything displayed is subject to the users forum read permissions.

=Header=

Firstly, create a file called home.php and include the standard phpBB3 header information. The $phpbb_root_path variable may need to be altered depending on where the phpBB installation is located. If the file is in the same directory as the phpBB root (the same area as viewtopic.php, viewforum.php, etc) then the relative file path './' does not need to be changed.

If, as is reasonably common, you have your phpBB installation in a directory called "forums" (ie. http://www.example.com/forums/) and you want the file home.php to be located in the website root directory (ie. http://www.example.com/home.php), then you would need to set the $phpbb_root_path to:

$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './forums/';

To go "back" a directory, you can use '../'. You would use this if, for instance, phpBB was installed in the website root directory (ie. http://www.example.com/) and you wanted to have your home.php scripts located in a folder within (ie. http://www.example.com/info/home.php)

The standard phpBB header looks like this:

session_begin; $auth->acl($user->data); $user->setup('viewforum');

bbcode.php needs to be included as when displaying the latest posts you may wish to parse the bbCode.

=Functions=

For the sake of simplicity when dealing with extracting posts or topics from multiple sources (ie. multiple topics or multiple forums), a function can be used to create the where clauses for the SQL query.

/* 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; }

= Forum and topic information =

We now include some of the forum and topic IDs that will be used throughout the script. $forum_id and $topic_id are both arrays that allow you to grab information from multiple sources. The $forum_id_where and $topic_id_where variables both call a new function, create_where_clauses, which will simplify the database queries.

$search_limit is the number of rows you want to grab. So if you want to get the 5 latest topics, you would set $search_limit to equal 5;

By leaving the array "$forum_id" empty, it will mean there are no restrictions and it will take the latest posts/topics from the entire forum instead of any particular area. Examples 3 and 4 demonstrate how to take the latest posts from specified areas and the entire board respectively.

$search_limit = 5;

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

$topic_id = array(20, 50); $topic_id_where = create_where_clauses($topic_id, 'topic');

= Database calls =

Now the actual database calls. We have quite a few examples.

 Note: If you are using phpBB 3.1.x references to  in the following examples must be changed to  .

Example 1: Display latest topics from specified forums
The first example is selecting the 5 latest topics from the forum(s) chosen above.

Note: an example of this can be seen at http://www.bilvardsforum.se/ (Swedish)

Example 1. (Display latest topics from specified forums)

$topics = 'SELECT * FROM '. TOPICS_TABLE. '           ' . $forum_id_where. '     		AND topic_status <> '. ITEM_MOVED. '     		AND topic_approved = 1 ORDER BY topic_id DESC';

$topics_result = $db->sql_query_limit($topics, $search_limit);

while( $topics_row = $db->sql_fetchrow($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'] . '&amp;t=' . $topics_row['topic_id'] . '&amp;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'] . '&amp;t=' . $topics_row['topic_id']);

$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, ));     }

Example 2: Display first post from the last five topics
The next example involves extracting the first post of each of the five last topics. It consists of quite a lot of the same code, except this time it also gets the post text and makes some bbCode related calls. Notice how the posts and topics are selected within the same SQL query - by doing a left join it means you do not have to include a second database query when looping through the results of the initial query.

For examples 2 to 4, see how a new bbcode object has been created and its associated functions used to allow the post text to be correctly parsed.

Note: an example of this can be seen at http://www.cricketmx.com/ (English)

Example 2. (Display first post from the last five topics)

$posts_ary = array(	   'SELECT'    => 'p.*, t.*',	    'FROM'      => array( POSTS_TABLE    => 'p', ),	   'LEFT_JOIN' => array( array(	           'FROM'  => array(TOPICS_TABLE => 't'),	            'ON'    => 't.topic_first_post_id = p.post_id'	        ) ),	   'WHERE'     => str_replace( array('WHERE ', 'forum_id'), array('', 't.forum_id'), $forum_id_where) . '						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']; $topic_author      = get_username_string('full', $posts_row['topic_poster'], $posts_row['topic_first_poster_name'], $posts_row['topic_first_poster_colour']); $topic_date      = $user->format_date($posts_row['topic_time']); $topic_link      = append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $posts_row['forum_id'] . '&amp;t=' . $posts_row['topic_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('announcements', array( 'TOPIC_TITLE'      => censor_text($topic_title), 'TOPIC_AUTHOR'      => $topic_author, 'TOPIC_DATE'      => $topic_date, 'TOPIC_LINK'      => $topic_link, 'POST_TEXT'        => censor_text($post_text), ));     }

Example 3: Display posts from specified topics
The third example is grabbing the latest posts from a specified topic(s).

Example 3. (Display posts from specified topics)

$posts_ary = array(	   'SELECT'    => 'p.*, t.*, 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.topic_id = t.topic_id'	        ), ),	   'WHERE'     =>  str_replace( array('WHERE ', 'topic_id'), array('', 't.topic_id'), $topic_id_where) . '						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'] . '&amp;t=' . $posts_row['topic_id'] . '&amp;p=' . $posts_row['post_id']). '#p'. $posts_row['post_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('announcements', array( 'TOPIC_TITLE'      => censor_text($topic_title), 'POST_AUTHOR'      => $post_author, 'POST_DATE'      => $post_date, 'POST_LINK'      => $post_link, 'POST_TEXT'        => censor_text($post_text), ));     }

Example 4: Display posts from anywhere
And finally, to display the latest posts from the entire board with no restrictions on topics.

The only difference is the removal of the line. It is replaced with an sql_in_set call that restricts the returned results to areas that the user has correct permissions for.

Note: an example of this can be seen at http://www.rmcgirr83.org/ (English)

Example 4. (Display posts from anywhere)

$posts_ary = array(	   'SELECT'    => 'p.*, t.*, 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.topic_id = t.topic_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'] . '&amp;t=' . $posts_row['topic_id'] . '&amp;p=' . $posts_row['post_id']). '#p'. $posts_row['post_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('announcements', array( 'TOPIC_TITLE'      => censor_text($topic_title), 'POST_AUTHOR'      => $post_author, 'POST_DATE'      => $post_date, 'POST_LINK'      => $post_link, 'POST_TEXT'        => censor_text($post_text), ));     }

= Footer and conclusion =

By editing the SQL query you can manipulate the examples above to do almost anything you would like with regards to displaying posts and topics on external pages.

By using the templating variables, you can also incorporate the results into a page styled like phpBB3. To do this you should end the page like this, and create an associated phpBB3 template (*.html) file. For further information about templating, read the phpBB wiki page on template syntax.

page_header('External page');

$template->set_filenames(array( 'body' => 'external_body.html' ));

page_footer; ?>