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

[mbooth]

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.

[James Birkett]

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:

$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.

[mbooth]

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

Now just to work out the exact syntax

[mbooth]

All sorted now.
Many thanks

[Reeve of Shinra]

mind sharing the final code? I was looking to do something similar to add to the profiles.

[mbooth]

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).

[vietnam-about]

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.

[vietnam-about]

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.

[Lynne]

Try query_first instead of query_read_slave

[Brandon_R]

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

[vietnam-about]

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>

[Grimwolge]

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

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