Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

1064 - You have an error in your SQL syntax


Simplyeasier

Recommended Posts

I don't know where else to go with this... :(

after a second look - think you may have an error in your code

 

 look at:

1064 .... syntax to use near ') left 

and at bottom of error message

products p) left join manufacturers

 

is that ")" supposed to be there?

Link to comment
Share on other sites

  • Replies 354
  • Created
  • Last Reply

I was getting the following 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 '?osCsid=f137a9a63cc07a7c3271394cd0810d8e) and pd.products_id = p.products_id and' at line 1

select count(*) as total from products p, products_description pd where p.products_status = '1' and p.products_id in (22,27,2?osCsid=f137a9a63cc07a7c3271394cd0810d8e) and pd.products_id = p.products_id and pd.language_id = '1'

[TEP STOP]

The error came when someone clicked GoogleCkeckout > and were taken to Google to complete an order > after order complete there is a link to return to site > when you click that link > above error 1064

 

The Fix: In GoogleCheckout use http: Callback Protocol for sandbox testing, the return to site/ continue shopping will not work using a shared ssl certificate and https: such as below, in configure.php files

 

define('HTTPS_CATALOG_SERVER', 'https://secure.yourHost.com/~userName');

 

This fix is not related to the topic of this thread... except it is a 1064 mySQL error

Link to comment
Share on other sites

I am getting this error in my customers table and since the person that designed my website has quit I am trying to figure out this OS Commerce. I printed this topic and will go thru and rry to understand directions but wanted to make sure this information is still accurage.

 

My error message is 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) left join address_book a on c.customers_id = a.customers_i.

 

I just recently moved from ipower.com to readyhosting.com but this error was on the old server also.

 

Appreciate any assistance.

Link to comment
Share on other sites

I am getting this error in my customers table ...Appreciate any assistance.

I feel like I've been trapped in the Emperor's chamber since I got my 1064 error... but couldn't help but notice it looks like you "might" have an error in your code...

My error message is 1064 ....

c.customers_id = a.customers_i.

should be?

c.customers_id = a.customers_id

 

Hope this helps...I'm not very good solving mySQL errors

Link to comment
Share on other sites

  • 2 weeks later...
I feel like I've been trapped in the Emperor's chamber since I got my 1064 error... but couldn't help but notice it looks like you "might" have an error in your code...

My error message is 1064 ....

c.customers_id = a.customers_i.

should be?

c.customers_id = a.customers_id

 

Hope this helps...I'm not very good solving mySQL errors

 

 

This is the entire page from the customers section of the Admin. Can anyone give me detail info on how to fix or a reasonable price to fix for me? I don't understand how this works.

select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, c.customers_group_id, c.customers_group_ra, a.entry_country_id, a.entry_company, cg.customers_group_name from customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id left join customers_groups cg on c.customers_group_id = cg.customers_group_id order by c.customers_id DESC limit 0, 20

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 ) left join address_book a on c.customers_id = a.customers_i

 

select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, c.customers_group_id, c.customers_group_ra, a.entry_country_id, a.entry_company, cg.customers_group_name from ( customers c ,20 ) left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id left join customers_groups cg on c.customers_group_id = cg.customers_group_id order by c.customers_id DESC limit 0

 

[TEP STOP]

Link to comment
Share on other sites

This is the entire page from the customers section of the Admin

This is the error message you're getting in admin? - the underlying page is admin/customers.php. You need to download this page via FTP if you don't have it on your local computer, then open it in a text editor. Then do a page search for / or find every instance in the code of:

 

a.customers_i

 

Check every instance on the page, there are several, and correct any to read:

 

a.customers_id

 

Then upload the page/file back to your site... If this does not fix it, or you cannot find any errors, you should try the fix that is the topic of this thread:

 

http://www.oscommerce.com/forums/index.php?s=&...st&p=583567

 

Hope this helps...I am not an expert in mySQL, so maybe someone that is can help?

Link to comment
Share on other sites

  • 3 weeks later...

Amazingly....I read through all 16 pages. I tried a few edits, and pretty much found my split page files to have already been modified for the changes indicated.

 

I still have a problem

 

I get the error below...whenever I attempt to edit a product from the admin page.

 

Everything else works pretty much perfectly....just 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 'where p.products_id = '63' and p.products_id = pd.products_id and pd.language_id' at line 1

select pd.products_name, pd.products_description, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id , p.products_ship_sepproducts p, products_description pd where p.products_id = '63' and p.products_id = pd.products_id and pd.language_id = '1'

[TEP STOP]

 

any help is appeciated.

~man....I'm such a noob.

Link to comment
Share on other sites

Amazingly....I read through all 16 pages. I tried a few edits, and pretty much found my split page files to have already been modified for the changes indicated.

 

I still have a problem

 

I get the error below...whenever I attempt to edit a product from the admin page.

 

Everything else works pretty much perfectly....just 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 'where p.products_id = '63' and p.products_id = pd.products_id and pd.language_id' at line 1

