问题描述:

How to select products which have all attributes specified by user

For example: user specified attributes to search products: 41,2,4,6

MYSQL structure is:

TABLE: offer

`id` int(11) NOT NULL auto_increment,

`title_pl` varchar(100) character set utf8 collate utf8_polish_ci NOT NULL,

TABLE: offer_att

`id` int(11) NOT NULL auto_increment,

`offer_id` int(11) NOT NULL,

`att_id` int(11) NOT NULL,

TABLE att

`id` int(11) NOT NULL auto_increment,

`title_pl` varchar(255) character set utf8 collate utf8_polish_ci NOT NULL,

网友答案:

Is this what you need?

SELECT *
FROM offer
WHERE id IN
    (SELECT offer_id
     FROM offer_att oa,
          att a
     WHERE a.title_pl IN (41,
                          2,
                          4,
                          6)
       AND a.id = oa.att_id);

P.S. Here is a sample fiddle.

网友答案:

If you want to fetch only offers that have all 4 attributes (41,2,4,6) together then you should use next query

SELECT
    o.id
    o.title_pl
FROM
    offer o INNER JOIN offer_att oa ON o.id = oa.offer_id AND oa.att_id IN (41,2,4,6)   
GROUP BY o.id
HAVING COUNT(DISTINCT oa.att_id) = 4

If you want to fetch offers that have at least one of attributes (41 or 2 or 4 or 6) use this one:

SELECT
    o.id
    o.title_pl
FROM
    offer o INNER JOIN offer_att oa ON o.id = oa.offer_id AND oa.att_id IN (41,2,4,6)   

PHP code to generate parameters for your SQL query:

<?php
$attributes = array(41, 2, 4, 6);
$count = count($attributes);
$list = implode(',', $attributes);
$query = '
    SELECT
        o.id
        o.title_pl
    FROM
        offer o INNER JOIN offer_att oa ON o.id = oa.offer_id AND oa.att_id IN (' . $list . ')  
';
$db->query($query);
?>
相关阅读:
Top