Jump to content
  • Checkout
  • Login
  • Get in touch


The e-commerce.

Advanced Attribute Search to use AND?


Recommended Posts

Hi, I apologise for cross-posting but I posted this under contribution support and didn't get a reply so wondered if I'd posted in the wrong place...


I am using the Advanced Attribute Search contribution (http://www.oscommerce.com/community/contributions,2337/) which adds drop down boxes to the advanced search page and pulls in the search options from the product attributes set up in the system.


I was wondering if anyone knows how to set this up so it displays results using the AND operator, rather than OR?


For example I have 3 attribute sets: type, size and colour.


If I select type->1 and colour->blue on the search page, the results returned are all type 1 products regardless of their colour, together with all blue products regardless of their type.


I would like this to return only products that have both attributes (ie. only type 1 products that are blue) but I can't work it out...


The code that is added to advanced_search_result.php (and the code I'm therefore assuming needs to be added to/changed is (line 297):


  // This is the accompanying code to the advanced_search.php that allows for products to be searched on their
 // attributes through a drop down list box, it was done by adam, if it helps you perhaps you can help me by donating through paypal, my
 // email address is: [email protected] so that I can make more contributions
 // If you have a look up near line 214 there is an additional from_str that includes product attributes in the from list
 $attributes_query = tep_db_query("select * from products_options where language_id = '" . (int)$languages_id . "'");
 $option_value_selected = false;
 $products_stock_attributes_array = array();
 while ($attributes = tep_db_fetch_array($attributes_query)) {
	 if (isset($HTTP_GET_VARS[$attributes["products_options_name"]]) && !empty($HTTP_GET_VARS[$attributes["products_options_name"]]))
    	 $option_value_selected = true;
 // the above while loop is to check to see if any values were selected
 if ($option_value_selected == true){
	 $attributes_query2 = tep_db_query("select * from products_options where language_id = '" . (int)$languages_id . "'");
	 while ($attributes2 = tep_db_fetch_array($attributes_query2)) {
   if (isset($HTTP_GET_VARS[$attributes2["products_options_name"]]) && !empty($HTTP_GET_VARS[$attributes2["products_options_name"]])) {
	 $str_selected_values = $str_selected_values . $HTTP_GET_VARS[$attributes2["products_options_name"]] . ','; 
	 $str_selected_values = substr($str_selected_values,0,strlen($str_selected_values) - 1);
	 $where_str .= "and pa.products_id = p.products_id and pa.options_values_id in (".$str_selected_values.")";
	 // end of accompanying code to advanced_search_result.php that accompanies advanced_search.php
	 // that will include all options_values_id that were selected


I have the setup to use AND as the default search operator but it doesn't affect the attributes search.... Any help would be greatly appreciated. Thanks.

Link to comment
Share on other sites

If it helps anyone help me (please :o )



The table which holds the data for the product attributes (called products_attributes), has columns of:






products_attributes_id is unique.

products_id corresponds with the table that holds further product info.

options_id is 1,2 or 3 (for each type of attribute - type, size, colour)

options_values_id is the column that I am trying to get the AND working for


So for example, 2 rows that correspond to the same product (id 31) are:

products_attributes_id = 01

products_id = 31

options_id = 2

options_values_id = 3


products_attributes_id = 02

products_id = 31

options_id = 3

options_values_id = 12


I need to display the product info of products where options_values_id = 3 and 12, so I've assumed the product above would fall into that.....?


Can someone tell me how I would write the select statement for that please?


Again, any help appreciated. Thanks

Link to comment
Share on other sites


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

  • Create New...