Topic Preview for Board3 : SQL conflict with "Recent Topics"

[contiw]

Your Portal Version: 1.0.0RC3
Your phpBB Type: Premodded phpBB
MODs installed: Yes
Your knowledge: Beginner
Boardlink: [url]http://localhost_only[/url]

MySQL Version: 5.1.48-community

What have you done before the problem was there?
Installed Topic Preview for Board3.

What have you already tryed to solve the problem?
Tried installation on fresh copy (no uninstall) of program and database, twice with same results.
In effect, if I disable Topic Preview in UCP > board prefs > edit display options, I get no SQL error and Recent Topics works ok.
Do not know what to do. Noob here. Please bear with me.

Description and Message
SQL ERROR [ mysqli ]

Column 'forum_id' in where clause is ambiguous [1052]

SQL

SELECT t.topic_title, t.forum_id, t.topic_id , pt.post_text AS topic_preview_text FROM phpbb_topics t LEFT JOIN phpbb_posts pt ON (pt.post_id = t.topic_first_post_id)WHERE t.topic_status <> 2 AND t.topic_approved = 1 AND (t.topic_type = 2 OR t.topic_type = 3) AND t.topic_moved_id = 0 AND forum_id IN ('4', '14', '15', '16', '5', '6') ORDER BY t.topic_time DESC LIMIT 5

BACKTRACE

FILE: includes/db/mysqli.php
LINE: 163
CALL: dbal->sql_error()

FILE: includes/db/mysqli.php
LINE: 205
CALL: dbal_mysqli->sql_query()

FILE: includes/db/dbal.php
LINE: 170
CALL: dbal_mysqli->_sql_query_limit()

FILE: portal/block/recent.php
LINE: 77
CALL: dbal->sql_query_limit()

FILE: index.php
LINE: 99
CALL: include('portal/block/recent.php')

[Marc]

What version of Board3 Portal are you using? It seems to me like your recent.php is outdated.

[contiw]

I have version 1.0.5.
The Check Version says it is updated.
Thanks Mark.

[Marc]

Replace the recent.php with the one from the current Board3 Portal package and redo the changes.

[contiw]

It looks like the incompatibilities are with "Topic Preview MOD" code.
Here is recent.php file with the blocks of code pertaining to Topic Preview MOD separated with dotted lines (................)
With the other Board3 blocks, Topic Preview MOD works ok.

Code: Select all

<?php

/**
*
* @package - Board3portal
* @version $Id: recent.php 589 2009-12-04 21:11:16Z marc1706 $
* @copyright (c) kevin / saint ( www.board3.de/ ), (c) Ice, (c) nickvergessen ( www.flying-bits.org/ ), (c) redbull254 ( www.digitalfotografie-foren.de ), (c) Christian_N ( www.phpbb-projekt.de )
* @based on: phpBB3 Portal by Sevdin Filiz, www.phpbb3portal.com
* @license http://opensource.org/licenses/gpl-license.php GNU Public License 
*
*/

if (!defined('IN_PHPBB') || !defined('IN_PORTAL'))
{
   exit;
}

//
// Exclude forums
//
$sql_where = '';
if ($portal_config['portal_recent_forum'] > 0)
{
	$exclude_forums = explode(',', $portal_config['portal_recent_forum']);
	
	$sql_where = ' AND ' . $db->sql_in_set('forum_id', $exclude_forums, ($portal_config['portal_exclude_forums']) ? true : false);
}

// Get a list of forums the user cannot read
$forum_ary = array_unique(array_keys($auth->acl_getf('!f_read', true)));

// Determine first forum the user is able to read (must not be a category)
$sql = 'SELECT forum_id
	FROM ' . FORUMS_TABLE . '
	WHERE forum_type = ' . FORUM_POST;

$forum_sql = '';
if (sizeof($forum_ary))
{
	$sql .= ' AND ' . $db->sql_in_set('forum_id', $forum_ary, true);
	$forum_sql = ' AND ' . $db->sql_in_set('t.forum_id', $forum_ary, true);
}

$result = $db->sql_query_limit($sql, 1);
$g_forum_id = (int) $db->sql_fetchfield('forum_id');

//
// Recent announcements
//

