Display Variant / Characheristic options on Category layout

Hello,
I have a website that sells products with different color options. These are set as variants of the parent product.

When a customer is viewing a category layout they only see the parent item and do nto know what colors are available until they go to the product view page.

To enhance the visitor experience (and hopefully increase sales) I would like to show the variant color options when they are browsing the main category page. On other e-commerce sites I have seen this displayed. If you look at the attcahed screesnhot this shows somethign similar to what I would like to display. The main prodicyt and then a simple set of colored blocks below which would be the variant options.

I did find a 5 year old forum post, but not sure this is still relevant or would work with the latest HikaShop:

https://www.hikashop.com/support/forum/4-how-to/886656-how-could-i-show-name-of-variants-in-the-category.html

Would it be possible for you to supply some sample code and the overrides required?

How do i request this as a feture to be included in future HikaShop relelases?

Thanks
Lee

Nicolas,
Thanks for the reply, I have modified my template (and changed the table prefix from the example code) but now get SQL error:

syntax error, unexpected 'c' (T_STRING) for this line:

$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=XXX and variant_product_id in ('.$parent_product_ids.') group by characteristic_id ORDER BY `c`.`characteristic_ordering` ASC';

Unfortunately my PHP / MySQL skills are not good enough to debug this error? Any advice?

I also fully understand your comments regaridng developing a solution that is suitable for 'everyone' and I suspect your todo list is long, but if there could be a discussion on developing some sort of solution, even if it is an extra plugin it would be greatly appreciated

Thanks

Nicolas,
I've done that and now get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''#__hikashop_product' WHERE (product_parent_id=1)' at line 1

I Appreciate all your efforts, thanks

Nicolas,
With your updated code I have this eroor:

1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') GROUP BY characteristic_id ORDER BY c.characteristic_ordering ASC' at line 1

/home/underdev/mxgroup-new.under-development.info/libraries/joomla/database/driver/mysqli.php (668)

Nicolas,
still does not seem 100% correct. With some help I managed to enter a var_dump to show the query and now get this:

Warning: implode(): Invalid arguments passed in /home/underdev/mxgroup-new.under-development.info/templates/t4_blank/html/com_hikashop/product/listing_img_title.php on line 96
string(265) "SELECT * FROM XXX_hikashop_variant AS v LEFT JOIN XXX_hikashop_characteristic AS c ON v.variant_characteristic_id=c.characteristic_id WHERE characteristic_parent_id=9 AND variant_product_id IN () GROUP BY characteristic_id ORDER BY c.characteristic_ordering ASC"

This is the code I now have:

<!-- product characteristics values -->
<span class="listing-char listing-show-characteristics">
<?php
$database = JFactory::getDBO();
$query="SELECT group_concat(product_id) AS product_ids FROM XXX_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->loadColumns();
$query = 'SELECT * FROM XXX_hikashop_variant AS v LEFT JOIN XXX_hikashop_characteristic AS c ON v.variant_characteristic_id=c.characteristic_id WHERE characteristic_parent_id=9 AND variant_product_id IN ('.implode(',',$parent_product_ids).') GROUP BY characteristic_id ORDER BY c.characteristic_ordering ASC';
$database->setQuery($query);
var_dump($database->getQuery());
die;
$rows = $database->loadObjectList();
foreach($rows as $row){

echo $row->characteristic_value." ";

}
?>
</span>
<!-- end -->

Nicolas,
Apologies just getting back on this project, I have now updated to Joomla 4 and Hikashop 4.5.1 but still cannot get the characteristics to display.

This is the code I have below, but when I have the $parent_product_ids=$database->loadColumns(); active the page fails with a MySQLi failure:

Call to undefined method Joomla\Database\Mysqli\MysqliDriver::loadColumns()

Can you help

