php - How do I get the total number of students who are only active
Get the solution ↓↓↓
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?
Answer
Solution:
You should change the is_active field to boolean.
Join the student table with student_session.student_id = students.student_id, and filter by the is_active field in student_session
Try this for active students in table students
public function Gettotalstudents($session_id, $section_id, $class_id)
{
$this->db->select('student_session.student_id');
$this->db->from("student_session,students"); //session table and student table
$this->db->where('student_session.student_id', 'student_id.student_id'); //join tables
$this->db->where('student_session.session_id', $session_id);
$this->db->where('student_session.section_id', $section_id);
$this->db->where('student_session.class_id', $class_id);
$this->db->where('student_id.is_active', 'yes'); //only active
return $this->db->count_all_results(); // count active
}
Try this for active students in table student_session
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);
$this->db->where('is_active', 'yes');
return $this->db->count_all_results('student_session');
}
Answer
Solution:
Or a little more succinctly, you can just use the mysqlCOUNT() function withAS:
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;
}
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: string literal contains an unescaped line break
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.