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

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


John Oligario

 

Knowledge Base Contributions

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


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

 

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

 

Charles


A kite flies highest AGAINST the wind !

 

"Life should NOT be a journey to the grave with the intention of arriving safely in an attractive and well preserved body, but rather to skid in sideways, a lover in one hand, martini in the other, body thoroughly used up, totally worn out and screaming ~ WOO HOO!! What a ride!"

Share this post


Link to post
Share on other sites

Worked for me too,SimplyEasier. Thanks. ;)


Sorry for my bad english language!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 :)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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 for that, my site is now back working

 

caroline

Share this post


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

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

:blush: :blush:

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×