php - 如何在codeigniter中获取数据库列的总和？

Question

friend 我无法获得空缺职位总和。这是我的代码。我得到 1(one) 而不是总和。帮我解决这个问题。

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); 

}

这是我的模型函数

模型:

函数 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();
}

这是我的查看页面

看法:

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

Answer 1

您正在使用 sum这是一个聚合函数，如果没有分组，它将在 if(isset($options['openpos'])){ ... } 中将整个表作为一个组您返回的模型的部分代码 num_rows()返回编号。行数，所以在你的情况下会有一行的总和值，因此你得到的结果是 1 改变你的

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;
}