<!-- product characteristics values -->
<span class="listing-char listing-show-characteristics">
<?php
$database = JFactory::getDBO();
$query="SELECT group_concat(product_id) AS product_ids FROM XXX_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->loadColumns();
if(!empty($parent_product_ids)) {
$query = 'SELECT * FROM XXX_hikashop_variant AS v LEFT JOIN XXX_hikashop_characteristic AS c ON v.variant_characteristic_id=c.characteristic_id WHERE characteristic_parent_id=9 AND variant_product_id IN ('.implode(',',$parent_product_ids).') GROUP BY characteristic_id ORDER BY c.characteristic_ordering ASC';
$database->setQuery($query);
var_dump($database->getQuery());
die;
$rows = $database->loadObjectList();
foreach($rows as $row){

echo $row->characteristic_value." ";

}
}
?>
</span>
<!-- end -->

Nicolas,
Thanks for the reply, I have removed the rouge 's' and now get this error!

1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') GROUP BY characteristic_id ORDER BY c.characteristic_ordering ASC' at line 1

Nicolas,
this is what I have:

<!-- product characteristics values -->
<span class="listing-char listing-show-characteristics">
<?php
$database = JFactory::getDBO();
$query="SELECT group_concat(product_id) AS product_ids FROM q19u3_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->loadColumn();
if(!empty($parent_product_ids)) {
$query = 'SELECT * FROM q19u3_hikashop_variant AS v LEFT JOIN q19u3_hikashop_characteristic AS c ON v.variant_characteristic_id=c.characteristic_id WHERE characteristic_parent_id=9 AND variant_product_id IN ('.implode(',',$parent_product_ids).') GROUP BY characteristic_id ORDER BY c.characteristic_ordering ASC';
$database->setQuery($query);
var_dump($database->getQuery());
die;
$rows = $database->loadObjectList();
foreach($rows as $row){

echo $row->characteristic_value." ";

}
}
?>
</span>
<!-- end -->

Nicolas,
Ok I have made some progress!

Looking at the code it had this:

AND product_quantity>0

Well my products do not have stock levels so it was never going to get the values as the database was showing product_quantity at -1!

removing that now gives me an array of ID's when I do the var_dump, for example:

array(1) { [0]=> string( "16,17,18" }

Next the second query that gets the variants uses this:

WHERE characteristic_parent_id=ZZZ

However my parent ID was not 9 as originally used in previous code. Looking in the XXX_hikashop_characteristic table and selelcting the correct characteristic_id, it is now displaying the product variant Characteristic as I need (see attached frontend screen shot).

So it is now 'working' finally we got there!

I really hope this functionality can be discussed and included as a feature request in future HikaShop releases.

Thank you for your time and efforts, it is most appreciated

Lee


For anyone else wanting this functionality here is the code that is added to a template override for product/listing_img_title.php. You need to change the XXX to your database table prefix and the ZZZ to the characteristic_id of the characteristic from the _hikashop_characteristic table of your database.

<!-- product characteristics values -->
<span class="listing-char listing-show-characteristics">
<?php
$database = JFactory::getDBO();
$query="SELECT group_concat(product_id) AS product_ids FROM XXX_hikashop_product WHERE (product_parent_id=".$this->row->product_id.") AND product_published > 0";
$database->setQuery($query);
$parent_product_ids=$database->loadColumn();
//var_dump($parent_product_ids);
if(!empty($parent_product_ids) && count($parent_product_ids)) {
$query = 'SELECT * FROM XXX_hikashop_variant AS v LEFT JOIN XXX_hikashop_characteristic AS c ON v.variant_characteristic_id=c.characteristic_id WHERE characteristic_parent_id=ZZZ AND variant_product_id IN ('.implode(',',$parent_product_ids).') GROUP BY characteristic_id ORDER BY c.characteristic_ordering ASC';
$database->setQuery($query);
$rows = $database->loadObjectList();
foreach($rows as $row){

echo $row->characteristic_value." ";
}
}
?>
</span>
<!-- end -->