SQL "ADVANCED SEARCH RESULT" QUERY FOR EXTRA FIELDS

[mercurio77]

THIS QUERY IS TAKEN FROM THE ADVANCED_SEARCH_RESULT.PHP WITH THE EXTRA FIELDS CONTRIBUTE AND

I WOULD LIKE TO PERFORM AN "AND" QUERY BETWEEN MULTIPLE VALUES I CHOOSE

FROM THE EXTRA FIELDS TABLE

 

select distinct p.products_image, 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 from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((p2pef.products_extra_fields_value like '%205%' AND p2pef.products_extra_fields_id = 1) ) and ((p2pef.products_extra_fields_value like '%55%' AND p2pef.products_extra_fields_id = 2) ) order by pd.products_name

 

HERE THE DUMP OF THE RELATED EXTRA FIELDS TABLE:

 

CREATE TABLE `products_to_products_extra_fields` (

`products_id` int(11) NOT NULL,

`products_extra_fields_id` int(11) NOT NULL,

`products_extra_fields_value` varchar(64) default NULL,

PRIMARY KEY (`products_id`,`products_extra_fields_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

--

-- Dump dei dati per la tabella `products_to_products_extra_fields`

--

 

INSERT INTO `products_to_products_extra_fields` (`products_id`, `products_extra_fields_id`, `products_extra_fields_value`) VALUES (28, 1, '205'),

(28, 2, '65'),

(28, 3, 'R15'),

(1, 1, '185'),

(1, 2, '44'),

(1, 3, 'R13'),

(26, 1, '190'),

(26, 2, '55'),

(26, 3, 'R17'),

(29, 1, '205'),

(29, 2, '55'),

(29, 3, 'R15');

 

FINALLY THIS IS THE URL I SEND TO THE SEARCH (FOR REFERENCE ONLY):

 

http://192.168.0.245/public/catalog/advanc...dfrom=&dto=

 

THANK YOU SO MUCH FOR ANY SUGGESTION

MYSQL GURU WELCOME ;)

[imqqmi]

I had the same problem with attributes. I use the Adam contribution to add drop down menus for all attributes grouped by optiongroups. This will search with an OR function so selecting more options adds more matches rather than less.

 

The solution to this may well be usable for your situation. Diego Medina from the forums.mysql.com suggested the folowing query:

 

SELECT count(*) as cnt, products_id FROM products_attributes

WHERE

options_values_id =1 OR

options_values_id =3 OR

options_values_id =5

GROUP BY products_id

HAVING cnt = 3

ORDER BY NULL;

 

This can be rewritten as:

 

SELECT distinct count(products_id) as cnt, products_id FROM products_attributes

WHERE

options_values_id in (1,3,5)

GROUP BY products_id

HAVING cnt = 2

ORDER BY products_id";

 

Which is exactly like the one Adam has written without the count() and HAVING clause.

 

How it works: The query counts the number of matching products_id, this should match the number of attributes the user selected. In the Adam code this is done in a while(){} loop. Each loop adds an attribute and the 'cnt =' is updated accordingly. So if there are 3 attributes selected by the user, each product should produce 3 matches and is returned by the database, 2 matches and the row is not returned.

 

You can modify his code by adding the count and having clause.

 

This will take care of the query, but will fail because the split_page_results.php class tries to strip out certain parts just to get a matching rows count. My solution (although crude and inefficient) was to run the full query and get the row count and feed in into the class variable. Like I said it tries to strip the having clause which uses the count() and the query fails if count() isn't followed by a HAVING cnt = x.

 

see his contrib:

http://addons.oscommerce.com/info/2337

 

 

His code:

  $attributes_query = tep_db_query("select * from " . TABLE_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 " . TABLE_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.")";

 

My modified code in advanced_search_result.php:

 

	$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 . "'");
 	//Inblik from OR to AND START
	$i=0;
	//Inblik END
	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"]] . ','; 
	  	//Inblik from OR to AND START
		$i++;
		//Inblik END
		}
 	}

	if( i ) {
		//Inblik from OR to AND START
		$having1_str = "group by p.products_id having cnt = ".$i." ";//" GROUP BY p.products_id HAVING total = ".$i." ";
		$select_str .= ", count(p.products_id) as cnt ";
		//Inblik END
	}

	$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.") ";

 

Further down find:

$listing_sql = $select_str . $from_str . $where_str . $order_str;

 

Replace with:

$listing_sql = $select_str . $from_str . $where_str . $having1_str . $order_str;

 

In split_page_results.php (catalog/includes/classes/)

 

 

From line 57 I have this (I don't have the original code handy) changed code is between the inblik comments:

		
 $pos_order_by = strpos($this->sql_query, ' order by', $pos_from);
     if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by;


		if (strpos($this->sql_query, 'distinct') || strpos($this->sql_query, 'group by')) {
       $count_string = '' . tep_db_input($count_key);
     } else {
       $count_string = tep_db_input($count_key);
     }

		// Inblik start

		if( pos_having ) $pos_shorter = 0; // Check if having is present in query
		else $pos_shorter = $pos_from;

		$finalquery = "select count(" . $count_string . ") as total, count($count_string) as cnt " . substr($this->sql_query, $pos_from, $pos_to-$pos_shorter);

		//echo '<br> pos_to '.$pos_to.'<br> pos_having '.$pos_having.'<br> pos_group_by '.$pos_group_by.'<br> pos_from '.$pos_from;
		//echo '<br>'. substr($this->sql_query, $pos_from, ($pos_to - pos_from));
		//echo '<br><br>'.$finalquery.'<br>';

		$result		= mysql_query($this->sql_query) or die(mysql_error());
		$matches	= mysql_num_rows($result);
		//echo '<br>matches = '.$matches;
		$this->number_of_rows = $matches;
		//inblik end

     $this->number_of_pages = ceil($this->number_of_rows / $this->number_of_rows_per_page);

 

 

How to change your code to do the same for extra fields is up to you.

 

 

 

Good luck!

Edited April 16, 2008 by imqqmi