Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

1064 - You have an error in your SQL syntax


Simplyeasier

Recommended Posts

Hi There, Please help I am having kittens here...

 

I have done a very stupid thing whilst trying to figure out easy populate - I think I have managed to delete all my products tables - and what this means is that I can no longer access the admin panel in the browser, instead I get the error message:

 

1146 - Table 'cmm_com_au_-_osc6.configuration' doesn't exist

 

select configuration_key as cfgKey, configuration_value as cfgValue from configuration

 

[TEP STOP]

 

I am running on MySQL 3.23.58

 

I contacted my hosting company and they are pretty useless and offer no support at all - they did suggest however to go to MyphpAdmin and select the database->SQL and then browse for the backup file and upload which I did and I get this error in Myphp after the upload

 

SQL-query:

 

-- MySQL dump 8.23

--

-- Host: localhost Database: cmm_com_au_-_osc6

------------------------------------------------------- --

-- Server version 3.23.58

--

-- Table structure for table `address_book`

--

DROP TABLE IF EXISTS address_book

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax near '---------------------------------------------------------

-- Server version 3.23' at line 4

 

If anyone could please help me out of this mess I will be eternally grateful... I did back up, but somehow managed to destroy the database anyway... :(

 

 

I fixed it - Phew!

 

The error that was appearing (MySQL said: Documentation

#1064 - You have an error in your SQL syntax near '---------------------------------------------------------

-- Server version 3.23' at line 4) led me to look into the backup SQL file that I had saved and I also opened an earlier saved version and I noticed there was a difference between the two.

 

The backup file that would not work was like this:

 

-- MySQL dump 8.23---- Host: localhost Database: cmm_com_au_-_osc6 ----------------------------------------------------------- Server version 3.23.58----

 

an earlier version looked different so I changed it to look the same i.e.

 

-- MySQL dump 8.23---- Host: localhost ##Database: cmm_com_au_-_osc6# Server version 3.23.58----

 

The difference was the # sign before and after and getting rid of the ------------------ not sure which bit fixed it, but it now works

 

I re-uploaded it and hey presto it worked!! Yippppeeeee :D

Link to comment
Share on other sites

  • Replies 354
  • Created
  • Last Reply
I fixed it - Phew!

 

The error that was appearing (MySQL said: Documentation

#1064 - You have an error in your SQL syntax near '---------------------------------------------------------

-- Server version 3.23' at line 4) led me to look into the backup SQL file that I had saved and I also opened an earlier saved version and I noticed there was a difference between the two.

 

The backup file that would not work was like this:

 

-- MySQL dump 8.23---- Host: localhost Database: cmm_com_au_-_osc6 ----------------------------------------------------------- Server version 3.23.58----

 

an earlier version looked different so I changed it to look the same i.e.

 

-- MySQL dump 8.23---- Host: localhost ##Database: cmm_com_au_-_osc6# Server version 3.23.58----

 

The difference was the # sign before and after and getting rid of the ------------------ not sure which bit fixed it, but it now works

 

I re-uploaded it and hey presto it worked!! Yippppeeeee :D

:thumbsup:

Link to comment
Share on other sites

A noob having a similiar problem, although mine isn?t because of mysql 4.xxx since I?m running 3.23.58-

I was installing a contribution and after altering the DB i got this message:

 

----------------------------------------------------------------

1064 - You have an error in your SQL syntax near ' p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, ' at line 1

 

select p.products_image, pd.products_name, pd.products_description,, 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, products_to_categories p2c left join specials s on p.products_id = s.products_id 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 = '59' order by pd.products_name

 

[TEP STOP]

---------------------------------------------------------------

 

Does anyone know what is wrong? I need to fix this fast cos im knee deep in it now that the site is down :-(

I?m afraid of doing anything before consulting some pro help here...

Link to comment
Share on other sites

Referring to my post above. When I replaced the edited files with the backups, somehow it never happened. Don?t know why but I tried to replace the files with the backed upped ones and this time the site got working again. So, this time I managed not to screw it up... *pheewww*

 

It?s not fun to explain to the boss that we ain?t doing business on the web today cos I messed it up and have no clue on how to fix it :-D

Link to comment
Share on other sites

Hi! for me, problems were instantly solved apllying this solution.

Thank you, Charles!

 

Andr?s.

 

 

To fix this

 

In admin/includes/classes find split_page_results.php and - BACK IT UP then find the lines

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

change to

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

if ($offset < 0)

{

$offset = 0 ;

}

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

 

hth

 

Charles

Link to comment
Share on other sites

NOTE - I have NOT asked you to change catalog/includes/classes/split_page_results.php - If you have a problem on the catalog side you may want to try the same change in that file - I did and it went wrong so the change to my catalog/includes/classes/split_page_results.php file is a little more elaborate and I will share that here if it becomes an issue - but for now it seems people will mostly have a problem with empty files on the admin side.

 

Charles, I have this problem in the catalog. could you please send your more complicated fixes at this point?

Link to comment
Share on other sites

Simplyeasier IS THE MASTA! WOW! How the hell did you figure that out? I have know Idea how. That was amazing. Literally. I deleted the tax zones default of Florida (as the tutorial I was using recommended) and got that freaky ass error. I googled it and PRESTO! Thanks for saving me boatloads of stress O great wise one. Keep it up!!!!!

 

http://www.notfarnow.com

Link to comment
Share on other sites

  • 2 weeks later...

HELP PLEASE! I am having this error and don't know what to do.

 

 

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

 

select count(p.products_id) as total order by pd.products_name

Link to comment
Share on other sites

Hi there,

 

I tried the above but am still having the following error message: (after installation of contribution purchase without account)

 

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 'customers_id, select customers_name, customers_company, custome

 

customers_id, select customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, currency, currency_value, date_purchased, orders_status, last_modified, cc_start, cc_issue, cc_cvv from orders where orders_id = '29'

 

can anybody help me fixing it? Will send you a box of sweets :)

Link to comment
Share on other sites

  • 1 month later...

mySQL query error: SELECT * FROM ibf_skin_templates_cache WHERE template_set_id= AND template_group_name IN ('skin_global', 'skin_shoutbox')

 

SQL error: 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 template_group_name IN ('skin_global', 'skin_shoutbox')' at

SQL error code:

Date: Saturday 18th of February 2006 03:08:38 AM

 

Apache version 1.3.34 (Unix)

 

PERL version 5.8.7

 

Path to PERL /usr/bin/perl

 

Path to sendmail /usr/sbin/sendmail

 

Installed Perl Modules Click to View

 

PHP version 4.4.1

 

MySQL version 4.0.25-standard-log

 

cPanel Build 10.8.1-STABLE 114

 

Theme cPanel X v2.5.0

 

plz help help help my site is going down plz guys !!

Link to comment
Share on other sites

Hi guys, I really need help now...

 

I installed CCC 9.3, works fine exept for a small detail, when I click on the link in the shopping cart infobox after the creation of a build it sends me the following error.

 

[sTART CODE]

 

 

1064 - You have an error in your SQL syntax near '' at line 1

 

select p.products_id from products p, products_to_categories ptc where p.products_status = '1' and p.products_id = ptc.products_id and ptc.categories_id =

 

[TEP STOP]

 

 

[END CODE] Any ideas that could have me go in the right direction, also, I think this causes to not have the builds show in the pendings on the admin side...

 

Thanks to all

Link to comment
Share on other sites

Hi

 

First off - this fix is actually already recorded in the contributions section and many thanks to those who recorded it - but I don't think it is a contribution - rather a tip - Secondly the same problem has arisen for 4 people in a few days and I dare say it will happen to more as hosts migrate their mySQL databases to version 4.1.xx -

 

Hence I post here :D

 

If you get an error whilst creating \ modifying your database such that thro admin you delete all entries on a particular table you will likely get this 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 '-20, 20' at line 1

 

followed by something like

 

select manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified from manufacturers order by manufacturers_name limit -20, 20

 

or

 

select banners_id, banners_title, banners_image, banners_group, status, expires_date, expires_impressions, date_status_change, date_scheduled, date_added from banners order by banners_title, banners_group limit -20, 20

 

depending on which table you have deleted all records from.

 

To fix this

 

In admin/includes/classes find split_page_results.php and - BACK IT UP then find the lines

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

 

change to

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

if ($offset < 0)

{

$offset = 0 ;

}

$sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

MySQL 4.1.xx handles negatives correctly (by forcing an error) in the code above unlike earlier versions of MySQL.

 

:o NOTE - I have NOT asked you to change catalog/includes/classes/split_page_results.php - If you have a problem on the catalog side you may want to try the same change in that file - I did and it went wrong so the change to my catalog/includes/classes/split_page_results.php file is a little more elaborate and I will share that here if it becomes an issue - but for now it seems people will mostly have a problem with empty files on the admin side.

 

hth

 

Charles

 

Thanks Charles - I tried what you asked me to do, but I cannot edit the file at all. I was able to copy what you wrote, but not paste or cut. I did back up the original file.

 

I am not a programmer. All I did was to open the file and tried to modify it. Is there something else I am supposed to do? The file properties were set for read, write and execute.

 

 

Debi

Link to comment
Share on other sites

Charles:

 

I tried what you told me to do, but I cannot edit the file at all. I checked the properties of the file, and it is set for write, read and execute. I cannot cut, paste or type in the file. I did back the file up.

 

I am not a programmer. All I did was to open the file. Is there something else, I am supposed to do?

 

Debi :huh:

Link to comment
Share on other sites

1064 - You have an error in your SQL syntax near '' at line 1

 

select p.products_id from products p, products_to_categories ptc where p.products_status = '1' and p.products_id = ptc.products_id and ptc.categories_id =

 

 

 

Anybody has an idea!!!!

Link to comment
Share on other sites

Hi,

when i create a system with CCC 9.3 it goes to the cart, than when I press on the shopping cart box link it sends me a error message like so:

 

 

1064 - You have an error in your SQL syntax near '' at line 1

 

select p.products_id from products p, products_to_categories ptc where p.products_status = '1' and p.products_id = ptc.products_id and ptc.categories_id =

 

[TEP STOP]

 

[code stop]

 

Any ideas before I submit my own revemped version of CCC.

Link to comment
Share on other sites

Charles,

 

Thank you so much. This fix worked on the first try. I had 4 1064 errors and it fixed them all.

 

Again, Thank you for helping the less knowledgable users work through problems!

 

Gidget

Link to comment
Share on other sites

It worked for me straight up from the initial posting by "Simplyeasier"

Thank you soo much.

 

It seemed that my hosting provider upgraded their SQL without notifying me and when I checked I had that dreaded "1064 - You have an error in your SQL syntax" error.

 

Everything works fine now.

Link to comment
Share on other sites

Hello,

 

I have a little problem with Shipping modules. If I install a shipping module in the admin panel, I received the following messages :

 

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 'True', 'False'), ', now())' at line 1

 

insert into configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) values ('Enable FLAT Shipping', 'MODULE_SHIPPING_FLAT_STATUS', 'True', 'Do you want to offer FLAT rate shipping?', '6', '0', 'tep_cfg_select_option(array('True', 'False'), ', now())

 

[TEP STOP]

 

Any help?

Thanks...

Link to comment
Share on other sites

Hi guys, I've been reading through the post and have a smiliar problem... On my catalogue side of things (on the index.php page), I am getting the following error (instead of a list of products):

 

1064 - You have an error in your SQL syntax near '-_oscommerce' at line 1

 

show table status from domainname_com_au_-_oscommerce

 

[TEP STOP]

 

Please please please does anyone know what's happening?

 

Thanks, Barbara

Link to comment
Share on other sites

Hi, thanks for posting this, unfortunately it did not fix my problem. =(

 

Here is what I am getting on catalog/account_history.php

 

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

 

select o.orders_id, o.date_purchased, o.delivery_name, o.billing_name, ot.text as order_total, s.orders_status_name from orders o, orders_total ot, orders_status s where o.customers_id = '20' and o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '1' order by orders_id DESC limit -10, 10

 

I did just change my orders.php but I had to in order to get the Cash/Check orders to show up in my admin section again.

 

Help!

 

Here is my /includes/classes/split_page code

 

  $offset = ($this->number_of_rows_per_page * ($this->current_page_number - 1));
 // begin make sure that $offset is not lower than 0
 if ($offset < 0 ) {
  $offset = 0;
 }
 // end make sure that $offset is not lower than 0
 $this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

 

MECHBUN,

Did you ever get this working properly? I am having the EXACT same issue/error and can't get it resolved.

 

If no orders were made it appears fine. Once an order is placed I get the 1064 error in the "order history" page.

 

I added the code to the admin/includes/class/split...but not sure what to do now to get it totaly resolved.

 

What exactly did you do?

 

Thanks!

~Mark

Link to comment
Share on other sites

Ok, got everything working now. wheeeewww....

 

The only thing is now my customer "order history" has -4, -3, -2, -1, 0 results pages.

 

I KNOW I saw this when I was looking through the 10,000 threads today but can't find it now.

 

~M

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...