Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

1064 - You have an error in your SQL syntax


Simplyeasier

Recommended Posts

HELP! I've been driving myself crazy trying to identify what it means when you get error 1064. The difference between what I'm reading here and my actual problem is that mine reads:

 

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 ("products_image_med", "products_image_lrg")

 

INSERT INTO products ( products_image, products_image_med, products_image_lrg, products_image_sm_1, products_image_xl_1, products_image_sm_2, products_image_xl_2, products_image_sm_3, products_image_xl_3, products_image_sm_4, products_image_xl_4, products_image_sm_5, products_image_xl_5, products_image_sm_6, products_image_xl_6, products_model, products_price, products_status, products_last_modified, products_date_added, products_date_available, products_tax_class_id, products_weight, products_quantity, manufacturers_id) VALUES ( 'image.jpg',big_imagemed.jpg", "raw_imagelrg.jpg", "", "", "", "", "", "", "", "", "", "", "", "", " 'model', '0', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, '1', '', '1', '218')

 

[TEP STOP]

 

Please can anyone help! I'm really going crazy.

Link to comment
Share on other sites

  • Replies 354
  • Created
  • Last Reply
HELP! I've been driving myself crazy trying to identify what it means when you get error 1064. The difference between what I'm reading here and my actual problem is that mine reads:

 

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 ("products_image_med", "products_image_lrg")

 

INSERT INTO products ( products_image, products_image_med, products_image_lrg, products_image_sm_1, products_image_xl_1, products_image_sm_2, products_image_xl_2, products_image_sm_3, products_image_xl_3, products_image_sm_4, products_image_xl_4, products_image_sm_5, products_image_xl_5, products_image_sm_6, products_image_xl_6, products_model, products_price, products_status, products_last_modified, products_date_added, products_date_available, products_tax_class_id, products_weight, products_quantity, manufacturers_id) VALUES ( 'image.jpg',big_imagemed.jpg", "raw_imagelrg.jpg", "", "", "", "", "", "", "", "", "", "", "", "", " 'model', '0', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, '1', '', '1', '218')

 

[TEP STOP]

 

Please can anyone help! I'm really going crazy.

 

Anyone have an idea as to what to do?

Link to comment
Share on other sites

I have a fresh install of the latest build. I haven't made any changes, still have the sample data. I am using mySQL 4.0.14. When entering a keyword into the search box, I get this error:

1064 - You have an error in your SQL syntax near 'distinct p.products_id) as total from products p left join manufacturers m usin' at line 1

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%rings%' or p.products_model like '%rings%' or m.manufacturers_name like '%rings%') )

[TEP STOP]

 

It looks like the split_page_results.php scripts have been fixed in this version. Any idea why I would be getting this error?

 

Thanks

Link to comment
Share on other sites

I have a fresh install of the latest build. I haven't made any changes, still have the sample data. I am using mySQL 4.0.14. When entering a keyword into the search box, I get this error:

1064 - You have an error in your SQL syntax near 'distinct p.products_id) as total from products p left join manufacturers m usin' at line 1

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%rings%' or p.products_model like '%rings%' or m.manufacturers_name like '%rings%') )

[TEP STOP]

 

It looks like the split_page_results.php scripts have been fixed in this version. Any idea why I would be getting this error?

 

Thanks

Could be a problem since the mysql5.x update with the later osc versions. There is some info here that is related.

http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html

 

See if using the older query line works in your case. You should have in your advanced_search_result.php this code:

 

  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

 

replac it with the old one is:

  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

and you need to comment out this line

  $from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

So is not getting duplicated. Does it make a difference when you change it? If so it may be necessary for these queries to change again, so they work on both mysql4.x and 5.x

 

Backup your files before changing anything.

Link to comment
Share on other sites

Thanks for the reply. I made the changes and got this error:

