vb.org Archive - Count users threads from specific forum ID's

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Count users threads from specific forum ID's (https://vborg.vbsupport.ru/showthread.php?t=233402)

bartek24m

01-18-2010 07:02 AM

Count users threads from specific forum ID's

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

Code:

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:

PHP Code:


		
			
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 [DATE]1263885685[/DATE] at [TIME]1263885685[/TIME] ---------------

anyone ?

bartek24m

02-11-2010 10:38 AM

bump !
please reply

kh99	02-11-2010 12:57 PM

I think something like this works for the query:

Code:

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

So I guess you'd want something like:

Code:

$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

02-11-2010 01:49 PM

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

Code:

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	02-11-2010 01:55 PM

Oh yeah, sorry, try

Code:

$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

02-11-2010 02:38 PM

yeah that's better !

now when i try to add in template MEMBERINFO

Code:

$count['count']

i can't save template i see this error

Code:

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:

Quote:

$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

Quote:

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	02-11-2010 02:53 PM

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:

Code:

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

in the plugin, then use

Code:

$bbuserinfo[memberinfo_threadcount]

in the template.

bartek24m

02-11-2010 06:29 PM

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:

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

Code:

<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	02-11-2010 06:48 PM

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

02-11-2010 07:14 PM

I dont understand what's:

Quote:

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

but if you mean, it should look like:

Plugin:

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)");

$template_hook; 

$template_hook[profile_stats_threadcount] = $count['count'];

template:

Code:

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

It still doesn't work :(

kh99	02-11-2010 07:19 PM

I meant it should look like:

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)");

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

02-11-2010 07:31 PM

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

kh99	02-11-2010 07:48 PM

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

Code:

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

02-11-2010 08:31 PM

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

Code:

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'].":

Code:

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

Quote:

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

in 3.7.4 version ?

kh99	02-11-2010 09:03 PM

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

Code:

$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

02-11-2010 09:40 PM

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

06-20-2014 01:00 AM

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	06-20-2014 07:53 AM

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

06-20-2014 10:01 AM

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	06-20-2014 10:24 AM

OK, I guess you could do something like this, at hook postbit_display_complete:

Code:

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

Code:

<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

06-20-2014 10:45 AM

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	06-20-2014 10:52 AM

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.

All times are GMT. The time now is 07:24 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.02515 seconds Memory Usage 1,815KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (20)bbcode_code_printable (1)bbcode_php_printable (4)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (22)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: