How to select with one PHP + MYSQL query for matches on all items?

Question

PHP

There are 2 tables in the database:
1) Product table product:
1 id int(11) AUTO_INCREMENT
2 code varchar(255)
3 category int(11)
4 manufacturer int(11)
5 name varchar(255)
6 description text
7 price float
8 quantity int(11)
9 url varchar(255)
10 seo_h1 varchar(255)
11 seo_title varchar(255)
12 seo_description varchar(255)
13 seo_keywords varchar(255)
14 seo_disallow int(1)
15 active int(1)
2) Additional attribute value table:
1 id int(11)
2 object_id int(11)
3 field_id int(11)
4 value varchar(255)
Each product can have an unlimited number of additional attributes.
The attribute table row is associated with the product by the object_id field
Product attributes with id x are all attributes with object_id = x You
must select a product whose properties are all equal to those passed.
those. you need to select all the fields of the product table,
where in the attribute table, for example,
the field_id attribute = 2 and its value value = 430, while the
field_id attribute = 3 and its value value = 0
How to do this in one query?

Answer 1

SELECT `p`.*
  FROM `product` AS `p`
  JOIN `attributes` AS `a1` ON `a1`.`object_id` = `p`.`id`
    AND `a1`.`field_id` = :field1 AND `a1`.`value` = :value1
  JOIN `attributes` AS `a2` ON `a2`.`object_id` = `p`.`id`
    AND `a2`.`field_id` = :field2 AND `a2`.`value` = :value2