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?

[marty_mcfly]

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:

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?

 

The problem is to do with the LIMIT -12, 12. The MySQL documentation states that both parameters must be non-negative. However, I have looked through my code and can't find why you are getting that.

 

Have you installed any contributions which may cause it?

 

Martin

[iscnitzan]

hi,

No - I didn't add nothing lately, I also don't know (or seem to find) where the limit (-12, 12) is actually been set....

 

Looked through the order.php file many times - I couldn't find it.

 

Here is where I think the error occurs, at list according to the error message:

<?php

if (isset($HTTP_GET_VARS['cID'])) {

$cID = tep_db_prepare_input($HTTP_GET_VARS['cID']);

$orders_query_raw = "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 " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";

} elseif (isset($HTTP_GET_VARS['status'])) {

$status = tep_db_prepare_input($HTTP_GET_VARS['status']);

$orders_query_raw = "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 " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by o.orders_id DESC";

} else {

$orders_query_raw = "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 " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";

}

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

while ($orders = tep_db_fetch_array($orders_query)) {

if ((!isset($HTTP_GET_VARS['oID']) || (isset($HTTP_GET_VARS['oID']) && ($HTTP_GET_VARS['oID'] == $orders['orders_id']))) && !isset($oInfo)) {

$oInfo = new objectInfo($orders);

}

 

if (isset($oInfo) && is_object($oInfo) && ($orders['orders_id'] == $oInfo->orders_id)) {

echo ' <tr id="defaultSelected" class="dataTableRowSelected" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $oInfo->orders_id . '&action=edit') . '\'">' . "\n";

} else {

echo ' <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '\'">' . "\n";

}

?>

 

But - I can't see where we define the parameters '-12, 12'

 

Can anyone see?

[iscnitzan]

Found the solution..

 

Read last post at General Support Forum :lol: