Display data based on multiple where clause?

Question

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

 ?>

Here is my edited code showing how the table is working right now I only included 4 customfields, as normally there is about 20:

<table style="font-size: 12.5px; width: 100%;" class="orders">
<tr style="border: 1px solid #ffb3b3;">
<th style="text-align:center;border: 1px solid #ffb3b3;">Local ICOS</th>
<th style="text-align:center;border: 1px solid #ffb3b3;">Description</th>
<th style="text-align:center;border: 1px solid #ffb3b3;">Batch ID</th>
<th style="text-align:center;border: 1px solid #ffb3b3;">Temp Zone</th>
</tr>

<?php
// Start the Output
foreach ( $this->products as $product ) {

    // Show the horizontal seperator
    if ($iBrowseCol == 1 && $iBrowseProduct > $BrowseProducts_per_row) { ?>
    <div class="horizontal-separator"></div>
    <?php }
?>

<tr style="border-bottom: 1px solid #ffb3b3; height: 30px; display:;">

<!-- ICOS -->
<td style="width: 80px;text-align: center;">
<?php 
$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query->select($db->quoteName(array('customfield_value', 'virtuemart_product_id')));
$query->from($db->quoteName('jos_virtuemart_product_customfields'));
$query->where($db->quoteName('virtuemart_custom_id') . ' LIKE '. $db->quote('5'). ' AND '. $db->quoteName('virtuemart_product_id') . 'LIKE ' . $db->quote($product->virtuemart_product_id));

$db->setQuery($query);
$results = $db->loadResult();
echo ($results); 
 ?>
</td>

<!-- Description -->
<td style="padding-left: 10px;width: 300px; color: #ff3333;">
<?php  echo $product->product_name; ?>
</td>

<!-- BATCH ID (SKU) -->
<td style="width: 80px;text-align: center;">
<?php 
$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query->select($db->quoteName(array('customfield_value', 'virtuemart_product_id')));
$query->from($db->quoteName('jos_virtuemart_product_customfields'));
$query->where($db->quoteName('virtuemart_custom_id') . ' LIKE '. $db->quote('6'). ' AND '. $db->quoteName('virtuemart_product_id') . 'LIKE ' . $db->quote($product->virtuemart_product_id));

$db->setQuery($query);
$results = $db->loadResult();
echo ($results); 
 ?>
</td>

<!-- Temp Zone -->
<td style="width: 80px;text-align: center;">
<?php 
$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query->select($db->quoteName(array('customfield_value', 'virtuemart_product_id')));
$query->from($db->quoteName('jos_virtuemart_product_customfields'));
$query->where($db->quoteName('virtuemart_custom_id') . ' LIKE '. $db->quote('21'). ' AND '. $db->quoteName('virtuemart_product_id') . 'LIKE ' . $db->quote($product->virtuemart_product_id));

$db->setQuery($query);
$results = $db->loadResult();
echo ($results); 
 ?>
</td>
</tr>
<?php } ?>
</table>

Ok. I have a dropdown with "Frozen" as an option. When I select "Frozen", only products with the Temp Zone "Frozen" must be shown. In each I will need to put a piece of code to show the information depending on the dropdown selection.

Answer 1

Try the following which has some modifications to your query and should hopefully print some data for you:

$db = JFactory::getDbo();

$query = $db->getQuery(true)
    ->select($db->qn(array('customfield_value', 'virtuemart_product_id', 'virtuemart_custom_id')))
    ->from($db->qn('#__virtuemart_product_customfields'))
    ->where($db->qn('virtuemart_custom_id') . ' = ' . $db->q('6'))
    ->where($db->qn('customfield_value') . ' = ' . $db->q('Frozen'))
    ->where($db->qn('virtuemart_product_id') . '= ' . $db->q($product->virtuemart_product_id));
$db->setQuery($query);

$results = $db->loadObjectList();

var_dump($results);

Changes:

• Correct definition for table name
• Replace LIKE with = as you're not trying to match something similar, but something exact.
• Use loadObjectList as you're calling getting data from multiple columns