Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Click on Specials and get this?


pablonona

Recommended Posts

Fresh install......

 

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 '-6, 6' 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 -6, 6

 

Any ideas where to start?

Link to comment
Share on other sites

http://www.oscommerce.com/forums/index.php?sho...4095&st=140

 

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

 

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.

 

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.

Link to comment
Share on other sites

Will look into it given the new info, thank you so much!

 

http://www.oscommerce.com/forums/index.php?sho...4095&st=140

 

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

 

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.

 

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.

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...