[FIXED] How do I get the total number of students who are only active

Issue

I have two tables that store data for students - the students table and student_session table

students table structure

CREATE TABLE IF NOT EXISTS `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `admission_no` varchar(100) DEFAULT NULL,
  `roll_no` varchar(100) DEFAULT NULL,
  `admission_date` date DEFAULT NULL,
  `firstname` varchar(100) DEFAULT NULL,
  `lastname` varchar(100) DEFAULT NULL,
  `rte` varchar(20) DEFAULT NULL,
  `image` varchar(100) DEFAULT NULL,
  `mobileno` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `is_active` varchar(255) DEFAULT 'yes',
  `disable_at` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

student_session table structure

CREATE TABLE IF NOT EXISTS `student_session` (
  `id` int(11) NOT NULL,
  `session_id` int(11) DEFAULT NULL,
  `student_id` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  `section_id` int(11) DEFAULT NULL,
  `route_id` int(11) NOT NULL,
  `hostel_room_id` int(11) NOT NULL,
  `vehroute_id` int(10) DEFAULT NULL,
  `transport_fees` float(10,2) NOT NULL DEFAULT 0.00,
  `fees_discount` float(10,2) NOT NULL DEFAULT 0.00,
  `is_active` varchar(255) DEFAULT 'no',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now, I've been able to get the total number of students in a class using this query

 public function Gettotalstudents($session_id, $section_id, $class_id) 
    {
        $this->db->select('student_id');
        $this->db->where('session_id', $session_id);
        $this->db->where('section_id', $section_id);
        $this->db->where('class_id', $class_id);
        return $this->db->count_all_results('student_session');
    }

However, there are some students who have been disabled for non-payment of school fees or those that have left the school permanently. The problem is since these students were only disabled and not deleted, the query still counts them to the active students.

Now, I want to exclude the disabled students from being counted.

Note - in the students table structure, the 'is_active' row stores data for if a student is active or disabled. 'yes' means a student is active, 'no' means a student has been disabled.

How do I go about this?

Solution

Or a little more succinctly, you can just use the mysql COUNT() function with AS:

public function Gettotalstudents($session_id, $section_id, $class_id) 
    {
        $this->db->select('COUNT(ss.student_id) as total');
        $this->db->from('student_session ss');
        $this->db->join('students st', 'st.id = ss.student_id');
        $this->db->where(array('st.is_active'=> 'yes','ss.session_id' => $session_id, 'ss.section_id' => $section_id, 'ss.class_id' => $class_id));
        return $this->db->get()->row()->total;
    }

Answered By - Kinglish