Mysql query to search data based on comma separated string of ID's

问题: I have two tables product and category table. On my website, I want to filter my product based on categories. So I am passing the categories ids as a query string in the UR...

问题:

I have two tables product and category table. On my website, I want to filter my product based on categories. So I am passing the categories ids as a query string in the URL. The ID's are passed in the URL as comma separated string.

I need a MySQL query or stored procedure that can search in the product table based on the category ids.

Product Table

| ID | Product_name | Categories                   |
| 1  | TV 1         | LED, 32 inch, HD             |
| 2  | TV 2         | OLED, 55 inch, Ultra HD (4K) |
| 3  | TV 3         | LCD , 24 inch, HD            |
| 4  | TV 4         | LED, 55 inch, Full HD        |

Category Table

| ID | Category Name |
| 1  | LED           |
| 2  | OLED          |
| 3  | LCD           |
| 4  | 32 inch       |
| 5  | 55 inch       |
| 6  | 24 inch       |
| 7  | HD            |
| 8  | Full HD       |
| 9  | Ultra HD (4K) |

If the filter query string is "1,3,7" the Product table should return below output

Product Table

| ID | Product_name | Categories        |
| 1  | TV 1         | LED, 32 inch, HD  |
| 3  | TV 3         | LCD , 24 inch, HD |

What query or stored procedure should be in MySQL?


回答1:

A possible solution ...

select p.* , count(c.id) as cnt_match   
from product as p 
join category_table  c 
where 
      c.id in ( 1,3,7) and 
      instr(replace(replace(concat(',',p.categories,','),' ,',','),', ',','),
            concat(',',c.categorie_name,','))>0
group by p.id,p.product_name,p.categories;

And the result is 3 rows , TV 4 is a HD

+------+--------------+-----------------------+-----------+
| id   | product_name | categories            | cnt_match |
+------+--------------+-----------------------+-----------+
|    1 | TV 1         | LED, 32 inch, HD      |         2 |
|    3 | TV 3         | LCD , 24 inch, HD     |         2 |
|    4 | TV 4         | LED, 55 inch, Full HD |         1 |
+------+--------------+-----------------------+-----------+

AND because the data are not clean in the field categories , you need to add some replace to remove space before and after coma

  • 发表于 2019-03-21 05:10
  • 阅读 ( 102 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除