PHP MySQL查询从多个ID阵列中获取类别名称(PHP MySQL Query Get Category Name From Multiple ID Array)

我有类别表tb_category

categoryid | category_name 1 | New Product 2 | Promo

我有产品表tb_product

productid | product_name | categoryid_fk 1 | Watch | 1 2 | Bag | 1,2

现在我想从categoryid_fk = name中获得类别名称, categoryid_fk = 1,2

怎么做?

我的SQL代码到目前为止:

//category id --> 1,2 $qCategory = mysqli_query($con, "SELECT * FROM tb_category WHERE categoryid IN ('" . $dProduct['categoryid_fk'] . "')"); foreach($qCategory as $cause) { echo $cause['category_name']; }

当我运行查询时,我只能得到带有单个categoryid标识的分类名称。

I have category table tb_category

categoryid | category_name 1 | New Product 2 | Promo

And I have product table tb_product

productid | product_name | categoryid_fk 1 | Watch | 1 2 | Bag | 1,2

And now I want to get category name from productid = 2 with categoryid_fk = 1,2

How to do that?

My SQL code so far:

//category id --> 1,2 $qCategory = mysqli_query($con, "SELECT * FROM tb_category WHERE categoryid IN ('" . $dProduct['categoryid_fk'] . "')"); foreach($qCategory as $cause) { echo $cause['category_name']; }

When I run the query I only get the category name with single categoryid.

最满意答案

使用find_in_set函数。 用法:find_in_set(search_string,字符串列表)。 在此处查看演示: http : //sqlfiddle.com/#!9/4e5a48/1

编辑:由于需求被改变,添加一个替换函数来删除空格,以便find_in_set可以工作。

SELECT tb_category.* FROM tb_category,tb_product WHERE find_in_set(categoryid, replace(categoryid_fk,' ','')) > 0 and productid = 2; Result: categoryid category_name 1 New Product 2 Promo

Use find_in_set function. Usage: find_in_set(search_string, list of strings). See demo here: http://sqlfiddle.com/#!9/4e5a48/1

EDIT: since the requirement is changed, add a replace function to remove spaces so that find_in_set will work.

SELECT tb_category.* FROM tb_category,tb_product WHERE find_in_set(categoryid, replace(categoryid_fk,' ','')) > 0 and productid = 2; Result: categoryid category_name 1 New Product 2 PromoPHP MySQL查询从多个ID阵列中获取类别名称(PHP MySQL Query Get Category Name From Multiple ID Array)

我有类别表tb_category

categoryid | category_name 1 | New Product 2 | Promo

我有产品表tb_product

productid | product_name | categoryid_fk 1 | Watch | 1 2 | Bag | 1,2

现在我想从categoryid_fk = name中获得类别名称, categoryid_fk = 1,2

怎么做?

我的SQL代码到目前为止:

//category id --> 1,2 $qCategory = mysqli_query($con, "SELECT * FROM tb_category WHERE categoryid IN ('" . $dProduct['categoryid_fk'] . "')"); foreach($qCategory as $cause) { echo $cause['category_name']; }

当我运行查询时,我只能得到带有单个categoryid标识的分类名称。

I have category table tb_category

categoryid | category_name 1 | New Product 2 | Promo

And I have product table tb_product

productid | product_name | categoryid_fk 1 | Watch | 1 2 | Bag | 1,2

And now I want to get category name from productid = 2 with categoryid_fk = 1,2

How to do that?

My SQL code so far:

//category id --> 1,2 $qCategory = mysqli_query($con, "SELECT * FROM tb_category WHERE categoryid IN ('" . $dProduct['categoryid_fk'] . "')"); foreach($qCategory as $cause) { echo $cause['category_name']; }

When I run the query I only get the category name with single categoryid.

最满意答案

使用find_in_set函数。 用法:find_in_set(search_string,字符串列表)。 在此处查看演示: http : //sqlfiddle.com/#!9/4e5a48/1

编辑:由于需求被改变,添加一个替换函数来删除空格,以便find_in_set可以工作。

SELECT tb_category.* FROM tb_category,tb_product WHERE find_in_set(categoryid, replace(categoryid_fk,' ','')) > 0 and productid = 2; Result: categoryid category_name 1 New Product 2 Promo

Use find_in_set function. Usage: find_in_set(search_string, list of strings). See demo here: http://sqlfiddle.com/#!9/4e5a48/1

EDIT: since the requirement is changed, add a replace function to remove spaces so that find_in_set will work.

SELECT tb_category.* FROM tb_category,tb_product WHERE find_in_set(categoryid, replace(categoryid_fk,' ','')) > 0 and productid = 2; Result: categoryid category_name 1 New Product 2 Promo