Jump to content

Archived

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

Simplyeasier

1064 - You have an error in your SQL syntax

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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
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!

Share this post


Link to post
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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Be sure you are editing the correct split_page_results.php file. There are two of them.

 

Jack

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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;

Share this post


Link to post
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.

Share this post


Link to post
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..

Share this post


Link to post
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.

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites

Hi.

I had the same Problem with mysql 5.0.32. With your code works now fine!!!

Thanks!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×