dayreach Posted June 19, 2007 Share Posted June 19, 2007 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 More sharing options...
dayreach Posted June 20, 2007 Share Posted June 20, 2007 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 More sharing options...
charmdiva Posted June 23, 2007 Share Posted June 23, 2007 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 More sharing options...
Guest Posted June 23, 2007 Share Posted June 23, 2007 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 More sharing options...
charmdiva Posted June 23, 2007 Share Posted June 23, 2007 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 More sharing options...
dayreach Posted June 26, 2007 Share Posted June 26, 2007 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 More sharing options...
dayreach Posted June 28, 2007 Share Posted June 28, 2007 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 More sharing options...
rasikan Posted July 11, 2007 Share Posted July 11, 2007 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 More sharing options...
lenamtl Posted July 28, 2007 Share Posted July 28, 2007 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 More sharing options...
jaxxxk Posted August 1, 2007 Share Posted August 1, 2007 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 More sharing options...
Jack_mcs Posted August 1, 2007 Share Posted August 1, 2007 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. All of My Addons Get the latest versions of my addons Recommended SEO Addons Link to comment Share on other sites More sharing options...
jaxxxk Posted August 1, 2007 Share Posted August 1, 2007 thanks for your respons but that didnt work eigther?? any ideas, thanks again! :thumbsup: Link to comment Share on other sites More sharing options...
Jack_mcs Posted August 2, 2007 Share Posted August 2, 2007 Be sure you are editing the correct split_page_results.php file. There are two of them. Jack Support Links: For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc. All of My Addons Get the latest versions of my addons Recommended SEO Addons Link to comment Share on other sites More sharing options...
XxWickedxX Posted August 12, 2007 Share Posted August 12, 2007 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 More sharing options...
XxWickedxX Posted August 12, 2007 Share Posted August 12, 2007 For example.. The code fix shown here in this quote was already in my file.. You need to changeCODE $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 More sharing options...
XxWickedxX Posted August 12, 2007 Share Posted August 12, 2007 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 More sharing options...
XxWickedxX Posted August 12, 2007 Share Posted August 12, 2007 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 More sharing options...
XxWickedxX Posted August 16, 2007 Share Posted August 16, 2007 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 More sharing options...
XxWickedxX Posted August 23, 2007 Share Posted August 23, 2007 Ok my problem has been resolved.. Anyone else who was experiencing my same error message on the catalog can see this thread/post http://www.oscommerce.com/forums/index.php?s=&...t&p=1123382 This is for the 1064 error that references the geo zones and such. Link to comment Share on other sites More sharing options...
louva-a-Deus Posted August 23, 2007 Share Posted August 23, 2007 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 More sharing options...
kornel76 Posted August 25, 2007 Share Posted August 25, 2007 Hi. I had the same Problem with mysql 5.0.32. With your code works now fine!!! Thanks! Link to comment Share on other sites More sharing options...
MCBParts Posted September 5, 2007 Share Posted September 5, 2007 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 More sharing options...
hltrex Posted September 10, 2007 Share Posted September 10, 2007 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 More sharing options...
enigmabunny Posted September 24, 2007 Share Posted September 24, 2007 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 More sharing options...
enigmabunny Posted September 24, 2007 Share Posted September 24, 2007 ok, i sorted it, insert wasnt spelt right (obviously) Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.