OSCOMMERCE SUPPORT CALL 702-453-3332

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

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:


My modified code in advanced_search_result.php:



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:



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



Good luck!