CODEWITHSUNDEEP
phpmysql
HiDayurie Dave
HiDayurie Dave

Reputation: 1807

PHP MySQL Query Get Category Name From Multiple ID Array

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.

Upvotes: 0

Views: 505

Answers (1)

jose_bacoy
jose_bacoy

Reputation: 12714

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

Upvotes: 1

Related Questions