1064 - You have an error in your SQL syntax near 'distinct p.products_id) as total from products p left join manufacturers m usin' at line 1

 

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%amethyst%' or p.products_model like '%amethyst%' or m.manufacturers_name like '%amethyst%') )

 

[TEP STOP]

 

 

Could be a problem since the mysql5.x update with the later osc versions. There is some info here that is related.

http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html

 

See if using the older query line works in your case. You should have in your advanced_search_result.php this code:

 

  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

 

replac it with the old one is:

  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

and you need to comment out this line

  $from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

So is not getting duplicated. Does it make a difference when you change it? If so it may be necessary for these queries to change again, so they work on both mysql4.x and 5.x

 

Backup your files before changing anything.

Link to comment
Share on other sites

Hi I posted this last week and no progress yet...

HELP! I've been driving myself crazy trying to identify what it means when you get error 1064. The difference between what I'm reading here and my actual problem is that mine reads:

 

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 ("products_image_med", "products_image_lrg")

 

INSERT INTO products ( products_image, products_image_med, products_image_lrg, products_image_sm_1, products_image_xl_1, products_image_sm_2, products_image_xl_2, products_image_sm_3, products_image_xl_3, products_image_sm_4, products_image_xl_4, products_image_sm_5, products_image_xl_5, products_image_sm_6, products_image_xl_6, products_model, products_price, products_status, products_last_modified, products_date_added, products_date_available, products_tax_class_id, products_weight, products_quantity, manufacturers_id) VALUES ( 'image.jpg',big_imagemed.jpg", "raw_imagelrg.jpg", "", "", "", "", "", "", "", "", "", "", "", "", " 'model', '0', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, '1', '', '1', '218')

 

[TEP STOP]

 

Please can anyone help! I'm really going crazy.

 

Does it help to know that this is the fresh OSC with the multi-images contri. installed? I really need to get this running right away. Please Help Guys!

Link to comment
Share on other sites

