CODEWITHSUNDEEP
mysqlsqlcodeigniterviewwhere-in
Shihas
Shihas

Reputation: 814

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.

Upvotes: 1

Views: 432

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

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.

Upvotes: 3

Related Questions