Jump to content

  • You cannot start a new topic
  • You cannot reply to this topic

Sql Query Issue While Using Json_Contains Rate Topic   - - - - -

 
  • ooaykac
  • Senior Member
  • Members
  • Join Date: 22-Nov 12
  • 297 posts

Posted 11 March 2019 - 02:16 PM #1

Not at localhost (wampserver) but I am getting an SQL error at live site while trying this query below:
 

$condition .= db_quote(" AND NOT JSON_CONTAINS(a.hb_items->'$[*].totalHBDiscount', '\"0.00\"', '$') ");
 

the whole query:
 

SELECT a.hb_kampanyakodu,a.hb_paketno,a.hb_orderid,a.hb_items,a.timestamp,a.order_id, a.hb_items->'$[*].totalHBDiscount' as total_discount FROM cscart_orders AS a WHERE 1 AND a.payment_id IN ('25') AND NOT JSON_CONTAINS(a.hb_items->'$[*].totalHBDiscount', '"0.00"', '$') AND a.timestamp >= 1551387600 AND a.timestamp <= 1552312950 ORDER BY a.order_id desc LIMIT 0, 20  

 
Error: 

<!--
 
Tygh\Exceptions\AException
 
Message
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 '>'$[*].totalHBDiscount', '"0.00"', '$')  AND a.timestamp >= 1552251600 AND a.tim' at line 1 (1064)SELECT COUNT(*) FROM cscart_orders AS a WHERE 1  AND a.payment_id IN ('25')  AND NOT JSON_CONTAINS(a.hb_items->'$[*].totalHBDiscount', '"0.00"', '$')  AND a.timestamp >= 1552251600 AND a.timestamp 
-->
 
Data in database I am serching in: 
[{"sku":"HBV000008CJIQ","quantity":1,"rate":"12","vat":32.17,"commission":25.31,"unitHBDiscount":"0.00","totalHBDiscount":"0.00"},{"sku":"HBV000004VVDF","quantity":1,"rate":"12","vat":39.64,"commission":31.19,"unitHBDiscount":"0.00","totalHBDiscount":"0.00"}]
 
 

PHP version at localhost is 7.0
 
PHP version at livesite is 7.2
 
what is the reason of this issue?

 

BTW, my mainly purpose is filtering result according to the value in json coded array value in database.



 
  • eComLabs
  • CS-Cart Expert
  • Authorized Reseller
  • Join Date: 27-Jan 14
  • 19796 posts

Posted 11 March 2019 - 02:37 PM #2

Looks like the issue is caused by 3rd party addon

 

http://prntscr.com/mwcx8m

 

Please contact addon developer


GET A FREE QUOTE | CS-Cart Add-ons | CS-Cart Licenses | CS-Cart Development | CS-Cart Design | Server Configuration | UniTheme and YOUPI
CS-Cart                USD 345     Multi-Vendor              USD 1250    CS-Cart RU                         24500 руб.
CS-Cart Ultimate  USD 775     CS-Cart + YOUPI      USD 545      CS-Cart RU + UniTheme    36000 руб.


 
  • ooaykac
  • Senior Member
  • Members
  • Join Date: 22-Nov 12
  • 297 posts

Posted 12 March 2019 - 10:01 AM #3

I didn't understand what does it mean that you sent.

 

PS: I am the developer.



 
  • eComLabs
  • CS-Cart Expert
  • Authorized Reseller
  • Join Date: 27-Jan 14
  • 19796 posts

Posted 12 March 2019 - 01:11 PM #4

Looks like JSON_CONTAINS is not used correctly. Please check documentation
 

GET A FREE QUOTE | CS-Cart Add-ons | CS-Cart Licenses | CS-Cart Development | CS-Cart Design | Server Configuration | UniTheme and YOUPI
CS-Cart                USD 345     Multi-Vendor              USD 1250    CS-Cart RU                         24500 руб.
CS-Cart Ultimate  USD 775     CS-Cart + YOUPI      USD 545      CS-Cart RU + UniTheme    36000 руб.


 
  • ooaykac
  • Senior Member
  • Members
  • Join Date: 22-Nov 12
  • 297 posts

Posted 15 March 2019 - 07:52 AM #5

Thanks for your help. But I realized that the problem is not based on the usage of JSON_CONTAINS, it is based on MYSQL version. JSON_CONTAINS works on MYSQL 5.7 or later. The problem has gone after updating MYSQL to 5.7.



 
  • eComLabs
  • CS-Cart Expert
  • Authorized Reseller
  • Join Date: 27-Jan 14
  • 19796 posts

Posted 15 March 2019 - 02:27 PM #6

Thanks for your help. But I realized that the problem is not based on the usage of JSON_CONTAINS, it is based on MYSQL version. JSON_CONTAINS works on MYSQL 5.7 or later. The problem has gone after updating MYSQL to 5.7.

 

Thank you for letting us know it


GET A FREE QUOTE | CS-Cart Add-ons | CS-Cart Licenses | CS-Cart Development | CS-Cart Design | Server Configuration | UniTheme and YOUPI
CS-Cart                USD 345     Multi-Vendor              USD 1250    CS-Cart RU                         24500 руб.
CS-Cart Ultimate  USD 775     CS-Cart + YOUPI      USD 545      CS-Cart RU + UniTheme    36000 руб.