1064 You have an error in your SQL syntax

  • Posts: 7
  • Thank you received: 0
8 years 2 months ago #225375

-- HikaShop version -- : 2.6.0
-- Joomla version -- : 3.4.8
-- PHP version -- : 5.4.3
-- Error-message(debug-mod must be tuned on) -- : I receive the error message when i do mass action on root category:

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 ') OR product_parent_id IN ()) ORDER BY product_code DESC LIMIT 0, 500' at line 1 SQL=SELECT hk_product.* FROM Chau_hikashop_product as hk_product WHERE (product_id IN () OR product_parent_id IN ()) ORDER BY product_code DESC LIMIT 0, 500

I make the following change and same problem

$query = 'SELECT category_id FROM '.hikashop_table('category').' WHERE category_parent_id='.(int)$this->getRoot().' AND category_type='.(int)$this->database->Quote($element).' LIMIT 1';
$this->database->setQuery($query);
$element = (int)$this->database->loadResult();

I receive the error message when i do mass action on root category:

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 ') OR product_parent_id IN ()) ORDER BY product_code DESC LIMIT 0, 500' at line 1 SQL=SELECT hk_product.* FROM Chau_hikashop_product as hk_product WHERE (product_id IN () OR product_parent_id IN ()) ORDER BY product_code DESC LIMIT 0, 500

I make the following change and same problem

$query = 'SELECT category_id FROM '.hikashop_table('category').' WHERE category_parent_id='.(int)$this->getRoot().' AND category_type='.(int)$this->database->Quote($element).' LIMIT 1';
$this->database->setQuery($query);
$element = (int)$this->database->loadResult();

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

  • Posts: 81361
  • Thank you received: 13035
  • MODERATOR
8 years 2 months ago #225378

Hi,

Change the line:

$ids[] = $mainProduct->product_id;
to:
$ids[] = (int)$mainProduct->product_id;
and change the line:
$query->where = array('product_id IN ('.implode(',',$ids).') OR product_parent_id IN ('.implode(',',$ids).')');
to:
if(count($ids))
					$query->where = array('product_id IN ('.implode(',',$ids).') OR product_parent_id IN ('.implode(',',$ids).')');
				else
					$query->where = array('product_id=0');
in the file plugins/hikashop/massaction_product/massaction_product.php

and that should fix that problem.

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

  • Posts: 11
  • Thank you received: 0
5 years 10 months ago #292611

I've got this message when trying to access Dashboard for HikaShop:
An error has occurred.
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 '*, COUNT(a.order_id) AS total FROM #__hikashop_order AS a LEFT JOIN #__hik' at line 1

What to do?

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

  • Posts: 81361
  • Thank you received: 13035
  • MODERATOR
5 years 10 months ago #292622

Hi,

Do that modification in order to get the full MySQL query leading to that error:
www.hikashop.com/support/forum/2-general...ror-1054.html#287524
That will allow us to have a better picture of the situation.

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

  • Posts: 11
  • Thank you received: 0
5 years 10 months ago #292630

Hi,

New MySQL query leading to that error:
( www.hikashop.com/support/forum/2-general...ror-1054.html#287524 )

An error has occurred.
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 '*, COUNT(a.order_id) AS total FROM #__hikashop_order AS a LEFT JOIN #__hik' at line 1 SELECT DISTINCT(prod.order_product_id), *, COUNT(a.order_id) AS total FROM hklco_hikashop_order AS a LEFT JOIN hklco_hikashop_order_product AS prod ON prod.order_id = a.order_id LEFT JOIN hklco_hikashop_product AS p ON prod.product_id = p.product_id LEFT JOIN hklco_hikashop_product_category AS cat ON cat.product_id = p.product_id OR cat.product_id=p.product_parent_id LEFT JOIN hklco_hikashop_category AS categ ON cat.category_id = categ.category_id LEFT JOIN hklco_hikashop_address AS b ON a.order_billing_address_id=b.address_id LEFT JOIN hklco_hikashop_zone AS z ON b.address_country=z.zone_namekey WHERE a.order_type='sale' AND a.order_status IN ('confirmed') AND cat.category_id IN ('40','41','42','43','44','45') AND a.order_created > 1493622000 AND a.order_created < 1554069540 GROUP BY z.zone_namekey

Last edit: 5 years 10 months ago by kaimor.

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

  • Posts: 81361
  • Thank you received: 13035
  • MODERATOR
5 years 10 months ago #292638

Hi,

It's apparently coming from a report which has been configured in a strange way. Disable them one by one via the menu Orders>Reports in order to find the one causing the issue. Then, delete it, or keep it unpublished and reconfigure it.

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

  • Posts: 11
  • Thank you received: 0
5 years 10 months ago #292666

Thank you,
It was "Map" report.
Now all is all right! :)

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

Time to create page: 0.075 seconds
Powered by Kunena Forum