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

select distinct p.products_id, p.products_image, p.products_model, p.products_quantity, p.products_weight, pd.products_name, p.manufacturers_id, p.products_price, p.products_date_added, 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, m.manufacturers_name, pd.products_description, cd.categories_name from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join categories_description cd on p2c.categories_id = cd.categories_id 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 cd.language_id = '1' order by cd.categories_name DESC, pd.products_name LIMIT -10, 10

 

i getting this error on my catalog side when Page Cache is enabled , if i loged in (Page Cache disabled) , so everything fine

 

i tried all suggestions from this thread and more , but still having trouble.

Please , if somebody have any idea what is causing this problem ...

Thank You !

Link to comment
Share on other sites

  • Replies 354
  • Created
  • Last Reply

Ok I am having this problem on the catalog side.

 

I applied the bug fix from http://www.oscommerce.com/community/bugs,1605

on both my admin/includes/classes/split_page_results.php

the admin works great

 

 

and /includes/classes/split_page_results.php

this is from the catalog side

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

if ($offset < 0)

{

$offset = 0 ;

}

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

 

 

 

 

and /includes/classes/split_page_results.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 'where lc.link_categories_id = lcd.link_categories_id and lc.link_categories_stat' at line 1

select lc.link_categories_id, lcd.link_categories_name, lcd.link_categories_description,peacem_link_categories lc, peacem_link_categories_description lcd where lc.link_categories_id = lcd.link_categories_id and lc.link_categories_status = '1' and lcd.language_id = '1' order by lcd.link_categories_name

[TEP STOP]

 

 

I even added a link to the database and still get this error.

please help if you can.

 

I added this to a different program and didn't have thiis problem

Link to comment
Share on other sites

I have tried the fix proposed here, but I still have error. I' 'using sort order by date on clients, and the problem shows itself when I try to do a search (search box) of any client.

 

MY VERSION IS 4.0.22-standard

 

Clientes Buscar:

 

 

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 c.customers_lastname like '%lero%' or c.customers_firstna

 

select count(*) as total from customers c, address_book a, customers_info ci where c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id and c.customers_id = ci.customers_info_id where c.customers_lastname like '%lero%' or c.customers_firstname like '%lero%' or c.customers_email_address like '%lero%'

 

THANKS FOR YOUR HELP

Link to comment
Share on other sites

I fixed it thanks!

 

The fix for this is:

 

Edit these two files:

admin/includes/classes/split_page_results.php

/includes/classes/split_page_results.php

 

Insert:

 

if ($offset < 0)

{

$offset = 0 ;

}

 

Just before this line:

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

 

Thanks a lot for the fix .... :thumbsup: :thumbsup: :thumbsup:

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;

Link to comment
Share on other sites

Ah! I am getting closer!! Now the account history page comes up properly, however, no orders are actually show in there. I made some new orders but they just wont show up in the account_history.php.

 

I'll be working on it too, but any help would be appreciated, thanks!

Link to comment
Share on other sites

  • 2 weeks later...

I am having the same problem, but running PHP 4.4.0 - I have changed the code as suggested with no luck.

 

Can anyone suggest where I need to look to fix this? Is there something I need to alter within the database itself?

 

Cheers,

Richard

Cheers,

Richard

Link to comment
Share on other sites

Thanks very much for posting this tip. I noticed the problem when I was installing a contribution and thought that was the cause.

 

I was losing all hope of fixing it after replacing the original files and restoring the database.

 

It turned out to be that the hosts had updated SQL and it just happened to coincide with me installing my contribution.

 

Thanks again!

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

 

Fifty Cheers.

Went to PhP 5 MySql 4.1+ yesterday night.

Got the described 1064, this morning when checking customer/orders.

1 search for 1064, your solution, applied, works.

Yes YOU ARE a CHAMP !

thanks

 

Walter Kempees (NL 2.2MS2)

(I am now going to search why some have such a nice Admin interface with icons and colors and stuff, and mine is plain grey)

:thumbsup:

Link to comment
Share on other sites

