Skip to content
Advertisement

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!

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement