Page 1 of 1

Problem with "Displaying posts and topics on external pages"

Posted: Thu Jan 20, 2011 8:06 am
by powerbonger
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

Re: Problem with "Displaying posts and topics on external pa

Posted: Thu Jan 20, 2011 8:09 am
by powerbonger
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. :?

Re: Problem with "Displaying posts and topics on external pa

Posted: Thu Jan 20, 2011 1:40 pm
by battye
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 :)

Re: Problem with "Displaying posts and topics on external pa

Posted: Fri Aug 12, 2011 8:31 am
by vaalsh
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!

Re: Problem with "Displaying posts and topics on external pa

Posted: Sat Aug 13, 2011 4:26 pm
by battye
Can you please post the first 10 lines of index_test.html?