Problem with If-Else If-Else Conditional in Plugin

[wottech]

I am having some issues with an If-Else If-Else that I have in a plugin for the postbit. The real kicker is it works on my own personal site, but it doesn't work on at least 3 other sites, running practically the exact same setup as me. From what I can tell, the conditional goes directly to the ELSE, but I am almost positive that the value being evaluated in the statement is being passed correctly. Here is the plugin code:

if(THIS_SCRIPT == 'showpost' OR THIS_SCRIPT == 'showthread')
{
switch ($vbulletin->options['postbittype'])
{
case 1: $pbq = "ORDER BY `main_vehicle` DESC LIMIT 1"; break;
case 2: $pbq = "ORDER BY `main_vehicle` DESC LIMIT 1"; break;
case 3: $pbq = "ORDER BY `vehicle_id` DESC LIMIT 10"; break;
}
$sql = $this->registry->db->query("select *, " . TABLE_PREFIX . "garage_user_vehicle.user_id AS PostUser
from " . TABLE_PREFIX . "garage_user_vehicle
LEFT JOIN " .TABLE_PREFIX . "garage_makes
on (" .TABLE_PREFIX . "garage_user_vehicle.make_id=" .TABLE_PREFIX . "garage_makes.id)
LEFT JOIN " .TABLE_PREFIX . "garage_models
on (" .TABLE_PREFIX . "garage_user_vehicle.model_id=" .TABLE_PREFIX . "garage_models.id)
WHERE " . TABLE_PREFIX . "garage_user_vehicle.user_id='" . $post['userid'] ."' " . $pbq);

if($this->registry->db->num_rows($sql) > 0)
{
if($vbulletin->options['postbittype'] == 3)
{
while ($entry = $this->registry->db->fetch_array($sql))
{
$newyr = substr($entry[made_year], -2);
$template_hook['postbit_userinfo_right_after_posts'] .= "<div><a href=garage_vehicle.php?do=view_vehicle&id=$entry[vehicle_id]>'$newyr $entry[make] $entry[model]</a></div>";
}
} else if($vbulletin->options['postbittype'] == 2) {
$entry = $this->registry->db->fetch_array($sql);
$newyr = substr($entry[made_year], -2);
$template_hook['postbit_userinfo_right_after_posts'] .= "<div><img src='images/garage/car.png'><a href=garage.php?do=user_garage_view&id=$entry[PostUser]> $post[username]'s Garage</a></div>";
} else {
$entry = $this->registry->db->fetch_array($sql);
$newyr = substr($entry[made_year], -2);
$template_hook['postbit_userinfo_right_after_posts'] .= "<div><a href=garage.php?do=user_garage_view&id=$entry[PostUser]>'$newyr $entry[make] $entry[model]</a></div>";
}
}
}

Any help would be appreciated. It wouldn't be so bad if it didn't work properly on my own site... :confused:

[Dismounted]

Turn on debug mode and use DEVDEBUG() to find which condition you go to.

(If I'm reading right, you're potentially running an SQL query on every post being displayed - that is not good.)

[wottech]

I have tried to join the showthread query, but have never figured out how to get it to work. If you could give me some hints on that as well, that would be great.

I'll try the debugging idea, although I know which condition it does to when it doesn't work. Will the debugging also tell me what value is being passed by the "vbulletin->options[postbittype]" variable?

Thanks for help!

[wottech]

I have narrowed it down to the fact that in some cases the vboption is being passed to the plugin, and in some cases it is not. Now I just need to figure out why it isn't being passed in these cases.

[Biker_GA]

Whoa.... You're hammering your db pretty hard with that. As Dismounted already stated, that's not a good thing.

[wottech]

All these great comments and no solutions. If I could find a decent thread on how to use the showthread_query hook, perhaps I could write it a different way. From what I can from the limited threads I found on that hook, there isn't a way to hook the WHERE and ORDER statements of the query.

Or, if someone could offer a better idea, I am more than willing to recode the hack. But so far, people can only tell me the code sucks and not offer any assistance.

[Lynne]

You can hook into the WHERE and ORDER by adding to the $postids and $postorder variables using that hook.

[wottech]

Thanks Lynne. I will look into that and see what I can come up with. Like I said, there are only a limited number of threads (that I can find in numerous searches) that discuss the showthread_query hook, and it is usually just a single post that doesn't go into very good discussion on how exactly the hook works. I have tried numerous times to use the hook, and have never been successful with it.

[Lynne]

Well, this is an example from a showthread_query plugin I have on my site. I don't add to the two variables I mentioned though.
$hook_query_fields = ",users.downloaded as downloaded, users.uploaded as uploaded";
$hook_query_joins = "LEFT JOIN " . TABLE_PREFIX . "users as users ON(user.userid = users.id)";
In this plugin, I added two new fields to be grabbed in the select statement and then added a join to another table in my database where those fields are from.

[Dismounted]

Lynne, you may want to use concatenation there - to prevent overwrites.

[Lynne]

Yep, you are right. I didn't think about that when I wrote it (and don't have another plugin using that location, so I haven't had problems.... yet!).

[wottech]

Ok, I am starting over from scratch and trying to slowly build the plugin back to what it was. But, of course, I am running into problems right away.


if(THIS_SCRIPT == 'showpost' OR THIS_SCRIPT == 'showthread')
{
$hook_query_fields .= ",garage_user_vehicle.user_id AS garageuser";
$hook_query_joins .= "LEFT JOIN " . TABLE_PREFIX . "garage_makes AS makes ON(garage_user_vehicle.make_id = garage_makes.id)";
}

It is getting added to the query, but results in an error saying the column doesn't exist. If I replace the "user_id" with *, then it says the table doesn't exist. This is on a bare bones dev system as well as a direct query via phpmyadmin. If I take out that call, the query runs just fine. But, I can also run a normal query to access just that table with no issues.

Any ideas?!?

EDIT: Here is the actual error I am getting...

Database error in vBulletin 3.8.2:

Invalid SQL:

SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS isdeleted,
user.*, userfield.*, usertextfield.*,
icon.title as icontitle, icon.iconpath,
avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight,
spamlog.postid AS spamlog_postid,
deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason,
editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
editlog.reason AS edit_reason, editlog.hashistory,
postparsed.pagetext_html, postparsed.hasimages,
sigparsed.signatureparsed, sigparsed.hasimages AS sighasimages,
sigpic.userid AS sigpic, sigpic.dateline AS sigpicdateline, sigpic.width AS sigpicwidth, sigpic.height AS sigpicheight,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid

,garage_user_vehicle.user_id AS garageuser
FROM vb_post AS post
LEFT JOIN vb_user AS user ON(user.userid = post.userid)
LEFT JOIN vb_userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN vb_usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
LEFT JOIN vb_icon AS icon ON(icon.iconid = post.iconid)
LEFT JOIN vb_avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN vb_customavatar AS customavatar ON(customavatar.userid = user.userid)
LEFT JOIN vb_spamlog AS spamlog ON(spamlog.postid = post.postid)
LEFT JOIN vb_deletionlog AS deletionlog ON(post.postid = deletionlog.primaryid AND deletionlog.type = 'post')
LEFT JOIN vb_editlog AS editlog ON(editlog.postid = post.postid)
LEFT JOIN vb_postparsed AS postparsed ON(postparsed.postid = post.postid AND postparsed.styleid = 1 AND postparsed.languageid = 1)
LEFT JOIN vb_sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND sigparsed.styleid = 1 AND sigparsed.languageid = 1)
LEFT JOIN vb_sigpic AS sigpic ON(sigpic.userid = post.userid)
LEFT JOIN vb_garage_makes AS makes ON(garage_user_vehicle.make_id = garage_makes.id)
WHERE post.postid IN (0,1)
ORDER BY post.dateline;

