PROBLEM with Viewing Customer Orders

[devangelous]

I've just install OS Commerce on a server purchased from Hostony and I get this error when I go to view orders:

 

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 c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum from customers c, orders_products op, orders o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC limit -20, 20

 

But when I install on my local machine, It works fine

[Guest]

could be the mysql version difference see this:

http://www.oscommerce.com/community/bugs,1605

[AlanR]

To save looking through that whole bug report here's the relevant section

 

Easiest solution:

edit /includes/classes/split_page_results.php

find

$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

(line about 66)

insert before it

if($offset <0 ) $offset = 0;

 

the same in admin/includes/classes/split_page_results.php

do the same, but this time it's line about 38. (add the line in red)

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

if($offset <0 ) $offset = 0;

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

[devangelous]

To save looking through that whole bug report here's the relevant section

 

Easiest solution:

edit /includes/classes/split_page_results.php

find

$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

(line about 66)

insert before it

if($offset <0 ) $offset = 0;

 

the same in admin/includes/classes/split_page_results.php

do the same, but this time it's line about 38. (add the line in red)

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

if($offset <0 ) $offset = 0;

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

 

Thanks for your support. I have come to understand that when there is no items in some specified fields, the error is present. And you solution only seems logical.

 

Again, thanks.

[Guest]

I tried them all. Been looking for 3 full days now to solve this problem and nothing seems to help. I found some posts (in trics and bugs) but none of the offered solutions worked.

 

So, in orders I get:

 

Bestellingen  Bestelnummer.:  
Status: Alle bestellingen 


Klant Bestelling totaal Besteldatum Status Actie  
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 = '4' and ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20

[TEP STOP]

 

Changing the split_page_results.php in admin/... and includes... didn't help. Even stranger: I emptied the split_page_results.php and the same error kept popping up.

 

The results don't have to be split... I would be happy if I would just see 1 page with all the orders. So if someone has a solution: please tell me. As i'm running on a standard hosting changing the Mysql version is not an option.

 

Thanks

[Guest]

Here is the line from the admin\orders.php that causes the error you mentioned:

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

 

Check with phpmyadmin the orders tables if they are empty. The 1605 bug is the first to check, as the limit that it gets, is a negative value and that could cause the problem. Couple of lines below the line of code you see above there is this:

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

 

So also check the MAX_DISPLAY_SEARCH_RESULTS Available through the configuration of your admin cpanel. Make sure its not 0.

[Guest]

Hi Enigma,

 

Thanks, there are orders... Just typing 1 2 or 3 in the input field returns a complete order.

 

The problem is really that I'm not that good wit code and stuff. Is there a way to get rid of the negative output that causes the problem? I don't mind that is it a workaround or something, I just wan to have a list of orders, I don't mind that it they are all on one page.