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

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";

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,81,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]

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 ...

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/showpost.php?p=760434&postcount=33

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

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';

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?main_page=products_new
How can i slove this problem. many thank.:clap:

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

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

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.

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 ... :smile:

I am getting a similar 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 '-0, 0' 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 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 = '172' order by p.products_sort_order, pd.products_name limit -0, 0]
With IE when I try to view my categories. Yesterday it was fine. Wednesday I updated my google feeder module and updated the sqls that went with it. The funny thing is it worked for 1 1/2 days after that. So I have now taken all of the google froogle files off my site and ran the unistall for the mod. the problem has gotten better, before the whole site errored.
With FireFox, my categories show up fine, but my main page gets this error after the featured products on the source page (note I have tried setting featured products and setting it to 0):
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 6<br />in:<br />[select p.products_id, pd.products_name, products_date_available as date_expected, p.master_categories_id from products p, products_description pd where p.products_id = pd.products_id and p.products_status = 1 and pd.language_id = '1' and p.products_date_available >20091016235959 order by date_expected desc limit
Any idea what I can do to fix it? I have checked the admin>configuration>max values and changed all to a value, but there is no change.

Thanks for any help!!

helpme:

I am getting a similar error:

With IE when I try to view my categories. Yesterday it was fine. Wednesday I updated my google feeder module and updated the sqls that went with it. The funny thing is it worked for 1 1/2 days after that. So I have now taken all of the google froogle files off my site and ran the unistall for the mod. the problem has gotten better, before the whole site errored.
With FireFox, my categories show up fine, but my main page gets this error after the featured products on the source page (note I have tried setting featured products and setting it to 0):

Any idea what I can do to fix it? I have checked the admin>configuration>max values and changed all to a value, but there is no change.

Thanks for any help!!

wanted to add, I had someone else check the site and with IE they are not getting the error on the categories pages, but only the main page.