Author Topic: Displaying data on multiple conditions of WHERE clause?  (Read 205 times)

mailblade

  • Jr. Member
  • **
  • Posts: 77
  • A beginner
Displaying data on multiple conditions of WHERE clause?
« on: February 04, 2018, 15:11:55 pm »
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]

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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 2741
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 &amp; 3.0.x.y
Re: Displaying data on multiple conditions of WHERE clause?
« Reply #1 on: February 05, 2018, 23:35:27 pm »
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]
$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

  • Jr. Member
  • **
  • Posts: 77
  • A beginner
Re: Displaying data on multiple conditions of WHERE clause?
« Reply #2 on: February 06, 2018, 10:28:02 am »
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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 2741
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 &amp; 3.0.x.y
Re: Displaying data on multiple conditions of WHERE clause?
« Reply #3 on: February 06, 2018, 10:58:08 am »
#_ 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.