Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Subcategory search in advanced_search_results.php


Rachael w.

Recommended Posts

I've google this and didn't come up with a solution. I would like my search results to include the subcategory name. An example would be like this. (independent of the advanced search)

I sell shirts.

 

My top category is shirts.

My subcategories are mens, womens, boys, etc.

I have generic descriptions which may or may not include the words mens womens boys, shirts etc. 

When a customer does a search for mens shirts it will return no results.

If they search for shirts results will show.

If they search just mens, no results. 

I've looked over advanced_search_results.php and I see some instances of subcategory code but not sure how it ties in to a general search. 

Here's my initial thoughts. This little bit of code looks like its looking for the subcategory in the database.

if (isset($HTTP_GET_VARS['categories_id']) && tep_not_null($HTTP_GET_VARS['categories_id'])) {
	
	if (isset($HTTP_GET_VARS['inc_subcat']) && ($HTTP_GET_VARS['inc_subcat'] == '1')) {
		$subcategories_array = array();
		tep_get_subcategories($subcategories_array, $HTTP_GET_VARS['categories_id']);
		$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";
		for ($i=0, $n=sizeof($subcategories_array); $i<$n; $i++ ) {
			$where_str .= " or p2c.categories_id = '" . (int)$subcategories_array[$i] . "'";
		}

		$where_str .= ")";
	} else {
		$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";
	}

}

and this little bit looks like it is where it's trying to match the keyword entered with the actual words in the database. 

if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {
	$where_str .= " and (";
	for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
		switch ($search_keywords[$i]) {
			case '(':
				case ')':
					case 'and':
						case 'or':
							$where_str .= " " . $search_keywords[$i] . " ";
							break;
						default:
							$keyword = tep_db_prepare_input($search_keywords[$i]);
							$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";
							
							if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";
							$where_str .= ')';
							break;
				}

			}

I just need some help getting that second bit of code to also search subcategory names for a match. 

 

Thank you in advance for your assistance!!!
 

Link to comment
Share on other sites

@

 

Hi Rachael,

 

It's not just a case of adding a bit of code in the second lot of code. The first bit of code is used for searching for products within the categories when a category and/or subcategories is selected in "advanced search". It's not actually using the category names to search within the categories but is using the category id's that marry up with the products.

 

In fact the category name is not even used in the initial query, so it's not just a simple case of using the category name to match the $keyword in the second lot of code as you say. Some changes are required in advanced_search_results.php to pull categories name out the categories_description table. We'd have to look at the code's logic to see if this can work.

 

cheers :santa:

My Add-ons
Advanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download Support
Ajax Product Listing for osC 2.3.4 (bootstrap) Download Support
Category New Products Carousel for osC 2.3.4 (bootstrap) Download Support
Category Popular Products Carousel for osC 2.3.4 (bootstrap) Download Support
Customer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download Support
Front Page New Products Carousel for osC 2.3.4 (bootstrap) Download Support

Index Nested - Product Listing for osC 2.3.4 (bootstrapDownload Support
Match Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download Support
Modular Category Page for osC 2.3.4 (bootstrap)
Download Support

NEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download Support
NEW Equal Height Module for osC 2.3.4 (bootstrapDownload Support
Products Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download Support
Twitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap)
Download Support

Upcoming Products Modules for osC 2.3.4 (bootstrap) Download Support

 
Assisted Add-ons
Scroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support
 
Bootstrap Add-ons created by other members
osCommerce Bootstrap Addons and Code

Link to comment
Share on other sites

@

 

First, check your setting in "Admin->Configuration->My Store->Default Search Operator". Try setting to "or" and see if that helps before trying the code below.

 

If not, then try the code below which will include category names in searches. I would still leave the above setting on "or".

 

Changes have been commented with // original followed by the original code and then the new code.

You only need to change these 4 lines, backup first. (code taken from osc 2.3.x, assume you have a 2.3.x version)


// original
//  $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";
  $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price, cd.categories_name ";

  if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
    $select_str .= ", SUM(tr.tax_rate) as tax_rate ";
  }

  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

  if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
    if (!tep_session_is_registered('customer_country_id')) {
      $customer_country_id = STORE_COUNTRY;
      $customer_zone_id = STORE_ZONE;
    }
    $from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";
  }

// original
//  $from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";
  $from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES_DESCRIPTION . " cd";

