php 如何在codeigniter中获取数据库列的总和？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25991853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get sum of database column in codeigniter?
提问by Arun
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.
朋友我无法获得空缺职位总和。这是我的代码。我得到 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);
}
This is my model function
这是我的模型函数
Model:
模型：
function GetRequirement($options = array()){ if(isset($options['requirementId'])) $this->db->where('requirementId',$options['requirementId']);
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>
回答by M Khalid Junaid
You are using sumwhich 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
您正在使用sumwhich 是一个聚合函数，如果没有 group by，它将在if(isset($options['openpos'])){ ... }您返回的模型的部分代码中将整个表作为一个组num_rows()，返回 no。行数，所以在你的情况下会有一行的总和值，因此你得到的结果是 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;
}
回答by Erwin van Hoof
I think the mysql statement has an error.
我认为mysql语句有错误。
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:
如果这不能解决您的问题，您可以尝试通过添加 exit($this->db->last_query()); 来输出 mysql 语句。尝试找出问题所在，如下所示：
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();
}
