how can we use where condition if data is stored as implode

Question

Here my table looks like this

i want to get all the departments if location_id is 3 that means if location id 3 is used i need to get the department_names of cardiology and hair so that i had done my code like this

My controller looks like this

public function get_location_departments()
{
   $d = $_POST['location'];
   $data['departments'] = $this->Hospital_model->get_location_departments($d);
   $this->load->view('frontend/ajax_get_departments',$data);
}

My model looks like this

public function get_location_departments($d)
{
  $this->db->where_in('location_id',$d);
  $query=$this->db->get('department')->result();
  return $query;
}

Please help me to solve my problem

Possible duplicate of [mysql check if numbers are in a comma separated list](https://stackoverflow.com/questions/2674011/mysql-check-if-numbers-are-in-a-comma-separated-list) — R B, May 18 '18 at 05:37
Are you only interested in fetching the first digit? if so why not use LIKE? — hungrykoala, May 18 '18 at 05:46

score 1 · Accepted Answer · answered May 18 '18 at 05:56

Hope this will help you :

You have to query first to get the department and get location_id into an array using explode

Your model get_location_departments method should be like this :

public function get_location_departments($d)
{
    if (! empty($d))
    {
        $query = $this->db->get('department');
        if ($query->num_rows() > 0 )
        {  
            foreach ($query->result() as $location)
            {
                $location_ids = explode(',',$location->location_id);
                if (in_array($d, $location_ids))
                {
                    $data[] = $location;
                }

            }
        }
    return $data;
    //print_r($data);  
    }
}

score 1 · Answer 2 · answered May 18 '18 at 19:06

imho the correct answer would be

public function get_location_departments($d)
{
    $query = $this->db
        ->where('FIND_IN_SET('.$this->db->escape($d).', location_id)', NULL, false)
        ->get('department')
        ->result();
    return $query;
}

There is a function called FIND_IN_SET for that purpose. You can find it in the mysql Documentation under https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set

how can we use where condition if data is stored as implode

2 Answers2