How do I bring information from a separate database

[kwblue]

Hello,

I see that certain plugins (vb_sponsor and others) use the forums database so that they can display information for each forum. To do that, they seem to create new columns in an already large forum datatable.

I already have a database that has information needed for each forum. It has the forumid tied to it so that I could quickly join tables. However - I don't understand how to get that information into my template.

What is the best way to do this? Do I need to take all that information and create new fields in the forum datatable? Or can I somehow write a plugin or something that would allow me to pull the information I want from the already defined datatable?

I really want to do this the best way and work properly with the vBulletin system, but am not quite sure how to proceed.

Thanks in advance.

[Dismounted]

Depending on where you want the information, you can create a plugin at a hook just before a query's execution (most queries have this hook). From there, you can add a join into the query, using the "database.table" way of referencing tables. This will work as long as the vBulletin MySQL user has access to the other database.

[kwblue]

Thanks for the answer! That sounds similar to what I was thinking. The table is in the same database, so I have no problems with security access to it.

Now - Since each forumid has information in my new datatable, what is the best way to perform the query? Do I pass a forumid in each time, somehow? Or is there some way I should structure it so that I can immediately pull out the right information.

I'm guessing some sort of array with the forumid as the index?

$MyInformation(forumid)?

That may not be proper php syntax, I'd have to look it up.


so - are arrays the best way to do this or parameter passing? If parameter passing, then how would I set that up as a plugin?

Thanks sooooo much.

--------------- Added 1229559777 at 1229559777 ---------------

ok, been trying to accomplish this, but having a difficult go at it. Here is pretty much what I am trying to do:

1. I created a Plugin:

$FDetails=$vbulletin->db->query_read("SELECT
`ForumDetails`.`SomeDetail`,
FROM
`ForumDetails` where `ForumDetails`.`forumid` = ".$forum[forumid]);

while ($FDetails_info= $vbulletin->db->fetch_array($FDetails))
{
$FDetails_info['MyDetails'] = $FDetails_info['SomeDetail'];
}


2. I set up the hook to be the: forumdata_start

3. In my template (forumhome_forumbit_level2_post) I put the following as a test:

<!--Testing -->
<if condition="$FDetails_info['MyDetails'] == 1">
&nbsp;FOUND!
</if>



I was hoping that when it got to the forum that I marked 'SomeDetail = 1' in the database, that it would put the text 'FOUND!' in the area of the template I wanted.

That didn't happen :) I got no errors, but I am assuming that I am doing it wrong. Either the wrong hook (I hope it's that simple) or I am just way off base with what I am trying to do.

Any help would be appreciated.

thanks!

--------------- Added 1229559977 at 1229559977 ---------------

BTW - That template shows the forums on the homepage. :) Just an FYI incase it is a custom template (I don't know all about the templates myself)

[Dismounted]

forumdata_start is inside the forum data manager - this is used to edit or create a forum, not fetch data. What you need to do is to find the query where the forum data is being fetched, and alter it.

[kwblue]

ooooh. :( Doesn't that mean that every time I upgraded after that, I would 'break' the code and have to re-do it?

[Dismounted]

Altering does not necessarily mean "edit the file directly". Say query A has a variable in it, let's call it $hooks_query_join (very big clue! ;)), and there's a hook right above it. You plug into that hook and change the variable, thereby altering it.

[kwblue]

I know what you are saying here :) So - that is a positive.

Now - I am trying to find where the query is and just can't find the one I want. I'll find it eventually, I think.. Eventually.

[Dismounted]

If you are looking at the forum home page, a good place to start would be index.php.

[kwblue]

Been looking in that one a lot along with the functions_forumlist. I must be too dense to find what I am looking for.

The construct_forum_bit looks like it builds the list and shells it out to the correct template (as far as where I know the html is parsed). However, there are no queries in there to note and I am getting a bit lost looking through it. Maybe just more time trying to understand each line of code will help.

The other area was the 'cache_ordered_forums', but that has no join (except the already filled tachyjoin), so if that was where I needed to spend my time I guess I would have to add my own joins in there too along with my own table fields.

Do you wanna be paid to do a quick example for me of what I want to do? :)

[kwblue]

So - I was able to do this for the homepage :) Thank you for that.

I did use the cache_ordered_forums and I hope that was the correct query. I did have to add my own $hook_query_fields and $hook_query_joins to the functions.php file.

