phpcodeigniterwhere-clausequery-builderlogical-grouping

codeigniter multiple or where, disregards the where not


I am trying to create a list of users in the database of volunteers, based on those who are active members, and which jobs they volunteer for.

problem is, it is including inactive members as well.

Here is my controller

function View_Vol_LIST()
{
$data = array();

    if($query = $this->report_model->Volunteer_list())
    {
        $data['records'] = $query;
    }
$data['main_content'] = 'volunteer_list';
$this->load->view('includes/template', $data);
}

Here is my model

function Volunteer_list()
{
    $query = $this->db->order_by('Last_Name', "ASC");
    $query = $this->db->order_by('First_Name', "ASC");
    $query = $this->db->where('Membership_Status !=', 'DNR');
    $query = $this->db->where('Vol_FA =', 'yes');
    $query = $this->db->or_where('Vol_TEL =', 'yes');
    $query = $this->db->or_where('Vol_CD =', 'yes');
    $query = $this->db->or_where('Vol_SCBA =', 'yes');
    $query = $this->db->or_where('Vol_MAIL =', 'yes');
    $query = $this->db->or_where('Vol_SML =', 'yes');
    $query = $this->db->or_where('Vol_BODCOM =', 'yes');
    $query = $this->db->or_where('Vol_OTHER =', 'yes');
    $query = $this->db->get('Membership');
    return $query->result();

It seems that the or_where is completely overriding the where != DNR (Did not Renew)

Help!

Thanks!


Solution

  • Try this as nested queries like yours in Active Record is not allowed in Codeigniter

    public function Volunteer_list(){
        $this->db->order_by('Last_Name', "ASC");
        $this->db->order_by('First_Name', "ASC");
        $this->db->where('Membership_Status !=', 'DNR');
        $this->db->where("(Vol_FA='yes'
                           OR Vol_TEL = 'yes'
                           OR Vol_CD='yes'
                           OR Vol_SCBA='yes'
                           OR Vol_MAIL='yes'
                           OR Vol_SML='yes'
                           OR Vol_BODCOM ='yes'
                           OR Vol_OTHER ='yes')", NULL, FALSE);
        $query = $this->db->get('Membership');
        return $query->result();
    }
    

    The NULL is value parameter in that query and FALSE is to remove query quotes.

    That will produce this:

    SELECT * FROM (`Membership`) 
    WHERE `Membership_Status` != 'DNR' 
    AND (Vol_FA='yes' 
         OR Vol_TEL = 'yes' 
         OR Vol_CD='yes' 
         OR Vol_SCBA='yes' 
         OR Vol_MAIL='yes' 
         OR Vol_SML='yes' 
         OR Vol_BODCOM ='yes' 
         OR Vol_OTHER ='yes')
    ORDER BY `Last_Name` ASC, `First_Name` ASC