Student Attendance System with PHP & MySQL

In our previous tutorial, we have explained how to develop Like and Dislike System with Ajax, PHP & MySQL. In this tutorial, we will explain how to develop Student Attendance System with PHP & MySQL.

Student attendance system or student monitoring system is a web application that used to manage the attendance of students in Schools, College or Institute. The attendance of students is managed by their class teachers with status as present, absent, late or half-day. The system also provides attendance reports based on dates.

So if you’re a developer and thinking about developing a attendance system with PHP and MySQL, then it’s very easy. In this tutorial, you will learn how to develop Student Attendance System with PHP & MySQL.

Also, read:

Follow and Unfollow System with PHP & MySQL

We will cover this tutorial with live example and implement following features:

Admin: Manage Users, students.
User Login: We allow user login (administrator, teacher) to access student attendance system to perform students attendance, view student attendance reports of a class.
Student: Display students list and view particular student details.
Attendance: Search students from a class and perform attendance.
Attendance Report: Search students report by class and dates.

So let’s implement Student Attendance System with PHP & MySQL. The major files are:

index.php
dashboard.php
User.php: User class to hold methods related to user.
student.php
student.js
Student.php: Student class to hold methods related to students.
attendance.php
attendance.js
attendance_report.php
attendance_report.js
Teacher.php: Teacher class to hold methods related to teacher.

Step1: Create MySQL Database Table

First we will create MySQL database tables to store teachers, students, class and attendance details. We will create sas_user table to store teacher login details.

CREATE TABLE `sas_user` (
  `id` int(11) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `gender` enum('male','female') CHARACTER SET utf8 NOT NULL,
  `mobile` varchar(50) NOT NULL,
  `designation` varchar(50) NOT NULL,
  `status` enum('active','pending','deleted','') NOT NULL DEFAULT 'pending',
  `role` enum('administrator','teacher','student') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will create table sas_students to store students details.

CREATE TABLE `sas_students` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(40) NOT NULL,
  `gender` varchar(40) NOT NULL,
  `dob` datetime NOT NULL,
  `photo` varchar(40) DEFAULT NULL,
  `mobile` int(10) UNSIGNED NOT NULL,
  `email` varchar(40) DEFAULT NULL,
  `current_address` varchar(40) DEFAULT NULL,
  `permanent_address` varchar(40) DEFAULT NULL,
  `father_name` varchar(255) NOT NULL,
  `father_mobile` int(10) UNSIGNED NOT NULL,
  `father_occupation` varchar(255) NOT NULL,
  `mother_name` varchar(255) NOT NULL,
  `mother_mobile` int(10) UNSIGNED NOT NULL,
  `admission_no` int(11) NOT NULL,
  `roll_no` int(11) NOT NULL,
  `stream` int(10) UNSIGNED DEFAULT NULL,
  `admission_date` datetime NOT NULL,
  `academic_year` int(10) UNSIGNED NOT NULL,
  `class` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We will create table sas_classes to store class and its teacher details.

CREATE TABLE `sas_classes` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(40) NOT NULL,
  `teacher_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and we will create table sas_attendance to store student attendance details.

CREATE TABLE `sas_attendance` (
  `attendance_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `status` enum('present','absent','late','half_day') NOT NULL,
  `attendance_date` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step2: Implement Teacher Login

First we will create login form in index.php to allow teacher login to access attendance system.

<form id="loginform" class="form-horizontal" role="form" method="POST" action="">                                    
	<div style="margin-bottom: 25px" class="input-group">
		<span class="input-group-addon"><i class="glyphicon glyphicon-user"></i></span>
		<input type="text" class="form-control" id="email" name="email" value="<?php if(!empty($_POST["email"])) { echo $_POST["email"]; } ?>" placeholder="email" style="background:white;" required>                                        
	</div>                                
	<div style="margin-bottom: 25px" class="input-group">
		<span class="input-group-addon"><i class="glyphicon glyphicon-lock"></i></span>
		<input type="password" class="form-control" id="password" name="password" value="<?php if(!empty($_POST["password"])) { echo $_POST["password"]; } ?>" placeholder="password" required>
	</div>					
	<div style="margin-top:10px" class="form-group">                               
		<div class="col-sm-12 controls">
		  <input type="submit" name="login" value="Login" class="btn btn-info">						  
		</div>						
	</div>		
</form>

We will handle login functionality on aform submit by calling method login() from class User.php.

if(!empty($_POST["login"]) && !empty($_POST["email"]) && !empty($_POST["password"])) {	
	$user->email = $_POST["email"];
	$user->password = $_POST["password"];	
	if($user->login()) {		
		header("Location: dashboard.php");		
	} else {
		$loginMessage = 'Invalid login! Please try again.';
	}
}

We will implement method login() in class User.php to handle login functionality.

public function login(){
	if($this->email && $this->password) {
		$sqlQuery = "
			SELECT * FROM ".$this->userTable." 
			WHERE email = ? AND password = ?";			
		$stmt = $this->conn->prepare($sqlQuery);
		$password = md5($this->password);
		$stmt->bind_param("ss", $this->email, $password);	
		$stmt->execute();
		$result = $stmt->get_result();
		if($result->num_rows > 0){
			$user = $result->fetch_assoc();
			$_SESSION["userid"] = $user['id'];
			$_SESSION["role"] = $user['role'];
			$_SESSION["name"] = $user['first_name']." ".$user['last_name'];					
			return 1;		
		} else {
			return 0;		
		}			
	} else {
		return 0;
	}
}

Step3: Display Student Listing

In student.php file, we will create HTML with Table to display students records in DataTable.

<div> 	
	<div class="panel-heading">
		<div class="row">
			<div class="col-md-10">
				<h3 class="panel-title"></h3>
			</div>				
		</div>
	</div>
	<table id="studentListing" class="table table-bordered table-striped">
		<thead>
			<tr>
				<th>#</th>
				<th>Name</th>					
				<th>Roll No</th>					
				<th>Class</th>					
				<th></th>									
			</tr>
		</thead>
	</table>
</div>

In student.js, we will implement functionality to make ajax request to load DataTable with students record.

var studentRecords = $('#studentListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": false,
	'serverMethod': 'post',		
	"order":[],
	"ajax":{
		url:"student_action.php",
		type:"POST",
		data:{action:'listStudents'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 4],
			"orderable":false,
		},
	],
	"pageLength": 10
});

We will call method listStudents() from class Student.php to display student list.

$student = new Student($db);

if(!empty($_POST['action']) && $_POST['action'] == 'listStudents') {
	$student->listStudents();
}

We will implement the method listStudents() in class Student.php and return students records as JSON data.

