If this is your first visit, check our documentation. To post, register or login.
Forums / Basic Configuration / 1064 You have an error in your SQL syntax when using product list Price Sort

1064 You have an error in your SQL syntax when using product list Price Sort

Locked

Started by missesbeehaven on 22 Jul 2009, 5:07 AM · Views: 2,520

Results 1 to 8 of 8
This thread is locked. New replies are disabled.
22 Jul 2009, 5:07 AM
#1
missesbeehaven avatar

missesbeehaven

New Zenner

Join Date:
Apr 2009
Posts:
57
Plugin Contributions:
0

1064 You have an error in your SQL syntax when using product list Price Sort

When I view any category of my store and click on the price link to sort the items by price I 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 '' at line 7
in:
[select p.products_image, pd.products_name, p.products_quantity, p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status from zen_products_description pd, zen_products p left join zen_manufacturers m on p.manufacturers_id = m.manufacturers_id, zen_products_to_categories p2c left join zen_specials s on p2c.products_id = s.products_id where p.products_status = 1 and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '316' order by ]

What is needed to correct this?

The forum posts I have found regarding this error have been about emails from the admin module of zen cart or initial installation of zen cart. My store seems to be functioning okay besides this error when I try to sort product lists by price.

Melanie
beehavendesignz DOT com

22 Jul 2009, 3:35 PM
#2
bunyip avatar

bunyip

Totally Zenned

Join Date:
Sep 2004
Location:
Western Massachusetts
Posts:
2,361
Plugin Contributions:
1

Re: 1064 You have an error in your SQL syntax when using product list Price Sort

a link to your site would be helpful

Neville
An assumption is what you arrive at when you get tired of thinking...

22 Jul 2009, 10:01 PM
#3
missesbeehaven avatar

missesbeehaven

New Zenner

Join Date:
Apr 2009
Posts:
57
Plugin Contributions:
0

Re: 1064 You have an error in your SQL syntax when using product list Price Sort

Opps yes that would help so sorry

it is https://www.missesbeehaven.com/shop/

Melanie
beehavendesignz DOT com

23 Jul 2009, 3:07 AM
#4
bunyip avatar

bunyip

Totally Zenned

Join Date:
Sep 2004
Location:
Western Massachusetts
Posts:
2,361
Plugin Contributions:
1

Re: 1064 You have an error in your SQL syntax when using product list Price Sort

It looks to me like you have a problem in the file includes/index_filters/default_filter.php

Have you edited that file at all? If not, try uploading a fresh copy from your install package.

If you do have a modified copy of that file, please attach it as a zip file here and I'll take a look at it for you.

For info - it's not just the price sort that has the problem - it also happens with product name sort. I'm thinking that there's a whole section of code missing from that filter file.

Neville
An assumption is what you arrive at when you get tired of thinking...

23 Jul 2009, 4:26 AM
#5
missesbeehaven avatar

missesbeehaven

New Zenner

Join Date:
Apr 2009
Posts:
57
Plugin Contributions:
0

Re: 1064 You have an error in your SQL syntax when using product list Price Sort

Thanks for the info.

I had a few weeks ago added the product filter mod, maybe that is why?

This is the mod link
https://www.zen-cart.com/forum/showthread.php?p=752107#post752107

I would be most appreciative if you could look at this for me, I really want to get rid of this horrible error

Melanie
beehavendesignz DOT com

23 Jul 2009, 4:29 AM
#6
missesbeehaven avatar

missesbeehaven

New Zenner

Join Date:
Apr 2009
Posts:
57
Plugin Contributions:
0

Re: 1064 You have an error in your SQL syntax when using product list Price Sort

PS.. So glad I came across your mod shop, I have sent you an email from your site link that you have in your signature :D

Melanie
beehavendesignz DOT com

23 Jul 2009, 5:35 AM
#7
bunyip avatar

bunyip

Totally Zenned

Join Date:
Sep 2004
Location:
Western Massachusetts
Posts:
2,361
Plugin Contributions:
1

Re: 1064 You have an error in your SQL syntax when using product list Price Sort

As I thought, there is a problem with that file: for some unkown reason, whoever modified it for the product filter mod didn't want the sort order capability used and commented out a bunch of lines that effectively broke the sql query and caused your error message.

Look at the code between lines 143 and 165 of your file:

      switch ($column_list[$sort_col-1]) {
        case 'PRODUCT_LIST_MODEL':
          //$listing_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_NAME':
          //$listing_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
          break;
        case 'PRODUCT_LIST_MANUFACTURER':
          //$listing_sql .= "m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_QUANTITY':
          //$listing_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_IMAGE':
          //$listing_sql .= "pd.products_name";
          break;
        case 'PRODUCT_LIST_WEIGHT':
          //$listing_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_PRICE':
//          $listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          //$listing_sql .= "p.products_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;

You need to uncomment all the lines starting with //listing... except for the second last one, so that entire section looks like this:

      switch ($column_list[$sort_col-1]) {
        case 'PRODUCT_LIST_MODEL':
          $listing_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_NAME':
          $listing_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
          break;
        case 'PRODUCT_LIST_MANUFACTURER':
          $listing_sql .= "m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_QUANTITY':
          $listing_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_IMAGE':
          $listing_sql .= "pd.products_name";
          break;
        case 'PRODUCT_LIST_WEIGHT':
          $listing_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_PRICE':
//          $listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          $listing_sql .= "p.products_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;

Then your sort capability should work fine again.

Neville
An assumption is what you arrive at when you get tired of thinking...

23 Jul 2009, 5:53 AM
#8
missesbeehaven avatar

missesbeehaven

New Zenner

Join Date:
Apr 2009
Posts:
57
Plugin Contributions:
0

Re: 1064 You have an error in your SQL syntax when using product list Price Sort

Thanks heaps that fixed it :clap:

Melanie
beehavendesignz DOT com