CODEWITHSUNDEEP
phpcodeigniteractiverecordwhere-clauselogical-grouping
subikshan M
subikshan M

Reputation: 263

Convert a select query seeking rows which have qualifying combinations of multiple column values to CodeIgniter's active record syntax

I have written in one SQL query and it's working fine. How do I write this in CodeIgniter?

$sql = "SELECT * 
         FROM single_message
         WHERE 
           ( school_id='$schoolId' AND 
             classId='$classId' AND 
             sectionId='$sectionId' AND 
             sender_id='$senderId' AND 
             receiver_id ='$receiverId'
           ) OR 
           ( chool_id='$schoolId' AND 
             classId='$classId' AND
             sectionId='$sectionId' AND
             sender_id='$receiverId' AND
             receiver_id ='$senderId'
           )
        ORDER BY messageId DESC"`;

This is what I've tried:

$condition = array(
                'school_id' => $schoolId,
                'classId' => $classId,
                'sectionId' => $sectionId,
                'sender_id' => $senderId,
                'receiver_id' => $receiverId
            );

$this->db->select('*');
$this->db->where($condition);
return $this->db->get('single_message')->result_array();

Upvotes: 2

Views: 4743

Answers (2)

Abhishek Singh
Abhishek Singh

Reputation: 509

As per your SQL query:

$sql = "SELECT * FROM single_message WHERE (school_id='$schoolId' AND classId='$classId' AND sectionId='$sectionId' AND sender_id='$senderId' AND receiver_id ='$receiverId') OR (chool_id='$schoolId' AND classId='$classId' AND sectionId='$sectionId' AND sender_id='$receiverId' AND receiver_id ='$senderId') ORDER BY messageId DESC";

There are two ways to write queries. I will explain both way one by one:

Solution 1st: You can simply just put your condition in where clause like explained below->

$condition = "(school_id='$schoolId' AND classId='$classId' AND sectionId='$sectionId' AND sender_id='$senderId' AND receiver_id ='$receiverId') OR (chool_id='$schoolId' AND classId='$classId' AND sectionId='$sectionId' AND sender_id='$receiverId' AND receiver_id ='$senderId')";

        $this->db->select('*');
        $this->db->where($condition);
        return $this->db->get('single_message')->result_array();

Here you can see that I have passes complete where condition in a string format. This is the fisrt solution. Another method to write this query is.

Solution 2nd: Query grouping-> It allows you to create groups of WHERE clauses by enclosing them in parentheses. So query would be like:

$condition['AND'] = array(
        'school_id' => $schoolId,
        'classId' => $classId,
        'sectionId' => $sectionId,
        'sender_id' => $senderId,
        'receiver_id' => $receiverId
    );
    $condition['OR'] = array(
        'school_id' => $schoolId,
        'classId' => $classId,
        'sectionId' => $sectionId,
        'sender_id' => $receiverId,
        'receiver_id' => $senderId
    );

    $this->db->select('*');

    // Starts first group
    $this->db->group_start();

// AND condition placed in below line
    $this->db->where($condition['AND']);

// First group ends here
    $this->db->group_end();

// Another group has been started here for OR clause
    $this->db->or_group_start();

// Here we placed our OR codition
    $this->db->where($condition['OR']);

// Second group ends here
    $this->db->group_end();

return $this->db->get('single_message')->result_array();

It will produce exact result you needed. Let me know if you have any query. For more details you can read Query Builder explaination here: Query Grouping

Upvotes: 1

Touheed Khan
Touheed Khan

Reputation: 2151

Passing second parameters as null and third parameter as false will result is not escaping your query.

Try this code:

$this->db->select('*');
$this->db->where("school_id='$schoolId' AND classId='$classId' AND sectionId='$sectionId' AND sender_id='$senderId' AND receiver_id ='$receiverId') OR (chool_id='$schoolId' AND classId='$classId' AND sectionId='$sectionId' AND sender_id='$receiverId' AND receiver_id ='$senderId'", null, false);
$this->db->order_by("messageId", "desc");
$this->db->get();

Here is Where reference from codeigniter documentation.

Upvotes: 0

Related Questions