Best method to retrieve node title when node_id is known

[LPH]

What is the best / efficient method to display the node title when the node_id is known?

I realize that may seem like a silly question but I'd rather not do an sql query and set the node_id. The question, then, is if there is a XenForo way to grab the node title based on the node_id. I didn't see anything in XenForo_Model_Node. Did I miss it?

 

[LPH]

This is the only way I know how to do this and realized my OP was a bit of a wet noodle ... sorry.

        $node_id = XenWord::getForumIdForPost( $post->ID );

        $category_qry = "
                    SELECT node_id, title FROM `xf_node`
                    WHERE node_id = $node_id
                    ";

        $nodes = XenForo_Application::get( 'db' )->fetchAll( $category_qry );

        foreach ( $nodes as $node) {

            echo esc_attr( $node['title'] );
        }

Anyway, does someone know of a more efficient method to get the node title by node id ?

 

[Chris D]

Remember there's various options for formatting the data fetched from queries. You're using fetchAll which assumes that you want all rows of the query result fetched into an array of rows. This is most appropriate when you know your query is going to return multiple rows, e.g. getting all threads in a forum is definitely likely to be more than a single row so fetchAll is most appropriate there.

However, you know for sure that you want to fetch, at most, a row of data, so instead of fetchAll, you should consider fetchRow. fetchRow will always return the first row it finds (even if there is more than one) in an array that represents that single row, e.g.

        $category_qry = "
                    SELECT node_id, title FROM `xf_node`
                    WHERE node_id = $node_id
                    ";

        $node = XenForo_Application::get( 'db' )->fetchRow( $category_qry );
        $nodeTitle = $node['title'];

As well as fetchRow, you might be able to do something else here. There is fetchOne. Fetch one will always return the first value, in the first column, in the first row in the result of the query. So, if you were to do:

$query = "
    SELECT title
    FROM xf_node
    WHERE node_id = ?
";

$nodeTitle = XenForo_Application::getDb()->fetchOne($query, $node_id);

Notice I've changed the query to only select "title" and I've changed it to use fetchOne. fetchOne will return simply the node title, nothing more, nothing less.

Please also take note of the use of prepared statements. It's best practice to do this with any "input" used in DB queries, especially if the input can be modified by a user. In the above code $node_id is actually passed into the fetchOne call where it is then made safe to use. Note that the second parameter in any of the fetch methods can also accept an array of parameters.

Finally another change I made is how to grab the database object. Using XenForo_Application::getDb() should allow your IDE to resolve that as the appropriate type, so it should suggest/auto complete the methods you can access by using that object.

 

[thedude]

Remember there's various options for formatting the data fetched from queries. You're using fetchAll which assumes that you want all rows of the query result fetched into an array of rows. This is most appropriate when you know your query is going to return multiple rows, e.g. getting all threads in a forum is definitely likely to be more than a single row so fetchAll is most appropriate there.

However, you know for sure that you want to fetch, at most, a row of data, so instead of fetchAll, you should consider fetchRow. fetchRow will always return the first row it finds (even if there is more than one) in an array that represents that single row, e.g.

        $category_qry = "
                    SELECT node_id, title FROM `xf_node`
                    WHERE node_id = $node_id
                    ";

        $node = XenForo_Application::get( 'db' )->fetchRow( $category_qry );
        $nodeTitle = $node['title'];

As well as fetchRow, you might be able to do something else here. There is fetchOne. Fetch one will always return the first value, in the first column, in the first row in the result of the query. So, if you were to do:

$query = "
    SELECT title
    FROM xf_node
    WHERE node_id = ?
";

$nodeTitle = XenForo_Application::getDb()->fetchOne($query, $node_id);

Notice I've changed the query to only select "title" and I've changed it to use fetchOne. fetchOne will return simply the node title, nothing more, nothing less.

Please also take note of the use of prepared statements. It's best practice to do this with any "input" used in DB queries, especially if the input can be modified by a user. In the above code $node_id is actually passed into the fetchOne call where it is then made safe to use. Note that the second parameter in any of the fetch methods can also accept an array of parameters.

Finally another change I made is how to grab the database object. Using XenForo_Application::getDb() should allow your IDE to resolve that as the appropriate type, so it should suggest/auto complete the methods you can access by using that object.

Other than answering op's question, that was a good writeup on selecting the most efficient db function for the query at hand.

 

[LPH]

Thank you @ChrisD

I opted to echo out the last statement which leads me to another question. In terms of efficiency, does it make a difference not to use a variable and echo the statement directly or go ahead and assign the variable $nodeTitle and echo $nodeTitle. The plan isn't to use $nodeTitle in any other location.

$node_id = XenWord::getForumIdForPost( $post->ID );

$query = "
    SELECT title
    FROM xf_node
    WHERE node_id = ?
";

echo XenForo_Application::getDb()->fetchOne($query, $node_id);

 

[Chris D]

For the difference it makes it doesn't matter

 

[Marcus]

You could also do this:

$node = XenForo_Model::create('XenForo_Model_Node')->getNodeById($nodeId);