Database errors in admin following PHP / mySQL upgrade

[Guest]

We have upgraded our site to PHP 5.2.12 and mySQL 5.0.77 and are now getting miscellaneous errors in certain errors of the admin control panel:

 

In Tax Zones:

 

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

select geo_zone_id, geo_zone_name, geo_zone_description, last_modified, date_added from geo_zones order by geo_zone_name limit -10, 10

[TEP STOP]

 

In Banner Manager:

 

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

select banners_id, banners_title, banners_image, banners_group, status, expires_date, expires_impressions, date_status_change, date_scheduled, date_added from banners order by banners_title, banners_group limit -10, 10

[TEP STOP]

 

And so on. Can anyone tell me how we can fix these issues? We can't go back to PHP4 / mySQL 4, and the only solution I've found talks about completely reinstalling osCommerce- and as this is a very large site we really can't rebuild that from scratch.

[MrPhil]

A search for limit negative gives: http://www.oscommerce.com/ext/update-20051113.html#_Toc119693704

 

You have to insert some code to make the starting row minimum 0, instead of a negative number.

[Guest]

A search for limit negative gives: http://www.oscommerce.com/ext/update-20051113.html#_Toc119693704

 

You have to insert some code to make the starting row minimum 0, instead of a negative number.

 

What code would I use to do this, and which file would I place this into?

[MrPhil]

Per the referenced posting, replace code similar to this:

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

with

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

You need to do this at least in any module which reports an SQL problem with negative limits, and you might as well check all places in your code where LIMIT or limit appears with two variable numbers following. The first is the starting line and should not be less than 0, the second (or only) number is the number of lines and should be at least 1.