Codeigniter count rows

Question

How can I count rows from database? I have 3 tables, namely:

questionnaire
choices
answer_sheet

Here is the table structure:

questionnaire

questionnaire_id
type_id_fk
question
answer
pg

choices

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

My question is, how can I count the unanswered questions and the incorrect questions?

Here is my model:

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

Here is my view:

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

Here is my controller

class Results extends 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);
    }

}

I inserted 2 questions in the questionnaire's table and I answered those 2 questions but it returns 2 in Unanswered function. For the incorrect function it returns 3

You didn't include the tables structure and we don't understand I answered those 2 questions but it returns 2 in Unanswered function. For the incorrect function it returns 3. Can you at least provide the column names of each table? — ekad
– ekad, Commented Jul 21, 2015 at 2:21

Community · Accepted Answer · 2017-05-23 10:34:15Z

0

Did you try whit a COUNT function?

Example:

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

You could look more here

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Jul 21, 2015 at 2:20

Gonz

1,20913 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

johnlopev Over a year ago

I haven't tried it. I will probably try it. Thanks.

johnlopev Over a year ago

Thanks for your answer. It works fine. But how can I count rows which are not answered yet as what I have stated in my post.

Gonz Over a year ago

You fixed the incorrect count? Why isnt working the left join whit the unanswered? What its your code at the moment? @JohnLopeValmoria

johnlopev · Accepted Answer · 2015-07-22 08:01:52Z

0

I just got the resolution by myself.

Here's my solution:

<?php
   $total = $this->Mresults->totalQuestions('1');
   $answered = $this->Mresults->countAnswered($userID, '1');
   $unAnswered = $total-$answered;
   echo $unAnswered;
?>

answered Jul 22, 2015 at 8:01

johnlopev

11 silver badge3 bronze badges

1 Comment

Gonz Over a year ago

You should post the queries, and then you can accept your own answer.

Collectives™ on Stack Overflow

Codeigniter count rows

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related