1064 - You have an error in your SQL syntax

June 7, 2005

this is where your problem is

final_price from, products_id from orders_products

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 ' products_id from orders_products where orders_id = '70'' at li

select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price from, products_id from orders_products where orders_id = '70'

[TEP STOP]

Thx,

Basalt

<{POST_SNAPBACK}>

June 7, 2005

i'm not a coder :( i don't see what you're talking about. is it missing something?

basalt · June 7, 2005

Hello,

I use Mysql 4.0.22 & PayPal_Shopping_Cart_IPN_v3.1.5

And I have this error message when I open the invoice or edit the order in the admin part I believe that it is necessary to publish the file order.php

Then there is probably a problem of compatibility with mysql 4.0.22

thanks,

Basalt

basalt · June 8, 2005

It's all right

Find:

$orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity,products_id from final_price from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . tep_db_input($order_id) . "'");

Replace with:

$orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . tep_db_input($order_id) . "'");

Thanks for all

Basalt

rootuser · June 21, 2005

:'( :'( Tried your bug fixes but the error is repeating

Manufacturers

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 manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified from manufacturers order by manufacturers_name limit -20, 20

Reviews

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 reviews_id, products_id, date_added, last_modified, reviews_rating from reviews order by date_added DESC limit -20, 20

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

select p.products_id, pd.products_name, p.products_price, s.specials_id, s.specials_new_products_price, s.specials_date_added, s.specials_last_modified, s.expires_date, s.date_status_change, s.status from products p, specials s, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id order by pd.products_name limit -20, 20

Products Expected

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 pd.products_id, pd.products_name, p.products_date_available from products_description pd, products p where p.products_id = pd.products_id and p.products_date_available != '' and pd.language_id = '1' order by p.products_date_available DESC limit -20, 20

Iam using

MySQL-client-4.1.11-0

MySQL-server-4.1.11-0

Please help me

rootuser · June 21, 2005

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;

scrap32 · June 22, 2005

I too, had this problem and tried your fix and it WORKED!!! Thank you so much :D :D

Simplyeasier · June 22, 2005

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;

<{POST_SNAPBACK}>

is this a joke ? - did you read the very first post in this thread - or are you into re-inventing the wheel ?

Charles

June 23, 2005

LOL

Lao · June 26, 2005

Worked for me too,SimplyEasier. Thanks. ;)

cdamianou · July 28, 2005

Hiya

Can someone please help me.

I tried the steps above and i cant seem to solve the problem.

I deleted all manufacturers from admin, and got 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 '-20, 20' at line 1

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

The same error is everywere were their is nothing.

Please can someone help.

OceanRanch · July 28, 2005

You'll need to make approximately the same change in the catalog/admin/includes/split_page_results.php. The line numbers are a bit different but the net result is the same. Search in the bug report section for the exact line number if you need to.

HTH

Tom

enigmaverse · August 1, 2005

tried this fix with no luck but my problem is slightly different. 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 'MAX_ROW_LISTS_OPTIONS' at line 1

select * from products_options where language_id = '1' order by products_options_id LIMIT 0, MAX_ROW_LISTS_OPTIONS

[TEP STOP]

I'm running MySQL 4.1.9 with PHP 4.3.11 this is a completely fresh install, no info added, no mods, nothing... just uploaded the files and ran the install. After install I applied the mod in the first post and reposted. I think my problem is more related to a problem in the language files, but I dont know php enough to know whats wrong... I get numerous other error including failure to load certain includes in the footer of the catalog (starting with the language include) and the define labels instead of actual titles(HEADING_TITLE )... no idea how to fix, running in circles at this point. and cant continue without getting at least a smaple cart up and running...

brzell · August 10, 2005

I am now getting this error on the live side of the cart when you click on the Reviews button under a choosen product.

Any ideas ?

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

Thanks

toby_uk · August 11, 2005

Hi,

