News:

Support the VirtueMart project and become a member

Main Menu

Displaying data on multiple conditions of WHERE clause?

Started by mailblade, February 04, 2018, 15:11:55 PM

Previous topic - Next topic

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


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

#1
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') ;


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.