1064 - You have an error in your SQL syntax

[klander]

Hi,

I was checking my orders and found the error below. I haven't made any changes to oscommerce. Please help.

Thanks.

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.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 orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.customers_id = '1' and o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by orders_id DESC limit -20, 20

[mitchind]

I'm pretty sure you can't have a negative number for the LIMIT Offset value.

The number -20 should probably be 0 (zero). I have no idea why it would have changed.

[klander]

Thanks for the quick response. Were does the -20 value reside?

[stevel]

I can't figure out which query you're using. The ones on orders.php don't include a limit clause. What page are you on when you get this error?

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;

[klander]

That did it! What do you think happened?

The error occurred when I clicked on "Orders" while in admin.

You guys are great. Thanks for the help.

[stevel]

Looks as if something was sending a "page 0" (or empty value) for the page number. That doesn't sound typical.

[redhunter]

Originally Posted by klander

That did it! What do you think happened?

The error occurred when I clicked on "Orders" while in admin.

You guys are great. Thanks for the help.

Looks like mysql 4.1 does not allow negtive numbers for limits. As such numbers make no sense, this would be a bugfix, from a mysql point of view.

The culprit code is in osCommerce:

$num_pages = ceil($query_num_rows / $max_rows_per_page);
if ($current_page_number > $num_pages) {
$current_page_number = $num_pages;
}
$offset = ($max_rows_per_page * ($current_page_number - 1));

in the case where there are no rows found to display (query_num_rows=0), the offset goes to negative max_rows_per_page. The posted fix compensates for this odd case, though a more logical fix would be to set numpages=1 if numpages=0. That is nitpicking though!

[stevel]

Thanks very much for this - I had not yet encountered it myself, but sure enough, I can reproduce it on my store and made the appropriate fixes.

Thank you so much for the help - I just had the same problem myself (I too have no idea why as I haven't changed anything) and was able to fix it after a few minutes spent here.

Once again everyone on these forums have proven themselves to be worth their weight in gold. Thank you!!

Samantha

This is a known bug with osC-MS2...http://www.oscommerce.com/community/bugs,1605

Bobby

[aragorn231]

Originally Posted by Chemo

This is a known bug with osC-MS2...http://www.oscommerce.com/community/bugs,1605

Hmm, just wasted 20 minutes going through the postings there, only to conclude that the solution that Viscid provided above is a good one!

So, if you don't want to waste your time (and risk ending up with a non-working 'solution' because some postings there contain wrong information), don't read the postings but just use the solution provided here.

[aragorn231]

This is in addition to the solution provided by Viscid.

It should be pointed out that the code in the admin section is not the same as the code in the store section, even though the files are named the same.

In the admin file (admin/includes/classes/split_page_results.php), the code line to look for is

PHP Code:

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


nm edited post found fix.

[linnetwoods]

Hope someone can help me...

I just deleted all the products in Specials and, after the last one was gone, all I could get on the Specials page was:

"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

[TEP STOP]"

Do I follow the same instructions from Viscid or must I do something else somewhere else?

While I'm at it, I have noticed that, in spite of selecting a product for the shopping cart which shows up in the box in the right-hand column, the shopping cart page says that there are no products in the cart... any clues on mhy that would be happening?

Thanks to anyone who can help - I am a novice at this stuff and finding it very difficult!

[stevel]

Same problem, same solution.

The cart problem is usually caused by an error in the configure.php for cookie domains.

[linnetwoods]

Thanks for the reply - actually, by the time I found it I had already found a solution to the Specials problem at OsCommerce.com bugs which worked perfectly and now I will address the cart problem...
Thanks again,

[stevel]

As I said, same problem, same solution.

[linnetwoods]

There seems to be no mention of cookies whatsoever in my configure.php, let alone one that includes http:// erroneously! I have been chasing around the osCommerce forums and elsewhere and have not managed to solve the problem so far. Any other advice you might be able to offer?Again, thanks for your previous effort.

[stevel]

You're probably looking in the wrong place. How about starting a new thread describing the problem and including the first 20 lines of catalog/includes/configure.php,