phpmysqlcodeigniterquery-builderwhere-in

CodeIgniter's where_in() method gives unexpected results when given a comma-separated string

I want to pass comma separated string to IN clause. My controller code is as:-

$batchpost = $this->input->post('batch');//print_r($batch);
    
$batch_id = '';
for ($i = 0; $i<count($batchpost); $i++) {
    $batch_id = $batch_id . $batchpost[$i] . ',';
}
$batch_string = rtrim($batch_id, ',');
$batch = str_replace(",", ",", $batch_string);

$list = $this->admin_model->get_attendance_datatables($batch);

Here $batchpost will return array value, and I want to convert it to a comma separated string and pass to the model.

My model code is as:-

$this->db->select('offline_student.*,batch.batch_name,offline_course.course');
$this->db->from($this->table8);
$this->db->join('offline_course', 'offline_course.id = offline_student.course', 'left');
$this->db->join('batch', 'batch.id = offline_student.batch', 'left');                
$this->db->where('offline_student.status', 'Active');
$this->db->where_in('batch.id', $batch);
$this->db->order_by('offline_student.id', 'asc');

Suppose in a row for batch column having two value(2,3)in database, if I pass only '2' or '2,3' to model, query will return my result, but when I pass only 3, It's not showing that record.

Solution

  • Remove the following lines:

    $batch_id = '';
for($i=0;$i<count($batchpost);$i++ ) { 
    $batch_id = $batch_id . $batchpost[$i] . ','; 
} 
$batch_string = rtrim($batch_id, ','); 
$batch = str_replace(",", ",", $batch_string) ;

    ...and make it like this:

    $batchpost = $this->input->post('batch');//print_r($batch); 
$list = $this->admin_model->get_attendance_datatables($batchpost);

    you don't need to convert $batchpost to comma separated string, because where_in() method expects an array on it's second param.

    See the docs of where_in(), here https://www.codeigniter.com/userguide3/database/query_builder.html#looking-for-specific-data