1064 error when I add 13 products to the basket (xsell mod query error)

[morrie]

Hi I hope someone can help when I add 13 products to the shopping basket I get the following error message:

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 = '14045' 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)

The site is running on:
Zen Cart 1.3.7
Database Patch Level: 1.3.7

The URL for the site is:
http://www.cdandvinyl.co.uk

Any help would be greatly appreciated.

Thanks
Chris

[James O]

I am getting a similar problem in admin when I run optimise database under tools store manager quote below

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 '-footwear_co_uk_welly' at line 1
in:
[SHOW TABLE STATUS FROM easy-footwear_co_uk_welly]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields

I found this thread on the Os commerce site
forums.oscommerce.com/index.php?showtopic=144095&st=0

But I am struggling to find which php file I should change

[James O]

Ok split page results is in zencart with the same structure as Os so I think you can fix it by changing
$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

to this

$this->sql_query .= " limit " . max($offset, 0) . ", " . $this->number_of_rows_per_page


Note the bit to change = $offset to max($offset, 0)

[DrByte]

Hi I hope someone can help when I add 13 products to the shopping basket I get the following error message:

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 = '14045' 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)

The site is running on:
Zen Cart 1.3.7
Database Patch Level: 1.3.7

The URL for the site is:
http://www.cdandvinyl.co.uk

Any help would be greatly appreciated.

Thanks
Chris

Ok split page results is in zencart with the same structure as Os so I think you can fix it by changing
$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

to this

$this->sql_query .= " limit " . max($offset, 0) . ", " . $this->number_of_rows_per_page


Note the bit to change = $offset to max($offset, 0)

The "real" fix is to deal with whatever setting in the xsell addon is set to 0 or left blank. Might also be good to alter the xsell addon so that it knows not to send an invalid value to the database.

[DrByte]

I found this thread on the Os commerce site
forums.oscommerce.com/index.php?showtopic=144095&st=0

But I am struggling to find which php file I should change

A Zen Cart forum search for "optimize show table status from" brings up this thread which has the solution you require. No need to search elsewhere :)
http://www.zen-cart.com/forum/showthread.php?t=100191

[James O]

Thank you Dr Byte thats my problem solved

james

[vones]

I tired link fix from DrByte but I am still getting this 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 '-2)' at line 1
in:
[(select p.products_id, p.products_image from products_xsell xp, products p, products_description pd where xp.products_id = '33' 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 products_xsell xp, products p, products_description pd where xp.products_id = '34' 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 products_xsell xp, products p, products_description pd where xp.products_id = '35' 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 products_xsell xp, products p, products_description pd where xp.products_id = '36' 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 products_xsell xp, products p, products_description pd where xp.products_id = '37' 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 products_xsell xp, products p, products_description pd where xp.products_id = '32' 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 products_xsell xp, products p, products_description pd where xp.products_id = '39' 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 products_xsell xp, products p, products_description pd where xp.products_id = '40' 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 products_xsell xp, products p, products_description pd where xp.products_id = '41' 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 products_xsell xp, products p, products_description pd where xp.products_id = '42' 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 products_xsell xp, products p, products_description pd where xp.products_id = '325' 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 products_xsell xp, products p, products_description pd where xp.products_id = '327' 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 products_xsell xp, products p, products_description pd where xp.products_id = '326' 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 products_xsell xp, products p, products_description pd where xp.products_id = '328' 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)]