// original
//  $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 ";
  $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 and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' ";

  if (isset($HTTP_GET_VARS['categories_id']) && tep_not_null($HTTP_GET_VARS['categories_id'])) {
    if (isset($HTTP_GET_VARS['inc_subcat']) && ($HTTP_GET_VARS['inc_subcat'] == '1')) {
      $subcategories_array = array();
      tep_get_subcategories($subcategories_array, $HTTP_GET_VARS['categories_id']);

      $where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

      for ($i=0, $n=sizeof($subcategories_array); $i<$n; $i++ ) {
        $where_str .= " or p2c.categories_id = '" . (int)$subcategories_array[$i] . "'";
      }

      $where_str .= ")";
    } else {
      $where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";
    }
  }

  if (isset($HTTP_GET_VARS['manufacturers_id']) && tep_not_null($HTTP_GET_VARS['manufacturers_id'])) {
    $where_str .= " and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
  }

  if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {
    $where_str .= " and (";
    for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
      switch ($search_keywords[$i]) {
        case '(':
        case ')':
        case 'and':
        case 'or':
          $where_str .= " " . $search_keywords[$i] . " ";
          break;
        default:
          $keyword = tep_db_prepare_input($search_keywords[$i]);
// original
//          $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";
          $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%' or cd.categories_name like '%" . tep_db_input($keyword) . "%'";
          if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";
          $where_str .= ')';
          break;
      }
    }
    $where_str .= " )";
  }

:santa:

My Add-ons
Advanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download Support
Ajax Product Listing for osC 2.3.4 (bootstrap) Download Support
Category New Products Carousel for osC 2.3.4 (bootstrap) Download Support
Category Popular Products Carousel for osC 2.3.4 (bootstrap) Download Support
Customer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download Support
Front Page New Products Carousel for osC 2.3.4 (bootstrap) Download Support