MySQL Error : Unknown column 'garage_user_vehicle.user_id' in 'field list'
Error Number : 1054
Request Date : Thursday, May 7th 2009 @ 05:18:47 PM
Error Date : Thursday, May 7th 2009 @ 05:18:47 PM
Script : http://bszopi.net/showthread.php?t=1
Referrer : http://bszopi.net/forumdisplay.php?f=2
IP Address : xxx.xxx.xxx.xxx
Username : bszopi
Classname : vB_Database
MySQL Version : 5.0.67-community

[RLShare]

By judging from your first post 'garage_user_vehicle' is another table, you are not joining that table into the query so how is Mysql supposed to know what you are looking for. You are only including the 'garage_makes' table.

[wottech]

So my first LEFT JOIN using the showthread_query hook needs to be to include 'garage_user_vehicle' then? Let me go try that...

Yeah, that didn't work either...

if(THIS_SCRIPT == 'showpost' OR THIS_SCRIPT == 'showthread')
{
$hook_query_fields .= ",garage_user_vehicle.*";
$hook_query_joins .= "LEFT JOIN " . TABLE_PREFIX . "garage_user_vehicle AS uservehicle ON(garage_user_vehicle.user_id = post.userid)";
}

It is still not seeing garage_user_vehicle as a valid table. If someone could explain how I can associate the garage "user_id" to the post "userid" using the showthread_query, I would greatly appreciate it. If I can get that far, then I can probably figure out the rest of it (maybe). Thanks for the help so far...

