Query where_in comma separated list-CodePudding

I have a 'list_table' table looks like:

id :  list 
1  :  1,2,44,5    
2  :  4,3,5,2,56,66

Is it possible to check if '44' is in List column in mysql database? I'm using codeigniter and my code looks like:

$this->db->select('*'); 
$this->db->from("list_table");
$this->db->where("find_in_set('44', 'list')");
$query = $this->db->get();
return $query->result();

I also tried with WHERE_IN but didn't get correct result.

CodePudding user response：

You need a true or false condition in the WHERE clause, so a comparison

$this->db->select('*'); 
$this->db->from("list_table");
$this->db->where("find_in_set('44', 'list') <> 0");
$query = $this->db->get();
return $query->result();

But it ot recomended to store data this way . Read mor in Is storing a delimited list in a database column really that bad?

CodePudding user response：

As nbk said, you need the true/false condition, however, the answer is not working for the OP. You need to remove the single quotes around list in that answer:

$this->db->where("find_in_set('44', 'list') <> 0");

Rewrite the code as below, minus the quotes around list:

$this->db->select('*'); 
$this->db->from("list_table");
$this->db->where("find_in_set('44', list) <> 0");
$query = $this->db->get();
return $query->result();

That should solve the issue for you.