There are three database tables for which you need to create a filter.

  1. table tag (id_tag, tag) which has all the tags together (in a heap)

Table tag

  1. tag_post (id_t_p, id_tag, id_post, num_tag_inpost)

Table tag_post

  1. post (id_post, post)

Post table

Example: if you want to remove one product from these tables, you need to take from the table tag_post c 1 to 8 for example, for id_post post 5, remove all id_tag id_tag and remove the tag from the tag table on these id tags then you get one item of type 1) артикул 2) Вид изделия 3)Материал 4) Основная вставка 5) Производитель is important to us only ID up to 5

I make a filter on php. And I find it very difficult to compile a SQL query ( A similar question in which I wanted to remove the goods from the database. Then I decided to add the numbering from 1 to the number of repeated digits in the database so that the query was simple. What does the filter look like

    2 answers 2

    I hope I understand the question correctly. You have designed the characteristics of the goods as tags through a many-to-many relationship.

    1. For an "economical" list of products, you can store the tag values ​​directly in the post table in a large text field (TEXT type is appropriate) in a serialized form. Then simple queries without filtering by tag will not require complex JOINs.
      So does stackoverflow itself!
    2. Filter by any of the above tags is possible as … WHERE tag_post.id_tag IN(1,3,5)
    3. The filter for all specified tags will require a maximum of work, so I will show in detail.

    Get a list of IT products that has all the necessary characteristics, that is, the necessary tags are listed:

     SELECT id_post FROM tag_post WHERE id_tag IN (1,3,5) GROUP BY id_post HAVING COUNT(*) = 3

    In the phrase HAVING, we indicate the number of tags that we have in the IN () brackets in the example of 3.
    If you need fields from post , use this query as a sub-query:

     SELECT p.* FROM post AS p JOIN ( SELECT id_post FROM tag_post WHERE id_tag IN (1,3,5) GROUP BY id_post HAVING COUNT(*) = 3 ) AS tpg USING(id_post)

      To get tags that correspond to the product, you can use the request

       SELECT p.post AS post, GROUP_CONCAT( DISTINCT t.tag ORDER BY tp.num_tag_inpost ) AS tags FROM post AS p JOIN tag_post AS tp ON p.id_post = tp.id_post JOIN tag AS t ON tp.id_tag = t.id_tag WHERE p.id_post = 5 GROUP BY p.id_post

      To receive goods that correspond to a set of specific tags, for example, with tag_id identifiers: 1, 2, 3, 4, 5, you can use this query

       SELECT p.post AS post FROM post AS p JOIN tag_post AS tp ON p.id_post = tp.id_post WHERE tp.id_tag IN(1, 2, 3, 4, 5) GROUP BY p.id_post
      • On this request, I receive 1 item. How to do to get all the products for which you have entered the tags in the filter's input so that you can later filter them by this filter!? For example, the user enters the name of 5 tags and I send their IDs via GET and take out the relevant goods - pwnz22
      • @ pwnz22, corrected the answer. - cheops
      • Something is not quite right (I send tag tags and then look for values ​​that are in table post by id_post, tag_id This is id_tag? - pwnz22
      • First, tags are searched in the tag_post table, and then products are searched for the id_post keys associated with them. - cheops
      • one
        Yes, you just need to substitute them instead of 1, 2, 3, 4, 5 in the construction of IN - cheops