Unknown error in SQL syntax

Printable View

Show 100 post(s) from this thread on one page

21 Sep 2009, 04:50 PM
dealbyethan.com

Unknown error in SQL syntax

The following message appears just below the shopping cart (on the "View Cart" page). How do I fix this problem? Please help. Thanks.

----------
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 '-1)' at line 1
in:
[(select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '9962' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '12603' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '6081' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '5082' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '3304' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '12600' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '10290' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '5668' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit -1)]
13 Jul 2010, 12:31 AM
sgflowers

Re: Unknown error in SQL syntax

Quote:

Originally Posted by dealbyethan.com

The following message appears just below the shopping cart (on the "View Cart" page). How do I fix this problem? Please help. Thanks.

----------
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 '-1)' at line 1
in:
[(select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '9962' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '12603' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '6081' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '5082' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '3304' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '12600' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '10290' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit 2) UNION (select p.products_id, p.products_image from zen_products_xsell xp, zen_products p, zen_products_description pd where xp.products_id = '5668' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status = '1' order by xp.products_id asc limit -1)]

hi did you ever solve this - i just updated to 1.3.9d and am getting the same issue
13 Jul 2010, 02:31 AM
dealbyethan.com

Re: Unknown error in SQL syntax

Not solved yet, still waiting for help.
13 Jul 2010, 04:15 AM
Ajeh

Re: Unknown error in SQL syntax

Are you still using Cross Sell on your site or did you change to Multi Cross Sell or anything?

Did you check all changes to manage the code for Cross Sell with your upgraded site?

NOTE: Half of the error message is with prefixes on the table names and half the code is without prefixes on the table names ...
13 Jul 2010, 02:50 PM
gilby

Re: Unknown error in SQL syntax

Quote:

Originally Posted by dealbyethan.com

Not solved yet, still waiting for help.

Looks like some adaptation of a cross sell on the shopping cart page. Is that right?
All the items seem to have a limit of 2 items except the last which has a limit of -1
Minus 1 is not a valid limit

Haven't looked at the code but I suspect in both cases there might be a total limit of 13 to display and when you add up 7 cross sells with a limit of 2 (total 14) then the next products cross sell has to be a -1 to bring it back to 13

Its only a theory but you could try setting the cross sell limit to an even number???
17 Jul 2010, 04:48 PM
dealbyethan.com

Re: Unknown error in SQL syntax

Quote:

Originally Posted by Ajeh

Are you still using Cross Sell on your site or did you change to Multi Cross Sell or anything?

Did you check all changes to manage the code for Cross Sell with your upgraded site?

NOTE: Half of the error message is with prefixes on the table names and half the code is without prefixes on the table names ...

We are using cross sell advanced and did check all changes to manage the code. You said half of the error message is with prefixes on the table names and half the code is without prefixes on the table names. How do I fix this issue?
17 Jul 2010, 05:29 PM
Ajeh

Re: Unknown error in SQL syntax

Is your database setup with tables that use the prefixes or is it setup without the prefixes?

Your code appears to have mixed settings and you need to first determine which is correct ...

Then, you will need to find the code that is not using the tables correctly ...

A search in the Developers Tool Kit for the word:
UNION

Zen Cart, by default, does not use UNION on its tables in v1.3.9 ... so a search on that word should help narrow down where that code is used and then you can fix it with the proper calls to the database tables ...
17 Jul 2010, 11:33 PM
gilby

Re: Unknown error in SQL syntax

Quote:

Originally Posted by Ajeh

Is your database setup with tables that use the prefixes or is it setup without the prefixes?

Your code appears to have mixed settings and you need to first determine which is correct ...

Then, you will need to find the code that is not using the tables correctly ...

A search in the Developers Tool Kit for the word:
UNION

Zen Cart, by default, does not use UNION on its tables in v1.3.9 ... so a search on that word should help narrow down where that code is used and then you can fix it with the proper calls to the database tables ...

I must be missing something!
All the tables here use 'zen_' as a prefix
I can't see any that don't use it????
18 Jul 2010, 12:05 AM
Ajeh

Re: Unknown error in SQL syntax

I must be losing my mind ... :blush:

The first time I looked at the SQL I thought I saw references without the zen_ in them ...

But now I am looking again and they appear to all be there ...
18 Jul 2010, 01:47 AM
gilby

Re: Unknown error in SQL syntax

Quote:

Originally Posted by dealbyethan.com

Not solved yet, still waiting for help.

Open includes/modules/YOUR_TEMPLATE/cart_upsell.php

lines around 38 and 95 read:

PHP Code:

$c += 2;

Try changing both to:

PHP Code:

$c = 2;

See if that works as a quick fix?

Show 100 post(s) from this thread on one page