Codeigniter - Multiple Where Clause (Code Refactor)

Question

I'm trying to fetch a data from a table and should count its no. of students value.

Output as follows:

     No. of Students per course
BSCS  3
BSIS  1
CT    2

Table course_code: BSCS = 3, BSIS = 1, CT = 2

I'm using multiple where clauses and it isn't working. Could someone help me on this please. Thanks!

Controller

     public function enrollment_summary()
      {
        $data['title'] = 'Enrollment Summary';
        $this->load->model('report_model');
        $data['bscs'] = $this->report_model->get_studentsPerCourse();
        $data['bsis'] = $this->report_model->get_studentsPerCourse();
        $data['ct'] = $this->report_model->get_studentsPerCourse();
        $this->load->view('report_enrollment_summary', $data);
      }

Model

    public function get_studentsPerCourse()
  {
    $bscs = $this->input->post('students');
    $bsis = $this->input->post('students');
    $ct = $this->input->post('students');
    $this->db->select('*');
    $this->db->from('students');
    $this->db->where('course_code', 'BSCS', $bscs);
    $this->db->where('course_code', 'BSIS', $bsis);
    $this->db->where('course_code', 'ct', $ct);
    return $this->db->count_all_results();
  }

@JitendraYadav I edited the question and include the desired output. Thanks in advance :) — Ronald Balza
– Ronald Balza, Commented Sep 29, 2020 at 3:43

Dark Knight · Accepted Answer · 2020-09-29 07:16:22Z

1

Corresponding MySQL query that you're looking for is

SELECT
    course_code,
    COUNT(0) AS student_count
FROM students
WHERE course_code IN ('BSCS', 'BSIS', 'ct')
GROUP BY course_code;

Now you can convert it to equivalent codeigniter query.

// Model code
public function get_studentsPerCourse()
{
    $bscs = $this->input->post('students');
    $bsis = $this->input->post('students');
    $ct = $this->input->post('students');

    $this->db->select('course_code, COUNT(0) AS student_count');
    $this->db->from('students');
    $this->db->where_in('course_code', ['BSCS', 'BSIS', 'ct']);
    $this->db->group_by("course_code");

    return $this->db->get();
}

Now in controller you can loop over and assign it to variables;

 public function enrollment_summary()
      {
        $data['title'] = 'Enrollment Summary';
        $this->load->model('report_model');

        $result = $this->report_model->get_studentsPerCourse();

        foreach ($result ->getResult() as $row)
        {
            $data[$row->course_code] = $row->student_count;
        }
        // make sure you take care of case sensitivity if required.

        $this->load->view('report_enrollment_summary', $data);
      }

edited Sep 29, 2020 at 7:16

answered Sep 29, 2020 at 3:52

Dark Knight

6,6211 gold badge22 silver badges40 bronze badges

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

5 Comments

Ronald Balza Over a year ago

@JitendraYadava the output is all 1.Am missing something here? Thanks :)

Dark Knight Over a year ago

If it works for you, don't forge to accept the answer, also if you facing any issue, either you can provide a comment or modify your question itself with more details.

Ronald Balza Over a year ago

@JitendraYadva - bro, the output is all 1. My desired output is counting how many students are there in each course. should be BSCS = 3, BSIS = 1, CT = 2. Thanks

Dark Knight Over a year ago

just try the query in mysql itself and see the results and let me know about results.

Dark Knight Over a year ago

see this fiddle, it is working fine

Collectives™ on Stack Overflow

Codeigniter - Multiple Where Clause (Code Refactor)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related