我有类别表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 | PromoAnd I have product table tb_product
productid | product_name | categoryid_fk 1 | Watch | 1 2 | Bag | 1,2And 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 PromoUse 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 | PromoAnd I have product table tb_product
productid | product_name | categoryid_fk 1 | Watch | 1 2 | Bag | 1,2And 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 PromoUse 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
发布评论