SQL Error when table empty

[Guest]

When, for example, a product is out of stock and you've chosen a re-stock you can then view a list of 'Products Expected' as expected.

 

However, if there are no products expected this error message is displayed:

 

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

 

This is happening throughout the admin system now in a variety of places such as product reviews, If there are no reviews you see a similar error. If there are no manufacturers, you get the message.

 

I don't know why this has suddenly started happening. I've 'fixed' it (read 'fudged' it) by adding a review to a product, adding a Manufacturer called 'various' etc., etc. It even happens if there are no customers or no orders of a particular status - everywhere there's 'nothing'.

 

Anyone got any clues as to what I've done?

 

Thanks

[♥yesudo]

This is to do with a change in MySQL 4.1.xx where negative results are flagged where in earlier versions they were ignored(incorrectly).

 

The solution is:

 

In admin/includes/classes/split_page_results.php

 

Find:

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

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

 

And change to:

 

$offset = ($max_rows_per_page * ($current_page_number - 1));

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

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

[Guest]

...and there's me thinking 'I won't bother Emmett with this, he's probably tired of my requests by now'.

 

But, to the rescue once again.

 

Many thanks Emmett

[♥yesudo]

np

 

Do the same to file:

 

In includes/classes/split_page_results.php

 

to allow the catalog side to function without issue.