That's because the syntax is wrong. It says:

 bla, bla p.products_ship_sepproducts p, products_description pd where bla, bla

where it should say:

 bla, bla p.products_ship_sepproducts p, p.perhaps_another_field from products p, products_description pd where bla, bla

Probably you messed up something adding a contribution?

Link to comment
Share on other sites

If you say so,....I won't doubt it.

 

Thanks...I'm going to try and fix all of those this morning, and will report back here so others can see the solution also.

 

Thanks.

~man....I'm such a noob.

Link to comment
Share on other sites

I haven't been able to figure out how to apply that fix.

 

Here's one area I think might be a problem....dunno:

$options = tep_db_query("select * from " . TABLE_PRODUCTS_OPTIONS . " where language_id = '" . $languages_id . "' order by products_options_name");
     while($options_values = tep_db_fetch_array($options)) {
       if ($attributes_values['options_id'] == $options_values['products_options_id']) {
         echo "\n" . '<option name="' . $options_values['products_options_name'] . '" value="' . $options_values['products_options_id'] . '" SELECTED>' . $options_values['products_options_name'] . '</option>';
       } else {
         echo "\n" . '<option name="' . $options_values['products_options_name'] . '" value="' . $options_values['products_options_id'] . '">' . $options_values['products_options_name'] . '</option>';
       }
     } 

~man....I'm such a noob.

Link to comment
Share on other sites

Here's one area I think might be a problem....dunno:

Nope, not it.

 

I would look for pieces of code that contain the text p.products_ship_sepproducts and where the query stats with select

Link to comment
Share on other sites

  • 3 weeks later...

Hi all,

 

I have tried a few of the fixes here but keep getting 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 ') and products_id in (74)' at line 1

 

select * from products_to_categories where categories_id not in () and products_id in (74)

 

[TEP STOP]

 

Please help I was hoping to make my store live this week :(

Link to comment
Share on other sites

  • 2 months later...

Dear,

 

I get the following error on the coupon page in my admin:

 

1054 - Unknown column 'cd.coupons_date_end' in 'order clause'

 

select * from discount_coupons cd order by cd.coupons_date_end, coupons_date_start limit 0, 20

 

[TEP STOP]

 

Who knows how I can change this, so that the coupons can start working?

I had an older version of the discount coupon contribution which I had changed manualle with alle the files 3.34

Thanks for helping me out!

 

Regards,

Vicky

Link to comment
Share on other sites

hey everyone,

 

i have am new to this, and i have very little experience in coding, but i puschased a template from a website. my webpage is ind.mflzone.com/osc

 

i have been getting several syntax errors, specially the 1064 when i click on "specials"

 

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

 

select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added DESC limit -9, 9

 

can anyone help me out with this? i would really really really appreciate it.

 

i also get a 1054 error when i click on my categories or use the search button.

 

 

Products meeting the search criteria

1054 - Unknown column 'p.products_id' in 'on clause'

 

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 '%hi%' or p.products_model like '%hi%' or m.manufacturers_name like '%hi%') )

 

 

please help :(

Link to comment
Share on other sites

'-20, 20' this (negative limit) is not allowed in mysql 5. Apply mysql 5 patches.

 

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

  • 2 months later...

Hello,

 

i tried all fixes posted in the entire thread, including that one:

 

nsert:

 

if ($offset < 0)

{

$offset = 0 ;

}

 

Just before this line:

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

 

My code section in split_page_results.php looks currently like that:

 

if ($this->current_page_number > $this->number_of_pages) {

$this->current_page_number = $this->number_of_pages;

}

 

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

 

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

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

}

 

My errors look like that trying to open any sub category:

 

Warning: Division by zero in /home/xxxxx/public_html/europe/includes/classes/split_page_results.php on line 59

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

 

select p.products_quantity, p.products_model, pd.products_name, p.products_image, 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 = '2' and p2c.categories_id = '34' order by pd.products_name limit -0, MAX_DISPLAY_SEARCH_RESULTS

 

Or like that trying to open a main category:

 

Warning: Division by zero in /home/xxxxx/public_html/europe/index.php on line 129

 

Warning: Division by zero in /home/xxxxxx/public_html/europe/index.php on line 131

 

Warning: Division by zero in /home/xxxxxxx/public_html/europe/index.php on line 131

 

Warning: Division by zero in /home/xxxxxxx/public_html/europe/index.php on line 129

 

here a copy of that code fragment in index.php from line 128 to line 132:

 

$cPath_new = tep_get_path($categories['categories_id']);

$width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%';

echo ' <td align="center" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">' . tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) . '<br>' . $categories['categories_name'] . '</a></td>' . "\n";

