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

[Guest]

Please help!

When I go to checkout I get an 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 ') and products_id in (166)' at line 1

 

select * from products_to_categories where categories_id not in () and products_id in (166)

 

[TEP STOP]

 

I have tested this, and it only happens for items 32 pounds or less in value. 32.01 value and it goes fine to the checkout page.

I tried disabling my PayPal Stand payments module, makes no difference.

I recently installed Shipping module for Parcelforce and Royal Mail but I did testing after this and everything was working fine.

I use mysql version 4.1.20 which seems pretty recent form the versions I can see available.

 

What does this error mean?

 

I had some '&' in the products but I removed all of these for no character confusion with the programming language. Do I need to remove ' or " anywhere they appear too? I have none in any product titles, but I do in descriptions.

 

But then why does it seem to be price sensitive? I can;t see any mimimum order values anywhere. Could I have set this in a php somewhere? I recall the shipping module set min weight 3kg for parcelfroce, and max weight 3kg for royal mail, but it had nothing to do with order values in monetary terms.

 

For reference my site is http://www.africantribalarts.co.uk

 

Any advice would be much appreciated!!!!

 

Thanks,

Carrie

:blush:

[Guest]

anyone??? is anyone out there??? Can anyone, would anyone help?

I am building this site for a lady for free because she is so nice...but...I am very new to osc!!!

[Guest]

anyone??? is anyone out there??? Can anyone, would anyone help?

I am building this site for a lady for free because she is so nice...but...I am very new to osc!!!

Try google

oscommerce 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL

[Guest]

Try google

oscommerce 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL

 

Thank you for answering.

I have googled until I can google no more. Founds lots of different things, and tried allt he recommends I found. Prob is their error seems a little different to mine each time (the product_id part)

 

Any other ideas?

 

Thank you so much!

[Guest]

Thank you for answering.

I have googled until I can google no more. Founds lots of different things, and tried allt he recommends I found. Prob is their error seems a little different to mine each time (the product_id part)

 

Any other ideas?

 

Thank you so much!

Oh well, it was worth a try.

[MrPhil]

It's a bug somewhere in osC, where the item list that's supposed to be the parentheses is missing:

 

categories_id not in () should be categories_id not in (item1, item2, item3)

 

Search these forums for "in ()" and you'll find other mentions of the problem.

[satish]

not in ()

 

is creating this but.

 

You need to have a conditional chek before placing this query.

 

 

Satish

[Guest]

It's a bug somewhere in osC, where the item list that's supposed to be the parentheses is missing:

 

categories_id not in () should be categories_id not in (item1, item2, item3)

 

Search these forums for "in ()" and you'll find other mentions of the problem.

 

thank goodness I am not going crazy. Thanks!

OSC won't let me search less than 3 letter words so I can;t do the search you suggested. I am going to keep trying to find similar threads adn bug issues though to see how they can be resolved.

Thanks!

[Guest]

not in ()

 

is creating this but.

 

You need to have a conditional chek before placing this query.

 

 

Satish

 

What does this mean? How do I do this?

Pretty please???

[satish]

not in ()

 

should have been

not in (value1,value2....).

 

If the query didnt had any then not in should have been not taken as a part of code.

 

Satish

[MrPhil]

In other words, somewhat sloppy coding. Whoever wrote the code that generates the MySQL query seems to have assumed that there will always be at least one item in the list. I think what satish was saying is that the code should have checked if there was something in the list before spitting out the code that used the list. Otherwise, omit the list from the query. ...AND name IN ()... would simply be entirely omitted.

[Guest]

In other words, somewhat sloppy coding. Whoever wrote the code that generates the MySQL query seems to have assumed that there will always be at least one item in the list. I think what satish was saying is that the code should have checked if there was something in the list before spitting out the code that used the list. Otherwise, omit the list from the query. ...AND name IN ()... would simply be entirely omitted.

 

excellent, getting somewhere. Thanks for explaining to the PHP pleb!

So... I don't suppose you would know what php file this is in?

Or am I using up all my Santa gifts in one blow here?

Thanks!

[Guest]

like... for admin/categories I have

$check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . (int)$key . "' and categories_id not in (" . $category_ids . ")");

 

and...includes/modules/shipping/parcelforce48.php:

if ( MODULE_SHIPPING_PARCELFORCE48_PER_CATS_ONLY_OR_ANY == 'Only' ){

$check_query = tep_db_query('select * from '.TABLE_PRODUCTS_TO_CATEGORIES.' where categories_id not in ('.MODULE_SHIPPING_PARCELFORCE48_PER_CATS_CATEGORIES.') and products_id in ('.$pID_list.')');

if (tep_db_num_rows($check_query))

$enable_rates = 0;

 

and...basically the same thing for royal mail, first, second, etc...

 

So I am wondering, I installed this shipping module contribution. But...maybe there is a bug in that? Can you see from the code above where the bug might be?

THANKS

[MrPhil]

Well, I found 36 places where there is "name IN (list)" or "name NOT IN (list)" that I will need to add some guard code to. This is just the base 2.2 RC2a install, with no contributions or add-ons. Note that I have modified some code already, so lines may not match up exactly:

 

catalog/account_notifications.php: products_id NOT IN (" . implode(',', $products_parsed) . ")");

catalog/account_notifications.php: products_id NOT IN (" . implode(',', $products_parsed) . ")");

catalog/admin/categories.php: categories_id NOT IN (" . $category_ids . ")");

catalog/admin/includes/modules/newsletters/product_notification.php: " WHERE products_id IN (" . $ids . ")");

catalog/admin/includes/modules/newsletters/product_notification.php: pn.products_id IN (" . $ids . ")");

catalog/includes/boxes/order_history.php: " WHERE products_id IN (" . $product_ids . ") AND

catalog/includes/boxes/best_sellers.php: '" . (int)$current_category_id . "' IN (c.categories_id, c.parent_id)

catalog/includes/modules/order_total/ot_total.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/order_total/ot_loworderfee.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/order_total/ot_tax.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/order_total/ot_subtotal.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/order_total/ot_shipping.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/chronopay.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/cc.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/nochex.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/cod.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/worldpay_junior.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/authorizenet_cc_aim.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/authorizenet_cc_sim.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/moneyorder.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/payquake_cc.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/psigate.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/secpay.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/ipayment_cc.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/paypal_standard.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/pm2checkout.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/paypal_direct.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/paypal_express.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/paypal_uk_direct.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/paypal_uk_express.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/payment/sofortueberweisung_direct.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/shipping/flat.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/shipping/item.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/shipping/table.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/shipping/zones.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

catalog/includes/modules/shipping/usps.php: " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

[Guest]

wow!

Thank you.

I am going to work my way through this.

The contribution writer offered to look at my site for me but wanted all my passwords and, although I am trusting, it just seemed a bit too trusting so determined to sort this out myself and with your very kind help.