Problem with "Displaying posts and topics on external pages"

All web design discussion, including Ultimate Quiz MOD support.

Moderator: CricketMX Forum Moderators

Post Reply
powerbonger
Greenhorn
Greenhorn
Posts: 2
Joined: Thu Jan 20, 2011 7:54 am

Hi! Total newbie to PHP and MySQL here. A couple of days ago I ran across battye's tutorial on how to display PHPBB3 posts and topics on external pages, found here: http://wiki.phpbb.com/display/MODDOCS/P ... rnal+pages


I followed the instructions to the best of my ability (er...copy paste :D ), created a home.php and external_body.html, and went with the code for Example 3, but I kept getting this error:
General Error
SQL ERROR [ mysql4 ]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND t.topic_status <> 2 AND t.topic_approved = 1 ORDER BY ' at line 2 [1064]

An SQL error occurred while fetching this page. Please contact the Board Administrator if this problem persists.

Here is the code I used for home.php (copy-paste goodness):

Code: Select all

<?php

/*
* home.php
* Description: example file for displaying latest posts and topics
* by battye (for phpBB.com MOD Team)
* September 29, 2009
*/
 
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './phpBB3/';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);
include($phpbb_root_path . 'includes/bbcode.' . $phpEx);
include($phpbb_root_path . 'includes/functions_display.' . $phpEx);
 
// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup('viewforum');

//
/* 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 = 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');
//
$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'] . '&t=' . $posts_row['topic_id'] . '&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),
      ));
   }
//
    page_header('External page');
 
    $template->set_filenames(array(
        'body' => 'external_body.html'
    ));
 
    page_footer();
?>
?>
And, just in case, here is the code for my external_body.html (which is located in the \prosilver\template\ folder of my phpbb3 directory:

Code: Select all

<!-- BEGIN announcements -->
Title: {announcements.TOPIC_TITLE}<br />
Post author: {announcements.TOPIC_AUTHOR}<br />
Post date: {announcements.POST_DATE}<br />
Last post text: {announcements.POST_TEXT}<br />
Post link: {announcements.POST_LINK}
<!-- END announcements -->

Can anyone show me where I went wrong? I'm still new to this, and would greatly appreciate any help. :)



Regards,

powerbonger
powerbonger
Greenhorn
Greenhorn
Posts: 2
Joined: Thu Jan 20, 2011 7:54 am

Oh, to add, when I tried to use the code for Example 4 on the same file above (replaced the code from Example 3), it worked as intended, and the error didn't show up. :?
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

Hmm, that is strange. I tested all of the examples before I published any of them online, and I'm not immediately seeing what the problem could be.

Could you try removing this line:

Code: Select all

     'ORDER_BY'  => 'p.post_id DESC',
And letting me know if you still get the SQL error?

Welcome to the forum powerbonger, feel free to check out OT Forum if you haven't already had the chance :)
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
vaalsh
Greenhorn
Greenhorn
Posts: 1
Joined: Fri Aug 12, 2011 8:16 am

Hi!

Script works fine on a separate page, but when i am trying to put it (include "home.php") on main page
of my site, I am getting error:

<< in file /includes/functions.php on line 4284: Cannot modify header information - headers already sent by (output started at index_test.html:6) >>

Any help greatly appreciated!
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

Can you please post the first 10 lines of index_test.html?
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
Post Reply