DataTables CRUD Operations using PHP, jQuery & MySQL

Posted onby
CRUD Operations heart of a dynamic application. CRUD stands for Create, Read, Update and Delete and these are four basic functions to manipulate data in a database. In this tutorial, you will learn how to implement DataTables CRUD Operations using PHP jQuery, and MySQL. We will cover this tutorial in easy steps with live demo to DataTables CRUD Operations using PHP, jQuery & MySQL.
Also Read : TinyMCE Editor Upload Image with PHP & Ajax
  • CREATE – INSERT record in database.
  • READ – read records from database.
  • UPDATE – Executes an UPDATE record in database
  • DELETE – delete specified records from databse.
So let’s implement TinyMCE Editor Upload Image with PHP. look following folder and file structure:
  • tinymce-upload-image-with-php-ajax
    • assets
      • css
        • style.css
      • js
        • ajax.js
    • templates
      • header.php
      • footer.php
    • modal
      • add.php
      • view.php
    • index.php
    • action.php
Step 1: Create MySQL Database Table
First, we will create database with the following table.
	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;
Step 2: Include the DataTables script(JS and CSS)
Step 3: Display Student List
In index.php, we will create Bootstrap table to display student records.

	
		

			Roll No
			Name
			Gender					
			Email
			Address
			Class					
			Action
In ajax.js, we will make ajax request to action.php to action student-list to load student data to Datatables.
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
});
Step 4: Database Connection class
Create a class file named DBConnection.php inside “class/” folder.
_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;
    }
}
?>
Step 5: Create a class file named Student.php inside “class/” folder.
Manage CRUD Operations 
_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);
		}
    }

}
?>
Create action file named action.php
Manage request and return as JSON data
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'],  
        ' View    Edit    Delete',
      );    
    }
  // 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);	
}
?>
Step 6 : Add and View Student Record
We will design Bootstrap modal to add, view and update student record.
add.php inside “modal/” folder
view.php inside “modal/” folder
Complete HTML file named index.php



  

    

      
DataTables CRUD Operations using PHP and MySQL

    

      

        

        	 Add Student 
            

				
					

						Roll No
						Name
						Gender					
						Email
						Address
						Class					
						Action
Complete jQuery file named ajax.js
Handle crud proccess
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;
		}
	});	
});
Create header.php and footer.php section of the webpage. The Bootstrap library is used to provide a better UI, so, include it in the header and footer section.
header.php



  
    
  
  
  
  
  
  
  
  
  DataTables CRUD Operations using PHP and MySQL | Web Haunt
footer.php
Also Read : Convert Timestamp to Time Ago using PHP
Demo Download