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

LPH

Well-known member
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?
 
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
 
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.
 
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));
 
  • Like
Reactions: LPH
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)
 
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:
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`
 

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
 
Top Bottom