PHP CRUD Create, edit, update and delete Records with MySQL database

here We learn to step by step simple crud operations in php using mysqli with full source code Example.

PHP MySQL CRUD Application

we have a create a simple 4 files like as bellow.

  • index.php
  • script.js
  • school/database.php
  • school/save.php

PHP and MySqli CRUD Operation Tutorial
PHP and MySqli CRUD Operation Tutorial

Also Read: PHP Jquery Ajax CRUD Example Tutorial From Scratch

Step 1: Html View File

index.php

<?php
include 'school/database.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="utf-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>PHP CRUDs Example - www.pakainfo.com</title>
	<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Varela+Round">
	<link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
	<link rel="stylesheet" href="css/style.css">
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
	<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
	<script src="ajax/ajax.js"></script>
</head>
<body>
    <div class="container">
	<p id="success"></p>
        <div class="table-wrapper">
            <div class="table-title">
                <div class="row">
                    <div class="col-sm-6">
						<h2>Manage <b>Students</b></h2>
					</div>
					<div class="col-sm-6">
						<a href="#addStudentModal" class="btn btn-success" data-toggle="modal"><i class="material-icons"></i> <span>Add New Student</span></a>
						<a href="JavaScript:void(0);" class="btn btn-danger" id="delete_multiple"><i class="material-icons"></i> <span>Delete</span></a>						
					</div>
                </div>
            </div>
            <table class="table table-striped table-hover">
                <thead>
                    <tr>
						<th>
							<span class="custom-checkbox">
								<input type="checkbox" id="selectAll">
								<label for="selectAll"></label>
							</span>
						</th>
						<th>SL NO</th>
                        <th>Student Name</th>
                        <th>Student Email</th>
						<th>Mobile</th>
                        <th>Address</th>
                        <th>ACTION</th>
                    </tr>
                </thead>
				<tbody>
				
				<?php
				$result = mysqli_query($conn,"SELECT * FROM students");
					$i=1;
					while($row = mysqli_fetch_array($result)) {
				?>
				<tr id="<?php echo $row["id"]; ?>">
				<td>
							<span class="custom-checkbox">
								<input type="checkbox" class="stud_checkbox" data-stud-id="<?php echo $row["id"]; ?>">
								<label for="checkbox2"></label>
							</span>
						</td>
					<td><?php echo $i; ?></td>
					<td><?php echo $row["studnm"]; ?></td>
					<td><?php echo $row["studemail"]; ?></td>
					<td><?php echo $row["mobile"]; ?></td>
					<td><?php echo $row["address"]; ?></td>
					<td>
						<a href="#editStudentModal" class="edit" data-toggle="modal">
							<i class="material-icons update" data-toggle="tooltip" 
							data-id="<?php echo $row["id"]; ?>"
							data-studnm="<?php echo $row["studnm"]; ?>"
							data-studemail="<?php echo $row["studemail"]; ?>"
							data-mobile="<?php echo $row["mobile"]; ?>"
							data-address="<?php echo $row["address"]; ?>"
							title="Edit"></i>
						</a>
						<a href="#deleteStudentModal" class="delete" data-id="<?php echo $row["id"]; ?>" data-toggle="modal"><i class="material-icons" data-toggle="tooltip" 
						 title="Delete"></i></a>
                    </td>
				</tr>
				<?php
				$i++;
				}
				?>
				</tbody>
			</table>
			
        </div>
    </div>
	<!-- Add Modal HTML -->
	<div id="addStudentModal" class="modal fade">
		<div class="modal-dialog">
			<div class="modal-content">
				<form id="stud_form">
					<div class="modal-header">						
						<h4 class="modal-title">Add Student</h4>
						<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
					</div>
					<div class="modal-body">					
						<div class="form-group">
							<label>Student NAME</label>
							<input type="text" id="studnm" name="studnm" class="form-control" required>
						</div>
						<div class="form-group">
							<label>Student EMAIL</label>
							<input type="email" id="studemail" name="studemail" class="form-control" required>
						</div>
						<div class="form-group">
							<label>Mobile</label>
							<input type="mobile" id="mobile" name="mobile" class="form-control" required>
						</div>
						<div class="form-group">
							<label>Address</label>
							<input type="address" id="address" name="address" class="form-control" required>
						</div>					
					</div>
					<div class="modal-footer">
					    <input type="hidden" value="1" name="type">
						<input type="button" class="btn btn-default" data-dismiss="modal" value="Cancel">
						<button type="button" class="btn btn-success" id="btn-add">Add</button>
					</div>
				</form>
			</div>
		</div>
	</div>
	<!-- Edit Modal HTML -->
	<div id="editStudentModal" class="modal fade">
		<div class="modal-dialog">
			<div class="modal-content">
				<form id="update_form">
					<div class="modal-header">						
						<h4 class="modal-title">Edit Student</h4>
						<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
					</div>
					<div class="modal-body">
						<input type="hidden" id="id_u" name="id" class="form-control" required>					
						<div class="form-group">
							<label>Student Name</label>
							<input type="text" id="studnm_u" name="studnm" class="form-control" required>
						</div>
						<div class="form-group">
							<label>Student Email</label>
							<input type="email" id="studemail_u" name="studemail" class="form-control" required>
						</div>
						<div class="form-group">
							<label>PHONE</label>
							<input type="mobile" id="mobile_u" name="mobile" class="form-control" required>
						</div>
						<div class="form-group">
							<label>City</label>
							<input type="address" id="address_u" name="address" class="form-control" required>
						</div>					
					</div>
					<div class="modal-footer">
					<input type="hidden" value="2" name="type">
						<input type="button" class="btn btn-default" data-dismiss="modal" value="Cancel">
						<button type="button" class="btn btn-info" id="update">Update</button>
					</div>
				</form>
			</div>
		</div>
	</div>
	<!-- Delete Modal HTML -->
	<div id="deleteStudentModal" class="modal fade">
		<div class="modal-dialog">
			<div class="modal-content">
				<form>
						
					<div class="modal-header">						
						<h4 class="modal-title">Delete Student</h4>
						<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
					</div>
					<div class="modal-body">
						<input type="hidden" id="id_d" name="id" class="form-control">					
						<p>Are you sure you want to delete these Records?</p>
						<p class="text-warning"><small>This action cannot be undone.</small></p>
					</div>
					<div class="modal-footer">
						<input type="button" class="btn btn-default" data-dismiss="modal" value="Cancel">
						<button type="button" class="btn btn-danger" id="delete">Delete</button>
					</div>
				</form>
			</div>
		</div>
	</div>

