Page 1 of 1
Topic Preview for Board3 : SQL conflict with "Recent Topics"
Posted: 2. August 2010 03:10
by 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')
Re: Topic Preview for Board3 : SQL conflict with "Recent Top
Posted: 14. August 2010 11:34
by Marc
What version of Board3 Portal are you using? It seems to me like your recent.php is outdated.
Re: Topic Preview for Board3 : SQL conflict with "Recent Top
Posted: 14. August 2010 15:00
by contiw
I have version 1.0.5.
The Check Version says it is updated.
Thanks Mark.
Re: Topic Preview for Board3 : SQL conflict with "Recent Top
Posted: 14. August 2010 22:47
by Marc
Replace the recent.php with the one from the current Board3 Portal package and redo the changes.
Re: Topic Preview for Board3 : SQL conflict with "Recent Top
Posted: 15. August 2010 02:39
by 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);
?>
Re: Topic Preview for Board3 : SQL conflict with "Recent Top
Posted: 16. August 2010 11:49
by 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.
Re: Topic Preview for Board3 : SQL conflict with "Recent Top
Posted: 16. August 2010 17:43
by 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.