How to search out the exact result which in the array data field?

Hello Madam, Sir,
I am really newbie  in codeigniter,  I like this frame much.
Now there is problem when I try to filter some product 
We have stored a products table has column  id,  name, category_id,   and the category_id stored the product cat id format  like ["4","25","31"],
which means the product   belongs to the sub cat ID 4,  and 25, 31.  at the same time.

when we want to search out all products belong to   cat ID 4 ,  we keep to use below code

$this->db->like('category_id', '4');    it returns result of  those products belong to  ["14","25","31"],  ["4","24","31"], ["9","22","34"] ..... 

we just need the result is    ["4","any","any"],  not  ["14","25","31"],  ["9","22","34"] .

How to solve this problem?

Best regards

Hello Madam, Sir,
I am really newbie  in codeigniter,  I like this frame much.
Now there is problem when I try to filter some product 
We have stored a products table has column  id,  name, category_id,   and the category_id stored the product cat id format  like ["4","25","31"],
which means the product   belongs to the sub cat ID 4,  and 25, 31.  at the same time.

when we want to search out all products belong to   cat ID 4 ,  we keep to use below code

$this->db->like('category_id', '4');    it returns result of  those products belong to  ["14","25","31"],  ["4","24","31"], ["9","22","34"] ..... 

we just need the result is    ["4","any","any"],  not  ["14","25","31"],  ["9","22","34"] .

How to solve this problem?

Best regards

Hi,

I wouldn't store a comma-delimited list of ids in a database field. I would use an additional "joining" table to the products and categories tables. for example:

table: products
columns: id, name
1, red shirt
2, blouse
3, running socks
4, large sweater

table: categories
columns: id, name
1, socks
2, shirts
3, trousers
4, tops


table: product_categories
columns: product_id, category_id
1, 2
1, 4
2, 4
3, 1
4, 4


and then for your specific query (raw sql for example)

SELECT p.id, p.name
FROM products p
INNER JOIN product_categories pc ON p.id = pc.product_id
AND pc.category_id = 4

Hope this helps.

Hello Madam, Sir,
I am really newbie  in codeigniter,  I like this frame much.
Now there is problem when I try to filter some product 
We have stored a products table has column  id,  name, category_id,   and the category_id stored the product cat id format  like ["4","25","31"],
which means the product   belongs to the sub cat ID 4,  and 25, 31.  at the same time.

when we want to search out all products belong to   cat ID 4 ,  we keep to use below code

$this->db->like('category_id', '4');    it returns result of  those products belong to  ["14","25","31"],  ["4","24","31"], ["9","22","34"] ..... 

we just need the result is    ["4","any","any"],  not  ["14","25","31"],  ["9","22","34"] .

How to solve this problem?

Best regards

Hi,

I wouldn't store a comma-delimited list of ids in a database field. I would use an additional "joining" table to the products and categories tables. for example:

table: products
columns: id, name
1, red shirt
2, blouse
3, running socks
4, large sweater

table: categories
columns: id, name
1, socks
2, shirts
3, trousers
4, tops


table: product_categories
columns: product_id, category_id
1, 2
1, 4
2, 4
3, 1
4, 4


and then for your specific query (raw sql for example)

SELECT p.id, p.name
FROM products p
INNER JOIN product_categories pc ON p.id = pc.product_id
AND pc.category_id = 4

Hope this helps.