Question 276903 in PHP Question posted in
Official manual can be found here.

Php – Query with OR & AND Clause in codeigniter3

I want to get patient data by user_id and also by added_by=user & self
but it shows data from other user_id bassis of "added_by"=>"self"

I know the SQL query that is:

SELECT * FROM `patients` WHERE ( `added_by` = 'user' OR `added_by` = 'self' ) AND `user_id` = '1'

but I need to know how to make query in codeigniter3
Here is my code:

public function get_all_patients_by_user($user_id)
    {
        $this->db->where(["added_by"=>"user","user_id"=>$user_id]);
        $this->db->or_where(["added_by"=>"self","user_id"=>$user_id]);
        $data = $this->db->get("patients")->result_array();
        return $data;
    }

Please help me to get exact code.

Tags:

2

Answers


  1. 0

    You must use group_start and group_end.
    So, your method should be:

    public function get_all_patients_by_user($user_id)
{
   return $this->db
        ->from('patients')
        ->group_start() // Here
        ->where('added_by', 'user')
        ->or_where('added_by', 'self')
        ->group_end() // and Here
        ->where('user_id', $user_id)
        ->get()
        ->result_array();
}

    You can learn more here:
    https://codeigniter.com/userguide3/database/query_builder.html?highlight=group_start#query-grouping

    Login or Signup to reply.
  2. 0

    Your original query is generating the wrong SQL structure due to the way CodeIgniter handles or_where and and_where conditions by default.

    To achieve the desired query structure where "added_by" is either "user" or "self" AND "user_id" is ‘1’, you should use CodeIgniter’s group_start and group_end methods to explicitly group the "added_by" conditions and then combine them with the "user_id".

    public function get_all_patients_by_user($user_id)
{
    $this->db->group_start();
    $this->db->where("added_by", "user");
    $this->db->or_where("added_by", "self");
    $this->db->group_end();
    $this->db->where("user_id", $user_id);

    $data = $this->db->get("patients")->result_array();
    return $data;
}

    This code generate the SQL query which you want.

    SELECT * FROM `patients` WHERE ( (`added_by` = 'user' OR `added_by` = 'self') AND `user_id` = '1' )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search