CODEWITHSUNDEEP
phpcodeigniter
Arun
Arun

Reputation: 97

how to get sum of database column in codeigniter?

Friends I'm Unable to get open positions Sum. here is my code. I am getting 1(one) instead of total sum. Help me to solve this issue.

Controller:

function index(){
    $userId = $this->phpsession->get("userId");
    $userType = $this->phpsession->get('userType');
    $date = date("Y-m-d");
    if(!$userId && !$this->phpsession->get("userType")){
        redirect(base_url().'user');
    }

    $config['base_url'] = base_url().'requirement/index';
    $config['total_rows'] = $this->requirement_model->GetRequirement(array("count"=>true));
    $config['per_page'] = 5;
    $config['cur_tag_open'] = '<a>';
    $config['cur_tag_close'] = '</a>';

    $this->pagination->initialize($config);

    $options['offset'] = $this->uri->segment(3);
    $options['limit'] = $config['per_page'];        
    $options['join']=true;

    $data['clients'] = $this->client_model->ajaxclient();
    $data['requirements'] = array(""=>"Choose requirement");

    $data['requirement'] = $this->requirement_model->GetRequirement($options);
    $data['links'] = $this->pagination->create_links();
    $data['totalactive']=$this->requirement_model->GetRequirement(array("find"=>true));
    $data['totalrequirement']=$this->requirement_model->GetRequirement(array("totalreq"=>true));
    $data['openpositions']=$this->requirement_model->GetRequirement(array("openpos"=>true));
    //print_R($data['openpositions']);exit;
    //echo "<pre>"; print_R($this->db->last_query()); exit;

    $data['page_title'] = "Requirement Details";
    $this->layout->view("requirement/index",$data); 

}

This is my model function

Model:

function GetRequirement($options = array()){ if(isset($options['requirementId'])) $this->db->where('requirementId',$options['requirementId']);

    if(isset($options['clientName']))
        $this->db->where('clientName',$options['clientName']);  

    if(isset($options['limit']) && isset($options['offset']))
        $this->db->limit($options['limit'], $options['offset']);
    else if(isset($options['limit']))
        $this->db->limit($options['limit']);

    $this->db->order_by("activateddate", "DESC");

    if(isset($options['join'])){

        $this->db->select('r.*,c.clientName as cName');
        $this->db->from('requirement as r');
        $this->db->join('clients as c','c.clientId=r.clientName');
        $query=$this->db->get();
        if(@$options['requirementId']) return $query->row(0);
        return $query->result();
    }

    if(isset($options['find'])){
        $this->db->select('distinct(clientName)');
        $this->db->from('requirement');
        $this->db->where('(clientName) and (noofpositions > 0) ');
        $this->db->count_all();
        $query=$this->db->get();
        return $query->num_rows();  
    }

    if(isset($options['totalreq'])){
        $this->db->select('requirementName');
        $this->db->from('requirement');
        $this->db->where('(noofpositions > 0) ');
        $this->db->count_all();
        $query=$this->db->get();
        return $query->num_rows();  
    }


    if(isset($options['openpos'])){
        $this->db->select_sum('openPos');   
        $this->db->from('requirement'); 
        $this->db->where('(closedPos = 0) ');   
        $this->db->count_all();
        $query=$this->db->get();
        return $query->num_rows();
    }

    $query = $this->db->get('requirement');

    if(isset($options['count'])) return $query->num_rows();

    if(@$options['requirementId']) return $query->row(0);

    return $query->result();
}

This is my View page

View:

<div class="inner">
    <h3><?php echo $openpositions; ?></h3>
     <p>Total Positions Opened</p>
</div>

Upvotes: 4

Views: 38886

Answers (2)

Erwin van Hoof
Erwin van Hoof

Reputation: 997

I think the mysql statement has an error.

Change the following line:

 $this->db->where('(closedPos = 0) ');

To

$this->db->where('closedPos', 0);

remove the following line: (this will count all rows and return the value, which you do not want)

 $this->db->countall();

If this does not solve your problem you could try outputting the mysql statement by adding exit($this->db->last_query()); to try and find the problem, like this:

     if(isset($options['openpos'])){
        $this->db->select_sum('openPos');   
        $this->db->from('requirement'); 
        $this->db->where('(closedPos = 0) ');   
        $this->db->count_all();
        $query=$this->db->get();

        // output last query
        exit($this->db->last_query());

        return $query->num_rows();

    }

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You are using sum which is an aggregate function and with out group by it will take whole table as one group in if(isset($options['openpos'])){ ... } part of code of your model your are returning num_rows() which returns the no. of rows so in your case there will be one row with the value of sum therefore you are getting result as 1 change your 

if (isset($options['openpos'])) {
    $this->db->select_sum('openPos');
    $this->db->from('requirement');
    $this->db->where('(closedPos = 0) ');
    $query = $this->db->get();
    return $query->row()->openpos;
}

Upvotes: 9

Related Questions