I would appreciate some help with the following.
I have two customfields with ID "6" and "7".
I want to display the customfield_value for customfield(6), only when a specific condition for customfield(7) is met. So it must display the data from customfield(6) only if customfield(7) is a certain value.
Here is the code I have wrote, but it is not working(only gives me blank on loading the data):
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('customfield_value', 'virtuemart_product_id', 'virtuemart_custom_id')));
$query->from($db->quoteName('jos_virtuemart_product_customfields'));
$query->where($db->quoteName('virtuemart_custom_id'). ' LIKE '. $db->quote('6'). ' AND '. $db->quoteName('customfield_value') . ' LIKE '. $db->quote('Frozen') . ' AND '. $db->quoteName('virtuemart_product_id'). 'LIKE ' . $db->quote($product->virtuemart_product_id));
$db->setQuery($query);
$results = $db->loadResult();
echo ($results);
?>
LIKE is only for strings virtuemart_custom_id is integer
$query->where($db->qn('virtuemart_custom_id'). '= '. $db->q('6'). ' AND '. $db->qn('customfield_value') . ' LIKE '. $db->q('Frozen') . ' AND '. $db->qn('virtuemart_product_id'). '=' . $db->q($product->virtuemart_product_id));
But the query should be
$query = 'SELECT pc2.customfield_value from #__virtuemart_product_customfields as pc
LEFT JOIN #__virtuemart_product_customfields as pc2 on pc2.virtuemart_product_id= pc.virtuemart_product_id AND pc2.virtuemart_custom_id=6
WHERE pc.virtuemart_custom_id=7
AND pc.virtuemart_product_id ='.$db->quote($product->virtuemart_product_id).'
AND pc.customfield_value LIKE '. $db->quote('Frozen') ;
Thanks so much for your reply Studio 42.
I really appreciate it. Just one question: In your own code, what does the "pc2" mean?
Also, do I need to include the # when writing down my table name in the database?
#_ is changed by joomla to the right prefix
pc2 is here the product_customfields as pc2
Because product_customfields as pc is already used to find the pc.customfield_value .
This is to prevent conflict, because same table #__virtuemart_product_customfields is used 2 times as pc and as pc2.
And we left join customfields table to get pc2.customfield_value with same product_id but having custom_id 6
On using left join, you have only a result if the first joined table have a result.