如何从数据库中计数行?我有3个表,分别是:
questionnaire
choices
answer_sheet
表结构如下:
问卷questionnaire_id
type_id_fk
question
answer
pg
选择choices_id
questionnaire_id_fk
type_id_fk
choices
pg
answer_sheet
answer_sheet_id
questionnaire_id_fk
choices_id_fk
user_id_fk
status
我的问题是,我如何计算unanswered
问题和incorrect
问题?
这是我的模型:
function countUnanswered($userID, $type) {
$this->db->select('*');
$this->db->from('questionnaire q');
$this->db->join('answer_sheet a', 'q.questionnaire_id=a.questionnaire_id_fk');
$this->db->where('q.type_id_fk', $type);
$this->db->where('a.user_id_fk', $userID);
$query = $this->db->get();
$rowcount = $query->num_rows();
return $rowcount;
}
function countIncorrect($userID, $type) {
$this->db->select('*');
$this->db->from('answer_sheet a');
$this->db->join('questionnaire q', 'q.answer!=a.choices_id_fk', 'LEFT');
$this->db->where('q.type_id_fk', $type);
$this->db->where('a.user_id_fk', $userID);
$query = $this->db->get();
$rowcount = $query->num_rows();
return $rowcount;
}
我的观点是:
<p><label>Unanswered:</label> <span class="badge"><?php echo $countUnanwered = $this->Mresults->countUnanswered('1'); ?></span></p>
<p><label>Incorrect:</label> <span class="badge"><?php echo $countIncorrect = $this->Mresults->countIncorrect($userID, '1'); ?></span></p>
这是我的控制器
类结果扩展CI_Controller {
public function view($page = 'results') {
if (!file_exists(APPPATH . '/views/pages/results/' . $page . '.php')) {
// Whoops, we don't have a page for that!
show_404();
}
$data['title'] = ucfirst($page) . ' | TOEFL Practice Test'; // Capitalize the first letter
$data['page'] = $page;
$this->load->view('template/header', $data);
if (isset($this->session->userdata['logged_in'])) {
$data['userID'] = ($this->session->userdata['logged_in']['userID']);
$data['username'] = ($this->session->userdata['logged_in']['username']);
$data['email'] = ($this->session->userdata['logged_in']['email']);
$userID = $this->session->userdata['logged_in']['userID'];
$this->load->view('pages/results/' . $page, $data);
} else {
$this->load->view('pages/user/login_form', $data);
}
$this->load->view('template/footer', $data);
}
}
我在问卷的表中插入了2个问题,我回答了这2个问题,但它在未回答的函数中返回2。对于错误的函数,它返回3
您是否尝试使用COUNT
函数?
的例子:
$this->db->select('user_id, COUNT(user_id) as total');
$this->db->group_by('user_id');
$this->db->order_by('total', 'desc');
$this->db->get('tablename', 10);
你可以在这里看更多
我自己刚弄到分辨率。
这是我的解决方案:
<?php
$total = $this->Mresults->totalQuestions('1');
$answered = $this->Mresults->countAnswered($userID, '1');
$unAnswered = $total-$answered;
echo $unAnswered;
?>