Query to show category name of a product

[inkomico]

Hi everyone!

I need to show the category name of a product in a query like this that show SKU:

Code Select Expand

$qGetSKU = 'select product_sku from #__virtuemart_products where virtuemart_product_id = '.$row['product_id'];
$dbj->setQuery($qGetSKU);
$SKUsrcstring = $dbj->loadResult();
echo $SKUsrcstring;


This is a very a simple query, but to show the category name I need to do some joins that I can´t figure out.

I just know that joining the tables  #__virtuemart_products #__virtuemart_product_categories #__virtuemart_categories_pt_br I got my result.

Could you help me with this code?

[GJC Web Design]

Code Select Expand

$query = $db->getQuery(true);
$query->select($db->quoteName(array('a.virtuemart_category_id', 'b.category_name')));
$query->from($db->quoteName('#__virtuemart_product_categories',a));
$query->where($db->quoteName('a.virtuemart_product_id')." = ".$row['product_id']);
$query->join('INNER', $db->quoteName('#__virtuemart_categories_'.VMLANG, 'b') . ' ON (' . $db->quoteName('a.virtuemart_category_id') . ' = ' . $db->quoteName('b.virtuemart_category_id') . ')');
$db->setQuery($query);
$cats = $db->loadObject();
print 'Debug Line '.__LINE__.' $cats <pre>'; print_r ($cats); print "</pre><br />\n";

e.g.

Debug Line 182 $cats
stdClass Object
(
    [virtuemart_category_id] => 2
    [category_name] => Letter Weight Products
)

[inkomico]

Exactly this, thank you!

[PRO]

where are you doing this at? product page?

[inkomico]

No PRO, in a custom search module.