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'

[herbsandhelpers]

• Hello! I'd to solve this bug:
  
  
• herbsandhel...
  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 pd.products_id, pd.products_name, p.products_date_available from products_description pd, products p where p.products_id = pd.products_id and p.products_date_available != '' and pd.language_id = '1' order by p.products_date_available DESC limit -20, 20
  
  
• herbsandhel...
  I've been into the SQL database and products_expected.php but I'm not sure what bit to change?
  
  
• herbsandhel...
  in SQL there seemed to be a comma rather than a closing bracket in the code but changing it made no difference Lorraine
  
  

[MrPhil]

This has been asked and answered many times before. Search for limit -20, 20 in this forum and you will find the code you have to change to prevent the "starting row number" of -20 (change from $row to max(0, $row)) in the split page results. It sounds like you have a very old version of osC that had this bug... perhaps time to upgrade to 2.3.1?

[herbsandhelpers]

Sorry to sound think but is the code in one of the php files or in sql? Where is the location of the split page results please?

[herbsandhelpers]

Hello Phil,

I've recently moved the shop to a new server and not done anything like this before. Where is the file to change the code in please or is it in SQL? Don't even know where to go. Searching for the terms suggested is not allowed

[MrPhil]

Google: site:www.oscommerce.com/forums "limit -20, 20" will give lots of hits.

 

catalog/includes/classes/split_page_results.php will have something like

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

Change it to

$this->sql_query .= " limit " . max($offset,0) . ", " . $this->number_of_rows_per_page;

 

catalog/admin/includes/classes/split_page_results.php will have something similar to

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

which you change to

$sql_query .= " limit " . max($offset, 0) . ", " . $max_rows_per_page;

 

The exact code may vary with your specific osC version, but it should be similar to those.

[herbsandhelpers]

Hello Phil,

I did exactly what you said and it worked! Sooo grateful! I must now look at the upgrade ...

 

Thank you all my errors now solved.

 

Lorraine

[MrPhil]

Good to hear that...