SQL Syntax

[apapresident]

When I go into administration in the customer order section here is what I get and I have no idea how to do whatever needs to be done to correct this or how to do it:

 

REPORTS

 

Best Customer Orders-Total

 

No. Customers Total Purchased

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

 

 

 

Best Products Purchased

 

No. Products Purchased

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, p.products_ordered, pd.products_name from products p, products_description pd where pd.products_id = p.products_id and pd.language_id = '1' and p.products_ordered > 0 group by pd.products_id order by p.products_ordered DESC, pd.products_name limit -20, 20

 

 

HEADING_TITLE

 

TABLE_HEADING_NUMBER TABLE_HEADING_PRODUCTS TABLE_HEADING_VIEWED

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, pd.products_viewed, l.name from products p, products_description pd, languages l where p.products_id = pd.products_id and l.languages_id = pd.language_id order by pd.products_viewed DESC limit -20, 20

[apapresident]

When I go into administration in the customer order section here is what I get and I have no idea how to do whatever needs to be done to correct this or how to do it:

 

REPORTS

 

Best Customer Orders-Total 

 

No. Customers Total Purchased 

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 

 

Best Products Purchased 

 

No. Products Purchased 

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, p.products_ordered, pd.products_name from products p, products_description pd where pd.products_id = p.products_id and pd.language_id = '1' and p.products_ordered > 0 group by pd.products_id order by p.products_ordered DESC, pd.products_name limit -20, 20

 

 

HEADING_TITLE 

 

TABLE_HEADING_NUMBER TABLE_HEADING_PRODUCTS TABLE_HEADING_VIEWED 

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, pd.products_viewed, l.name from products p, products_description pd, languages l where p.products_id = pd.products_id and l.languages_id = pd.language_id order by pd.products_viewed DESC limit -20, 20

<{POST_SNAPBACK}>

 

Actually, the above pertained to the REPORTS section. I also have the below problem when going into the CUSTOMERS section:

 

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

[♥bruyndoncx]

It could be that your splitpageresults class is not working properly because of a variable not being passed (php 5 ? change in ini settings ?).

 

You can solve this by adding a check to split_page_results class towards the end.

If tte lower limit < 0 set it to 0.

 

I remember seeing something like this on one of my clients sides that was solved like this ...

[♥bruyndoncx]

In includes/classes/split_page_results.php (admin and/or catalog section)

Change from

      $offset = ($this->number_of_rows_per_page * ($this->current_page_number - 1));

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

 

To

      $offset = ($this->number_of_rows_per_page * ($this->current_page_number - 1));

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

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

 

Make sure your page browsing is fully functional. This quick fix might get you to see the first page, but something more serious might still be wrong causing you not to seee page 2, 3, 4, ...

 

HTH