</body>
</html>    

Reusable PHP CRUD DATABASE FUNCTIONS
Reusable PHP CRUD DATABASE FUNCTIONS

Also Read: PHP – MySQLi Insert Update Delete CRUDs Operation

Read Also:  Update query in codeigniter Example

script.js

<script>
    $(document).on('click','#btn-add',function(e) {
		var data = $("#stud_form").serialize();
		$.ajax({
			data: data,
			type: "post",
			url: "school/save.php",
			success: function(responseOfStudents){
					var responseOfStudents = JSON.parse(responseOfStudents);
					if(responseOfStudents.statusCode==200){
						$('#addStudentModal').modal('hide');
						alert('Student added successfully !'); 
                        location.reload();						
					}
					else if(responseOfStudents.statusCode==201){
					   alert("Please fill all the fields !");
					}
			}
		});
	});
	$(document).on('click','.update',function(e) {
		var id=$(this).attr("data-id");
		var studnm=$(this).attr("data-studnm");
		var studemail=$(this).attr("data-studemail");
		var mobile=$(this).attr("data-mobile");
		var address=$(this).attr("data-address");
		$('#id_u').val(id);
		$('#studnm_u').val(studnm);
		$('#studemail_u').val(studemail);
		$('#mobile_u').val(mobile);
		$('#address_u').val(address);
	});
	
	$(document).on('click','#update',function(e) {
		var data = $("#update_form").serialize();
		$.ajax({
			data: data,
			type: "post",
			url: "school/save.php",
			success: function(responseOfStudents){
					var responseOfStudents = JSON.parse(responseOfStudents);
					if(responseOfStudents.statusCode==200){
						$('#editStudentModal').modal('hide');
						alert('Student updated successfully !'); 
                        location.reload();						
					}
					else if(responseOfStudents.statusCode==201){
					   alert("Please fill all the fields !");
					}
			}
		});
	});
	$(document).on("click", ".delete", function() { 
		var id=$(this).attr("data-id");
		$('#id_d').val(id);
		
	});
	$(document).on("click", "#delete", function() { 
		$.ajax({
			url: "school/save.php",
			type: "POST",
			cache: false,
			data:{
				type:3,
				id: $("#id_d").val()
			},
			success: function(responseOfStudents){
					$('#deleteStudentModal').modal('hide');
					$("#"+responseOfStudents).remove();
			
			}
		});
	});
	$(document).on("click", "#delete_multiple", function() {
		var stud = [];
		$(".stud_checkbox:checked").each(function() {
			stud.push($(this).data('stud-id'));
		});
		if(stud.length <=0) {
			alert("Please select records."); 
		} 
		else { 
			WRN_PROFILE_DELETE = "Are you sure you want to delete "+(stud.length>1?"these":"this")+" row?";
			var checked = confirm(WRN_PROFILE_DELETE);
			if(checked == true) {
				var selected_values = stud.join(",");
				console.log(selected_values);
				$.ajax({
					type: "POST",
					url: "school/save.php",
					cache:false,
					data:{
						type: 4,						
						id : selected_values
					},
					success: function(response) {
						var ids = response.split(",");
						for (var i=0; i < ids.length; i++ ) {	
							$("#"+ids[i]).remove(); 
						}	
					} 
				}); 
			}  
		} 
	});
	$(document).ready(function(){
		$('[data-toggle="tooltip"]').tooltip();
		var checkbox = $('table tbody input[type="checkbox"]');
		$("#selectAll").click(function(){
			if(this.checked){
				checkbox.each(function(){
					this.checked = true;                        
				});
			} else{
				checkbox.each(function(){
					this.checked = false;                        
				});
			} 
		});
		checkbox.click(function(){
			if(!this.checked){
				$("#selectAll").prop("checked", false);
			}
		});
	});

