Author Topic: SQL error at product details page after applying price sorting in a category  (Read 1325 times)

borro

  • Jr. Member
  • **
  • Posts: 207
  • Wish you happiness!
    • web developing, web design
  • VirtueMart Version: a few
Hello!

I've faced with issue:
If you apply price sorting in a category view and then open some product there you will see such kind of error:
Quote
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 'Фукс" на натуральном камне" ORDER BY product_price ASC LI' at line 1 SQL=SELECT p.`virtuemart_product_id`, `l`.`product_name` FROM `ytgb1_virtuemart_products` as p INNER JOIN `ytgb1_virtuemart_products_ru_ru` as l using (`virtuemart_product_id`) LEFT JOIN `ytgb1_virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `ytgb1_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `ytgb1_virtuemart_product_prices` as pp ON p.`virtuemart_product_id` = pp.`virtuemart_product_id`
WHERE ( `pc`.`virtuemart_category_id` = 2 AND ( `ps`.`virtuemart_shoppergroup_id`= "1" OR `ps`.`virtuemart_shoppergroup_id` IS NULL ) AND p.`published`="1" ) AND p.`virtuemart_product_id`!="4011" AND product_price >= "Статуэтка "Фукс" на натуральном камне" ORDER BY product_price ASC LIMIT 1
As you can see there problem in where clause here
product_price >= "Статуэтка "Фукс"

Did somebody fix it?

To fix it you have to open \administrator\components\com_virtuemart\models\product.php in public function getNeighborProducts
before code:
Code: [Select]
foreach ($neighbors as &$neighbor) {paste this line of code:
Code: [Select]
            if($whereorderByName=="product_price")
                foreach($product->allPrices as $key=>$value){
                    if($value['virtuemart_shoppergroup_id'] == Х ) {
                        $orderByValue = $value['product_price'];
                        break;
                    }
                }
where X is the needed shoppergroup_id value

Good luck
Wish you happiness!

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4615
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
HI,
thanks to see this post : http://forum.virtuemart.net/index.php?topic=79800.0
greets,
patrick