SQL Error

[melanieh]

I have a template that was made for version 4.x but is on a host with 5.x I have fixed all the sql errors except one.

Site is: http://www.happyhousewares.com/index.php

If you click on the "specials" link (http://www.happyhousewares.com/specials.php) the error is:

 

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 '-9, 9' at line 1

 

select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added DESC limit -9, 9

 

Any specifics on how I could fix this? I am very new to the oscommerce so I appreciate the assistance.

[♥geoffreywalton]

Check this link in my profile.

 

How to update to RC2A and sort out PHP5/MySQL5 upgrade problems.

 

HTH

 

G

[MrPhil]

Really nothing to do with MySQL version (except perhaps that version 5 now complains about an illegal starting row number, while 4 ignored it). You need a couple of lines patched.

In admin/includes/classes/split_page_results.php change

      if ($current_page_number > $num_pages) {
       $current_page_number = $num_pages;
     }

to

$current_page_number = max(1, min($num_pages, $current_page_number));

and in includes/classes/split_page_results.php change

      if ($this->current_page_number > $this->number_of_pages) {
       $this->current_page_number = $this->number_of_pages;
     }

to

$this->current_page_number = max(1, min($this->number_of_pages, $this->current_page_number));

What happens is that a current page number of "0" gets passed in rather than a minimum of "1". A current page of 0 creates an $offset with a negative value. Newer osC code uses max($offset, 0) rather than just $offset in the "limit" clause, but the end effect is the same.

 

If this doesn't cure it, look around for an SQL statement with a "limit" clause, and do whatever is needed to make sure the first number (starting row) is at least 0.