Shihas Shihas - 1 year ago 56
SQL Question

SELECT query from field having multiple value with WHERE_IN clause

The following is a VIEW search_result.

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');


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 Source

I think you need to use FIND_IN_SET here:

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

$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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download