HELP... 1064 You have an error in your SQL syntax;

[limelites]

I'm having trouble since installing a neat drop down filter.

I'm trying to add the line below into my default_filter.php file so that all categories will display my products in the new to old order by default. It works well, but as soon as I select a changed sort order from the filter drop down, I get a 1064 SQL syntax error. Can anyone see what's wrong with this code:

$listing_sql .= " order by p.products_date_added DESC, pd.products_name";

[limelites]

Here is a complete copy of the 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_date_added DESC' at line 7
in:
[select distinct p.products_image, m.manufacturers_name, p.products_model, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join 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 IN(6,24,7,23,19,245,4,5,211,2,88,89,32,36,42,43,44,45,46,47,48,49,50,51,52,53,54 ,55,56,57,58,59,60,61,62,63,64,65,66,67,80,69,70,71,72,73,74,75,76,77,78,79,87,8 1,82,83,84,85,86,9,39,38,37,40,253,41,92,93,94,95,90,91,264,33,34,35,255,256,257 ,258,259,260,261,262,263,26,27,25,28,29,30,31,273) order by m.manufacturers_name order by p.products_date_added DESC]

[Ajeh]

You have 2 "order by" statements ...

However you are building the SQL statement you are building it incorrectly and getting the 2 order by statements added ...

[limelites]

Hi Linda,

Thank you for your reply, I appreciate you taking the time to look at this. My PHP skills are pretty darned weak and this one has me bamboozled. I've pasted the contents of the PHP file here:

http://www.zen-cart.com/forum/showpo...4&postcount=33

Perhaps, if you get time you could take a look and see where I'm going wrong?

[bunyip]

The major problem you're having is that now with your custom dropdown filter, you have sort orders being set in two places: with your dropdown and with the column headings (the default way that the cart sets the orders, using a $_GET['sort'] parameter). In order to avoid the sql error, you need to chop that down to just a single way to set the sort order - probably by deleting or commenting out the entire section of code from lines 139 to 193.
Then to set your default value to the new->old that you want, change line 50 of your code to:

$alpha_sort = ' order by p.products_date_added DESC'; 

[totomony]

Can anybody help .
when i clike the "new product " Icon" and it show

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 '-0, 0' at line 11
in:
[SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status, p.master_categories_id FROM products p LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 and p.products_date_added >=20090911 order by p.products_date_added DESC, pd.products_name limit -0, 0]

Now I'm using zencart v.1.38 and use the class green template.

http://www.kissmama.com/index.php?ma...e=products_new
How can i slove this problem. many thank.

[Ajeh]

Check your settings in the Configuration ... Maximum Values ... for things set to 0 ...

[totomony]

Maximum Values
New Products Listing- Number Per Page 0 Info


New Product Listing - Limited to ...
Limit the New Product Listing to
0= All Products
1= Current Month
7= 7 Days
14= 14 Days
30= 30 Days
60= 60 Days
90= 90 Days
120= 120 Days

[totomony]

Cheer!!!!!!!!!!!!!!!!!!!!!
Thanks Thanks Ajeh :
Just now i change the new product listing value from 0-10
Now everything is ok.
Thank you very much indeed.

[Ajeh]

You are most welcome ... thanks for the update that changing the New Products Listing- Number Per Page from 0 to a value fixed the limit 0 error on your site ...