DataTables CRUD Operations using PHP, jQuery & MySQL

	CREATE TABLE `student` (
	  `id` int(11) NOT NULL,
	  `roll_no` varchar(10) NOT NULL,
	  `name` varchar(255) NOT NULL,
	  `email` varchar(255) NOT NULL,
	  `address` text NOT NULL,
	  `class_name` varchar(255) NOT NULL,
	  `gender` varchar(15) NOT NULL,
	  `created_date` datetime NOT NULL DEFAULT current_timestamp()
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	ALTER TABLE `student`
	  ADD PRIMARY KEY (`id`);

	ALTER TABLE `student`
	  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

CREATE TABLE `student` (

`id` int(11) NOT NULL,

`roll_no` varchar(10) NOT NULL,

`name` varchar(255) NOT NULL,

`email` varchar(255) NOT NULL,

`address` text NOT NULL,

`class_name` varchar(255) NOT NULL,

`gender` varchar(15) NOT NULL,

`created_date` datetime NOT NULL DEFAULT current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `student`

ADD PRIMARY KEY (`id`);

ALTER TABLE `student`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

	<link href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
	<script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>

1 2	<link href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css"> <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>

<table id="student-list" class="table table-bordered table-striped">
	<thead>
		<tr>
			<th>Roll No</th>
			<th>Name</th>
			<th>Gender</th>					
			<th>Email</th>
			<th>Address</th>
			<th>Class</th>					
			<th>Action</th>				
		</tr>
	</thead>
</table>

1

2

3

4

5

6

7

8

9

10

11

12

13

<thead>

<tr>

<th>Gender</th>

<th>Email</th>

<th>Address</th>

<th>Class</th>

<th>Action</th>

</tr>

</thead>

</table>

var studentRecords = jQuery('#student-list').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,
	"order":[],
	"ajax":{
		url:"action.php",
		type:"POST",
		data:{action:'fetch_all_student'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0,6],
			"orderable":false,
		},	
	],
	"pageLength": 10
});

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

var studentRecords = jQuery('#student-list').DataTable({

"lengthChange": false,

"processing":true,

"serverSide":true,

"order":[],

"ajax":{

url:"action.php",

type:"POST",

data:{action:'fetch_all_student'},

dataType:"json"

},

"columnDefs":[

{

"targets":[0,6],

"orderable":false,

},

],

"pageLength": 10

});

<?php
// class
class DbConnection {
    // Connect to an ODBC database using an alias
    private $_dbHostName = "localhost";
    private $_dbName = "webhaunt_DB";
    private $_dbUserName = "root";
    private $_dbPassword = "";
    private $_conn;
    // __construct
    public function __construct() {
    	try {
        	$this->_conn = new PDO("mysql:host=$this->_dbHostName;dbname=$this->_dbName", $this->_dbUserName, $this->_dbPassword, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
            // set the PDO error mode to exception
        	$this->_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	    } catch(PDOException $e) {
			echo "Error!: " . $e->getMessage();
		}

    }
    // return Connection
    public function returnConnection() {
        return $this->_conn;
    }
}
?>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

<?php

// class

class DbConnection {

// Connect to an ODBC database using an alias

private $_dbHostName = "localhost";

private $_dbName = "webhaunt_DB";

private $_dbUserName = "root";

private $_dbPassword = "";

private $_conn;

// __construct

public function __construct() {

try {

$this->_conn = new PDO("mysql:host=$this->_dbHostName;dbname=$this->_dbName", $this->_dbUserName, $this->_dbPassword, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));

// set the PDO error mode to exception

$this->_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e) {

echo "Error!: " . $e->getMessage();

}

// return Connection

public function returnConnection() {

return $this->_conn;

}

?>

<?php
include("DBConnection.php");
// Student
class Student 
{
    // Properties    
    protected $db;
    private $_studentID;
    private $_rollNo;
    private $_name;
    private $_email;
    private $_address;
    private $_gender;
    private $_className;
    private $_searchVal;
    private $_orderBy;
    private $_start;
    private $_length;

    // Methods
    public function setStudentID($studentID) {
        $this->_studentID = $studentID;
    }
    public function setName($name) {
        $this->_name = $name;
    }
    public function setRollNo($rollNo) {
        $this->_rollNo = $rollNo;
    }
    public function setEmail($email) {
        $this->_email = $email;
    }
    public function setAddress($address) {
        $this->_address = $address;
    }
    public function setGender($gender) {
        $this->_gender = $gender;
    }
    public function setClass($className) {
        $this->_className = $className;
    }
    public function setSearchVal($searchVal) {
        $this->_searchVal = $searchVal;
    }
    public function setOrderBy($orderBy) {
        $this->_orderBy = $orderBy;
    }
    public function setStart($start) {
        $this->_start = $start;
    }
    public function setLength($length) {
        $this->_length = $length;
    }

    
    // __construct
    public function __construct() {
        $this->db = new DBConnection();
        $this->db = $this->db->returnConnection();
    }

