1

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.

|improve this question
  • Turn on debug and development errors. There are numerous problems with your code. – jamesgarrett Feb 5 '18 at 8:59
  • @MailBlade Better than the screenshot that Lodder asked for... the best way to offer db table structures and data is to export a minimal, yet sufficient sample of data from your relevant tables and paste the structures and data into an sqlfiddle link for us. This way we can develop and test our solutions for you AND future researchers will have absolute clarity about what your issue is and if they are having the same one. – mickmackusa♦ May 28 '18 at 14:04
  • @MailBlade please improve this open/abandoned question so that we can provide you with optimal support and educate you on better coding practices. – mickmackusa♦ Jun 21 '18 at 22:37
  • Thanks for your reply @mickmackusa. Yeah I know this is not the optimal way to perform this query, but so far it is working as intended. I had another question where I am using some "JOIN" queries – MailBlade Jun 23 '18 at 5:25
  • 1
    I've also solved this issue a while ago, but thank you @mickmackusa. I've just been crazy busy with a new project these past few months and I've still wanted to update my Q's! Apologies will sort it out within the next few days – MailBlade Feb 2 at 13:59
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
|improve this answer
  • Thanks so much for your reply. I really do appreciate it. I have used this, and now I am getting this as a value: "array(0) { }" Is that correct? How do I go about displaying the data for "virtuemart_custom_id[6]" where "virtuemart_custom_id[21]" is "Frozen"? – MailBlade Feb 6 '18 at 9:17
  • Are you sure that there is a row in the database, where virtuemart_custom_id = 6, customfield_value = Frozen and virtuemart_product_id = xxx? xxx being whatever $product->virtuemart_product_id is equal to – Lodder Feb 6 '18 at 9:30
  • Yes. You see, "Frozen" value belongs to a different 'virtuemart_custom_id (which is 21)'. So it must get the value of 'virtuemart_custom_id[6]' IF 'virtuemart_custom_id[21] == "Frozen"' – MailBlade Feb 6 '18 at 9:34
  • @MailBlade - Would you mind taking a screenshot of your database row so that I can see and add it to your question? Still haven't had my morning coffee so I'll need something visual :) – Lodder Feb 6 '18 at 9:37
  • yes no problem at all. Thanks a lot for your help. – MailBlade Feb 6 '18 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.