.......................................................................................
// BEGIN Topic Preview Mod
if ($config['topic_preview_limit'] && $user->data['user_topic_preview'])
{
	$sql_join = ' LEFT JOIN ' . POSTS_TABLE . ' pt ON (pt.post_id = t.topic_first_post_id)';
	$sql_select = ', pt.post_text AS topic_preview_text';

	$sql = 'SELECT t.topic_title, t.forum_id, t.topic_id ' . $sql_select . '
		FROM ' . TOPICS_TABLE . ' t
		' . $sql_join . 'WHERE t.topic_status <> ' . FORUM_LINK . '
			AND t.topic_approved = 1 
			AND (t.topic_type = ' . POST_ANNOUNCE . ' OR t.topic_type = ' . POST_GLOBAL . ')
			AND t.topic_moved_id = 0
			' . $sql_where . '' .  $forum_sql . '
		ORDER BY t.topic_time DESC';
}
else
{
..........................................................


	$sql = 'SELECT topic_title, forum_id, topic_id
		FROM ' . TOPICS_TABLE . ' t
		WHERE topic_status <> ' . FORUM_LINK . '
			AND topic_approved = 1 
			AND (topic_type = ' . POST_ANNOUNCE . ' OR topic_type = ' . POST_GLOBAL . ')
			AND topic_moved_id = 0
			' . $sql_where . '' .  $forum_sql . '
		ORDER BY topic_time DESC';

...................................................................
}
// END Topic Preview Mod
...................................................................

$result = $db->sql_query_limit($sql, $portal_config['portal_max_topics']);

while(($row = $db->sql_fetchrow($result)) && ($row['topic_title']))
{
	// auto auth
	if (($auth->acl_get('f_read', $row['forum_id'])) || ($row['forum_id'] == '0'))
	{
		$template->assign_block_vars('latest_announcements', array(
			// BEGIN Topic Preview Mod
			'TOPIC_PREVIEW_TEXT'	=> (!empty($row['topic_preview_text'])) ? censor_text(trim_topic_preview($row['topic_preview_text'], $config['topic_preview_limit'])) : character_limit($row['topic_title'], $portal_config['portal_recent_title_limit']),
			// END Topic Preview Mod
			'TITLE'			=> character_limit($row['topic_title'], $portal_config['portal_recent_title_limit']),
			'FULL_TITLE'	=> censor_text($row['topic_title']),
			'U_VIEW_TOPIC'	=> append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . (($row['forum_id'] == 0) ? $g_forum_id : $row['forum_id']) . '&t=' . $row['topic_id'])
		));
	}
}
$db->sql_freeresult($result);

//
// Recent hot topics
//

......................................................................
// BEGIN Topic Preview Mod
if ($config['topic_preview_limit'] && $user->data['user_topic_preview'])
{
	$sql_join = ' LEFT JOIN ' . POSTS_TABLE . ' pt ON (pt.post_id = t.topic_first_post_id)';
	$sql_select = ', pt.post_text AS topic_preview_text';

	$sql = 'SELECT t.topic_title, t.forum_id, t.topic_id ' . $sql_select . '
		FROM ' . TOPICS_TABLE . ' t
		' . $sql_join . 'WHERE t.topic_approved = 1 
			AND t.topic_replies >=' . $config['hot_threshold'] . '
			AND t.topic_moved_id = 0
			' . $sql_where . '' .  $forum_sql . '
		ORDER BY t.topic_time DESC';
}
else
{
........................................................................

	$sql = 'SELECT topic_title, forum_id, topic_id
		FROM ' . TOPICS_TABLE . ' t
		WHERE topic_approved = 1 
			AND topic_replies >=' . $config['hot_threshold'] . '
			AND topic_moved_id = 0
			' . $sql_where . '' .  $forum_sql . '
		ORDER BY topic_time DESC';

......................................................................
}
// END Topic Preview Mod
......................................................................

$result = $db->sql_query_limit($sql, $portal_config['portal_max_topics']);

while(($row = $db->sql_fetchrow($result)) && ($row['topic_title']))
{
	// auto auth
	if (($auth->acl_get('f_read', $row['forum_id'])) || ($row['forum_id'] == '0'))
	{
		$template->assign_block_vars('latest_hot_topics', array(
			// BEGIN Topic Preview Mod
			'TOPIC_PREVIEW_TEXT'	=> (!empty($row['topic_preview_text'])) ? censor_text(trim_topic_preview($row['topic_preview_text'], $config['topic_preview_limit'])) : character_limit($row['topic_title'], $portal_config['portal_recent_title_limit']),
			// END Topic Preview Mod
			'TITLE'			=> character_limit($row['topic_title'], $portal_config['portal_recent_title_limit']),
			'FULL_TITLE'	=> censor_text($row['topic_title']),
			'U_VIEW_TOPIC'	=> append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . (($row['forum_id'] == 0) ? $g_forum_id : $row['forum_id']) . '&t=' . $row['topic_id'])
		));
	}
}
$db->sql_freeresult($result);