if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != $number_of_categories)) {

echo ' </tr>' . "\n";

 

Can anybody help me to sort this out?

 

Your help would be greatly appreciated.

Link to comment
Share on other sites

Ok for me the problem is clear. My provider moved the database to a new server. Many values in various SQL tables are missing or even empty, causing that error mentioned earlier.

 

Sadly they were working on that issue for hours, or better it looked like that.

After that they were telling me to check my PHP files so i trusted their diagnosis, what a mistake.

 

So if you encounter that problem and no PHP fixes seem to work, check your configuration table first. In my case letters in various words, code sequences were missing.

Link to comment
Share on other sites

  • 2 weeks later...

Please help.

I installed a contribution "Product Lead Time" by modifying the required files by adding only the needed language. I also ran the followng SQL query

 

ALTER TABLE `products` ADD `products_lead_time` INT NOT NULL DEFAULT '0';

 

Before running the query I backed up my database.

 

I receved a syntax 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 'p, products_description pd where p.products_id = '328' and p.products_id = pd.pr' at line 1

 

I could not determine how to remedy this problem so I restored my files back to the way they were. Now I am still getting a syntax error as follows:

 

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 'p, products_description pd where p.products_id = '328' and p.products_id = pd.pr' at line 1

 

select pd.products_name, pd.products_description, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, products_length, products_width, products_height, products_ready_to_ship, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id fromproducts p, products_description pd where p.products_id = '328' and p.products_id = pd.products_id and pd.language_id = '1'

 

I am out of options as I have examined my files closely and they are back to the status they were in before I ran the SQL query and changed the files. I have also conducted a restore on my database.

 

Any help will be appreciated. My store address is www.americanfhb.com

You can email me if needed at [email protected]

 

Thanks

Link to comment
Share on other sites

Seems my Admin side is now working but the live store side is not???

Still trying to figure it out. Any ideas from anyone?

 

Please help.

I installed a contribution "Product Lead Time" by modifying the required files by adding only the needed language. I also ran the followng SQL query

 

ALTER TABLE `products` ADD `products_lead_time` INT NOT NULL DEFAULT '0';

 

Before running the query I backed up my database.

 

I receved a syntax 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 'p, products_description pd where p.products_id = '328' and p.products_id = pd.pr' at line 1

 

I could not determine how to remedy this problem so I restored my files back to the way they were. Now I am still getting a syntax error as follows:

 

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 'p, products_description pd where p.products_id = '328' and p.products_id = pd.pr' at line 1

 

select pd.products_name, pd.products_description, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, products_length, products_width, products_height, products_ready_to_ship, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id fromproducts p, products_description pd where p.products_id = '328' and p.products_id = pd.products_id and pd.language_id = '1'

 

I am out of options as I have examined my files closely and they are back to the status they were in before I ran the SQL query and changed the files. I have also conducted a restore on my database.

 

Any help will be appreciated. My store address is www.americanfhb.com

You can email me if needed at [email protected]

 

Thanks

Link to comment
Share on other sites

Well I fixed it! I noticed a post from someone else that mentioned removing 'white' spaces between coding in the files. Seems when I was adding the contribution I added spaces between some of the lines to help me see the text. I removed all of the blank lines and that made it work. PHP sure is particular! Whew!

 

 

Seems my Admin side is now working but the live store side is not???

Still trying to figure it out. Any ideas from anyone?

Link to comment
Share on other sites

Excuse my english i will explain in spanish.

 

Para mi la solucion a ese error fue editar la base de datos en mysql.

1. Entre a "mysql" mediante "phpadmin"

2. Click en "manufacturers"

3. Click en el tab que dise "insert"

4. En "manufacturers_id" puse (1) y en "manufacturers_name" puse una letra (a)

5. y eso fue todo... luego fui al panel de administracion de oscommerce y edite la informacion del manufacturero.

 

Esto tambien funciona si usted borro todas las categorias en el panel de administracion de oscommerce y le sale este error. (1064 - You have an error in your SQL syntax). Añadir valor en la base de datos produce que oscommerce pueda leer de la base de datos y no produsca un error.

 

(editar el .php puede producir una inestabilidad en su pagina).

 

(http://www.internetnets.com)

webmaster

Link to comment
Share on other sites

  • 1 month later...

Well I fixed it! I noticed a post from someone else that mentioned removing 'white' spaces between coding in the files. Seems when I was adding the contribution I added spaces between some of the lines to help me see the text. I removed all of the blank lines and that made it work. PHP sure is particular! Whew!

The only place where php cannot tolerate a white line is the first line of a file, IIRC.

 

 

In your sql above that is throwing the error, I see this word: fromproducts

 

php really *needs* the whitespace there...

Community Bootstrap Edition, Edge

 

Avoid the most asked question. See How to Secure My Site and How do I...?

Link to comment
Share on other sites

  • 2 weeks later...

Hi everyone,

 

I'm having this error only with one page checkout when a returning customer logs in and tries to buy something.

 

 

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 'where customer_id = '3'' at line 1

 

select amount from where customer_id = '3'

 

 

Sorry to double post but I haven't seen anyone with this exact problem anywhere and it appears to be some database issue outside the scope of that forum. I'm using mySQL 5.1.30 and the split page results fix at the beginning of this forum has already been applied. I triple checked the installation of one page checkout I did. Could this be some strange conflict with purchase without account even though they're supposed to work together? Any ideas would be much appreciated.

 

Thanks,

 

Nick

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...