Index Nested - Product Listing for osC 2.3.4 (bootstrapDownload Support
Match Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download Support
Modular Category Page for osC 2.3.4 (bootstrap)
Download Support

NEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download Support
NEW Equal Height Module for osC 2.3.4 (bootstrapDownload Support
Products Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download Support
Twitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap)
Download Support

Upcoming Products Modules for osC 2.3.4 (bootstrap) Download Support

 
Assisted Add-ons
Scroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support
 
Bootstrap Add-ons created by other members
osCommerce Bootstrap Addons and Code

Link to comment
Share on other sites

Thank you so much Dominic. I have added the code but I am getting an error here:

  $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 and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' ";

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 'cd where p.products_status = '1' and p.products_id = pd.products_id and pd.langu' 

I'll play with it a little and see what I come up with. 

Link to comment
Share on other sites

@

 

Hi Rachael,

 

I tested this in osc 2.3.1 and 2.3.4, using the 4 lines I posted above without error. Check you're code. Make sure the new lines are replacing the original lines exactly as shown above. When you copy and paste from the code above it may also copy the numbers, which you need to remove from the beginning of the line if they're there.

 

If you're still getting errors post all the code from your file here.

 

cheers

My Add-ons
Advanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download Support
Ajax Product Listing for osC 2.3.4 (bootstrap) Download Support
Category New Products Carousel for osC 2.3.4 (bootstrap) Download Support
Category Popular Products Carousel for osC 2.3.4 (bootstrap) Download Support
Customer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download Support
Front Page New Products Carousel for osC 2.3.4 (bootstrap) Download Support

Index Nested - Product Listing for osC 2.3.4 (bootstrapDownload Support
Match Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download Support
Modular Category Page for osC 2.3.4 (bootstrap)
Download Support

NEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download Support
NEW Equal Height Module for osC 2.3.4 (bootstrapDownload Support
Products Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download Support
Twitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap)
Download Support

Upcoming Products Modules for osC 2.3.4 (bootstrap) Download Support

 
Assisted Add-ons
Scroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support
 
Bootstrap Add-ons created by other members
osCommerce Bootstrap Addons and Code

Link to comment
Share on other sites

I will take some time to look closer at it tomorrow, tis the season for busy bees and I just have to finish a couple other projects before getting back to this. I will certainly post my error resolution then. Thank you so much Dominic!

Link to comment
Share on other sites

@

 

Just had a thought. You also need to make sure that these lines don't wrap around (ie broken line), they need to be continuous/straight. 

 

Tis the season!

 

cheers :santa:

My Add-ons
Advanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download Support
Ajax Product Listing for osC 2.3.4 (bootstrap) Download Support
Category New Products Carousel for osC 2.3.4 (bootstrap) Download Support
Category Popular Products Carousel for osC 2.3.4 (bootstrap) Download Support
Customer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download Support
Front Page New Products Carousel for osC 2.3.4 (bootstrap) Download Support

Index Nested - Product Listing for osC 2.3.4 (bootstrapDownload Support
Match Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download Support
Modular Category Page for osC 2.3.4 (bootstrap)
Download Support

NEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download Support
NEW Equal Height Module for osC 2.3.4 (bootstrapDownload Support
Products Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download Support
Twitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap)
Download Support

Upcoming Products Modules for osC 2.3.4 (bootstrap) Download Support

 
Assisted Add-ons
Scroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support
 
Bootstrap Add-ons created by other members
osCommerce Bootstrap Addons and Code

Link to comment
Share on other sites

Woot!!!! Got it to work!!! I have no clue what I was doing wrong but your code works like a charm (once you have it installed right!). Thank you so much Dominic! I could NOT have done it without you. 

(w00t)  :beers:  :beers:  :beers:  Next round of beer is on me!

Link to comment
Share on other sites

Hi auzStar

 

I search for the same. May i don't want see the products in the search... i want see a list of categories...

 

so i have ink shop for printers... the ink him self have own name like "hp nr. 11 black" but the clients search for the printer name linke "Business Inkjet 2250". an this name is in my shop the category/subcategory.

 

so when i search for "Business Inkjet 2250" i want a list of printers (categories) with this therm..like this:

 

Business Inkjet 2250

Business Inkjet 2250 ST

Business Inkjet 2250 TN

Business Inkjet 2250 XI

 

This are all subcategories... 

 

i hope you understand my english and what i mean :-)

 

do you have/know any solution for this

 

many thanks

 

 

 

Link to comment
Share on other sites

@@xplosiv

 

Hi Andy,

 

Yes, the mod above for queenzukie displays "products only" in search results, even though the keywords match sub-category names. 

You want to display only category names in search results, presumably with these results being clickable links to the categories. 

 

There is an addon "Display matching categories in search results" here-> http://addons.oscommerce.com/info/5918 you may be able to use. It's for osc 2.2 but could be adaptable to newer versions of osc.

 

If I get a chance I will have a good look at it.

 

cheers

 

 

 

My Add-ons
Advanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download Support
Ajax Product Listing for osC 2.3.4 (bootstrap) Download Support
Category New Products Carousel for osC 2.3.4 (bootstrap) Download Support
Category Popular Products Carousel for osC 2.3.4 (bootstrap) Download Support
Customer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download Support
Front Page New Products Carousel for osC 2.3.4 (bootstrap) Download Support

Index Nested - Product Listing for osC 2.3.4 (bootstrapDownload Support
Match Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download Support
Modular Category Page for osC 2.3.4 (bootstrap)
Download Support

NEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download Support
NEW Equal Height Module for osC 2.3.4 (bootstrapDownload Support
Products Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download Support
Twitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap)
Download Support

Upcoming Products Modules for osC 2.3.4 (bootstrap) Download Support

 
Assisted Add-ons
Scroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support
 
Bootstrap Add-ons created by other members
osCommerce Bootstrap Addons and Code

Link to comment
Share on other sites

  • 2 weeks later...

@@xplosiv

 

@@xplosiv

 

Hi Andy,

 

Yes, the mod above for queenzukie displays "products only" in search results, even though the keywords match sub-category names. 

You want to display only category names in search results, presumably with these results being clickable links to the categories. 

 

There is an addon "Display matching categories in search results" here-> http://addons.oscommerce.com/info/5918 you may be able to use. It's for osc 2.2 but could be adaptable to newer versions of osc.

 

If I get a chance I will have a good look at it.

 

cheers

 

Hi Andy,

 

If you still need this I have adapted the osc 2.2 version of the "Display matching categories in search results" addon to osc 2.3.x

Links below:

download: http://addons.oscommerce.com/info/9197

support: http://www.oscommerce.com/forums/topic/399388-contribution-match-categories-in-search-results-for-oscommerce-versions-23x/

 

cheers

My Add-ons
Advanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download Support
Ajax Product Listing for osC 2.3.4 (bootstrap) Download Support
Category New Products Carousel for osC 2.3.4 (bootstrap) Download Support
Category Popular Products Carousel for osC 2.3.4 (bootstrap) Download Support
Customer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download Support
Front Page New Products Carousel for osC 2.3.4 (bootstrap) Download Support

Index Nested - Product Listing for osC 2.3.4 (bootstrapDownload Support
Match Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download Support
Modular Category Page for osC 2.3.4 (bootstrap)
Download Support

NEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download Support
NEW Equal Height Module for osC 2.3.4 (bootstrapDownload Support
Products Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download Support
Twitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap)
Download Support

Upcoming Products Modules for osC 2.3.4 (bootstrap) Download Support

 
Assisted Add-ons
Scroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support
 
Bootstrap Add-ons created by other members
osCommerce Bootstrap Addons and Code

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...