Cool. On the right track now. I did the fix on Post #1 here.

 

The problem I was having was in my "Specials" admin page. I did your fix and now I can use my Specials admin. Now got a new issue: On the client side, the specials.php page displays the total number of products -1. SO one less than it should. Is there another file I need modify?

 

Colin

Link to comment
Share on other sites

Whatever I do, I keep getting

 

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 ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20

 

 

And I'm getting a little nerveous :-)

 

I don't think that there is anything I didn't try. Is there a way that someone would post the complete altered working files? Or is there a way to excude this LIMIT thing? Because I want to have the site running... It has been weeks now that I'm trying to get this fixed...

Link to comment
Share on other sites

BTW I emptied split_page results in admin... and I keep getting the same error ?!

 

I wonder then how the error can be in that file?

Link to comment
Share on other sites

Whatever I do, I keep getting

 

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 ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20

And I'm getting a little nerveous :-)

 

I don't think that there is anything I didn't try. Is there a way that someone would post the complete altered working files? Or is there a way to excude this LIMIT thing? Because I want to have the site running... It has been weeks now that I'm trying to get this fixed...

 

The fix worked for me, should work for u too.

I'll try and trace your problem, donot despair, I'll be back.

 

Walter

Link to comment
Share on other sites

The fix worked for me, should work for u too.

I'll try and trace your problem, donot despair, I'll be back.

 

Walter

 

Found the following text in teh BUG list, it is posted by HPDL.

Basically the same fix as post #1 but more compact:

 

29 Sep 2005 19:21:49 hpdl (AT) oscommerce (DOT) com

 

Thanks for the report, here is the official fix:

 

Line 67 in catalog/includes/classes/split_page_results.php must be changed from:

 

$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;

 

Line 38 in catalog/admin/includes/classes/split_page_results.php must be changed from:

 

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

 

to:

 

$sql_query .= " limit " . max($offset, 0) . ", " . $max_rows_per_page;

Link to comment
Share on other sites

Found the following text in teh BUG list, it is posted by HPDL.

Basically the same fix as post #1 but more compact:

 

Thanks for the effort Walter... but as I wrote: I tried it all and nothing helped.

 

Isn't it strage that even an empty split.... . php gave me the same error. That can only mean that the error is somewhere else.

 

So what I'm going to try now is installing one of the add-ons and if that doesn't work I delete the whole thing and start over with an easier system.

Link to comment
Share on other sites

In admin/orders.php i found this bit of code

<?php
if (isset($HTTP_GET_VARS['cID'])) {
  $cID = tep_db_prepare_input($HTTP_GET_VARS['cID']);
  $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";
} elseif (isset($HTTP_GET_VARS['status'])) {
  $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
  $orders_query_raw = "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 " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by o.orders_id DESC";
} else {
  $orders_query_raw = "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 " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
}

 

Is there a way to exclude the DESC? Will that help? Is there a way to get rid of that LIMIT stuff? Is it possible to just generate a plain list, no split page?

Link to comment
Share on other sites

In admin/orders.php i found this bit of code
<?php
if (isset($HTTP_GET_VARS['cID'])) {
  $cID = tep_db_prepare_input($HTTP_GET_VARS['cID']);
  $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";
} elseif (isset($HTTP_GET_VARS['status'])) {
  $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
  $orders_query_raw = "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 " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by o.orders_id DESC";
} else {
  $orders_query_raw = "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 " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
}

 

Is there a way to exclude the DESC? Will that help? Is there a way to get rid of that LIMIT stuff? Is it possible to just generate a plain list, no split page?

 

 

you do not want to loose the DESC.

it means descending as oposed to ASC

 

rhis raw sql is being parsed somewhere and gets the limit added.

 

Still looking 4U

Link to comment
Share on other sites

you do not want to loose the DESC.

it means descending as oposed to ASC

 

rhis raw sql is being parsed somewhere and gets the limit added.

 

Still looking 4U

 

 

Thanks :thumbsup:

Link to comment
Share on other sites

  • 1 month later...

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

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...