Announcement

Collapse
No announcement yet.

How do I run a query to count number of threads by current user in forum x?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How do I run a query to count number of threads by current user in forum x?

    and dump the results in a variable, which i can then use to check against an option I've created.

    The count will be called during the submission of a new thread (by the hook newthread_start) if that makes any difference. (Ideally to restrict the amount of threads that a user has in any one forum)

    Many thanks in advance.

  • #2
    In the "thread" database all that comes to mind for you to compare would be forumid.
    So run a query that grabs the current forumid, matches it with the forumid in the database, then grabs the username, matches it with the "postusername" field in the database and returns all threads started by that user, then count them and return the results.

    Hypothetical:
    PHP Code:
    $forumid forum ID;
    $username $this->post['username'];
    $sql "SELECT COUNT(*) FROM `thread` WHERE `forumid` = `$forumid` AND `postusername` = `$username`";
    $result $sql
    My SQL knowledge is extremely limited. It would go (somewhat) along them lines. Above isn't valid syntax, purely because you'd need to define the forum ID (I think the $username is right) and then refine the SQL.

    Comment


    • #3
      Thanks James, that's roughly the logistics I have now.

      Now just to work out the exact syntax

      Comment


      • #4
        All sorted now.
        Many thanks

        Comment


        • #5
          mind sharing the final code? I was looking to do something similar to add to the profiles.
          Plan, Do, Check, Act!

          Comment


          • #6
            Oops, I missed this reply, I'm really sorry.

            Unfortunately, it was someone else using their own product who supplied the answer to me, as such it's his code and I don't have permission to redistribute it, as much as I would love to....
            If you like I can put you in touch with the chap that wrote it (He's registered on here).

            Comment


            • #7
              Could you please tell me how to do it also? I am using a paid poster service and need to know how much I have to pay them based on number of threads and posts. I am not an IT guy so I really need detailed instruction how to do it.
              Many thanks in advance.
              Last edited by vietnam-about; Tue 6th Apr '10, 1:28pm.
              Premier discussion and information about Vietnam at

              www.vietnam-about.com

              Comment


              • #8
                I add this code into function prepare_posts()

                $threads_temp = $this->registry->db->query_read_slave("
                SELECT COUNT(*) AS threadscount
                FROM " . TABLE_PREFIX . "thread AS thread
                WHERE thread.postuserid = " . $this->userinfo['userid'] . "
                ");
                $this->prepared['threads'] = vb_number_format($threadstemp['threadscount']);

                and this code into template member_block_aboutme

                <dl class="stats">
                <dt>Total Threads</dt>
                <dd>{vb:raw prepared.threads}</dd>
                </dl>

                but I always get 0 as number of threads started by a user.
                Can anybody help me?

                Thanks in advance.
                Last edited by vietnam-about; Tue 6th Apr '10, 1:28pm.
                Premier discussion and information about Vietnam at

                www.vietnam-about.com

                Comment


                • #9
                  Try query_first instead of query_read_slave

                  Please don't PM or VM me for support - I only help out in the threads.
                  vBulletin Manual & vBulletin 4.0 Code Documentation (API)
                  Want help modifying your vbulletin forum? Head on over to vbulletin.org
                  If I post CSS and you don't know where it goes, throw it into the additional.css template.

                  W3Schools &lt;- awesome site for html/css help

                  Comment


                  • #10
                    You need the underscore in the variable, $threadstemp to $threads_temp.

                    Comment


                    • #11
                      My bad. The version I posted here has error with underscore. However it was not the problem.
                      The problem is when I tried to display the $threads_temp alone, I have got the result Resource ID 30. It means it is just a pointer or address which points to the real result. I read again about mySQL and it is true. I have to use fetch_array to pull out the result.

                      Final version looks like this:

                      function prepare_posts()
                      {
                      $threads_resource = $this->registry->db->query_read_slave("
                      SELECT COUNT(*) AS threadscount
                      FROM " . TABLE_PREFIX . "thread AS thread
                      WHERE thread.postuserid = " . $this->userinfo['userid'] . "
                      ");
                      $threads_result = $this->registry->db->fetch_array($threads_resource);
                      $this->prepared['threads'] = $threads_result['threadscount'];

                      $this->prepared['posts'] = $this->userinfo['posts'] - $this->prepared['threads'];
                      }

                      And in template:
                      <dl class="stats">
                      <dt>Total Threads</dt>
                      <dd>{vb:raw prepared.threads}</dd>
                      </dl>
                      Last edited by vietnam-about; Tue 6th Apr '10, 1:29pm.
                      Premier discussion and information about Vietnam at

                      www.vietnam-about.com

                      Comment


                      • #12
                        I know maybe not 100% relevant to this thread, but if you're struggling with your syntax for your SQL queries, try adding this somewhere after your SQL query

                        Code:
                        echo mysql_error();
                        Its saved me a million times when I couldn't get my SQL syntax right. Rather than *nothing* being displayed, it should tell you where your error is

                        Cheers

                        Nick

                        Comment

                        widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                        Working...
                        X