Ardee NetGeek Ardee NetGeek - 2 months ago 15
PHP Question

Combine where - like - whre_in --- CodeIgniter

let say I have a model in CI to returning what user(s) need to ..

$find = 'something';
$id_user = Array ( [0] => 1 [1] => 5 [2] => 20 [3] => 21 ) ;


so, I have to put that in here but something must goes wrong ..

public function find_pr_by_user ($find,$id_users) {
$this -> db -> where_in ('req_by_id'.$id_users || 'check_by_id',$id_users || 'approve_by_id',$id_users);
$this -> db -> where ("(ref_no LIKE '%$find%' || pr_title LIKE '%$find%' || req_date LIKE '%$find%')");
$query = $this -> db -> get ('pr_data') ;
return $query -> result () ;
}


anyone help, please ... :)

Answer

This answer is under assumption that you need parenthesis in the first where_in() in your code.

Unfortunately, CodeIgniter does not fully support parenthesis with Active Record. Therefore you will have to use where() with more complex SQL syntax in it.

public function find_pr_by_user ($find,$id_users) {
    $id_users_glued = implode(",",$id_users);
    $this->db->where('(req_by_id IN (' . $id_users_glued . ') || check_by_id IN (' . $id_users_glued . ') || approve_by_id IN (' . $id_users_glued . '))');
    $this->db->where("(ref_no LIKE '%$find%' || pr_title LIKE '%$find%' || req_date LIKE '%$find%')");
    $query = $this->db->get('pr_data') ;
    return $query->result () ;
}

In CI's Active Record, this is how the syntax will be treated:

The FIRST WHERE will be treated as: WHERE (req_by_id IN ($id_users_glued) || check_by_id IN ($id_users_glued) || approve_by_id IN ($id_users_glued)

The $id_users_glued will produce something like 1,2,3,4,5

The SECOND WHERE will be treated as: AND (ref_no LIKE '%$find%' || pr_title LIKE '%$find%' || req_date LIKE '%$find%')

Note: I haven't tested the code because I don't have your db structure. Let me know if it's not working.

Comments