Query dynamic id

  • Posts: 1119
  • Thank you received: 114
6 years 5 months ago #293507

Hi,

I do have such query which displays characteristic values of the product in product listing:

<?php 
$database = JFactory::getDBO();
$query="SELECT group_concat(product_id) as product_ids from `ozi8g_hikashop_product` WHERE ( product_parent_id=".$this->row->product_id.")  and product_quantity>0 and product_published>0 ";
$database->setQuery($query);
$parent_product_ids=$database->loadResult();
$this->row->product_id=$parent_product_ids;


  $cat_id = '21';


$query = 'select * from ozi8g_hikashop_variant as v left join ozi8g_hikashop_characteristic as c on v.variant_characteristic_id=c.characteristic_id where characteristic_parent_id='.$cat_id.' and variant_product_id in  ('.$parent_product_ids.') GROUP BY characteristic_id ORDER BY `c`.`characteristic_ordering` ASC';
$database->setQuery($query);
$rows = $database->loadObjectList();
foreach($rows as $row){

echo '<span class="tz-pchars">'.$row->characteristic_value.'</span>';

}
?>

Now the issue that i have 2 size characteristics. One for clothes and second for shoes with different id's. Is there a way to have dynamic characteristic_parent_id? I have spent some time but only what i could think is to set id based on category id which works well but it gives other issues for module which display random products...

Could Anyone with better knowledge give some hint please?

Thanks

Please Log in or Create an account to join the conversation.

  • Posts: 82867
  • Thank you received: 13374
  • MODERATOR
6 years 5 months ago #293528

Hi,

It depends... :)
If you have no other characteristics in your products, then simply remove "characteristic_parent_id='.$cat_id.' and" so that it will pick the values of all the characteristics of the product, regardless of the characteristic used in the product.
If that's not the case, then you can create a custom product field via the menu Display>Custom fields in order to fill the characteristic id for each product, and then use $this->row->XXX instead of $cat_id (where XXX is the column name of the custom field) so that it can be different for different products even in the same listing.

The following user(s) said Thank You: kyratn

Please Log in or Create an account to join the conversation.

  • Posts: 1119
  • Thank you received: 114
6 years 5 months ago #293641

Hi,

So your hint Nicolas was really awesome. ;) My products have 2 characteristics so first option won't work. For the second one, i dont want to enter some id for every product as it will take time. What i did, i added NOT Operator and changed "where characteristic_parent_id='.$cat_id.' " to "WHERE NOT characteristic_parent_id='.$cat_id.' " and entered id which i dont want to show. Now all works perfect. I hope this will help someone in the future.

Thanks for support Nicolas.

Have a great day

The following user(s) said Thank You: nicolas

Please Log in or Create an account to join the conversation.

Time to create page: 0.048 seconds
Powered by Kunena Forum