Rachael w. Posted December 10, 2014 Share Posted December 10, 2014 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 More sharing options...
auzStar Posted December 10, 2014 Share Posted December 10, 2014 @ 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-onsAdvanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download SupportAjax Product Listing for osC 2.3.4 (bootstrap) Download SupportCategory New Products Carousel for osC 2.3.4 (bootstrap) Download SupportCategory Popular Products Carousel for osC 2.3.4 (bootstrap) Download SupportCustomer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportFront Page New Products Carousel for osC 2.3.4 (bootstrap) Download SupportIndex Nested - Product Listing for osC 2.3.4 (bootstrap) Download SupportMatch Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download SupportModular Category Page for osC 2.3.4 (bootstrap) Download SupportNEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download SupportNEW Equal Height Module for osC 2.3.4 (bootstrap) Download SupportProducts Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download SupportTwitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportUpcoming Products Modules for osC 2.3.4 (bootstrap) Download Support Assisted Add-onsScroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support Bootstrap Add-ons created by other membersosCommerce Bootstrap Addons and Code Link to comment Share on other sites More sharing options...
auzStar Posted December 10, 2014 Share Posted December 10, 2014 @ 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-onsAdvanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download SupportAjax Product Listing for osC 2.3.4 (bootstrap) Download SupportCategory New Products Carousel for osC 2.3.4 (bootstrap) Download SupportCategory Popular Products Carousel for osC 2.3.4 (bootstrap) Download SupportCustomer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportFront Page New Products Carousel for osC 2.3.4 (bootstrap) Download SupportIndex Nested - Product Listing for osC 2.3.4 (bootstrap) Download SupportMatch Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download SupportModular Category Page for osC 2.3.4 (bootstrap) Download SupportNEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download SupportNEW Equal Height Module for osC 2.3.4 (bootstrap) Download SupportProducts Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download SupportTwitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportUpcoming Products Modules for osC 2.3.4 (bootstrap) Download Support Assisted Add-onsScroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support Bootstrap Add-ons created by other membersosCommerce Bootstrap Addons and Code Link to comment Share on other sites More sharing options...
Rachael w. Posted December 10, 2014 Author Share Posted December 10, 2014 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 More sharing options...
auzStar Posted December 11, 2014 Share Posted December 11, 2014 @ 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-onsAdvanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download SupportAjax Product Listing for osC 2.3.4 (bootstrap) Download SupportCategory New Products Carousel for osC 2.3.4 (bootstrap) Download SupportCategory Popular Products Carousel for osC 2.3.4 (bootstrap) Download SupportCustomer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportFront Page New Products Carousel for osC 2.3.4 (bootstrap) Download SupportIndex Nested - Product Listing for osC 2.3.4 (bootstrap) Download SupportMatch Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download SupportModular Category Page for osC 2.3.4 (bootstrap) Download SupportNEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download SupportNEW Equal Height Module for osC 2.3.4 (bootstrap) Download SupportProducts Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download SupportTwitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportUpcoming Products Modules for osC 2.3.4 (bootstrap) Download Support Assisted Add-onsScroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support Bootstrap Add-ons created by other membersosCommerce Bootstrap Addons and Code Link to comment Share on other sites More sharing options...
Rachael w. Posted December 11, 2014 Author Share Posted December 11, 2014 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 More sharing options...
auzStar Posted December 11, 2014 Share Posted December 11, 2014 @ 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-onsAdvanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download SupportAjax Product Listing for osC 2.3.4 (bootstrap) Download SupportCategory New Products Carousel for osC 2.3.4 (bootstrap) Download SupportCategory Popular Products Carousel for osC 2.3.4 (bootstrap) Download SupportCustomer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportFront Page New Products Carousel for osC 2.3.4 (bootstrap) Download SupportIndex Nested - Product Listing for osC 2.3.4 (bootstrap) Download SupportMatch Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download SupportModular Category Page for osC 2.3.4 (bootstrap) Download SupportNEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download SupportNEW Equal Height Module for osC 2.3.4 (bootstrap) Download SupportProducts Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download SupportTwitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportUpcoming Products Modules for osC 2.3.4 (bootstrap) Download Support Assisted Add-onsScroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support Bootstrap Add-ons created by other membersosCommerce Bootstrap Addons and Code Link to comment Share on other sites More sharing options...
Rachael w. Posted December 11, 2014 Author Share Posted December 11, 2014 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 More sharing options...
xplosiv Posted December 16, 2014 Share Posted December 16, 2014 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 More sharing options...
auzStar Posted December 17, 2014 Share Posted December 17, 2014 @@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-onsAdvanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download SupportAjax Product Listing for osC 2.3.4 (bootstrap) Download SupportCategory New Products Carousel for osC 2.3.4 (bootstrap) Download SupportCategory Popular Products Carousel for osC 2.3.4 (bootstrap) Download SupportCustomer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportFront Page New Products Carousel for osC 2.3.4 (bootstrap) Download SupportIndex Nested - Product Listing for osC 2.3.4 (bootstrap) Download SupportMatch Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download SupportModular Category Page for osC 2.3.4 (bootstrap) Download SupportNEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download SupportNEW Equal Height Module for osC 2.3.4 (bootstrap) Download SupportProducts Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download SupportTwitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportUpcoming Products Modules for osC 2.3.4 (bootstrap) Download Support Assisted Add-onsScroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support Bootstrap Add-ons created by other membersosCommerce Bootstrap Addons and Code Link to comment Share on other sites More sharing options...
auzStar Posted December 29, 2014 Share Posted December 29, 2014 @@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-onsAdvanced Cache Control Tool for osCommerce 2.3.x (non-bootstrap) Download SupportAjax Product Listing for osC 2.3.4 (bootstrap) Download SupportCategory New Products Carousel for osC 2.3.4 (bootstrap) Download SupportCategory Popular Products Carousel for osC 2.3.4 (bootstrap) Download SupportCustomer Testimonials for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportFront Page New Products Carousel for osC 2.3.4 (bootstrap) Download SupportIndex Nested - Product Listing for osC 2.3.4 (bootstrap) Download SupportMatch Categories in Search Results for osCommerce versions 2.3.x (non-bootstrap) Download SupportModular Category Page for osC 2.3.4 (bootstrap) Download SupportNEW Australia Post Shipping Modules for osCommerce 2.3.x (non-bootstrap) Download SupportNEW Equal Height Module for osC 2.3.4 (bootstrap) Download SupportProducts Low Stock Report for osC 2.3.x (bootstrap and non-bootstrap) Download SupportTwitter Typeahead Autocomplete Search for osCommerce 2.3.4 (bootstrap and non-bootstrap) Download SupportUpcoming Products Modules for osC 2.3.4 (bootstrap) Download Support Assisted Add-onsScroll Boxes for osCommerce 2.3.x (bootstrap and non-bootstrap) Download Support Bootstrap Add-ons created by other membersosCommerce Bootstrap Addons and Code Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.