sql query in callback + pass variable value

[ungovernable]

New to XenForo, still learning...

I'm trying to make an addon to query the database and output members postcount in specific forum

template message_user_info

<xen:callback class="anarchoi_postcount_test" method="getTest">{$user.username}</xen:callback>

file /library/anarchoi/postcount/test.php

class anarchoi_postcount_test {
   public static function getTest($content) {
   // return $content;
      $sql_query = "SELECT * FROM xf_thread WHERE username = '$content' AND node_id = '70'";
      $query = mysql_query($sql_query);
      $post_count = mysql_num_rows($query);
   }
}

Just missing the database connection now... How can i call XenForo functions to establish connection to the database with this code ?

Thanks !

 

[Robust]

class anarchoi_postcount_test {
   public static function getTest() {
$visitor = XenForo_Visitor::getInstance();

$db = XenForo_Application::getDb();
$postCount = $db->fetchOne('SELECT COUNT(*) FROM xf_thread WHERE user_id = ?', $visitor['user_id']);
   }
}

btw, writing this in XenForo so if I got a few methods spelt wrong or have some poor formatting... well yeah. You can fix it in your IDE I suppose

 

[ungovernable]

I want to get the poster's postcount from a specific forum, not the current visitor postcount
Basically, i'm trying to add another postcount just next to the default XenForo postcount under the avatars when viewing a thread.
That's why i'm passing the poster username in template message_user_info

So using your code i modified mine like this:

class anarchoi_postcount_test {
   public static function getTest($content) {
       $db = XenForo_Application::getDb();
       $postCount = $db->fetchOne('SELECT COUNT(*) FROM xf_thread WHERE node_id = "70" AND username = "$content"');
      return $postCount;
   }
}

$content is the poster username passed in the template

However, the code is always returning zero

 

[Robust]

Zend_Debug::dump($content);

What does that dump onto the page? Add it right at the start of the function.

 

[wang]

That query counts all threads in a particular node. It does not pull poster's postcount information from a specific node.

 

[ungovernable]

That query counts all threads in a particular node. It does not pull poster's postcount information from a specific node.

It counts all threads posted by a specific username in a particular node

 

[Robust]

That query counts all threads in a particular node. It does not pull poster's postcount information from a specific node.

Woops, yeah, that's thread count. I just followed the same table. Nice spot.

Changing xf_thread to xf_post should do it, add in a join into the xf_thread table to check the node_id.

Something like:
SELECT COUNT(xf_post.*) FROM xf_post LEFT JOIN xf_thread ON (xf_thread.thread_id = xf_post.thread_id) WHERE [...]

It counts all threads posted by a specific username in a particular node

Yeah, not sure if you wanted thread count or post count. You mentioned post count in a node, but the query example you gave was looking for thread count.

 

[wang]

It counts all threads posted by a specific username in a particular node

Right, but do you want to output users threads or post counts for a specific node? Because in your previous posts you are always talking about the post counts and not threads.

 

[ungovernable]

Sorry my bad, i meant threads count started in a specific node_id

 

[Chris D]

The query in your last post is literally querying for the username "$content" rather than whatever the value of $content is. The query is in single quotes so it is presumed to be a literal string.

You want something like this (but it's still wrong):

$postCount = $db->fetchOne('SELECT COUNT(*) FROM xf_thread WHERE node_id = 70 AND username = ' . $content);

Why's it still wrong? Well, you should never allow what is essentially user generated input to be used directly in a query like that. If someone registered with the username something like:

1; TRUNCATE xf_user;

Well, you'd lose your entire xf_user table.

Always, pretty much just as a rule with very few exceptions, use prepared statements.

So the correct way would be:

$postCount = $db->fetchOne('SELECT COUNT(*) FROM xf_thread WHERE node_id = 70 AND username = ?', $content);

The ? are placeholders for input into the query, which are then bound in order to the value or values in the second argument of the query/fetch methods, and prepared so they are safe and cannot be used to to inject malicious queries into your database.

As an example if you had multiple values to bind to the query, you can use an array:

$postCount = $db->fetchOne('SELECT COUNT(*) FROM xf_thread WHERE node_id = ? AND username = ?', array(70, $content));

 

[ungovernable]

Awesome ! Thanks for the explanations and very complete reply. It is now working as expected !
Was planning to run $content into mysql_real_escape_string() but your method seems easier.

Now that i understand the base i'll start working on more addons