//
// Recent topic (only show normal topic)
//

.........................................................................
// BEGIN Topic Preview Mod
if ($config['topic_preview_limit'] && $user->data['user_topic_preview'])
{
	$sql_join = ' LEFT JOIN ' . POSTS_TABLE . ' pt ON (pt.post_id = t.topic_first_post_id)';
	$sql_select = ', pt.post_text AS topic_preview_text';

	$sql = 'SELECT t.topic_title, t.forum_id, t.topic_id ' . $sql_select . '
		FROM ' . TOPICS_TABLE . ' t
		' . $sql_join . 'WHERE t.topic_status <> ' . ITEM_MOVED . '
			AND t.topic_approved = 1 
			AND t.topic_type = ' . POST_NORMAL . '
			AND t.topic_moved_id = 0
			' . $sql_where . '' .  $forum_sql . '
		ORDER BY t.topic_time DESC';
}
else
{
.....................................................................

	$sql = 'SELECT topic_title, forum_id, topic_id
		FROM ' . TOPICS_TABLE . ' t
		WHERE topic_status <> ' . ITEM_MOVED . '
			AND topic_approved = 1 
			AND topic_type = ' . POST_NORMAL . '
			AND topic_moved_id = 0
			' . $sql_where . '' .  $forum_sql . '
		ORDER BY topic_time DESC';

...............................................................
}
// END Topic Preview Mod
...............................................................

$result = $db->sql_query_limit($sql, $portal_config['portal_max_topics']);

while(($row = $db->sql_fetchrow($result)) && ($row['topic_title']))
{
	// auto auth
	if (($auth->acl_get('f_read', $row['forum_id'])) || ($row['forum_id'] == '0'))
	{
		$template->assign_block_vars('latest_topics', array(
			// BEGIN Topic Preview Mod
			'TOPIC_PREVIEW_TEXT'	=> (!empty($row['topic_preview_text'])) ? censor_text(trim_topic_preview($row['topic_preview_text'], $config['topic_preview_limit'])) : character_limit($row['topic_title'], $portal_config['portal_recent_title_limit']),
			// END Topic Preview Mod
			'TITLE'			=> character_limit($row['topic_title'], $portal_config['portal_recent_title_limit']),
			'FULL_TITLE'	=> censor_text($row['topic_title']),
			'U_VIEW_TOPIC'	=> append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $row['forum_id'] . '&t=' . $row['topic_id'])
		));
	}
}
$db->sql_freeresult($result);

$template->assign_var('S_DISPLAY_RECENT', true);

?>

[Marc]

Ok, find every sql query that looks like this:

Code: Select all

   $sql = 'SELECT topic_title, forum_id, topic_id
      FROM ' . TOPICS_TABLE . ' t
      WHERE topic_status <> ' . FORUM_LINK . '
         AND topic_approved = 1
         AND (topic_type = ' . POST_ANNOUNCE . ' OR topic_type = ' . POST_GLOBAL . ')
         AND topic_moved_id = 0
         ' . $sql_where . '' .  $forum_sql . '
      ORDER BY topic_time DESC';

And add a t. in front of everything, i.e.:

Code: Select all

   $sql = 'SELECT t.topic_title, t.forum_id, t.topic_id
      FROM ' . TOPICS_TABLE . ' t
      WHERE t.topic_status <> ' . FORUM_LINK . '
         AND t.topic_approved = 1
         AND (t.topic_type = ' . POST_ANNOUNCE . ' OR t.topic_type = ' . POST_GLOBAL . ')
         AND t.topic_moved_id = 0
         ' . $sql_where . '' .  $forum_sql . '
      ORDER BY t.topic_time DESC';

Then it should work.

[contiw]

OK, you fixed it.
but I had to ad a "t." also on line 26 to the "forum_id" variable like

Code: Select all

	$sql_where = ' AND ' . $db->sql_in_set('t.forum_id', $exclude_forums, ($portal_config['portal_exclude_forums']) ? true : false);

I have tested the queries without the t. and it works ok just the same.
So it looks like this is the only correction needed.
I left the queries as you suggested anyway.

Thank you.