Help with JOIN active record class

[eluser]chefnelone[/eluser]
Hello

Which operator should I use to do this:
I have this line which works fine if categories.id_category = products.categorias

Code:

$this->db->join('categories', 'categories.id_category = products.categorias');

Then the join is done if e.g.:
categories.id_category= '5'
and
products.categorias = '5'

But what I need is something like:

categories.id_category IS CONTAINED IN products.categorias
Then the join should work if e.g.:
categories.id_category= '5'
and
products.categorias = '5 7 8 1'
because '5' is in '5 7 8 1'

Something like:

Code:

$this->db->join('categories', 'categories.id_category IS CONTAINED IN products.categorias');

[eluser]WanWizard[/eluser]
This is very bad practice, and leads to all kinds of issues and complication, this being one of them. You have a many-to-many relationship between two tables, you should use a junction table to make the relation. That would allow you to use standard joins.

I know in the WHERE clause you can use a REGEXP to find all products that have a specific value:

Code:

$product_id = '5';
$query = $this->db->query("SELECT * FROM products WHERE REGEXP('^ {$product_id}$| {$product_id} | {$product_id}$')");

(note: this is from the top of my head...)

[eluser]chefnelone[/eluser]
Thanks wanwizard
I know, it was a sort of shorcut to achieve something. Eventually I did it the right way (created new table...)