SQL Error when checking Orders

[iscnitzan]

Hi,

 

I have OSC store working for a while. So far has been on a server with SQL v 4.0.22 and worked fine..

but the server was slow & old - so - moved to a better one, which has SQL 4.1.21 installed.

 

Everything seems to function fine on the new server except:

 

When I check orders from /admin/customers.php screen, and I click a customer who DOSE-NOT have any orders on file (just a registered customer) - I get 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 '-12, 12' 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 = '7' and o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by orders_id DESC limit -12, 12

 

[TEP STOP]

 

I tried checking everything - nothing seems off....

 

Worth mentioning - when I click to check an order for customer who HAS orders on file - there is no problems! the order shows as needed. Only clicking clients with no orders gets the error.

 

Meaning - there is something wrong with queries on empty records - but I don't know what!

 

Can anyone help?

[AJRYAN]

I am having the same problem. Can anyone please shed some light on this?

 

Thanks

[iscnitzan]

From what I understand there is an error in the way SQL 4.1 interprets the command:

 

sort order by orders_id DESC limit -12, 12

 

 

I hope there is someone with extensive SQL experience to help us, this is a nagging problem.

 

Please

[iscnitzan]

OK, did some checking... and Finally found the error and FIX !!

 

The problem is the SQL 4.1.x dose not work with negative query results (-12, 12).

 

These results derive form the splitPageResults function to split the page into MAX_DISPLAY_SEARCH_RESULTS...

 

Now since we have customers with no orders - the starting number is 0 and somehow the MAX_DISPLAY_SEARCH_RESULTS of 12 becomes -12, thus error with SQL 4.1.x.

 

Now I know what is wrong! How to fix it is another story... :'(

 

I found that the following is the "spliting function" in order.php line 363:

$orders_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $orders_query_raw, $orders_query_numrows);

$orders_query = tep_db_query($orders_query_raw);

 

And - I also found that the math is in admin/includes/classes/split_page_result.php ...

 

there find this

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

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

}

 

and replace with this

// TN Add if rows more then 1 to eliminate negative sql error

if ($query_num_rows > 1){

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

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

}

}

// TN end of change

 

Finally - I can go to sleep :thumbsup: