Where to include a new variable?

[Defkalion]

I have built an SQL query that returns the count of a user's topics in certain forums. I want to use this as a variable, so I can use it in my postbit templates.

The problem is I don't know how to do that.

The code I THINK I should use to define the variable with this SQL query is this;

$stories = $db->query_first("
SELECT
Count(vbthread.threadid)
FROM vbthread
WHERE
(vbthread.forumid = '57' OR
vbthread.forumid = '58' OR
vbthread.forumid = '59' OR
vbthread.forumid = '60' OR
vbthread.forumid = '61' OR
vbthread.forumid = '62' OR
vbthread.forumid = '63' ) AND
vbthread.postuserid = '$post[userid]'
");

1. Is this correct?
2. Where should I use this code to define the variable and then be able to use the $stories variable inside my postbit templates?

[Defkalion]

Can anyone please help on this ?

[Defkalion]

Ok I'm coming back on this, since I solved it - but partially;

I have created a plugin with this code;

PHP Code:

$stories = $this->registry->db->query_first("
                    SELECT count(threadid) AS num
                    FROM vbthread AS thread
                    WHERE 
                       ( forumid = 57 or
                        forumid = 58 or
                        forumid = 59 or
                        forumid = 60 or
                        forumid = 61 or
                        forumid = 62 or
                        forumid = 63 ) and
                        postuserid = " . $post['userid'] . "
"); 


And then I use the variable $stories[num] inside my postbit template to display the result. This part is working fine.

But now I cannot use my external RSS forum feeds, I get a database error, saying that:

PHP Code:

Database error in vBulletin 3.6.8:

Invalid SQL:

                    SELECT count(threadid) AS num
                    FROM vbthread AS thread
                    WHERE
                       ( forumid = 57 or
                        forumid = 58 or
                        forumid = 59 or
                        forumid = 60 or
                        forumid = 61 or
                        forumid = 62 or
                        forumid = 63 ) and
                        postuserid =;

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 '' at line 11
Error Number : 1064
Date         : Wednesday, January 9th 2008 @ 12:29:11 PM
Script       : http://www.MYDOMAIN.COM/external.php??type=rss2&forumid=57
Referrer     :
IP Address   : xxxx
Username     : not registered user
Classname    : vb_database 


I think this is because of the HOOK location I've put my plugin in. I put it in postbit_display_start. Is this correct? Should I choose another hook location?

[Opserty]

Have you tried $post['postuserid'] instead of just $post['userid'] maybe that will correct it?

Also you can just say this for your query:
[sql]
SELECT COUNT(threadid) AS num FROM vbthread WHERE forumid IN (57, 58, 59..., 63) AND postuserid = $post['postuserid'][/sql]

Finally remember you are running a query on every postbit on every showthread so you may want to rethink what you are doing. You may be able to modify the showthread query using the showthread_query hook and add a join/count to the query.

[ZomgStuff]

postuserid =;

?



.

[Defkalion]

Thankyou so much for your reply.

Originally Posted by Opserty

Have you tried $post['postuserid'] instead of just $post['userid'] maybe that will correct it?

I tried but this produced an SQL error whenever I tried to view any topic.

Originally Posted by Opserty

Also you can just say this for your query:
[sql]
SELECT COUNT(threadid) AS num FROM vbthread WHERE forumid IN (57, 58, 59..., 63) AND postuserid = $post['postuserid'][/sql]

This produced the same SQL error as the above.

Originally Posted by Opserty

Finally remember you are running a query on every postbit on every showthread so you may want to rethink what you are doing. You may be able to modify the showthread query using the showthread_query hook and add a join/count to the query.

I know and you are correct, but I do not know how to make this work in any other way. Thankyou for your suggestion but I cannot implement this without some help. You mean I should use the same query but insert it at the showthread_query hook? What do you mean add a join/count to the query?

Anyway my website is not that big so I don't have performance issues with this hack, so even if I could correct this problem with my RSS feeds I would keep it in the postbit.

Any other ideas?

Thankyou for your time.
Regards.

--------------- Added [DATE]1199918887[/DATE] at [TIME]1199918887[/TIME] ---------------

Ok someone else helped me, the solution is here:

http://www.vbnova.com/showthread.php?p=7259#post7259

Thanks for taking the time to read this.

[Defkalion]

Ok I'm re-surfacing this thread because I need some help.

I have created a plugin in the Postbit_display_start hook with this code;

PHP Code:

$stories = $this->registry->db->query_first("
                    SELECT count(threadid) AS num
                    FROM vbthread AS thread
                    WHERE 
                       ( forumid = 57 or
                        forumid = 58 or
                        forumid = 59 or
                        forumid = 60 or
                        forumid = 61 or
                        forumid = 62 or
                        forumid = 63 ) and
                        postuserid = " . $post['userid'] . "
"); 


And then I use the variable $stories[num] inside my postbit template to display the result (the result is all the threads a user has started in specific forums). This part is working fine (I only have a small forum so no delay problems)

The thing is, I want to display the same information in the MEMBERINFO page, anyone know how to do that? I suppose I have to add the variable to another hook as well?

[GameWizard]

I believe the hook for the member profiles would be member_complete

[Opserty]

By the way, you can shorten your SQL command by using the [minicode]WHERE forumid IN (57, 58, ...63)[/minicode]

Even though the other bit in my previous post may not have worked, the SQL I posted on the line above will.

[Defkalion]

Thanks fellows for your help.

GameWizard, I tried copying the plugin and using it to the member_complete hook and didn't work. I also tried using the member_start hook, but again no go.

I think I also have to change the last part of the code where it says

postuserid = " . $post['userid'] . "

because that one was used in the postbit, so shouldn't I change that to something like

postuserid = " . $userinfo['userid'] . "

so that it works in member profile page, or am I doing silly things?