Adding WHERE statement to a DB query

[Slyfox1]

Hi,

I'm using a HACK that pulls threads from the forums table and displays the latest X threads.

There are permissions on the HACk, but there's no way to exclude specific forums.

So, I was hoping someone might be able to look at the section of code below and perhaps suggest a way to either exclude specific forums, or add forums to the viewing list. I would be happy enough to add 50 forum ID's to restrict one forum.

Cheers

$latestthreads = $db->query_read("
SELECT thread.*, thread.open AS open, forum.title as forumname, forum.forumid as forumid, icon.iconpath AS iconpath, icon.iconid,
IF (thread.iconid=0, 'images/icons/icon1.gif', icon.iconpath)as icon
FROM " . TABLE_PREFIX . "thread as thread
LEFT JOIN " . TABLE_PREFIX . "forum as forum ON (forum.forumid = thread.forumid)
LEFT JOIN " . TABLE_PREFIX . "icon as icon ON (icon.iconid = thread.iconid)
$excludedthreads
ORDER BY thread.lastpost DESC
LIMIT 0,$number
");

[Adrian Schneider]

What is $excludedthreads ? I'm assuming it contains "WHERE" - so make sure that its either always there or make it that way.

Here would be the condition to limit it to certain forums: thread.forumid IN (1, 2, 3) and to exclude: NOT (thread.forumid IN (1, 2, 3))You're probably best off loading them into an array and imploding them inside the query $forums = array(1, 2, 3, ...);
$forumlist = implode(',', $forums); then use thread.forumid IN ($forumlist)

[Slyfox1]

Hi and thanks for your reply.

I should have posted the entire code to make sure the addition fits in. I tried to add the statement(s) below and it failed :( My fault for not knowing where to add the code.

Here's the entire section

$number = $vbulletin->options[latest_x_threads_number];

foreach ($vbulletin->userinfo['forumpermissions'] AS $forumid => $fperms)
{
if (!($fperms & $vbulletin->bf_ugp_forumpermissions['canview']) OR !($fperms & $vbulletin->bf_ugp_forumpermissions['cansearch']) OR !verify_forum_password($forumid, $forum['password'], false) OR !($vbulletin->forumcache[$forumid]['options'] & $vbulletin->bf_misc_forumoptions['indexposts']))
{
$excludelist .= ",$forumid";
}
else if ((!$vbulletin->GPC['titleonly'] OR $vbulletin->GPC['showposts']) AND !($fperms & $vbulletin->bf_ugp_forumpermissions['canviewthreads']))
{ // exclude forums that have canview but no canviewthreads if this is a post search
$excludelist .= ",$forumid";
}
}
if($excludelist != ''){
$excludedthreads = "WHERE forum.forumid NOT IN (0$excludelist)";
}
$latestthreads = $db->query_read("
SELECT thread.*, thread.open AS open, forum.title as forumname, forum.forumid as forumid, icon.iconpath AS iconpath, icon.iconid,
IF (thread.iconid=0, 'images/icons/icon1.gif', icon.iconpath)as icon
FROM " . TABLE_PREFIX . "thread as thread
LEFT JOIN " . TABLE_PREFIX . "forum as forum ON (forum.forumid = thread.forumid)

LEFT JOIN " . TABLE_PREFIX . "icon as icon ON (icon.iconid = thread.iconid)
$excludedthreads
ORDER BY thread.lastpost DESC
LIMIT 0,$number
");

while ($thread = $db->fetch_array($latestthreads))
{
if ($thread['replycount'] >= $vbulletin->options['hotnumberposts'] OR $thread['views'] >= $vbulletin->options['hotnumberviews'])
{
$thread['statusicon'] = "_hot_new";
}

if ($thread['open'] == '0')
{
$thread['statusicon'] = "_lock";

if ($thread['replycount'] >= $vbulletin->options['hotnumberposts'] OR $thread['views'] >= $vbulletin->options['hotnumberviews'])
{
$thread['statusicon'] = "_hot_lock";
}
}
eval('$latest_x_threads_bits .= "' . fetch_template('latest_x_threads_bits') . '";');
}
eval('$latest_x_threads = "' . fetch_template('latest_x_threads') . '";');

[Adrian Schneider]

Try this, and just add to the $excludearray at the beginning to exclude more forums or the $includeforums to only show those forums, $number = $vbulletin->options['latest_x_threads_number'];

$conditions = array('1=1');
$excludearray();
$includeforums = array(1, 2, 3);

foreach ($vbulletin->userinfo['forumpermissions'] AS $forumid => $fperms)
{
if (
!($fperms & $vbulletin->bf_ugp_forumpermissions['canview']) OR
!($fperms & $vbulletin->bf_ugp_forumpermissions['cansearch']) OR
!verify_forum_password($forumid, $forum['password'], false) OR
!($vbulletin->forumcache[$forumid]['options'] & $vbulletin->bf_misc_forumoptions['indexposts'])
)
{
$excludearray[] = $forumid;
}

else if (
(!$vbulletin->GPC['titleonly'] OR $vbulletin->GPC['showposts']) AND
!($fperms & $vbulletin->bf_ugp_forumpermissions['canviewthreads'])
)
{
$excludearray[] = $forumid;
}
}

if (!empty($excludelist))
{
$conditions[] = 'forum.forumid NOT IN (' . implode(',', $excludearray) . ')';
}

if (!empty($includeforums))
{
$conditions[] = 'forum.forumid IN (' . implode(',', $includeforums) . ')';
}

$latestthreads = $db->query_read("
SELECT thread.*, thread.open AS open, forum.title as forumname, forum.forumid as forumid,
icon.iconpath AS iconpath, icon.iconid, IF (thread.iconid=0, 'images/icons/icon1.gif', icon.iconpath)as icon

FROM " . TABLE_PREFIX . "thread as thread
LEFT JOIN " . TABLE_PREFIX . "forum as forum ON (forum.forumid = thread.forumid)
LEFT JOIN " . TABLE_PREFIX . "icon as icon ON (icon.iconid = thread.iconid)

WHERE " . implode(' and ', $conditions) . "

ORDER BY thread.lastpost DESC
LIMIT 0,$number
");

while ($thread = $db->fetch_array($latestthreads))
{
if (
$thread['replycount'] >= $vbulletin->options['hotnumberposts'] OR
$thread['views'] >= $vbulletin->options['hotnumberviews']
)
{
$thread['statusicon'] = "_hot_new";
}


if (!$thread['open'])
{
$thread['statusicon'] = "_lock";

if (
$thread['replycount'] >= $vbulletin->options['hotnumberposts'] OR
$thread['views'] >= $vbulletin->options['hotnumberviews'])
{
$thread['statusicon'] = "_hot_lock";
}
}

eval('$latest_x_threads_bits .= "' . fetch_template('latest_x_threads_bits') . '";');
}

eval('$latest_x_threads = "' . fetch_template('latest_x_threads') . '";');
untested

[Slyfox1]

Hey,

I tried to remove the Included forums and add the ones to the exclude and got a blank white page, I then tried to code as is and got the same blank page :(

[Adrian Schneider]

My Apologies... near the top, change this $excludearray(); to $excludearray = array();

[Slyfox1]

OK, added the changes and no more blank pages. Removed the entries in the Include section and added one subforum to the exclude list, but posts in this subforum still appear.

Is it different for subforums?

Thanks again.,

[Adrian Schneider]

Can you post your exact code here, thanks.

[Slyfox1]

$number = $vbulletin->options['latest_x_threads_number'];

$conditions = array('1=1');
$excludearray = array(18);
$includeforums = array();

foreach ($vbulletin->userinfo['forumpermissions'] AS $forumid => $fperms)
{
if (
!($fperms & $vbulletin->bf_ugp_forumpermissions['canview']) OR
!($fperms & $vbulletin->bf_ugp_forumpermissions['cansearch']) OR
!verify_forum_password($forumid, $forum['password'], false) OR
!($vbulletin->forumcache[$forumid]['options'] & $vbulletin->bf_misc_forumoptions['indexposts'])
)
{
$excludearray[] = $forumid;
}

else if (
(!$vbulletin->GPC['titleonly'] OR $vbulletin->GPC['showposts']) AND
!($fperms & $vbulletin->bf_ugp_forumpermissions['canviewthreads'])
)
{
$excludearray[] = $forumid;
}
}

if (!empty($excludelist))
{
$conditions[] = 'forum.forumid NOT IN (' . implode(',', $excludearray) . ')';
}

if (!empty($includeforums))
{
$conditions[] = 'forum.forumid IN (' . implode(',', $includeforums) . ')';
}

$latestthreads = $db->query_read("
SELECT thread.*, thread.open AS open, forum.title as forumname, forum.forumid as forumid,
icon.iconpath AS iconpath, icon.iconid, IF (thread.iconid=0, 'images/icons/icon1.gif', icon.iconpath)as icon

FROM " . TABLE_PREFIX . "thread as thread
LEFT JOIN " . TABLE_PREFIX . "forum as forum ON (forum.forumid = thread.forumid)
LEFT JOIN " . TABLE_PREFIX . "icon as icon ON (icon.iconid = thread.iconid)

WHERE " . implode(' and ', $conditions) . "

ORDER BY thread.lastpost DESC
LIMIT 0,$number
");

while ($thread = $db->fetch_array($latestthreads))
{
if (
$thread['replycount'] >= $vbulletin->options['hotnumberposts'] OR
$thread['views'] >= $vbulletin->options['hotnumberviews']
)
{
$thread['statusicon'] = "_hot_new";
}


if (!$thread['open'])
{
$thread['statusicon'] = "_lock";

if (
$thread['replycount'] >= $vbulletin->options['hotnumberposts'] OR
$thread['views'] >= $vbulletin->options['hotnumberviews'])
{
$thread['statusicon'] = "_hot_lock";
}
}

eval('$latest_x_threads_bits .= "' . fetch_template('latest_x_threads_bits') . '";');
}

eval('$latest_x_threads = "' . fetch_template('latest_x_threads') . '";');

[Adrian Schneider]

Typo, change this if (!empty($excludelist))with this if (!empty($excludearray))

[Slyfox1]

BINGO!!

We have a winner :)

Thanks a lot..Really appreciate it.

Sly