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;

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

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:

SELECT COUNT(threadid) AS num FROM vbthread WHERE forumid IN (57, 58, 59..., 63) AND postuserid = $post['postuserid']

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.

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



Also you can just say this for your query:

SELECT COUNT(threadid) AS num FROM vbthread WHERE forumid IN (57, 58, 59..., 63) AND postuserid = $post['postuserid']


This produced the same SQL error as the above. :(


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

--------------- Added 1199918887 at 1199918887 ---------------

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;

$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 WHERE forumid IN (57, 58, ...63)

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?