public function listStudents(){
		
	$sqlQuery = "SELECT s.id, s.name, s.roll_no, c.name as class_name
	FROM ".$this->studentTable." as s 
	LEFT JOIN ".$this->classTable." as c ON s.class = c.id ";
	
	if(!empty($_POST["search"]["value"])){
		$sqlQuery .= 'WHERE (s.id LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR s.name LIKE "%'.$_POST["search"]["value"].'%" ';			
		$sqlQuery .= ' OR s.roll_no LIKE "%'.$_POST["search"]["value"].'%" ';			
		$sqlQuery .= ' OR c.name LIKE "%'.$_POST["search"]["value"].'%") ';								
	}
	
	if(!empty($_POST["order"])){
		$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
	} else {
		$sqlQuery .= 'ORDER BY s.id DESC ';
	}
	
	if($_POST["length"] != -1){
		$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
	}
	
	$stmt = $this->conn->prepare($sqlQuery);
	$stmt->execute();
	$result = $stmt->get_result();	
	
	$stmtTotal = $this->conn->prepare("SELECT * FROM ".$this->studentTable);
	$stmtTotal->execute();
	$allResult = $stmtTotal->get_result();
	$allRecords = $allResult->num_rows;
	
	$displayRecords = $result->num_rows;
	$records = array();		
	while ($student = $result->fetch_assoc()) { 				
		$rows = array();			
		$rows[] = $student['id'];
		$rows[] = ucfirst($student['name']);
		$rows[] = $student['roll_no'];		
		$rows[] = $student['class_name'];				
		$rows[] = '<button type="button" name="view" id="'.$student["id"].'" class="btn btn-info btn-xs view"><span class="glyphicon glyphicon-file" title="View"></span></button>';
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

h2>Step4: Manage Student Attendance

In attendance.php file, we will create HTML to search class student and perform students attendance.

<div class="row">
	<div class="col-md-12">
		<div class="box box-primary">
			<div class="box-header with-border">
				<h3 class="box-title"><i class="fa fa-search"></i> Select Class For Attendance</h3>
			</div>
			<form id="form1" action="" method="post" accept-charset="utf-8">
				<div class="box-body">						
					<div class="row">
						<div class="col-md-4">
							<div class="form-group">
								<label for="exampleInputEmail1">Class</label><small class="req"> *</small>
								<select id="classid" name="classid" class="form-control" required>
									<option value="">Select</option>
									<?php echo $teacher->classList(); ?>												
								</select>
								<span class="text-danger"></span>
							</div>
						</div>																	
					</div>
				</div>
				<div class="box-footer">
					<button type="button" id="search" name="search" value="search" style="margin-bottom:10px;" class="btn btn-primary btn-sm  checkbox-toggle"><i class="fa fa-search"></i> Search</button> <br>
				</div>
			</form>
		</div>
	</div>
</div>
<div class="row">					
	<form id="attendanceForm" method="post">					
		<div style="color:red;margin-top:20px;" class="hidden" id="message"></div>
		<button type="submit" id="saveAttendance" name="saveAttendance" value="Save Attendance" style="margin-bottom:10px;" class="btn btn-primary btn-sm  pull-right checkbox-toggle hidden"><i class="fa fa-save"></i> Save Attendance</button> <table id="studentList" class="table table-bordered table-striped hidden">
			<thead>
				<tr>
					<th>#</th>								
					<th>Roll No</th>	
					<th>Name</th>
					<th>Attendance</th>													
				</tr>
			</thead>
		</table>
		<input type="hidden" name="action" id="action" value="updateAttendance" />
		<input type="hidden" name="att_classid" id="att_classid" value="" />
		<input type="hidden" name="att_sectionid" id="att_sectionid" value="" />
	</form>
</div>

In attendance.js file, we will implement student attendance functionality.

$('#search').click(function(){
	$('#studentList').removeClass('hidden');
	$('#saveAttendance').removeClass('hidden');
	if ($.fn.DataTable.isDataTable("#studentList")) {
		$('#studentList').DataTable().clear().destroy();
	}
	var classid = $('#classid').val();		
	if(classid) {
		$.ajax({
			url:"attendance_action.php",
			method:"POST",
			data:{classid:classid, action:"attendanceStatus"},
			success:function(data) {					
				$('#message').text(data).removeClass('hidden');	
			}
		})
		$('#studentList').DataTable({
			"lengthChange": false,
			"processing":true,
			"serverSide":true,
			"order":[],
			"ajax":{
				url:"attendance_action.php",
				type:"POST",				
				data:{classid:classid, action:'getStudents'},
				dataType:"json"
			},
			"columnDefs":[
				{
					"targets":[0],
					"orderable":false,
				},
			],
			"pageLength": 10
		});				
	}
});	
$("#classid").change(function() {		
	$('#att_classid').val($(this).val());		
});	
$("#sectionid").change(function() {
	$('#att_sectionid').val($(this).val());		
});
$("#attendanceForm").submit(function(e) {		
	var formData = $(this).serialize();
	$.ajax({
		url:"attendance_action.php",
		method:"POST",
		data:formData,
		success:function(data){				
			$('#message').text(data).removeClass('hidden');				
		}
	});
	return false;
});

We will class method updateAttendance() to perform student attendance.

$student = new Student($db);

if(!empty($_POST['action']) && $_POST['action'] == 'updateAttendance') {
	$student->updateAttendance();
}

We will implement method updateAttendance() in class Student.php

public function updateAttendance(){	
	$attendanceYear = date('Y'); 
	$attendanceMonth = date('m'); 
	$attendanceDay = date('d'); 
	$attendanceDate = $attendanceYear."/".$attendanceMonth."/".$attendanceDay;	
	
	$sqlQuery = "SELECT * FROM ".$this->attendanceTable." 
		WHERE class_id = '".$_POST["att_classid"]."' AND attendance_date = '".$attendanceDate."'";			
	
	$stmt = $this->conn->prepare($sqlQuery);
	$stmt->execute();
	$result = $stmt->get_result();
	$attendanceDone = $result->num_rows;		
	
	if($attendanceDone) {
		foreach($_POST as $key => $value) {				
			if (strpos($key, "attendencetype_") !== false) {
				$student_id = str_replace("attendencetype_","", $key);
				$attendanceStatus = $value;					
				if($student_id) {
					$updateQuery = "UPDATE ".$this->attendanceTable." SET status = '".$attendanceStatus."'
					WHERE student_id = '".$student_id."' AND class_id = '".$_POST["att_classid"]."' AND attendance_date = '".$attendanceDate."'";						
					
					$stmt = $this->conn->prepare($updateQuery);							
					$stmt->execute();
					
				}
			}				
		}	
		echo "Attendance updated successfully!";			
	} else {
		foreach($_POST as $key => $value) {				
			if (strpos($key, "attendencetype_") !== false) {
				$student_id = str_replace("attendencetype_","", $key);
				$attendanceStatus = $value;					
				if($student_id) {
					$insertQuery = "INSERT INTO ".$this->attendanceTable."(student_id, class_id, status, attendance_date) 
					VALUES ('".$student_id."', '".$_POST["att_classid"]."', '".$attendanceStatus."', '".$attendanceDate."')";
					
					$stmt = $this->conn->prepare($insertQuery);							
					$stmt->execute();
				}
			}
			
		}
		echo "Attendance save successfully!";
	}	
}

Step5: Attendance Report

In attendance_report.php file, we will create HTML to search and display attendance report.

<div class="row">					
	<form id="attendanceForm" method="post">					
		<table id="studentList" class="table table-bordered table-striped hidden">
			<thead>
				<tr>
					<th>#</th>								
					<th>Roll No</th>	
					<th>Name</th>
					<th>Attendance</th>													
				</tr>
			</thead>
		</table>					
	</form>
</div>

In attendance_report.php, we will make ajax request to load attendance report.

$('#search').click(function(){
	$('#studentList').removeClass('hidden');		
	if ($.fn.DataTable.isDataTable("#studentList")) {
		$('#studentList').DataTable().clear().destroy();
	}
	var classid = $('#classid').val();		
	var attendanceDate = $('#attendanceDate').val();		
	if(classid && attendanceDate) {			
		$('#studentList').DataTable({
			"lengthChange": false,
			"processing":true,
			"serverSide":true,
			"order":[],
			"ajax":{
				url:"attendance_action.php",
				type:"POST",				
				data:{classid:classid, attendanceDate:attendanceDate, action:'getStudentsAttendance'},
				dataType:"json"
			},
			"columnDefs":[
				{
					"targets":[0],
					"orderable":false,
				},
			],
			"pageLength": 10
		});				
	}
});

We will call method getStudentsAttendance() to get student attendance details.

if(!empty($_POST['action']) && $_POST['action'] == 'getStudentsAttendance') {
	$student->classId = $_POST["classid"];
	$student->attendanceDate = $_POST["attendanceDate"];
	$student->getStudentsAttendance();
}

We will implement method getStudentsAttendance() in class Student.php and return details as JSON data to load into DataTable.

public function getStudentsAttendance(){		
	if($this->classId && $this->attendanceDate) {
		$sqlQuery = "SELECT s.id, s.name, s.photo, s.gender, s.dob, s.mobile, s.email, s.current_address, s.father_name, s.mother_name,s.admission_no, s.roll_no, s.admission_date, s.academic_year, a.status
			FROM ".$this->studentTable." as s
			LEFT JOIN ".$this->attendanceTable." as a ON s.id = a.student_id
			WHERE s.class = '".$this->classId."' AND a.attendance_date = '".$this->attendanceDate."'";
		if(!empty($_POST["search"]["value"])){
			$sqlQuery .= ' AND (s.id LIKE "%'.$_POST["search"]["value"].'%" ';
			$sqlQuery .= ' OR s.name LIKE "%'.$_POST["search"]["value"].'%" ';
			$sqlQuery .= ' OR s.admission_no LIKE "%'.$_POST["search"]["value"].'%" ';	
			$sqlQuery .= ' OR s.roll_no LIKE "%'.$_POST["search"]["value"].'%" ';	
			$sqlQuery .= ' OR a.attendance_date LIKE "%'.$_POST["search"]["value"].'%" )';
		}
		if(!empty($_POST["order"])){
			$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
		} else {
			$sqlQuery .= 'ORDER BY s.id DESC ';
		}
		if($_POST["length"] != -1){
			$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
		}	
		
		
		$stmt = $this->conn->prepare($sqlQuery);
		$stmt->execute();
		$result = $stmt->get_result();	
					
		$stmtTotal = $this->conn->prepare("SELECT * FROM ".$this->attendanceTable);
		$stmtTotal->execute();
		$allResult = $stmtTotal->get_result();
		$allRecords = $allResult->num_rows;
	
		$displayRecords = $result->num_rows;		
		
		$studentData = array();				
		while ($student = $result->fetch_assoc()) {			
			$attendance = '';
			if($student['status'] == 'present') {
				$attendance = '<small class="label label-success">Present</small>';
			} else if($student['status'] == 'late') {
				$attendance = '<small class="label label-warning">Late</small>';
			} else if($student['status'] == 'absent') {
				$attendance = '<small class="label label-danger">Absent</small>';
			} else if($student['status'] == 'half_day') {
				$attendance = '<small class="label label-info">Half Day</small>';
			}				
			$studentRows = array();			
			$studentRows[] = $student['id'];				
			$studentRows[] = $student['roll_no'];
			$studentRows[] = $student['name'];		
			$studentRows[] = $attendance;					
			$studentData[] = $studentRows;
		}
		
		$output = array(
			"draw"	=>	intval($_POST["draw"]),			
			"iTotalRecords"	=> 	$displayRecords,
			"iTotalDisplayRecords"	=>  $allRecords,
			"data"	=> 	$studentData
		);
		echo json_encode($output);
		
	}
}

You may also like:

You can view the live demo from the Demo link and can download the source from the Download link below.
Demo Download

6 thoughts on “Student Attendance System with PHP & MySQL”

eyad says:

September 24, 2020 at 7:50 am

cant add students or teachers ???
where is the Admin Panel ???
coderszine team says:

October 26, 2020 at 2:18 pm

We will update this tutorial very soon with admin panel. thanks!
CJ says:

August 4, 2021 at 3:39 pm

can’t login with user name a password in DB please can you help?
coderszine team says:

August 13, 2021 at 5:06 pm

Have you try to debug issue, may be there any error, you can provide us code to b=debug issue. thanks!
awdwa says:

November 20, 2021 at 9:48 am

cant add students or teachers ???
where is the Admin Panel ???
coderszine team says:

April 9, 2022 at 6:48 am

Tutorial is updated and you can manage users, students etc by admin login.

Comments are closed.