    // create student record in database
    public function create() {
		try {
    		$sql = 'INSERT INTO student (roll_no, name, email, address, class_name, gender)  VALUES (:roll_no, :name, :email, :address, :class_name, :gender)';
    		$data = [
			    'roll_no' => $this->_rollNo,
                'name' => $this->_name,
			    'email' => $this->_email,
                'address' => $this->_address,
                'class_name' => $this->_className,
                'gender' => $this->_gender,
			];
	    	$stmt = $this->db->prepare($sql);
	    	$stmt->execute($data);
			$status = $this->db->lastInsertId();
            return $status;

		} catch (Exception $err) {
    		die("Error!: ".$err);
		}

    }

    // update student record in database
    public function update() {
        try {
		    $sql = "UPDATE student SET roll_no=:roll_no, name=:name, email=:email, address=:address, class_name=:class_name, gender=:gender WHERE id=:student_id";
		     $data = [
			    'roll_no' => $this->_rollNo,
                'name' => $this->_name,
                'email' => $this->_email,
                'address' => $this->_address,
                'class_name' => $this->_className,
                'gender' => $this->_gender,
                'student_id' => $this->_studentID,
			];
			$stmt = $this->db->prepare($sql);
			$stmt->execute($data);
			$status = $stmt->rowCount();
            return $status;
		} catch (Exception $err) {
			die("Error!: " . $err);
		}
    }

    // gwt student record from database
    public function getTotalResult() {
        try {
            $sql = "SELECT * FROM student";
            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            $result = $stmt->rowCount();
            return $result;
        } catch (Exception $e) {
            die("Error!: " . $err);
        }
    }
   
    // get all student records from database
    public function getList() {
    	try {
            // keyword serach
            $sqlQuery = '';
            if(!empty($this->_searchVal)){
                $sqlQuery .= 'WHERE (id LIKE "%'.$this->_searchVal.'%" ';
                $sqlQuery .= ' OR roll_no LIKE "%'.$this->_searchVal.'%" ';            
                $sqlQuery .= ' OR name LIKE "%'.$this->_searchVal.'%" ';
                $sqlQuery .= ' OR email LIKE "%'.$this->_searchVal.'%" ';
                $sqlQuery .= ' OR address LIKE "%'.$this->_searchVal.'%") ';
                $sqlQuery .= ' OR class_name LIKE "%'.$this->_searchVal.'%") ';
                $sqlQuery .= ' OR gender LIKE "%'.$this->_searchVal.'%") ';        
            }
            // sorting
            if(!empty($this->_orderBy)){
                $sqlQuery .= 'ORDER BY '.$this->_orderBy['0']['column'].' '.$this->_orderBy['0']['dir'].' ';
            } else {
                $sqlQuery .= 'ORDER BY id DESC ';
            }
            // paging
            if($this->_length != -1){
                $sqlQuery .= 'LIMIT ' . $this->_start . ', ' . $this->_length;
            }

    		$sql = "SELECT id, roll_no, name, email, address, class_name, gender, created_date FROM student ".$sqlQuery;
		    $stmt = $this->db->prepare($sql);
		    $stmt->execute();
		    $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            return $result;
		} catch (Exception $err) {
		    die("Error!: " . $err);
		}
    }
    // gwt student record from database
    public function getStudent() {
        try {
            $sql = "SELECT id, roll_no, name, email, address, class_name, gender, created_date FROM student WHERE id=:student_id";
            $stmt = $this->db->prepare($sql);
            $data = [
                'student_id' => $this->_studentID
            ];
            $stmt->execute($data);
            $result = $stmt->fetch(\PDO::FETCH_ASSOC);
            return $result;
        } catch (Exception $e) {
            die("Error!: " . $err);
        }
    }

