CodeIgniter SELECT query WHERE any value of a PHP array is found in a column of comma-separated strings [duplicate]

Question

The following is a VIEW search_result. skills column contains the values that are GROUP_CONCAT with values in some other table.

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.

I want to display all those rows which contain any of that skills as output.

Shihas · Accepted Answer · 2016-11-09 07:33:39Z

3

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.

edited Nov 9, 2016 at 7:33

Shihas

81415 silver badges45 bronze badges

answered Nov 9, 2016 at 7:19

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shihas Over a year ago

what if I remove the where clause and change $x=0 inside the for loop?

Tim Biegeleisen Over a year ago

@Shihas This might work, depending on the behavior of or_where. If it can be called without first calling where() then it might be OK. Otherwise, stick with the code I gave you.

Collectives™ on Stack Overflow

CodeIgniter SELECT query WHERE any value of a PHP array is found in a column of comma-separated strings [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related