Shihas Shihas - 1 month ago 9
SQL Question

SELECT query from field having multiple value with WHERE_IN clause

The following is a VIEW search_result.

skills
column contains the values that are GROUP_CONCAT with values in some other table.

search_result VIEW

Now I'm trying to select values from this VIEW with a where_in clause like this:

$s_id = array('2D Design','3D Design');

$this->db->select('id');
$this->db->from('search_result');
$this->db->where_in('skills',$s_id);


On executing this query the result is only 3 rows i.e skills which contains only 2D Design or 2D Design.

result on executing same query in phpmyadmin

I want to display all those rows which contain any of that skills as output. So please help me to do this.

Answer

I think you need to use FIND_IN_SET here:

$s_id = array('2D Design','3D Design');

$this->db->select('id');
$this->db->from('search_result');
$this->db->where("FIND_IN_SET('$s_id[0]', skills) != ", 0);

for($x=1; $x < count($s_id); $x++) {
    $this->db->or_where("FIND_IN_SET('$s_id[$x]', skills) != ", 0);
}

The logic here is that you effectively want to use FIND_IN_SET on each skill in your array to see if it be contained within the CSV list of skills in your table. You can't do this with a single call, and each check should be ORed together in the WHERE clause.