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)]

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

Not solved yet, still waiting for help.

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

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

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?

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

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

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

dealbyethan.com:

Not solved yet, still waiting for help.

Open includes/modules/YOUR_TEMPLATE/cart_upsell.php

lines around 38 and 95 read:

$c += 2;

Try changing both to:

$c = 2;

See if that works as a quick fix?

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

Hi Ajeh,

Thanks for your help. The database is setup with the prefixes. Searched for UNION and found these. What do we do next?

/includes/modules/YOUR_TEMPLATE/cart_upsell.php

Line #34 : $xsell_query_raw .= "(select p.products_id, p.products_image from " . TABLE_PRODUCTS_XSELL . " xp, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where xp.products_id = '" . $prod[$i] . "' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . $_SESSION['languages_id'] . "' and p.products_status = '1' order by xp.products_id asc limit " . $extra . ") UNION ";

Line #91 : and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . $extra . ") UNION ";

Any idea what specific Cross Sell add on you have installed on your site?

I have a number of them, but none use the cart_upsell.php file ... :unsure:

Ajeh:

Any idea what specific Cross Sell add on you have installed on your site?

I have a number of them, but none use the cart_upsell.php file ... :unsure:

Its this one....
http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=283

I found an ancient one just now ...

Can you see what setting you are using for:
NUMBER_XSELLS_DISPLAY

If you do not know where the setting is, go to the Tools ... Developers Tool Kit ...

In the top input box enter:
NUMBER_XSELLS_DISPLAY

Select NONE and click Search ... if it is in the configuration table it will give you the location and you can hit edit ...

If not, use the bottom input box in the Developers Tool Kit and search on:
NUMBER_XSELLS_DISPLAY

see if you can find where the value is defined for this ...

gilby:

Its this one....
http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=283

Yeps that is the one I just dug up ... :smile:

Ajeh:

I found an ancient one just now ...

Can you see what setting you are using for:
NUMBER_XSELLS_DISPLAY

If you do not know where the setting is, go to the Tools ... Developers Tool Kit ...

In the top input box enter:
NUMBER_XSELLS_DISPLAY

Select NONE and click Search ... if it is in the configuration table it will give you the location and you can hit edit ...

If not, use the bottom input box in the Developers Tool Kit and search on:
NUMBER_XSELLS_DISPLAY

see if you can find where the value is defined for this ...

Hi Ajeh,

The results are

/includes/modules/YOUR_TEMPLATE/cart_upsell.php

Line #9 : define('NUMBER_XSELLS_DISPLAY', '6');
Line #28 : $c = NUMBER_XSELLS_DISPLAY;

dealbyethan.com:

Hi Ajeh,

The results are

/includes/modules/YOUR_TEMPLATE/cart_upsell.php

Line #9 : define('NUMBER_XSELLS_DISPLAY', '6');
Line #28 : $c = NUMBER_XSELLS_DISPLAY;

Have you tried my suggestion in post# 10 ??

gilby:

Have you tried my suggestion in post# 10 ??

Hi, we can try that. But how do we know whether it solves the problem? The error seems to be generated randomly. Not sure how to reproduce it. Any idea?

dealbyethan.com:

Hi, we can try that. But how do we know whether it solves the problem? The error seems to be generated randomly. Not sure how to reproduce it. Any idea?

Well you could put those 8 items into your shopping cart again and see if it happens :huh:

The problem with the sql statement is right at the very end
Where it says "limit -1"
You cannot select with a limit of -1
(not was it intended to work that way)

Now that piece of code is generated in the next line after those that I suggest you change using $c
So at that point $c must be -3
So with the existing code $c +=2 makes $c equal to -1
You make sure that the code can never be negative by hard coding it to $c = 2

Its a problem with how the $c is calculated with various quantities in the cart.
Rather than spend time working out the correct mathematical fix
Just hard code it so that it can never be a negative number.

And of course get back to us if it doesn't work.....

Hi,

Tried adding those 8 xsell products. The error did not reappear. Also found another record where similar error was generated and tried adding those products (NOT xsell products) but the error still did not appear.

I think the error perhaps depends on what xsell products are displayed? The tricky thing is xsell products are displayed randomly. If anyone is trying to fix the same problem, please report back whether the problem is fixed after trying gilby's suggestion in post #10. Thanks.