--------------- Added 1241738473 at 1241738473 ---------------

Ok finally figured it out! Now to continue on with the rest of the queries...

--------------- Added 1241747134 at 1241747134 ---------------

Ok, I am finally back to the original issue - the IF-ELSE IF-ELSE conditional not working inside the plugin. I think I have another issue with the ORDER statement, as in 1 instance I am trying to retrieve up to 10 entries, but that causes the post to repeat itself. I may just have to do away with that as an option... Anyway, here are the 2 plugins I currently have working using the showthread_query:

if(THIS_SCRIPT == 'showpost' OR THIS_SCRIPT == 'showthread')
{
switch ($vbulletin->options['postbittype'])
{
case 1: $pbq = ",uservehicle.main_vehicle DESC LIMIT 1"; break;
case 2: $pbq = ",uservehicle.main_vehicle DESC LIMIT 1"; break;
case 3: $pbq = ",uservehicle.vehicle_id DESC LIMIT 10"; break;
}
$postorder .= $pbq;
$hook_query_fields .= ",uservehicle.user_id AS garageuser";
$hook_query_fields .= ",makes.make AS garagemake";
$hook_query_fields .= ",uservehicle.made_year AS garageyear";
$hook_query_fields .= ",models.model AS garagemodel";
$hook_query_joins .= "LEFT JOIN " . TABLE_PREFIX . "garage_user_vehicle AS uservehicle ON(uservehicle.user_id = post.userid)";
$hook_query_joins .= "LEFT JOIN " . TABLE_PREFIX . "garage_makes AS makes ON (uservehicle.make_id = makes.id)";
$hook_query_joins .= "LEFT JOIN " . TABLE_PREFIX . "garage_models AS models ON (uservehicle.model_id = models.id)";
}

$newyr = substr($post[garageyear], -2);
$gi = 0;

if($vbulletin->options['postbittype'] == 3)
{
while ($gi<10) {
$template_hook['postbit_userinfo_right_after_posts'] .= "<div>Test</div>";
$gi = $gi+1;
}
} else if($vbulletin->options['postbittype'] == 2) {
$template_hook['postbit_userinfo_right_after_posts'] .= "<div><img src='images/garage/car.png'><a href=garage.php?do=user_garage_view&id=$post[garageuser]> $post[username]'s Garage</a></div>";
} else {
$template_hook['postbit_userinfo_right_after_posts'] .= "<div><a href=garage.php?do=user_garage_view&id=$post[garageuser]>'$newyr $post[garagemake] $post[garagemodel]</a></div>";
}

I know the postbittype is being set, as it is in the db correctly, and as stated earlier, when it tries to return multiple entries (and that option is set), then the same post is displayed X number of times. So what are the possible issues with the conditional which is causing it not to work? Thanks!