Is it better for me to create this as a 'product' or something and would that ensure this update doesn't get written over once a new update comes out? What is the proper procedure for that?

Did I do this correctly? It wasn't difficult, really, just took some figuring out.

[Dismounted]

If it works, it works! :p I do not have a copy of vBulletin in front of me right now, so I am not sure if it is the best way. However, there is no need for a product as you have edited the files directly - products deal with plugins, phrases and templates.

[kwblue]

If it works, it works! :p I do not have a copy of vBulletin in front of me right now, so I am not sure if it is the best way. However, there is no need for a product as you have edited the files directly - products deal with plugins, phrases and templates.

ok, that makes sense. It does work, but I noticed yesterday that the forum info seems to have disappeared.. .so always says 'Last Post' = Never, 'Threads' = 0, 'Posts' = 0. :( Not sure why that would have happened.

[Dismounted]

Something must have gone wrong (corrupted cache, maybe). Remove your custom code, and rebuild the cache (Admin CP > Maintenance > Update Counters).

[kwblue]

I tried to update the counters without removing my code and it didn't seem to help. The query (when I copy it to a query analyzer) runs fine and brings back all the right information as far as lastpostid and all other forum db info.

When I disable my 'plugin code' it works fine... so something has to be happening with my query addition. I am just not sure why yet.

[Dismounted]

Post your snippet of code so I can take a look.

[kwblue]

This is the functions.php file code that I added. Quite simple, really, and I highlighted the additions so you can see them quickly:


$hook_query_fields = $hook_query_joins = $hook_query_where = '';


($hook = vBulletinHook::fetch_hook('cache_ordered_forums')) ? eval($hook) : false;

// get subscribed forums too
if ($userid)
{
$query = "
SELECT subscribeforumid, $counter_select $hook_query_fields
". iif($vbulletin->options['threadmarking'], ', forumread.readtime AS forumread') . "
FROM " . TABLE_PREFIX . "forum AS forum
LEFT JOIN " . TABLE_PREFIX . "subscribeforum AS subscribeforum ON (subscribeforum.forumid = forum.forumid AND subscribeforum.userid = $userid)
" . iif($vbulletin->options['threadmarking'], " LEFT JOIN " . TABLE_PREFIX . "forumread AS forumread ON (forumread.forumid = forum.forumid AND forumread.userid = $userid)") . "
$tachyjoin
$hook_query_joins
";
}
// just get counters
else
{
$query = "
SELECT $counter_select $hook_query_fields
". iif($vbulletin->options['threadmarking'] AND $vbulletin->userinfo['userid'], ', forumread.readtime AS forumread') . "
FROM " . TABLE_PREFIX . "forum AS forum
" . iif($vbulletin->options['threadmarking'] AND $vbulletin->userinfo['userid'], " LEFT JOIN " . TABLE_PREFIX . "forumread AS forumread ON (forumread.forumid = forum.forumid AND forumread.userid = " . $vbulletin->userinfo['userid'] . ")") . "
$tachyjoin
$hook_query_joins
";
}
}


It's that simple in the functions file. My plugin is pretty simple too:


$hook_query_fields = ",`ForumDetails`.`Details_UID`,
`ForumDetails`.`Clean_Name`,
`ForumDetails`.`forumid`,
`ForumDetails`.`Information1`,
`ForumDetails`.`Information2`,
`ForumDetails`.`Information3`";

$hook_query_joins = "LEFT JOIN " . TABLE_PREFIX . "ForumDetails ON (ForumDetails.forumid = forum.forumid)";

That's pretty much it. The plugin is tied to: cache_ordered_forums and does allow me to show the 'ForumDetails'.

However - all the stats seem to disappear. They are there and when the query is joined I can see all the same information as I do without the plugin. I ran both queries in an analyzer and the same data is returned in both queries :(

[kwblue]

I figured it out. It was a query issue. I had 2 forumid's in there and the forumcache script got confused.

Thanks for all your help.

[kwblue]

I thought since this thread is mine... and the next step is pretty similar... I would just continue the post here :)

Anyway - Now that my homepage is up to date with the proper information.. I want each FORUM to have specific information as well.

I can't, for the life of me, find where to add these same query joins there. Any pointers?

[kwblue]

bumpity bump bump :)