1064:You have an error in your SQL syntax;

Fatal 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 'order by p.products_sort_order, pd.products_name order by p.products_sort_order,' at line 1

Website is https://www.handsonco.com

This error I have when I am trying to use search in my website. The order by statement repeated 3 times at the end of SQL. In order to find where is the error come from, I added to code /includes/modules/pages/advanced_search_results/header_php.php everywhere where I found “order by” numbers like " order 1 by”, “order 2 by” and so on. The strange think is just one occurrence of "order by" statement has this number. Looks like”

crl3x84120x%') )) order 3 by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name limit 22 in /home/handsoo6/public_html/includes/classes/db/mysql/query_factory.php on line 120

I can’t figure out where other 2 occurrences in error message above come from

Thank you

Sorry guys error log looks like :
[02-May-2014 12:22:14] PHP Fatal 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 'order by p.products_sort_order, pd.products_name order by p.products_sort_order,' at line 1 :: SELECT DISTINCT p.products_image, p.products_quantity , m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id FROM (znc_products p LEFT JOIN znc_manufacturers m USING(manufacturers_id), znc_products_description pd, znc_categories c, znc_products_to_categories p2c ) LEFT JOIN znc_meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1 WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 AND p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND ((pd.products_name LIKE '%crl3x84120x%' OR p.products_model LIKE '%crl3x84120x%' OR m.manufacturers_name LIKE '%crl3x84120x%' OR (mtpd.metatags_keywords LIKE '%crl3x84120x%' AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description LIKE '%crl3x84120x%' AND mtpd.metatags_description !='') OR pd.products_description LIKE '%crl3x84120x%') )) order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name limit 22 in /home/handsoo6/public_html/includes/classes/db/mysql/query_factory.php on line 120

Try making the modification suggested by lat9 at the following link. It will give you the location that has caused the error. It will not specifically assist in identifying the other 2 locations of the the sql statement that you have stated exist. (Don't know if they truly do or not)

http://www.zen-cart.com/showthread.php?211396-Use-debug_backtrace%28%29-to-determine-query_factory-caller-on-error

If you look at SQL error is understandable see below in bold, question is how it is happened! : SELECT DISTINCT p.products_image, p.products_quantity , m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id FROM (znc_products p LEFT JOIN znc_manufacturers m USING(manufacturers_id), znc_products_description pd, znc_categories c, znc_products_to_categories p2c ) LEFT JOIN znc_meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1 WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 AND p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND ((pd.products_name LIKE '%crl3x84120x%' OR p.products_model LIKE '%crl3x84120x%' OR m.manufacturers_name LIKE '%crl3x84120x%' OR (mtpd.metatags_keywords LIKE '%crl3x84120x%' AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description LIKE '%crl3x84120x%' AND mtpd.metatags_description !='') OR pd.products_description LIKE '%crl3x84120x%') )) **order by **p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name

holsterland:

If you look at SQL error is understandable see below in bold, question is how it is happened! : SELECT DISTINCT p.products_image, p.products_quantity , m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id FROM (znc_products p LEFT JOIN znc_manufacturers m USING(manufacturers_id), znc_products_description pd, znc_categories c, znc_products_to_categories p2c ) LEFT JOIN znc_meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1 WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 AND p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND ((pd.products_name LIKE '%crl3x84120x%' OR p.products_model LIKE '%crl3x84120x%' OR m.manufacturers_name LIKE '%crl3x84120x%' OR (mtpd.metatags_keywords LIKE '%crl3x84120x%' AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description LIKE '%crl3x84120x%' AND mtpd.metatags_description !='') OR pd.products_description LIKE '%crl3x84120x%') )) **order by **p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name

Part of understanding how it has happened is understanding at what line the sql was run that gave/had the incorrect sql statement. Right now all that is known is that an error occured and the query factory (where all sql should be processed) is the last place to know that there was a problem. By adding the code provided by lat9 the line number that pushed the query to the query factory will be returned and from there can back track how the query was created and in your case how two additional order by's that were not in the location that you added numbers appeared in your query.

mc12345678:

Try making the modification suggested by lat9 at the following link. It will give you the location that has caused the error. It will not specifically assist in identifying the other 2 locations of the the sql statement that you have stated exist. (Don't know if they truly do or not)

http://www.zen-cart.com/showthread.php?211396-Use-debug_backtrace%28%29-to-determine-query_factory-caller-on-error

Regarding the above statement made earlier. Although I am still slightly confused, I will say that something I thought I understood about the original post was that I thought there was a claim of finding a similar query (without all of the order by statements) in multiple locations in the file(s). It seemed that the OP was trying to identify where else the same query was generated/used. I thought that there was a claim that three such locations existed and that two were still to be discovered.

Anyways, the suggestion of adding the sql debug code offered by lat9 and to be incorporated into 1.6.0 as I understand remains my suggestion at this time regardless of what I previously understood/misunderstood.