Sometimes osCommerce store’s default search feature finds no product and search repeatedly returned ‘There is no product that matches the search criteria.’ even though you know products are there. And I had searched the exact name of product which was there and search could not even fetch that!
It took me an hour or so, and lot of fruitless scrolling down on osCommerce forums in the search of a quick solution but it was not to be.
Strangely nobody had ever posted this issue, or noticed, they were actually complaining to make results refined and more useful and here I was not even getting a simple result. Initially I thought I must have made some mistake while customizing it. Here are my findings and the solution!
After further re-testing and moving products around in categories, I found out that this problem occurs when no products are assigned under any category. osCommerce search feature won’t fetch those products which have been added at top level without any category.
Problem lies in the MySQL query that osCommerce constructs in the advanced_search_result.php page.
In advanced_search_result.php, on line 225 osc adds this where clause checking products_id to products to categories p2c.products_id and p2c.categories_id = c.categories_id whereas some products have not been added in products to categories (p2c) table.
To correct this, find this code on line 225:
225 226 227
$where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";
Replace it by following code:
225 226 227 228 229 230
$where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' "; if (isset($HTTP_GET_VARS['categories_id']) && tep_not_null($HTTP_GET_VARS['categories_id'])) $where_str .= " and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";
I have taken out problematic code and have added it in the query on the next line only if the specific category is being searched; in that case it’s just fine to ignore those products which are not under that category.
And this simple hack took away the dreaded “There is no product that matches the search criteria.” Message and fetched the products.
Hope that helps.