• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Please help clean up working code to pull threads based on forum id

LPH

Well-known member
#1
After almost a week, I have working code to pull a forum id from a select that is used in a query to list threads for that forum.

The code works but is wild that I must wrap quotes around $forum_number at one part to get the query to run properly.

PHP:
        $forum_number =  $instance['show_forum_number'];

        $forum_number = " \"$forum_number\" ";

        $select_thread_qry = '
                    SELECT thread_id, title, node_id, discussion_state FROM `xf_thread`
                    WHERE discussion_state = 1 AND node_id = ' .  $forum_number  . '
                    ORDER BY `thread_id` DESC
                    LIMIT 50
                    ';

        $select_threads = XenForo_Application::get( 'db' )->fetchAll( $select_thread_qry );
Do you have any suggestions?
 

Insy

Well-known member
#2
PHP:
$db = XenForo_Application::get('db');

$forum_number = $instance['show_forum_number'];

$select_threads = $db->fetchAll('
   SELECT thread_id, title, node_id, discussion_state
   FROM `xf_thread`
   WHERE discussion_state = 1 AND node_id = ' . $db->quote($forum_number) . '
   ORDER BY `thread_id` DESC
   LIMIT 50
';
I've not tested this code
 

LPH

Well-known member
#3
Ah. I learned something new - PDO::quote !

Yes. Your code almost works. It is missing the closing parenthesis in the fetchAll but ... once that is put in then it runs the query properly.
 

Daniel Hood

Well-known member
#4
It's encouraged to use the XenForo Models plus then you can do it all in one line ;)

PHP:
$select_threads = XenForo_Model::create('XenForo_Model_Thread')->getThreads(array('node_id' => $instance['show_forum_number'], 'discussion_state' => 1), array('limit' => 50));
 

LPH

Well-known member
#5
It's encouraged to use the XenForo Models plus then you can do it all in one line ;)
Thank you ! This is exactly what I must learn !

Version 3 of the bridge is focused on using more of the XenForo code and methods but it's a huge step moving from procedural and building the queries to learning the XF way. But it is exciting to learn better ways to integrate WP and XF.

(y)
 

LPH

Well-known member
#6
Wow ! Two lines replaced all of that wild stuff. Best of all, the threads are returned much faster (no actual benchmarks ) !

(y)

PHP:
        $feature_node_qry = '
                    SELECT node_id, title, display_in_list, node_type_id FROM `xf_node`
                    WHERE display_in_list = 1 AND node_type_id = "Forum"
                    ORDER BY `title` ASC
                    ';

        $feature_nodes = XenForo_Application::get( 'db' )->fetchAll( $feature_node_qry );

        $db = XenForo_Application::get('db');

        $forum_number = $instance['show_forum_number'];

        $select_threads = $db->fetchAll('
               SELECT thread_id, title, node_id, discussion_state
               FROM `xf_thread`
               WHERE discussion_state = 1 AND node_id = ' . $db->quote($forum_number) . '
               ORDER BY `thread_id` DESC
               LIMIT 50
            ');
Replaced by

PHP:
$feature_nodes = XenForo_Model::create('XenForo_Model_Forum')->getForums(array('display_in_list' => 1 ) );

$select_threads = XenForo_Model::create('XenForo_Model_Thread')->getThreads(array('node_id' => $instance['show_forum_number']   , 'discussion_state' => 1), array('limit' => 50));
 
Last edited:

truonglv

Formerly Nobita.Kun
#7
Wow ! Two lines replaced all of that wild stuff. Best of all, the threads are returned much faster (no actual benchmarks ) !

(y)

PHP:
        $feature_node_qry = '
                    SELECT node_id, title, display_in_list, node_type_id FROM `xf_node`
                    WHERE display_in_list = 1 AND node_type_id = "Forum"
                    ORDER BY `title` ASC
                    ';

        $feature_nodes = XenForo_Application::get( 'db' )->fetchAll( $feature_node_qry );

        $db = XenForo_Application::get('db');

        $forum_number = $instance['show_forum_number'];

        $select_threads = $db->fetchAll('
               SELECT thread_id, title, node_id, discussion_state
               FROM `xf_thread`
               WHERE discussion_state = 1 AND node_id = ' . $db->quote($forum_number) . '
               ORDER BY `thread_id` DESC
               LIMIT 50
            ');
Replaced by

PHP:
$feature_nodes = XenForo_Model::create('XenForo_Model_Forum')->getForums(array('display_in_list' => 1 ) );

$select_threads = XenForo_Model::create('XenForo_Model_Thread')->getThreads(array('node_id' => $instance['show_forum_number']   , 'discussion_state' => 1), array('limit' => 50));
`discussion_state` should be in range values: 'visible', 'moderated', 'deleted' not `1`
 

LPH

Well-known member
#10
Thank you Bob. Never do an ORDER BY using the index for enumeration sorting or there might be unexpected results. Got it.

I also see that the $fetchOptions were wrong for orderDirection. Here is the corrected code.

PHP:
$select_threads = XenForo_Model::create('XenForo_Model_Thread')->getThreads(array('node_id' => $instance['show_forum_number'], 'discussion_state' => 'visible'), array('limit' => 50, 'order' => 'thread_id', 'orderDirection' => 'desc') );
This seems to be working in the widget.

One line down ... thousands to go :D