Count users threads from specific forum ID's

[bartek24m]

I need insert in member profile page (memberinfo_block_statistics)
MYSQL query (count threads from specific forums ID)

https://vborg.vbsupport.ru/external/2014/06/39.png

What query i should use in hook area ?
I search forum with similar problem but i only found this::

SELECT t.forumid = 1 AND t.forumid = 2 AND t.forumid = 3
FROM thread AS t
WHERE t.postuserid = X
Is it correct? What should be a right location for this hook?
What should i repleace X with to get stats for every single user ?


edit: i found also something like this:

if (is_array($vbulletin->forumcache))
{
foreach ($vbulletin->forumcache AS $forum)
{
$totalthreads += $forum['threadcount'];
if ($forum['forumid'] == xx) $forumthreadcount = $forum['threadcount'];
}
}

i need copy it somewhere in member.php and place $forumthreadcount in memberinfo_block_statistics template
It does not work

--------------- Added 1263885685 at 1263885685 ---------------

anyone ?

[bartek24m]

bump !
please reply

[kh99]

I think something like this works for the query:

SELECT COUNT(*) AS count FROM thread WHERE postuserid = id AND forumid IN(1, 2)

So I guess you'd want something like:

$count = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM ".TABLE_PREFIX."thread
WHERE postuserid = $vbulletin->userinfo[userid] AND
forumid IN(1, 2, 3)");

And the result would be in $count['count'].

Where to put it is another question, it looks like the HTML for those blocks is generated in member.php around line 474, and there's a member_build_blocks_start hook right before that, so maybe that's a good place.

[bartek24m]

Thank you for reply mate !

i think that someting else is wrong in $vbulletin->userinfo[userid]
after create new plugin in member profile i can see this error

Database error in vBulletin 3.7.4:

Invalid SQL:
SELECT COUNT(*) AS count FROM thread WHERE postuserid = Array[userid] AND forumid IN(1, 2, 3);

MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[userid] AND forumid IN(1, 2, 3)' at line 2
Error Number : 1064

[kh99]

Oh yeah, sorry, try

$count = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM ".TABLE_PREFIX."thread
WHERE postuserid = ".
$vbulletin->userinfo['userid']."
AND forumid IN(1, 2, 3)");

[bartek24m]

yeah that's better !

now when i try to add in template MEMBERINFO

$count['count']

i can't save template i see this error



The following error occurred when attempting to evaluate this template:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/sportforum/domains/sportforum.pl/public_html/includes/adminfunctions_template.php(3772) : eval()'d code on line 8

This is likely caused by a malformed conditional statement. It is highly recommended that you fix this error before continuing, but you may continue as-is if you wish.

maybe i should use $count[count] insted of $count['count'] ?

or maybe in hook area
insed of:

$count = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM ".TABLE_PREFIX."thread
WHERE postuserid = ".
$vbulletin->userinfo['userid']."
AND forumid IN(164, 165, 166)");


use

ob_start();

$count = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM ".TABLE_PREFIX."thread
WHERE postuserid = ".
$vbulletin->userinfo['userid']."
AND forumid IN(164, 165, 166)");


$count = ob_get_contents();
ob_end_clean();
and then in template paste $count

[kh99]

Sorry, obviously I didn't try any of this and to be honest I keep forgetting what works in templates. Try $count[count], it should be the same (you might also want to use some variable name other than 'count', it's kind of a common name). There's probably some "right" way to do this without creating a new variable.

I don't think you need to do anything like using ob_start/end. You may need to be using a global, however (I already forgot what the code looked like). I think this may be what template hooks are for, so you don't have to worry about variable name conflicts and globals. Maybe someone who knows more will enlighten us :)

ETA: Meanwhile, maybe try something like:

$vbulletin->userinfo['memberinfo_threadcount'] = $count['count'];

in the plugin, then use

$bbuserinfo[memberinfo_threadcount]

in the template.

[bartek24m]

i think we're so close but again something is wrong it show always 0

in nut shell what i've done:
name: samle_test
location: member_build_blocks_start
code:
$count = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM ".TABLE_PREFIX."thread
WHERE postuserid = ".
$vbulletin->userinfo['userid']."
AND forumid IN(164, 165, 166)");
$vbulletin->userinfo['memberinfo_threadcount'] = $count['count'];

in template: memberinfo_block_statistics

<li><span class="shade">Płatne Artykuły:</span> $bbuserinfo[memberinfo_threadcount]</li>



And in member profile i can see

Płatne Artykuły: 0

It shoud be 386 not 0

DEMO: http://www.sportforum.pl/members/marcin-hejnowicz.html (Go To Statistic)

[kh99]

Well, I guess that means the $bbuserinfo didn't work like I'd guessed. Try this instead: use $template_hook[profile_stats_threadcount] in both places instead of $vbulletin->userinfo['memberinfo_threadcount'] and $bbuserinfo.. etc. And if that doesn't work, add a

global $template_hook;

line before you set it in the plugin.

[bartek24m]

I dont understand what's:

add a global $template_hook;
line before you set it in the plugin.

but if you mean, it should look like:

Plugin:


$count = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM ".TABLE_PREFIX."thread
WHERE postuserid = ".
$vbulletin->userinfo['userid']."
AND forumid IN(164, 165, 166)");
$template_hook;
$template_hook[profile_stats_threadcount] = $count['count'];


