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

sql query in callback + pass variable value

Discussion in 'XenForo Development Discussions' started by ungovernable, Feb 23, 2016.

  1. ungovernable

    ungovernable Member

    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
    Code:
    <xen:callback class="anarchoi_postcount_test" method="getTest">{$user.username}</xen:callback>
    file /library/anarchoi/postcount/test.php
    Code:
    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 !
     
  2. Robust

    Robust Well-Known Member

    Code:
    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 :p
     
  3. ungovernable

    ungovernable Member

    I want to get the poster's postcount from a specific forum, not the current visitor postcount :p
    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:
    Code:
    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
     
  4. Robust

    Robust Well-Known Member

    Zend_Debug::dump($content);

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

    wang Well-Known Member

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

    ungovernable Member

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

    Robust Well-Known Member

    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 [...]

    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 likes this.
  8. wang

    wang Well-Known Member

    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.
     
  9. ungovernable

    ungovernable Member

    Sorry my bad, i meant threads count started in a specific node_id
     
  10. Chris D

    Chris D XenForo Developer Staff Member

    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):
    PHP:
    $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:
    Code:
    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:
    PHP:
    $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:
    PHP:
    $postCount $db->fetchOne('SELECT COUNT(*) FROM xf_thread WHERE node_id = ? AND username = ?', array(70$content));
     
    Brad L, ungovernable and Brogan like this.
  11. ungovernable

    ungovernable Member

    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 :D
     

Share This Page