vb.org Archive
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)
-   -   Adding WHERE statement to a DB query (https://vborg.vbsupport.ru/showthread.php?t=132147)

Slyfox1 11-21-2006 10:03 PM
Adding WHERE statement to a DB query
 
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

PHP Code:
$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 11-21-2006 10:07 PM
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: [sql]thread.forumid IN (1, 2, 3)[/sql] and to exclude: [sql]NOT (thread.forumid IN (1, 2, 3))[/sql]You're probably best off loading them into an array and imploding them inside the query
PHP Code:
$forums = array(1, 2, 3, ...);
$forumlist = implode(',', $forums); 
then use [sql]thread.forumid IN ($forumlist)[/sql]

Slyfox1 11-21-2006 10:40 PM
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
PHP Code:
$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 11-21-2006 10:49 PM
Try this, and just add to the $excludearray at the beginning to exclude more forums or the $includeforums to only show those forums,
PHP Code:
$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 11-22-2006 12:24 AM
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 11-22-2006 12:27 AM
My Apologies... near the top, change this
PHP Code:
$excludearray(); 
to
PHP Code:
$excludearray = array(); 

Slyfox1 11-22-2006 12:48 AM
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 11-22-2006 01:09 AM
Can you post your exact code here, thanks.

Slyfox1 11-22-2006 01:56 AM
PHP Code:
$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 11-22-2006 02:03 AM
Typo, change this
PHP Code:
if (!empty($excludelist)) 
with this
PHP Code:
if (!empty($excludearray)) 


All times are GMT. The time now is 02:49 PM.
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.02231 seconds
  • Memory Usage 1,846KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (9)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (10)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete