For some reason the query I put directly to DB works, but when set in Joomla syntax it is returning only 1 result, and there should be a lot more...
Clear Mysql query:
SELECT vmp.virtuemart_product_id as 'product_id', vmp.product_parent_id as 'parent_id', vmp.product_sku as 'sku', vmp.product_in_stock as 'stock', vmp.published as 'published', pc.virtuemart_category_id as "category" FROM `mus_virtuemart_products` as vmp JOIN mus_virtuemart_product_categories as pc ON pc.virtuemart_product_id=vmp.product_parent_id WHERE vmp.product_parent_id>0 AND pc.virtuemart_category_id=105;
$db = JFactory::getDbo(); $query = $db->getQuery(true); $query->select(array( 'vmp.virtuemart_product_id as product_id', 'vmp.product_parent_id as parent_id', 'vmp.product_sku as sku', 'vmp.product_in_stock as stock', 'vmp.published as published', 'pc.virtuemart_category_id as category' )) ->from($db->quoteName('#__virtuemart_products','vmp')) ->join('INNER', $db->quoteName('#__virtuemart_product_categories','pc') . ' ON (' . $db->quoteName('pc.virtuemart_product_id') . ' = ' . $db->quoteName('vmp.virtuemart_product_id') . ')') ->where($db->quoteName('pc.virtuemart_category_id') . ' = '. $db->quote($category). ' AND '. $db->quoteName('vmp.product_parent_id') . ' >0'); $db->setQuery($query); $podaci = $db->loadAssocList();
I have tried various combinations, and am always getting only 1 result. I think I have limited the issue to 1 condition in the WHERE clause:
->where($db->quoteName('vmp.product_parent_id') . ' >0');
I have tried to set "zero" as
$zero="0", but didn't change anything.
After a few iterations in my code I start getting an error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3
When I change my
where condition to only
$db->quoteName('pc.virtuemart_category_id') . ' = '. $db->quote($category) the query works ok.