Table of contents

Delete Record in PHP & MySQL Using Ajax

In this tutorial, you will learn how to delete data in PHP & MySQL using ajax we know that this is a common function when creating software. So I hope that you will learn from this simple method and use it in your future project.

 

Delete Record in PHP & MySQL Using Ajax

 

With the help of ajax we are enabled to request the server to delete a specific record without reloading our page and automatically remove our record in the lists. So for you to understand we use a Simple Employee Database to delete a record.

 

Creating Database

So first create your database name for anything you want. After creating your database then you will need to create our "employees" table. Kindly check the code below for the SQL example.

 

CREATE TABLE `employees` (
  `id` int(10) NOT NULL,
  `email` varchar(100) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `address` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

ALTER TABLE `employees`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;

 

Take note that you can add your table either from the MySQL Command line or in PHPMyAdmin for a better experience. It depends on you.

 

Creating Index HTML

In this section, we will create our index.html and put this code below.

<!doctype html>
<html lang="en">
<head>
  	<title>Delete Data in PHP & MySQL Using Ajax</title>

  	<!-- Bootstrap CSS -->
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  	
  	<!-- Page CSS -->
  	<link rel="stylesheet" href="assets/css/styles.css">
</head>
  
<body>
   
	<div class="container">

		<br><br>

	    <h1>Delete Data in PHP & MySQL Using Ajax</h1>

	    <br><br>
	    
	    <div class="row">
	    	<div class="col-md-4">
	    		<h3>Add New Employee</h3>

			    <form action="save.php" id="form">
			    	<div class="form-group">
					    <label for="email">Email</label>
					    <input class="form-control" type="text" name="email">
				  	</div>
				  	<div class="form-group">
					    <label for="first_name">First Name</label>
					    <input class="form-control" type="text" name="first_name">
				  	</div>
				  	<div class="form-group">
					    <label for="last_name">Last Name</label>
					    <input class="form-control" type="text" name="last_name">
				  	</div>
				  	<div class="form-group">
					    <label for="address">Address</label>
					    <textarea class="form-control" type="text" name="address" rows="3"></textarea>
				  	</div>
				  	<button type="button" class="btn btn-primary" id="btnSubmit">Submit</button>
				</form>
	    	</div>

	    	<div class="col-md-8">
	    		<h3>List of Employees</h3>
	    		<div id="employees-list"></div>
	    	</div>
	    </div>
	</div>

	<!-- The Modal -->
	<div class="modal" id="edit-employee-modal">
	  	<div class="modal-dialog">
		    <div class="modal-content">

		      	<!-- Modal Header -->
		      	<div class="modal-header">
			        <h4 class="modal-title">Edit Employee</h4>
			        <button type="button" class="close" data-dismiss="modal">&times;</button>
		      	</div>

		      	<!-- Modal body -->
		      	<div class="modal-body">
		        	<form action="update.php" id="edit-form">
		        		<input class="form-control" type="hidden" name="id">
				    	<div class="form-group">
						    <label for="email">Email</label>
						    <input class="form-control" type="text" name="email">
					  	</div>
					  	<div class="form-group">
						    <label for="first_name">First Name</label>
						    <input class="form-control" type="text" name="first_name">
					  	</div>
					  	<div class="form-group">
						    <label for="last_name">Last Name</label>
						    <input class="form-control" type="text" name="last_name">
					  	</div>
					  	<div class="form-group">
						    <label for="address">Address</label>
						    <textarea class="form-control" type="text" name="address" rows="3"></textarea>
					  	</div>
					  	<button type="button" class="btn btn-primary" id="btnUpdateSubmit">Update</button>
					  	<button type="button" class="btn btn-danger float-right" data-dismiss="modal">Close</button>
					</form>


		      	</div>

		    </div>
	  	</div>
	</div>


	<!-- Must put our javascript files here to fast the page loading -->
	
	<!-- jQuery library -->
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
	<!-- Popper JS -->
	<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
	<!-- Bootstrap JS -->
	<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
	<!-- Page Script -->
	<script src="assets/js/scripts.js"></script>

</body>
  
</html>

 

Delete PHP Function

Here we will create a file delete.php and use it for deleting an employee record.

<?php
	$request = $_REQUEST; //a PHP Super Global variable which used to collect data after submitting it from the form
	$id = $request['employee_id']; //employee ID we are using it to get the employee record

	$servername = "localhost"; //set the servername
	$username = "root"; //set the server username
	$password = ""; // set the server password (you must put password here if your using live server)
	$dbname = "demos"; // set the table name

	$mysqli = new mysqli($servername, $username, $password, $dbname);

	if ($mysqli->connect_errno) {
	  echo "Failed to connect to MySQL: " . $mysqli->connect_error;
	  exit();
	}

	// Set the DELETE SQL data
	$sql = "DELETE FROM employees WHERE id='".$id."'";

	// Process the query so that we will save the date of birth
	if ($mysqli->query($sql)) {
	  echo "Employee has been successfully deleted.";
	} else {
	  echo "Error: " . $sql . "<br>" . $mysqli->error;
	}

	// Close the connection after using it
	$mysqli->close();
?>

 

Ajax Delete Function

In this function, we will create an ajax delete that will use to communicate with the server. You will found this function under scripts.js you will see it when you download the complete source code.

function del() 
{
	$(document).delegate(".btn-delete-employee", "click", function() {

		if (confirm("Are you sure you want to delete this record?")) {
		    var employeeId = $(this).attr('data-id'); //get the employee ID

		    // Ajax config
			$.ajax({
		        type: "GET", //we are using GET method to get data from server side
		        url: 'delete.php', // get the route value
		        data: {employee_id:employeeId}, //set data
		        beforeSend: function () {//We add this before send to disable the button once we submit it so that we prevent the multiple click
		            
		        },
		        success: function (response) {//once the request successfully process to the server side it will return result here
		            // Reload lists of employees
	            	all();

		            alert(response)
		        }
		    });
		}
	});
}

 

Then we will call the del function above.

$(document).ready(function() {
	// Delete record via ajax
	del();
});

 

So after you follow the above code you will learn how I usually code a delete function on PHP & MySQL using Ajax. I hope you learn from it. To see it in action just click the download button below.

 

Download

 

 

NOTE: Kindly don't use this code example as is, do some research also about how to secure your PHP code like SQL Injection and CSRF attacks. And before deleting the record you need to sanitize the submitted ID so that it will safe when querying it to your database. You must also check the ID if existing in your database before deleting the record.

 

Happy Coding :)