I am just setting up my first OS Commerce project and am experiencing a slight variation on the problem in this thread.

The following error is displayed when I click on 'Orders' in the admin section:

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

[TEP STOP]

Is anyone available to offer help as to what the problem is?

Many thanks to anyone that can help :)

OceanRanch · August 11, 2005

You'll need to apply the fix to both catalog and admin versions of split_page_results.php.

See bug report

http://www.oscommerce.com/community/bugs,1605

HTH

Tom

toby_uk · August 12, 2005

Hi,

Thanks for the reply.

I applied the fix to admin/includes/class/split_page_results.php, however I tried changing the includes/classes/split_page_results.php but the file is different to the admin one and the lines:

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

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

Do not exsist in that file; changing the admin file did allow me to view the orders page (resolved the 1064 error). However now when I try and delete Manufacturers the following error is displayed:

Warning: Cannot modify header information - headers already sent by (output started at /home/sansal00/public_html/admin/includes/classes/split_page_results.php:109) in /home/sansal00/public_html/admin/includes/functions/general.php on line 18

Any idea's what is causing this problem?

Thanks

webskate · August 12, 2005

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

<{POST_SNAPBACK}>

Thanks for that, my site is now back working

caroline

toby_uk · August 12, 2005

Hi,

Thanks for the reply.

I applied the fix to admin/includes/class/split_page_results.php, however I tried changing the includes/classes/split_page_results.php but the file is different to the admin one and the lines:

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

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

Do not exsist in that file; changing the admin file did allow me to view the orders page (resolved the 1064 error). However now when I try and delete Manufacturers the following error is displayed:

Warning: Cannot modify header information - headers already sent by (output started at /home/sansal00/public_html/admin/includes/classes/split_page_results.php:109) in /home/sansal00/public_html/admin/includes/functions/general.php on line 18

Any idea's what is causing this problem?

Thanks

<{POST_SNAPBACK}>

It appears the fix did indeed work, but through my stupidness I created another error, I left a space below ?>

:blush: :blush:

toby_uk · August 25, 2005

Hi,

I'm back again, I thought this problem had been fixed, but unfortunately not. I applied the fix to admin/includes/class/split_page_results.php, however I tried changing the includes/classes/split_page_results.php but the file is different to the admin one so didn't know which bit to change. The change meant I am able to update from the admin side with no problems, now however the 'specials' page results in 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 '-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

[TEP STOP]

See the problem Here

Can anybody help, I have no idea what to do now :(

Thanks

Jan Zonjee · August 25, 2005

I have no idea what to do now :(

The code is pretty similar. $offset is determined in line 65 and should not be lower than 0.

This should work:

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

toby_uk · August 25, 2005

Thank you JanZ that seems to have done the trick :D

August 26, 2005

I am also getting a similar error, but the fixes don't seem to be working. There are no orders and when you click on customers/orders, you get the following error:

"Orders Order ID:

Status: All OrdersPendingProcessingDelivered

Customers Order Total Date Purchased Status Action

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

[TEP STOP]"

CODE:

admin/includes/classes/split_page_results.php:

$offset = ($max_rows_per_page * ($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

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

}

catalog/includes/classes/split_page_results.php:

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

Any suggestions?

Thank you,

Rebecca

August 26, 2005

Works OK for now.

Rebecca

lauren4521 · August 28, 2005

Just a thank you to Charles for this. Just fixed my problem and I couldn't be happier that it was so easy!!!

Lauren

1064 - You have an error in your SQL syntax

Recommended Posts

Guest

Guest

basalt

basalt

rootuser

rootuser

scrap32

Simplyeasier

Guest

Lao

cdamianou

OceanRanch

enigmaverse

brzell

toby_uk

OceanRanch

toby_uk

webskate

toby_uk

toby_uk

Jan Zonjee

toby_uk

Guest

Guest

lauren4521

Archived

Browse

Activity