    // delete student record from database
    public function delete() {
    	try {
	    	$sql = "DELETE FROM student WHERE id=:student_id";
		    $stmt = $this->db->prepare($sql);
		    $data = [
		    	'student_id' => $this->_studentID
			];
	    	$stmt->execute($data);
            $status = $stmt->rowCount();
            return $status;
	    } catch (Exception $err) {
		    die("Error!: " . $err);
		}
    }

}
?>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

<?php

include("DBConnection.php");

// Student

class Student

{

// Properties

protected $db;

private $_studentID;

private $_rollNo;

private $_name;

private $_email;

private $_address;

private $_gender;

private $_className;

private $_searchVal;

private $_orderBy;

private $_start;

private $_length;

// Methods

public function setStudentID($studentID) {

$this->_studentID = $studentID;

}

public function setName($name) {

$this->_name = $name;

}

public function setRollNo($rollNo) {

$this->_rollNo = $rollNo;

}

public function setEmail($email) {

$this->_email = $email;

}

public function setAddress($address) {

$this->_address = $address;

}

public function setGender($gender) {

$this->_gender = $gender;

}

public function setClass($className) {

$this->_className = $className;

}

public function setSearchVal($searchVal) {

$this->_searchVal = $searchVal;

}

public function setOrderBy($orderBy) {

$this->_orderBy = $orderBy;

}

public function setStart($start) {

$this->_start = $start;

}

public function setLength($length) {

$this->_length = $length;

}

// __construct

public function __construct() {

$this->db = new DBConnection();

$this->db = $this->db->returnConnection();

}

// create student record in database

public function create() {

try {

$sql = 'INSERT INTO student (roll_no, name, email, address, class_name, gender) VALUES (:roll_no, :name, :email, :address, :class_name, :gender)';

$data = [

'roll_no' => $this->_rollNo,

'name' => $this->_name,

'email' => $this->_email,

'address' => $this->_address,

'class_name' => $this->_className,

'gender' => $this->_gender,

];

$stmt = $this->db->prepare($sql);

$stmt->execute($data);

$status = $this->db->lastInsertId();

return $status;

} catch (Exception $err) {

die("Error!: ".$err);

}

// update student record in database

public function update() {

try {

$sql = "UPDATE student SET roll_no=:roll_no, name=:name, email=:email, address=:address, class_name=:class_name, gender=:gender WHERE id=:student_id";

$data = [

'roll_no' => $this->_rollNo,

'name' => $this->_name,

'email' => $this->_email,

'address' => $this->_address,

'class_name' => $this->_className,

'gender' => $this->_gender,

'student_id' => $this->_studentID,

];

$stmt = $this->db->prepare($sql);

$stmt->execute($data);

$status = $stmt->rowCount();

return $status;

} catch (Exception $err) {

die("Error!: " . $err);

}

// gwt student record from database

public function getTotalResult() {

try {

$sql = "SELECT * FROM student";

$stmt = $this->db->prepare($sql);

$stmt->execute();

$result = $stmt->rowCount();

return $result;

} catch (Exception $e) {

die("Error!: " . $err);

}

// get all student records from database

public function getList() {

try {

// keyword serach

$sqlQuery = '';

if(!empty($this->_searchVal)){

$sqlQuery .= 'WHERE (id LIKE "%'.$this->_searchVal.'%" ';

$sqlQuery .= ' OR roll_no LIKE "%'.$this->_searchVal.'%" ';

$sqlQuery .= ' OR name LIKE "%'.$this->_searchVal.'%" ';

$sqlQuery .= ' OR email LIKE "%'.$this->_searchVal.'%" ';

$sqlQuery .= ' OR address LIKE "%'.$this->_searchVal.'%") ';

$sqlQuery .= ' OR class_name LIKE "%'.$this->_searchVal.'%") ';

$sqlQuery .= ' OR gender LIKE "%'.$this->_searchVal.'%") ';

}

// sorting

if(!empty($this->_orderBy)){

$sqlQuery .= 'ORDER BY '.$this->_orderBy['0']['column'].' '.$this->_orderBy['0']['dir'].' ';

} else {

$sqlQuery .= 'ORDER BY id DESC ';

}

// paging

if($this->_length != -1){

$sqlQuery .= 'LIMIT ' . $this->_start . ', ' . $this->_length;

}

$sql = "SELECT id, roll_no, name, email, address, class_name, gender, created_date FROM student ".$sqlQuery;

$stmt = $this->db->prepare($sql);

$stmt->execute();

$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);

return $result;

} catch (Exception $err) {

die("Error!: " . $err);

}

// gwt student record from database

public function getStudent() {

try {

$sql = "SELECT id, roll_no, name, email, address, class_name, gender, created_date FROM student WHERE id=:student_id";

$stmt = $this->db->prepare($sql);

$data = [

'student_id' => $this->_studentID

];

$stmt->execute($data);

$result = $stmt->fetch(\PDO::FETCH_ASSOC);

return $result;

} catch (Exception $e) {

die("Error!: " . $err);

}

// delete student record from database

public function delete() {

try {

$sql = "DELETE FROM student WHERE id=:student_id";

$stmt = $this->db->prepare($sql);

$data = [

'student_id' => $this->_studentID

];

$stmt->execute($data);

$status = $stmt->rowCount();

return $status;

} catch (Exception $err) {

die("Error!: " . $err);

}

?>

<?php
// include class 
include_once 'class/Student.php';
// object
$studentObj = new Student();
// post method
$_post = $_POST;
$json = array();	
$studentInfo = array();
// create student record in database
if(!empty($_post['action']) && $_post['action']=="create") {
  $studentObj->setRollNo($_post['roll_no']);
	$studentObj->setName($_post['name']);
	$studentObj->setEmail($_post['email']);
	$studentObj->setAddress($_post['address']);
  $studentObj->setGender($_post['gender']);
  $studentObj->setClass($_post['class_name']);
	$status = $studentObj->create();
	if(!empty($status)){
		$json['msg'] = 'success';
		$json['task_id'] = $status;
	} else {
		$json['msg'] = 'failed';
		$json['task_id'] = '';
	}
	header('Content-Type: application/json');
  echo json_encode($json);  	
}

// get all student records in database
if(!empty($_post['action']) && $_post['action']=="fetch_all_student") {

  if(!empty($_post["search"]["value"])){
       $studentObj->setSearchVal($_post["search"]["value"]);       
  }
  if(!empty($_post["order"])){
    $studentObj->setOrderBy($_post["order"]);
  } 

  if($_post["length"]){
      $studentObj->setStart($_post["start"]);
      $studentObj->setLength($_post["length"]);
  }
  // get total result
  $totalResult = $studentObj->getTotalResult();
  // get student information
  $studentInformation = $studentObj->getList();
  foreach($studentInformation as $key=>$element) {    
      $studentInfo[] = array(
        $element['roll_no'], 
        $element['name'], 
        $element['gender'],
        $element['email'], 
        $element['address'], 
        $element['class_name'],  
        '<a data-studentid="'.$element["id"].'" class="text-white btn btn-info btn-sm view-student"> View </a>  <a data-studentid="'.$element["id"].'" class="text-white btn btn-success btn-sm update-student"> Edit </a>  <a data-studentid="'.$element["id"].'" class="text-white btn btn-danger btn-sm delete-student"> Delete</a>',
      );    
    }
  // draw data  
  $json['studentData'] = array(
      "draw"        =>  intval($_post["draw"]),
      "recordsTotal"    =>  $totalResult,
      "recordsFiltered"   =>  count($studentInformation),  
      "data"          =>  $studentInfo
    );
  header('Content-Type: application/json');
  echo json_encode($json['studentData']); 
}

// get student record in database
if(!empty($_post['action']) && $_post['action']=="fetch_student") {
	$studentObj->setStudentID($_post['student_id']);
	$json['fetchStudent'] = $studentObj->getStudent();
	header('Content-Type: application/json');
	echo json_encode($json['fetchStudent']); 
}

// update student record in database
if(!empty($_post['action']) && $_post['action']=="update") {
	$studentObj->setStudentID($_post['student_id']);
  $studentObj->setRollNo($_post['roll_no']);
	$studentObj->setName($_post['name']);
  $studentObj->setEmail($_post['email']);
  $studentObj->setAddress($_post['address']);
  $studentObj->setGender($_post['gender']);
  $studentObj->setClass($_post['class_name']);
	$status = $studentObj->update();
	if(!empty($status)){
		$json['msg'] = 'success';
	} else {
		$json['msg'] = 'failed';
	}
	header('Content-Type: application/json');	
	echo json_encode($json); 
}

// delete student record from database
if(!empty($_post['action']) && $_post['action']=="delete") {
	$studentObj->setStudentID($_post['student_id']);
	$status = $studentObj->delete();
	if(!empty($status)){
		$json['msg'] = 'success';
	} else {
		$json['msg'] = 'failed';
	}
	header('Content-Type: application/json');	
	echo json_encode($json);	
}
?>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

<?php

// include class

include_once 'class/Student.php';

// object

$studentObj = new Student();

// post method

$_post = $_POST;

$json = array();

$studentInfo = array();

// create student record in database

if(!empty($_post['action']) && $_post['action']=="create") {

$studentObj->setRollNo($_post['roll_no']);

$studentObj->setName($_post['name']);

$studentObj->setEmail($_post['email']);

$studentObj->setAddress($_post['address']);

$studentObj->setGender($_post['gender']);

$studentObj->setClass($_post['class_name']);

$status = $studentObj->create();

if(!empty($status)){

$json['msg'] = 'success';

$json['task_id'] = $status;

} else {

$json['msg'] = 'failed';

$json['task_id'] = '';

}

header('Content-Type: application/json');

echo json_encode($json);

}

// get all student records in database

if(!empty($_post['action']) && $_post['action']=="fetch_all_student") {

if(!empty($_post["search"]["value"])){

$studentObj->setSearchVal($_post["search"]["value"]);

}

if(!empty($_post["order"])){

$studentObj->setOrderBy($_post["order"]);

}

if($_post["length"]){

$studentObj->setStart($_post["start"]);

$studentObj->setLength($_post["length"]);

}

// get total result

$totalResult = $studentObj->getTotalResult();

// get student information

$studentInformation = $studentObj->getList();

foreach($studentInformation as $key=>$element) {

$studentInfo[] = array(

$element['roll_no'],

$element['name'],

$element['gender'],

$element['email'],

$element['address'],

$element['class_name'],

'<a data-studentid="'.$element["id"].'" class="text-white btn btn-info btn-sm view-student"> View </a> <a data-studentid="'.$element["id"].'" class="text-white btn btn-success btn-sm update-student"> Edit </a> <a data-studentid="'.$element["id"].'" class="text-white btn btn-danger btn-sm delete-student"> Delete</a>',

);

}

// draw data

$json['studentData'] = array(

"draw" => intval($_post["draw"]),

"recordsTotal" => $totalResult,

"recordsFiltered" => count($studentInformation),

"data" => $studentInfo

);

header('Content-Type: application/json');

echo json_encode($json['studentData']);

}

// get student record in database

if(!empty($_post['action']) && $_post['action']=="fetch_student") {

$studentObj->setStudentID($_post['student_id']);

$json['fetchStudent'] = $studentObj->getStudent();

header('Content-Type: application/json');

echo json_encode($json['fetchStudent']);

}

// update student record in database

if(!empty($_post['action']) && $_post['action']=="update") {

$studentObj->setStudentID($_post['student_id']);

$studentObj->setRollNo($_post['roll_no']);

$studentObj->setName($_post['name']);

$studentObj->setEmail($_post['email']);

$studentObj->setAddress($_post['address']);

$studentObj->setGender($_post['gender']);

$studentObj->setClass($_post['class_name']);

$status = $studentObj->update();

if(!empty($status)){

$json['msg'] = 'success';

} else {

$json['msg'] = 'failed';

}

header('Content-Type: application/json');

echo json_encode($json);

}

// delete student record from database

if(!empty($_post['action']) && $_post['action']=="delete") {

$studentObj->setStudentID($_post['student_id']);

$status = $studentObj->delete();

if(!empty($status)){

$json['msg'] = 'success';

} else {

$json['msg'] = 'failed';

}

header('Content-Type: application/json');

echo json_encode($json);

}

?>

<div class="modal" id="create-student" tabindex="-1" role="dialog">
  <div class="modal-dialog" role="document">
    <form class="form-horizontal" id="student-frm">
    <input type="hidden" name="action" id="action">    
    <input type="hidden" name="student_id" id="student_id">    
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title"> Add Student</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
        <div class="form-group">
            <input type="text" name="roll_no" class="form-control input-roll-no" id="rollno" aria-describedby="rollnoHelp" placeholder="Roll No" required="required">
        </div>
        <div class="form-group">
            <input type="text" name="name" class="form-control input-name" id="name" aria-describedby="nameHelp" placeholder="Name" required="required">
        </div>
        <div class="form-group">
            <input type="email" name="email" class="form-control input-email" id="email" aria-describedby="emailHelp" placeholder="Email" required="required">
        </div>
        <div class="form-group">
            <div class="form-group">
            <input type="text" name="gender" class="form-control input-gender" id="gender" aria-describedby="genderHelp" placeholder="Gender" required="required">
        </div>
        </div>
        <div class="form-group">
            <input type="text" name="address" class="form-control input-address" id="address" aria-describedby="addressHelp" placeholder="Address" required="required">
        </div>
        <div class="form-group">
            <div class="form-group">
            <input type="text" name="class_name" class="form-control input-class" id="class-name" aria-describedby="classHelp" placeholder="Class" required="required">
        </div>
        </div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-primary" id="student-btn">Add</button>
        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
      </div>
    </div>
    </form>
  </div>
</div>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

<h5 class="modal-title"> Add Student</h5>

</button>

</div>

</div>

</div>

</div>

</div>

</div>

</div>

<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>

</div>

</form>

</div>

<div class="modal" id="view-student" tabindex="-1" role="dialog">
  <div class="modal-dialog" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title">View Student</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
        <div class="form-group">
            <div class="card card-primary card-outline">
              <div class="card-body box-profile">
                <div class="text-center">
                  <img class="profile-user-img img-fluid img-circle" src="assets/images/user.jfif" height="130" width="130" alt="Student profile picture">
                </div>
                <h3 class="profile-username text-center" id="vs-name">Web Haunt</h3>
                <p class="text-muted text-center" id="vs-email">info@webhaunt.com</p>

                <ul class="list-group list-group-unbordered mb-3">
                  <li class="list-group-item">
                    <b>Roll No</b> <a class="float-right" id="vs-rollno">0001</a>
                  </li>
                  <li class="list-group-item">
                    <b>Class</b> <a class="float-right" id="vs-class-name">UKG</a>
                  </li>
                  <li class="list-group-item">
                    <b>Gender</b> <a class="float-right" id="vs-gender">Male</a>
                  </li>
                  <li class="list-group-item">
                    <b>Address</b> <a class="float-right" id="vs-address">Address</a>
                  </li>
                </ul>
              </div>
              <!-- /.card-body -->
            </div>
        </div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
      </div>
    </div>
  </div>
</div>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

<h5 class="modal-title">View Student</h5>

</button>

</div>

</div>

<h3 class="profile-username text-center" id="vs-name">Web Haunt</h3>

<p class="text-muted text-center" id="vs-email">info@webhaunt.com</p>

</li>

<b>Class</b> <a class="float-right" id="vs-class-name">UKG</a>

</li>

<b>Gender</b> <a class="float-right" id="vs-gender">Male</a>

</li>

<b>Address</b> <a class="float-right" id="vs-address">Address</a>

</li>

</ul>

</div>

</div>

<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>

</div>

<?php
include('templates/header.php');
?>
<section class="showcase">
  <div class="container">
    <div class="pb-2 mt-4 mb-2 border-bottom">
      <h2>DataTables CRUD Operations using PHP and MySQL</h2>
    </div>
      <div class="row align-items-center">
        <div class="col-md-12 col-md-right">
        	<a id="add-student" class="text-white btn btn-primary btn-sm float-right" style="margin-bottom: 5px;"> Add Student </a>
            <table id="student-list" class="table table-bordered table-striped">
				<thead>
					<tr>
						<th>Roll No</th>
						<th>Name</th>
						<th>Gender</th>					
						<th>Email</th>
						<th>Address</th>
						<th>Class</th>					
						<th>Action</th>				
					</tr>
				</thead>
			</table>
        </div>
      </div>
  </div>
</section>
<?php 
include('templates/footer.php');
include('modal/add.php');
include('modal/view.php');
?>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

<?php

include('templates/header.php');

?>

<h2>DataTables CRUD Operations using PHP and MySQL</h2>

</div>

<a id="add-student" class="text-white btn btn-primary btn-sm float-right" style="margin-bottom: 5px;"> Add Student </a>

<thead>

<tr>

<th>Gender</th>

<th>Email</th>

<th>Address</th>

<th>Class</th>

<th>Action</th>

</tr>

</thead>

</table>

</div>

</section>

<?php

include('templates/footer.php');

include('modal/add.php');

include('modal/view.php');

?>

jQuery(document).ready(function(){	
	var studentRecords = jQuery('#student-list').DataTable({
		"lengthChange": false,
		"processing":true,
		"serverSide":true,
		"order":[],
		"ajax":{
			url:"action.php",
			type:"POST",
			data:{action:'fetch_all_student'},
			dataType:"json"
		},
		"columnDefs":[
			{
				"targets":[0,6],
				"orderable":false,
			},	
		],
		"pageLength": 10
	});

	jQuery(document).on('click', '#add-student', function() {
		jQuery('#create-student').modal('show');
		jQuery('#student-frm')[0].reset();
		jQuery('.modal-title').html(" Add Student");
		jQuery('#action').val('create');
		jQuery('#student-btn').text('Add');
	});		

	jQuery("#student-list").on('click', '.update-student', function(){
		var student_id = jQuery(this).data("studentid");
		var action = 'fetch_student';
		jQuery.ajax({
			url:'action.php',
			method:"POST",
			data:{student_id:student_id, action:action},
			dataType:"json",
			success:function(json){
				jQuery('#create-student').modal('show');
				jQuery('#action').val('update');
				jQuery('#student_id').val(json.id);
				jQuery('#rollno').val(json.roll_no);
				jQuery('#name').val(json.name);
				jQuery('#email').val(json.email);
				jQuery('#gender').val(json.gender);				
				jQuery('#class-name').val(json.class_name);
				jQuery('#address').val(json.address);	
				jQuery('.modal-title').html(" Edit Student");
				jQuery('#student-btn').text('Update');
			}
		})
	});

	jQuery("#student-list").on('click', '.view-student', function(){
		var student_id = jQuery(this).data("studentid");
		var action = 'fetch_student';
		jQuery.ajax({
			url:'action.php',
			method:"POST",
			data:{student_id:student_id, action:action},
			dataType:"json",
			success:function(json){
				jQuery('#view-student').modal('show');
				jQuery('#vs-rollno').text(json.roll_no);
				jQuery('#vs-name').text(json.name);
				jQuery('#vs-email').text(json.email);
				jQuery('#vs-gender').text(json.gender);				
				jQuery('#vs-class-name').text(json.class_name);
				jQuery('#vs-address').text(json.address);
			}
		})
	});

	// create/update 
	jQuery(document).on('click','button#student-btn', function(e){
		e.preventDefault();
		var formData = jQuery('form#student-frm').serialize();
		var student_id = jQuery('input#student_id').val();
		if(student_id){
			var action = 'update';
		} else {
			var action = 'create';
		}
		jQuery.ajax({
			url:"action.php",
			method:"POST",
			data:formData,
			 beforeSend: function () {
	            jQuery('button#student-btn').button('loading');
	        },
	        complete: function () {
	            jQuery('button#student-btn').button('reset');
	            jQuery('#student-frm')[0].reset();
	            jQuery('#create-student').modal('hide');
	        }, 
			success:function(data){				
				studentRecords.ajax.reload();
			}
		})
	});	

	jQuery("#student-list").on('click', '.delete-student', function(){
		var student_id = jQuery(this).data("studentid");	
		var action = "delete";
		if(confirm("Are you sure you want to delete this student?")) {
			jQuery.ajax({
				url:"action.php",
				method:"POST",
				data:{student_id:student_id, action:action},
				success:function(data) {					
					studentRecords.ajax.reload();
				}
			})
		} else {
			return false;
		}
	});	
});

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

jQuery(document).ready(function(){

var studentRecords = jQuery('#student-list').DataTable({

"lengthChange": false,

"processing":true,

"serverSide":true,

"order":[],

"ajax":{

url:"action.php",

type:"POST",

data:{action:'fetch_all_student'},

dataType:"json"

},

"columnDefs":[

{

"targets":[0,6],

"orderable":false,

},

],

"pageLength": 10

});

jQuery(document).on('click', '#add-student', function() {

jQuery('#create-student').modal('show');

jQuery('#student-frm')[0].reset();

jQuery('.modal-title').html(" Add Student");

jQuery('#action').val('create');

jQuery('#student-btn').text('Add');

});

jQuery("#student-list").on('click', '.update-student', function(){

var student_id = jQuery(this).data("studentid");

var action = 'fetch_student';

jQuery.ajax({

url:'action.php',

method:"POST",

data:{student_id:student_id, action:action},

dataType:"json",

success:function(json){

jQuery('#create-student').modal('show');

jQuery('#action').val('update');

jQuery('#student_id').val(json.id);

jQuery('#rollno').val(json.roll_no);

jQuery('#name').val(json.name);

jQuery('#email').val(json.email);

jQuery('#gender').val(json.gender);

jQuery('#class-name').val(json.class_name);

jQuery('#address').val(json.address);

jQuery('.modal-title').html(" Edit Student");

jQuery('#student-btn').text('Update');

}

})

});

jQuery("#student-list").on('click', '.view-student', function(){

var student_id = jQuery(this).data("studentid");

var action = 'fetch_student';

jQuery.ajax({

url:'action.php',

method:"POST",

data:{student_id:student_id, action:action},

dataType:"json",

success:function(json){

jQuery('#view-student').modal('show');

jQuery('#vs-rollno').text(json.roll_no);

jQuery('#vs-name').text(json.name);

jQuery('#vs-email').text(json.email);

jQuery('#vs-gender').text(json.gender);

jQuery('#vs-class-name').text(json.class_name);

jQuery('#vs-address').text(json.address);

}

})

});

// create/update

jQuery(document).on('click','button#student-btn', function(e){

e.preventDefault();

var formData = jQuery('form#student-frm').serialize();

var student_id = jQuery('input#student_id').val();

if(student_id){

var action = 'update';

} else {

var action = 'create';

}

jQuery.ajax({

url:"action.php",

method:"POST",

data:formData,

beforeSend: function () {

jQuery('button#student-btn').button('loading');

},

complete: function () {

jQuery('button#student-btn').button('reset');

jQuery('#student-frm')[0].reset();

jQuery('#create-student').modal('hide');

},

success:function(data){

studentRecords.ajax.reload();

}

})

});

jQuery("#student-list").on('click', '.delete-student', function(){

var student_id = jQuery(this).data("studentid");

var action = "delete";

if(confirm("Are you sure you want to delete this student?")) {

jQuery.ajax({

url:"action.php",

method:"POST",

data:{student_id:student_id, action:action},

success:function(data) {

studentRecords.ajax.reload();

}

})

} else {

return false;

}

});

<!DOCTYPE html>
<html lang="en">
<head>
  <link rel="canonical" href="https://www.webhaunt.com/" />
  <meta name="author" content="WebHaunt">  
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="">
  <meta name="author" content="">
  <link rel="icon" href="https://webhaunt.com/wp-content/uploads/2016/11/webhaunt-150x142.png" sizes="32x32" />
  <link rel="icon" href="https://webhaunt.com/wp-content/uploads/2016/11/webhaunt.png" sizes="192x192" />
  <link rel="apple-touch-icon-precomposed" href="https://webhaunt.com/wp-content/uploads/2016/11/webhaunt.png" />
  <meta name="msapplication-TileImage" content="https://webhaunt.com/wp-content/uploads/2016/11/webhaunt.png" />
  <title>DataTables CRUD Operations using PHP and MySQL | Web Haunt</title>
  <!-- Bootstrap core CSS -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/css/bootstrap.min.css" />
  <!-- Custom fonts for this template -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.7.2/css/all.min.css" />
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/simple-line-icons/2.4.1/css/simple-line-icons.css" />
  <link href="https://fonts.googleapis.com/css?family=Lato:300,400,700,300italic,400italic,700italic" rel="stylesheet" type="text/css">
  <link href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
  <!-- Custom styles for this template -->
  <link href="assets/css/style.css" rel="stylesheet"> 
</head>
<body>
  <!-- Navigation -->
  <nav class="navbar navbar-expand-lg navbar-dark bg-dark static-top header-bg-dark" style="background: ##FFFFFF!;">
  <div class="container">
    <a class="navbar-brand font-weight-bold" href="https://webhaunt.com"><h1>Web Haunt</h1></a>
    <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarResponsive" aria-controls="navbarResponsive" aria-expanded="false" aria-label="Toggle navigation">
          <span class="navbar-toggler-icon"></span>
        </button>
    <div class="collapse navbar-collapse" id="navbarResponsive">
      <ul class="navbar-nav ml-auto">
        <li class="nav-item active">
          <a class="nav-link" href="https://webhaunt.com/">Home
                <span class="sr-only">(current)</span>
              </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="https://webhaunt.com/php-script-free-demo/">LIVE DEMO</a>
        </li>
      </ul>
    </div>
  </div>
</nav>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

<!DOCTYPE html>

<head>

<title>DataTables CRUD Operations using PHP and MySQL | Web Haunt</title>

</head>

<body>

<a class="navbar-brand font-weight-bold" href="https://webhaunt.com"><h1>Web Haunt</h1></a>

</button>

<a class="nav-link" href="https://webhaunt.com/">Home

<span class="sr-only">(current)</span>

</a>

</li>

</li>

</ul>

</div>

</nav>

<!-- Footer -->
  <footer class="footer bg-light footer-bg-dark">
    <div class="container">
      <div class="row">
        <div class="col-lg-6 h-100 text-center text-lg-left my-auto">
          <ul class="list-inline mb-2">
            <li class="list-inline-item">
              <a href="#">About</a>
            </li>
            <li class="list-inline-item">&sdot;</li>
            <li class="list-inline-item">
              <a href="#">Contact</a>
            </li>
            <li class="list-inline-item">&sdot;</li>
            <li class="list-inline-item">
              <a href="#">Terms of Use</a>
            </li>
            <li class="list-inline-item">&sdot;</li>
            <li class="list-inline-item">
              <a href="#">Privacy Policy</a>
            </li>
          </ul>
          <p class="small mb-4 mb-lg-0" style="color: #E6ECF0;">Copyright &copy;  2017 - <?php print date('Y', time());?> <a style="color: #E6ECF0;" href="https://webhaunt.com/">WEBHAUNT.COM</a> All rights reserved.</p>
        </div>
        <div class="col-lg-6 h-100 text-center text-lg-right my-auto">
          <ul class="list-inline mb-0">
            <li class="list-inline-item mr-3">
              <a href="#">
                <i class="fab fa-facebook fa-2x fa-fw"></i>
              </a>
            </li>
            <li class="list-inline-item mr-3">
              <a href="#">
                <i class="fab fa-twitter-square fa-2x fa-fw"></i>
              </a>
            </li>
            <li class="list-inline-item">
              <a href="#">
                <i class="fab fa-instagram fa-2x fa-fw"></i>
              </a>
            </li>
          </ul>
        </div>
      </div>
    </div>
  </footer>
  <!-- Bootstrap core JavaScript -->
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/js/bootstrap.bundle.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
  <script src="assets/js/ajax.js"></script>
</body>
</html>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

<a href="#">About</a>

</li>

<a href="#">Contact</a>

</li>

<a href="#">Terms of Use</a>

</li>

<a href="#">Privacy Policy</a>

</li>

</ul>

<p class="small mb-4 mb-lg-0" style="color: #E6ECF0;">Copyright © 2017 - <?php print date('Y', time());?> <a style="color: #E6ECF0;" href="https://webhaunt.com/">WEBHAUNT.COM</a> All rights reserved.</p>

</div>

</a>

</li>

</a>

</li>

</a>

</li>

</ul>

</div>

</footer>

</body>

</html>