Unknown error in SQL syntax

[dealbyethan.com]

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

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

[gilby]

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

Its this one....
http://www.zen-cart.com/index.php?ma...roducts_id=283

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

[Ajeh]

Its this one....
http://www.zen-cart.com/index.php?ma...roducts_id=283

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

[dealbyethan.com]

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;

[gilby]

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

[dealbyethan.com]

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?

[gilby]

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

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

[dealbyethan.com]

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.