SQL, Syntax Error

[apk]

Hi,

 

When i click on reviews or specials in the admni cp i get this error message:

 

Specials  

Products Products Price 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 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]
 

 

I have no idea what to do, i have phpmyadmin and would not know where to start. Is there an easy way to fix this?

 

Piers

[tvega]

I get the same message and already posted a simialr thread. Does anybody have a suggestion?

 

Hi,

 

When i click on reviews or specials in the admni cp i get this error message:

 

Specials  

Products Products Price 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 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]
 

 

I have no idea what to do, i have phpmyadmin and would not know where to start. Is there an easy way to fix this?

 

Piers

<{POST_SNAPBACK}>

[bbtjlgt]

The problem is with the limit clause of your sql statement. The following explains how to use the limit clause.

 

**MySQL Manual | 13.1.7 SELECT Syntax**

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants. With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

 

mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15

 

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax. To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

 

mysql> SELECT * FROM table LIMIT 95,18446744073709551615;

 

With one argument, the value specifies the number of rows to return from the beginning of the result set:

 

mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rows

 

In other words, LIMIT n is equivalent to LIMIT 0,n.

*****************************************

 

So to fix your code change the limit clause from "limit -20, 20" to "limit 0,20" or "limit 20".

[kremdela]

new to the forum, I was having the same error after a MySQL upgrade.

the solution, for future interested parties

 

in the following two files:

 

../catalog/admin/includes/classes/split_page_results.php

../catalog/includes/classes/split_page_results.php

 

just before the last line:

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

 

add the line:

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

 

the problem is that newer MySQL versions don't allow negative values in LIMITs,

so this makes the query LIMIT 0, 20, which is the desired outcome

 

hope that saves someone some trouble.

[apk]

Hi,

 

thanks, i looked at the php files,

 

the last chunk of the ./includes/classes/.... was:

 

// next window of pages
     if ($cur_window_num < $max_window_num) $display_links_string .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . (($cur_window_num) * $max_page_links + 1), $request_type) . '" class="pageResults" title=" ' . sprintf(PREVNEXT_TITLE_NEXT_SET_OF_NO_PAGE, $max_page_links) . ' ">...</a> ';

// next button
     if (($this->current_page_number < $this->number_of_pages) && ($this->number_of_pages != 1)) $display_links_string .= ' <a href="' . tep_href_link(basename($PHP_SELF), $parameters . 'page=' . ($this->current_page_number + 1), $request_type) . '" class="pageResults" title=" ' . PREVNEXT_TITLE_NEXT_PAGE . ' "><u>' . PREVNEXT_BUTTON_NEXT . '</u></a> ';

     return $display_links_string;
   }

// display number of total products found
   function display_count($text_output) {
     $to_num = ($this->number_of_rows_per_page * $this->current_page_number);
     if ($to_num > $this->number_of_rows) $to_num = $this->number_of_rows;

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

     if ($to_num == 0) {
       $from_num = 0;
     } else {
       $from_num++;
     }

     return sprintf($text_output, $from_num, $to_num, $this->number_of_rows);
   }
 }
?>

 

I could not see the line there.

 

Please advise

 

Piers