extra postbit info from database

[swiNz]

Hello,

I've got a question for you, i'm running a car website and i'm using the MOD D.T.O. Garage, in that mod you can add your car and put some information in your own profile like car make, model, year etc.

Now i want to show the users car make, model and year (if existing) in the postbit.

Do you guys have any idea how to do that? The DTO table where the vehicle information is is called "DTO_garage_vehicle" and the info i want are 'year' 'make' and 'model' in that table!

Hope you can help me out!

[kh99]

You might be able to create a new plugin using hook showthread_query and code like:

$hook_query_fields .= " , DTO_garage_vehicle.* ";
$hook_query_joins .= " LEFT JOIN DTO_garage_vehicle ON (DTO_garage_vehicle.userid = post.userid) ";

Then in the postbit (or postbit_legacy) template use {vb:var post.year}, {vb:var post.make}, etc.

The above assumes that DTO_garage_vehicle has a userid field.

[swiNz]

Thanks, that works like a charm!
Now i only need to put a filter on it, i want to limit the query to only one vehicle (latest added) because a user can have multiple cars.

i tried the code

PHP Code:

$hook_query_fields .= " , DTO_garage_vehicle.* ";
$hook_query_joins .= " LEFT JOIN DTO_garage_vehicle ON (DTO_garage_vehicle.userid = post.userid) ORDER BY `DTO_garage_vehicle`.`time_added` DESC LIMIT 0,1"; 


But this code gives me an error, sorry if i'm being noob here, never worked with LEFT JOIN (i only know the simple querys)

[kh99]

Oh, right. I should have thought that a user might have more than one vehicle. I'm not exactly an sql expert myself, but I don't know of any way to use a join to handle that situation. You'll probably have to add another query for each user to get their vehicle records.

At postbit_display_complete you could do something like:

global $vbulletin;

if ($vehicle = $vbulletin->db->query_first_slave("SELECT year, make, model FROM DTO_garage_vehicle WHERE userid = " . $post['userid']))
{
    $post['year'] = $vehicle['year'];
    $post['make'] = $vehicle['make'];
    $post['model'] = $vehicle['model'];
}

or if you wanted to show all of a user's vehicles you could use query_read_slave() instead and have a while loop to get each vehicle, then maybe save an array and use a vb:each loop in the template.

Also I should mention that this adds an additional query for each post. I know people try to limit the number of queries being done to make their site run faster, but I don't know how much of an effect this would have. It probably depends a lot on how busy your site is and what kind of server you have. But if it's a problem you could probably do something like make a custom profile field with the info pre-formatted, and update it when the user changes their vehicle information. Then you would only need to use the profile field in the postbit (and it would already be available without any extra queries).

Edit: the code I posted above was orignially missing the "global" line.

[swiNz]

I've got some problem with the first piece of code (ur first post)
When i run that "plugin" some members "disappear" in the forums (they show up as guest where they posted), any idea why? (could it have something to do with the option people can set if their garage is visible for everybody or for friends only? )

Edit:

It appears that this people who show up as guest don't have a garage (car) added to their profile..

[kh99]

I don't know why that would be - maybe you could try changing the hook_query_fields to

$hook_query_fields .= " , DTO_garage_vehicle.year, DTO_garage_vehicle.make, DTO_garage_vehicle.model,  ";

but like I mentioned, I think that method's going to duplicate posts if someone has more than one car.

[swiNz]

Yeah, that's going to give a problem with more garages per member, i've tried to check if a user has a garage with the folowing code (but that doesnt work also)

PHP Code:

$counter = mysql_query("SELECT COUNT(*) AS id FROM DTO_garage_vehicle WHERE userid = post.userid");
$num = mysql_fetch_array($counter);
$count = $num["id"];

if ($count = 0) {

}
else {

$hook_query_fields .= " , DTO_garage_vehicle.* ";
$hook_query_joins .= " LEFT JOIN DTO_garage_vehicle ON (DTO_garage_vehicle.userid = post.userid) ";

} 


[kh99]

Well, the problem is that at that point you have no $post['userid'] yet - what we're trying to do is add to the query that gets the posts, to save doing extra queries. If you're going to do an extra query anyway, then you might as well try what I posted in my second post.

[swiNz]

I've tried your code, but i had to edit it a little bit..

Thank you so much for your help, now i also understand how the vbulletin system is working!

It you want to know what i'm using now:

PHP Code:

global $vbulletin;

if (!$post['userid']) {
    // This is for the deleted (guest) users on the forum, they don't have a userid, so i made them one :D
    $usersid = 1292;
}
else {
    $usersid = $post['userid'];
}

if ($vehicle = $vbulletin->db->query_first_slave("SELECT year, make, model, color, packages FROM DTO_garage_vehicle WHERE userid = " . $usersid))
{
    $post['year'] = $vehicle['year'];
    $post['make'] = $vehicle['make'];
    $post['model'] = $vehicle['model'];
    $post['color'] = $vehicle['color'];
    $post['packages'] = $vehicle['packages'];
}
else {
    // I also wanted to show people if a member has no garage :)
    $nogarage = TRUE;
}