Need help for complex query

I have a product table, category table and another table to join product and category where category stored in JSON format. What I need to do is to get all the product with a list category IDs.

Category table

Code:

-----------------------
| id | category_name
-----------------------

Product table

Code:

-----------------------
| id | product_title
-----------------------

Product Category relation table

Code:

-----------------------
| product_id | categories [varchar(100) but data stored in JSON format]
-----------------------
| 1          | [1,4,6]
-----------------------
| 2          | [34,8,9]
-----------------------
| 2          | [27,18,29]

According to example, what will be the query if I need to get all the product with the category list (array)[34,18,6]

I will be very glad if you suggest any better table structures. Thanks in advance. 

"Who thinks in code"
Perfectly describes who I am
mbparvez.me

PHP Code:

//With current data store:
    $find  = [34,18,6];
    $where = [];

    foreach ($find as $cID)
    {
        $where[] = "categories LIKE '%[".$cID.",%'";
        $where[] = "categories LIKE '%,".$cID.",%'";
        $where[] = "categories LIKE '%,".$cID."]%'";
    }
    
    $strW = "(".implode(" OR ", $where).")";
    
    // Query
    $sql  = " SELECT product_id";
    $sql .= " FROM Product_Category";
    $sql .= " WHERE ".$strW; 


Learning CI4 from my works, from errors and how to fix bugs in the community

Love CI & Thanks CI Teams

After

PHP Code:

$query = "SELECT * FROM Product WHERE id IN (".$sql.")"; 


Learning CI4 from my works, from errors and how to fix bugs in the community

Love CI & Thanks CI Teams

It is not very nice way to store multiple values in one field - it is not convenient and not practical.
You can modify your relationship table to store category IDs one per field, but on different rows.
So your table structure should be:

Category table:

Code:

-----------------------
| id | category_name
-----------------------

Product table:

Code:

-----------------------
| id | product_title
-----------------------

Product_category table:

Code:

-----------------------
| product_id | category_id
-----------------------
| 1          | 1
-----------------------
| 1          | 4
-----------------------
| 1          | 6
-----------------------
| 2          | 34
-----------------------
| 2          | 8
-----------------------
| 2          | 9
-----------------------