OSCOMMERCE SUPPORT CALL 702-453-3332

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

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.

hth

Charles

OK. Thx 4 ur wamrly support!
My site works fine now...

Thanks so much. Great work!

Cheers,

David

I am getting the following error, when I try to delete products through the admin section, and I'm wondering if it is related to what you are discussing here? I have tried your fix, but it didn't resolve the problem... the error is a bit different though.

I get at this url catalog/admin/categories.php?action=delete_product_confirm&cPath=81 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 '' at line 1

delete from products_attributes where products_id=

[TEP STOP]

The only place I see in the site where the "delete from products_attributes where products_id=" is is in the admin/includes/classes/database.php file, where it says:

function jjg_db_attributeSets($arr_attributeSetID, $productID, $action, $link = 'db_link'){

//remove all references to this product_id in these tables, and reinsert new ones
if( $action == 'update_product' || $action == 'delete_product_confirm'){
$delete_query = "delete from products_attributes where products_id=".$productID;
tep_db_query($delete_query, $link);
$delete_query = "delete from products_attributes_sets_to_products where products_id=".$productID;
tep_db_query($delete_query, $link);
}
//if you remove the product, then our work is done
if( $action == 'delete_product_confirm' ){
return;
}

Thanks for any clues! This wasn't broken in the past, but I've been adding several contributions, including the attribute sets contribution.

Janet

I would suggest the contrib work you refer to has broken your site

The fix here is only if you have an instal on MySQL v 4.1.xx AND you delete all of the records on any of the tables.

Charles

Janet,

Regarding:

Try: (single quote, double quote, dot, (integer)$productID, dot, double quote, single quote, double quote)

Also the $productID shouldn't contain any references to attributes ( with {number} ) in it. Therefore the casting to an integer.

Thanks Jan, I tried your code suggestion (well the first one, anyway, I wasn't sure where to edit the $productID though). I still get the error, plus there are other errors when I try to add an attribute that I hadn't seen prior to posting my question. I think my best bet is to uninstall the attribute sets contrib and see if the site stabilizes, and then figure what to do next. I need to add size attributes to all the products, which the attribute sets would be great for, since there's so much repetition, but I also have tons of unique colors and color combos, which need to be added individually. It seems that attribute sets isn't meant to be combined with using the attributes section too... sigh...

Many thanks for your assistance (again!)
Janet

Great tip.

Thanks for the post.

worked very well.. now I just need to sort my other problem.

Yep, problem there to. When you list 'all order' when logged in as a customer you get a similar error. I'm going to try and fix it myself with the admin patch from above.

Great solution!

Love u!

My error message is slightly different and reads:

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

delete from products_options where products_id =206'

[TEP STOP]

I tried your fix to no avail. I really need to get this ironed. Please advise. It only happens when I try to delete a product. The product gets deleted, but the error still pops up every time.

I tried your fix and I thought it worked but now when I go to delete a manufacturer I get this:



what do you think might have happened?

You most probably have white lines at the top or bottom of your amended file.

Use an pure text editor (notepad will do) and make sure all white lines at the top and bottom of your file are deleted.

Then try again.

Charles

I also get this when I try to add a manufacturer now:

FIXED!!!


DUDE! YOU'RE A CHAMPION!!!!!





cheers,
ekendra

I tried the fix with no joy.....

I get the error when I select Customers/Orders in admin. I looked at the orders table, it seemed fine.
I am running on MySQL 4.023a

The only changes to the basic db have been the addition of the Downloads Controller and the Master/slave sql files.

All other db functions seem to work fine.

For a moment I thought I had found the solution...ah well

Thnks anyway...any other thoughts that may be of help here

This is a fix for MySQL 4.1.xx

Charles

Ok, I tried that, but it didn't work.

I got the dreaded 1064 error when I deleted all of the manufacturers. Oops! How do I add manufacturers now?

So I am in the admin section trying to edit the manufacturers.

"Manufacturers

Manufacturers 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 '-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

[TEP STOP] "

Heeellp!

Are you running mysql 4.1.xx ? Have you changed the correct file in EXACTLY the way described ?

This has worked for the others - should work for you if the answers to the 2 questions are yes

Charles

I figured it out. I went to my host, Dreamhost, and entered a value from there. They have a nice MySQL interface

Thanks,
Christy

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

I'm still on MySQL 3.23 ... and getting this error. is there a workaround for this?

what error are you getting techbot?

this is where your problem is

final_price from, products_id from orders_products

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

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

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

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

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;

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

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

Charles

LOL

Worked for me too,SimplyEasier. Thanks.

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.

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

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

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

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

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

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

Thanks for that, my site is now back working

caroline

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

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

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

Thank you JanZ that seems to have done the trick

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

Works OK for now.

Rebecca

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

Lauren