</script>

Step 2: Connect Database

school/database.php

<?php
$servername = "localhost";
$username = "root";
$password = "[email protected]";
$dbname = "students_info";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

Also Read: PHP MySQL CRUDs Create, Insert, Update and Delete operations

Read Also:  php Two Decimal Places without rounding off | php 2 decimal places without rounding

Step 3: Save/Store Data in MySQL Database

school/save.php

 <?php
include 'database.php';
if(count($_POST)>0){
	if($_POST['type']==1){
		$studnm=$_POST['studnm'];
		$studemail=$_POST['studemail'];
		$mobile=$_POST['mobile'];
		$address=$_POST['address'];
		$sql = "INSERT INTO `students`( `studnm`, `studemail`,`mobile`,`address`) 
		VALUES ('$studnm','$studemail','$mobile','$address')";
		if (mysqli_query($conn, $sql)) {
			echo json_encode(array("statusCode"=>200));
		} 
		else {
			echo "Error: " . $sql . "<br>" . mysqli_error($conn);
		}
		mysqli_close($conn);
	}
}
if(count($_POST)>0){
	if($_POST['type']==2){
		$id=$_POST['id'];
		$studnm=$_POST['studnm'];
		$studemail=$_POST['studemail'];
		$mobile=$_POST['mobile'];
		$address=$_POST['address'];
		$sql = "UPDATE `students` SET `studnm`='$studnm',`studemail`='$studemail',`mobile`='$mobile',`address`='$address' WHERE id=$id";
		if (mysqli_query($conn, $sql)) {
			echo json_encode(array("statusCode"=>200));
		} 
		else {
			echo "Error: " . $sql . "<br>" . mysqli_error($conn);
		}
		mysqli_close($conn);
	}
}
if(count($_POST)>0){
	if($_POST['type']==3){
		$id=$_POST['id'];
		$sql = "DELETE FROM `students` WHERE id=$id ";
		if (mysqli_query($conn, $sql)) {
			echo $id;
		} 
		else {
			echo "Error: " . $sql . "<br>" . mysqli_error($conn);
		}
		mysqli_close($conn);
	}
}
if(count($_POST)>0){
	if($_POST['type']==4){
		$id=$_POST['id'];
		$sql = "DELETE FROM students WHERE id in ($id)";
		if (mysqli_query($conn, $sql)) {
			echo $id;
		} 
		else {
			echo "Error: " . $sql . "<br>" . mysqli_error($conn);
		}
		mysqli_close($conn);
	}
}

?>


Related FAQ

Here are some more FAQ related to this Article:

  1. Read Also:  jQuery Foreach Loop ( jQuery.each() ) Example
  2. Read Also:  Laravel Convert object Array JSON String Example
  3. Read Also:  PHP and MySqli CRUD Operation Tutorial
  4. Read Also:  PHP MySQLi Insert Update Delete Query Builder
  5. Read Also:  Vue.js CRUD Example Tutorial For Beginners
  6. Read Also:  WooCommerce Get Product Taxonomies
  7. Read Also:  Format Phone Number using PHP
  8. Read Also:  PHP mysqli_num_fields - Function MySQLi Num Fields
  9. Read Also:  Multiple Ternary Operator in php Examples
  10. Read Also:  Frontaccounting API - Frontaccounting documentation - Frontaccounting tutorial