Displaying data on multiple conditions of WHERE clause?

[mailblade]

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):

Code Select Expand


<?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);

 ?>


[Studio 42]

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

Code Select Expand

$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') ;

[mailblade]

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?

[Studio 42]

#_ 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.