What not even a suggestion? I posted this over a week ago and CharmDiva gets answered first (that's great but.... hey a little help here). Have I written my post incorrectly? Is it an unsolvable issue? Come on guys!

Link to comment
Share on other sites

  • 2 weeks later...

Hi Guys,

I have the same problem... I deleted a row in one of the tables and I noticed this error happening after this...

But my MySQL is version 5.0.27

 

Please help

 

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 p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.' at line 1

Link to comment
Share on other sites

  • 3 weeks later...

Hi,

 

I'm having similar issue after installed Imprint contribution

after modifying the product name of an already ordered product

 

admin side : edit order:

 

1064 - Erreur de syntaxe près de 'and pta.products_text_attributes_id = ota.products_text_attribut' à la ligne 1

 

select ota.*, pta.products_text_attributes_name from orders_text_attributes as ota, products_text_attributes as pta where ota.orders_id = 17 and ota.products_id = and pta.products_text_attributes_id = ota.products_text_attributes_id

 

 

but the solution

 

 if ($offset < 0)
{
$offset = 0;
}

 

is not solving the problem

 

any clue?

 

thanks

Lenamtl

Link to comment
Share on other sites

Hi I am getting the same 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 '-6, 6' at line 1

 

select geo_zone_id, geo_zone_name, geo_zone_description, last_modified, date_added from geo_zones order by geo_zone_name limit -6, 6

 

[TEP STOP]

 

I get it in the admin section when I click on links in my Localisation/Taxes secion and in the reports section. Should I be editing the "split_page_results" file? I have followed you instructions but I cant seem to get it fixed... The code you are instructing to replace varies slightly for me. On mine it reads:

 

$offset = ($this->number_of_rows_per_page * ($this->current_page_number - 1));

 

$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

 

Again is this right? Last note to say I am using an older version of OsCommerce as it is a template. Thanks in advance, luke

Link to comment
Share on other sites

Hi I am getting the same error:

I get it in the admin section when I click on links in my Localisation/Taxes secion and in the reports section. Should I be editing the "split_page_results" file? I have followed you instructions but I cant seem to get it fixed... The code you are instructing to replace varies slightly for me. On mine it reads:

Again is this right? Last note to say I am using an older version of OsCommerce as it is a template. Thanks in advance, luke

You need to change
$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

to

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

 

Jack

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

  • 2 weeks later...

I have tried every fix for this talked about in the past 3 years of conversation covered in this thread.. Nothing works. Went I went to make the changes for most the change was already there by default when I got my script I guess. I am going to have to figure this out myself or we may go for a 4th year of this problem still existing..

Link to comment
Share on other sites

For example.. The code fix shown here in this quote was already in my file..

 

 

You need to change

CODE

$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

to

CODE

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

Link to comment
Share on other sites

Let me be more specific actually and give credit where credit is due... The very first post in this thread by Charles fixed my admin perfectly. However the catalog portion has the same problem and none of the fixes mentioned in these 10 pages fix the catalog side of things as I have shown in above post.

 

Thanks for any pointers.. I am not real smooth with SQL or its functions... I can run tables and delete them but thats about it.

Link to comment
Share on other sites

thanks for your respons but that didnt work eigther?? any ideas,

 

thanks again! :thumbsup:

 

 

Ok here is what I changed my /admin/includes/classes/split_page_results.php file to and the admin works great now.

 

$offset = ($max_rows_per_page * ($current_page_number - 1));
if ($offset < 0)
{
$offset = 0;
}
$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

 

However I am still not able to fix the catalog side of the house. /includes/classes/split_page_results.php

 

I still get 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 'or z2g.zone_id = 0 or z2g.zone_id IS NULL )

 

SELECT dc2z.geo_zone_id FROM discount_coupons_to_zones dc2z LEFT JOIN zones_to_geo_zones z2g USING( geo_zone_id ) WHERE ( z2g.zone_id= or z2g.zone_id = 0 or z2g.zone_id IS NULL ) AND ( z2g.zone_country_id= or z2g.zone_country_id = 0 ) AND dc2z.coupons_id="TEST3"

 

[TEP STOP]

 

I have tried the fix that OScommerce released to fix this in the bug report and it does not work.. I have tried every proposed fix in this entire thread for the catalog side and nothing works... I just don't know what to do for it..

Link to comment
Share on other sites

I have posted in this thread, the thread for the discount coupon codes contribution, and in the oscommerce bug report system and have had no attempts at a suggestion.. This is like pulling teeth and the Novocaine is starting to wear off.. Hope to hear something soon.. I have no experience at all with this type of error or how to fix it myself.. I tried though.

Link to comment
Share on other sites

Please, I tried to apply the mentioned corrections. The error message disappeared but, the customers' requests are not being exhibited.

 

 

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 '-20, 20' at line 1

select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '4' and s.orders_status_id = '1' and ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20

[TEP STOP]

Link to comment
Share on other sites

  • 2 weeks later...

Right... so oscommerce has frozen up on me, an error keeps me from importing

 

 

The error that I'm having when I try to import products:

 

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 ' / UPDATE categories_description SET categories_name = 'Parts' WHERE categories' at line 1

Link to comment
Share on other sites

I have same problem :

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 'order by pd.products_name limit 20, 20' at line 1

 

select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '24' order by order by pd.products_name limit 20, 20

 

But your solve can not fix it. you can help me again

 

Thks.

Link to comment
Share on other sites

  • 2 weeks later...

hey guys, so im having the same problem too, i just tried amending a customers order by adding a product, and when i click add i get the 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 'in sert into orders_products_attributes set orders_id = 69, orders_p' at line 1

 

in sert into orders_products_attributes set orders_id = 69, orders_products_id = 156, products_options = '0', products_options_values = '0', options_values_price = '0', price_prefix = '0'

 

[TEP STOP]

 

ive looked through this topic but still cant get it to work, any ideas anyone?

 

cheers

 

Eddie

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...