Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

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

Recommended Posts

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:

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

not in ()

 

is creating this but.

 

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

 

 

Satish

Ask/Skype for Free osCommerce value addon/SEO suggestion tips for your site.

 

Check My About US For who am I and what My company does.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Ask/Skype for Free osCommerce value addon/SEO suggestion tips for your site.

 

Check My About US For who am I and what My company does.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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()) . "')");

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...