template:
<li><span class="shade">Płatne Artykuły:</span> $template_hook[profile_stats_threadcount]</li>

It still doesn't work :(

[kh99]

I meant it should look like:


$count = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM ".TABLE_PREFIX."thread
WHERE postuserid = ".
$vbulletin->userinfo['userid']."
AND forumid IN(164, 165, 166)");
global $template_hook;
$template_hook[profile_stats_threadcount] = $count['count'];

If that doesn't work I give up, someone else will have to help. :)

[bartek24m]

unfortunately it doesn't work :(
ok, thank you for you time.
i hope that somene else answer.

[kh99]

OK, I hate to give up, so I actually went to the test site and tried this (which I should have done from the start and saved us both time).

I made this plugin and used the hook member_profileblock_fetch_unwrapped


if ($this->template_name == 'memberinfo_block_statistics')
{
global $vbulletin;

$count = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM ".TABLE_PREFIX."thread
WHERE postuserid = ".
$vbulletin->userinfo['userid']."
AND forumid IN(164, 165, 166)");
}


Then in the template I just used $count[count]. This avoids the global variable problem because the hook is in the same place as the template eval.

Sorry I wasted your time before, this time I *really* quit :)

[bartek24m]

You are great man ! ;)
But i have still problem ...
It works perfect when I set specific number of postuserid like:

if ($this->template_name == 'memberinfo_block_statistics')
{
global $vbulletin;

$count = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM thread
WHERE postuserid = 1491
AND forumid IN(164, 165, 166)");
}

and it doesn't when i use: ".$vbulletin->userinfo['userid'].":

if ($this->template_name == 'memberinfo_block_statistics')
{
global $vbulletin;

$count = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM thread
WHERE postuserid = ".$vbulletin->userinfo['userid']."
AND forumid IN(164, 165, 166)");
}

instead of the number threads, the result of counting is always 0 :(

is there any another way to replace

".$vbulletin->userinfo['userid']."

in 3.7.4 version ?

[kh99]

Oh, I wish it were true, but in fact that problem is probably why it didn't work before. I should have thought of making sure that it was right.

Anyway, here we go again :): I found this in class_profileblock.php (the same file where the hook is called):


$requirements = $this->registry->db->query_read_slave("
SELECT blockid, requirement
FROM " . TABLE_PREFIX . "profileblockprivacy
WHERE userid = " . intval($this->profile->userinfo['userid']) . "
");

So maybe you can use intval($this->profile->userinfo['userid']).

[bartek24m]

YEAH ! it works !
Exactly what i want !

Thank you again mate without your help i cant solve the problem!

Give via PM you paypal account.

[blind-eddie]

I know this is an old thread Kevin but, I have edited this and edited this to get this to work on postbit_legacy with no luck.

I tried to get it to show (link) all threads by user in postbit_legacy but, only in select forum.

Any chance you could have a look at your code again and help me out?
I would really appreciate it.

Though, I have done a great deal of research before posting this request, I was not able to locate anything like this, so, if you or anyone that reads this know of an edit or addon that does what I am seeking please let me know...

Thank you for your time.... Tim

[kh99]

Wow, that's an old one. But I'm not sure what you're asking. The code above was only showing how many threads a user has in a certain set of forums. It sounds like you want links or something?

[blind-eddie]

I am looking to edit this to show how many threads a user has in a certain set of forums, but not in a profile tab, I want it to show on the postbit_legacy, below total post.
I want to allow members to be able to click the link similar to what is show below, but in the postbit_legacy template, not in a profile tab.
I am only going to show the link in the same forum that I want it to show all the threads a user made in those forums.
I think I said that right....


https://vborg.vbsupport.ru/external/2014/06/39.png

[kh99]

OK, I guess you could do something like this, at hook postbit_display_complete:
$forumids=array(1, 2, 3);
$show['threadcount'] = in_array($post['forumid'], $forumids);
if ($show['threadcount'])
{
$threadcount = $vbulletin->db->query_first("SELECT COUNT(*) AS count
FROM ".TABLE_PREFIX."thread
WHERE postuserid = ". $post['userid']."
AND forumid IN (" . implode(',', $forumids) . ");
if ($threadcount === FALSE)
$show['threadcount'] = false;
else
$threadcount = $threadcount['count'];
}
and then in the postbit_legacy template, something like:
<if condition="$show['threadcount']">
Threads: $threadcount<br />
</if>

I see you said that you want it to be a link, but I don't know what you want it to link to. But you can probably change the html above to be a link.

I should also point out that adds a query for each postbit displayed. If you're concerned about that, you could add a column to the user table and adjust the count whenever a thread is created or deleted, but of course that takes more work and probably a few more plugins. You could also keep and array for users that you've already done the query for, so if for instance the same user posts 5 times on a page, you'd only be doing it once.

[blind-eddie]

Perfect, Thank you.

I did read this thread wrong, I thought this was a clickable link to find all threads started by user in specific forum, similar to "Find all post made by" in the postbit drop down.
But, I will test your code for now.

Thank you for your time Kevin.

[kh99]

OK, but I'm thinking the link you want wouldn't be that difficult. If you figure out the right search link you'd just have to change the template code to <a href=... and you'd be there. I'm pretty sure it can be done but I can't figure out the url right now.