OpenCart SQL request and display on product template page

Question

I am trying to access an id a table to display on the product page on OpenCart 2.0.3.1.

I am trying to access an id from a table and then bring it into the product page and display it on the variant options on the drop-down.

I run the SQL for one product and get the id 21. so for debugging reasons I add the two variables (product_id and option_id) manually so those arent causing an issue. Below is a screenshot of the sql query working

So it returns with the number 21. Obviously when I change the sql to be the product_id and option_id for each product it will return the relevant id.

I have this setup:

catalog/model/catalog/product.php

public function getOtpOptionId($product_id)
{
    $otp_option_query = $this->db->query("SELECT id from oc_otp_option_value where product_id = 62 AND parent_option_value_id = 20");
    $otp_option = $otp_option_query->row;
    return $otp_option;
}

catalog/controller/product/product.php

below

foreach ($option['product_option_value'] as $option_value) {

I add

$data['otp_option_id'] = $this->model_catalog_product->getOtpOptionId($this->request->get['product_id']);

catalog/theme/*/template/product/product.tpl

I change:

<option value="<?php echo $option_value['option_value_id']; ?>"><?php echo $option_value['name']; ?></option>

to be

<option value="<?php echo $option_value['option_value_id']; ?>"><?php echo $option_value['name']; ?> (<?php echo $data['otp_option_id']; ?>)</option>

I would expect it to show all the options in the drop-down with (21)

for example:
red (21)
blue (21)
Green (21)

It just shows each option with no number and only the ().

What am I doing wrong?

Thanks in advance

/******* UPDATE *******/

So I have changed the code in the model to pick up the id of each option rather than using numbers

    public function getOtpOptionId($product_id, $option_value_id)
{
    $otp_option_query = $this->db->query("SELECT id from oc_otp_option_value where product_id = '" . (int)$product_id . "' AND parent_option_value_id = '" . (int)$option_value_id . "'");
    $otp_option = $otp_option_query->row;
    return $otp_option;
}

DigitCart DigitCart · Accepted Answer · 2017-12-21T13:34:57

If you do not want to use this code inside foreach loop later:

$data['otp_option_id'] = $this->model_catalog_product->getOtpOptionId($this->request->get['product_id']);

you should not put it inside the loop, you can place it after this code, that is outside the loop:

$data['options'] = array();

And in your view file, instead of:

<option value="<?php echo $option_value['option_value_id']; ?>"><?php echo $option_value['name']; ?> (<?php echo $data['otp_option_id']; ?>)</option>

Use:

<option value="<?php echo $option_value['option_value_id']; ?>"><?php echo $option_value['name']; ?> (<?php echo $otp_option_id; ?>)</option>

OpenCart SQL request and display on product template page

1 Answers