AJAX Add Edit Delete Records in Database using PHP and jQuery

Last modified on May 8th, 2018 by Vincy.

The Database add, modify, delete, list, view actions are the common tasks for many application functionalities. If you want to implement these functionalities in your application, this post will be helpful for you. In this example, I have implemented the database CRUD for the comment system with AJAX. The CRUD action HTML page contains button handles to trigger the AJAX request.

crud-functions

View Demo

I have created a database table comment to store the users’ comments. I have used jQuery handlers to generate AJAX request for calling PHP CRUD code. After successful CRUD action, the AJAX callback will update the status on the browser. Let us learn how to add, edit and delete records in MySQL database using PHP via AJAX. If you are looking for a non-AJAX plain PHP CRUD, go through my previous tutorial.

Database Script

Run this database script to set up the CRUD example on your local machine. It shows the CREATE statement and key indexes for the comment table

--
-- Database: `blog_samples`
--

-- --------------------------------------------------------

--
-- Table structure for table `comment`
--

CREATE TABLE `comment` (
  `id` int(11) NOT NULL,
  `message` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `comment`
--
ALTER TABLE `comment`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `comment`
--
ALTER TABLE `comment`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

Add Edit Delete HTML Handles

This code shows the HTML for displaying the database add, edit and delete action handles to the user. I have shown a text area to add a new comment to the database via AJAX. After entering the comment, the Add button triggers the AJAX call to insert the user’s comment to the browser. After successful insert, the AJAX response will update the newly added record in the browser. Each record in the comments list view contain the Edit and the Delete buttons to trigger respective database actions. We have form controls to enter user data and trigger AJAX event to perform the database action. This code initially displays the list of records from database table. It also has the comment-add form to enter text content to be inserted via AJAX.

<div id="comment-list-box">
    <?php if(!empty($comments)) { foreach($comments as $k=>$v) { ?>
    </p>
    <p>// display comments list
    <div class="message-box" id="message_<?php echo $comments[$k]["id"];?>
        ">
        <div>
            <button class="btnEditAction" name="edit"
                onClick="showEditBox(<?php echo $comments[$k]["id"]; ?>)">Edit</button>
            <button class="btnDeleteAction" name="delete"
                onClick="callCrudAction('delete',<?php echo $comments[$k]["id"]; ?>)">Delete</button>
        </div>
        <div class="message-content">
            <?php echo $comments[$k]["message"]; ?>
        </div>
    </div>
    <?php }} ?>
</div>
<div id="frmAdd">
    <textarea name="txtmessage" id="txtmessage" cols="80" rows="10"></textarea>
    <p>
        <button id="btnAddAction" name="submit"
            onClick="callCrudAction('add','')">Add Comment</button>
    </p>
</div>

Once the add form is submitted then the form data is sent to the PHP page via an AJAX call. On successful add event, it reloads the list of records displayed above add form.

jQuery AJAX Function to Query for Database CRUD

In this jQuery AJAX script, the callCrudAction() function contains JavaScript switch cases to send the CRUD request. In each case, I send the required CRUD action to be performed with other AJAX parameters. In PHP the action parameter is received and used to dispatch to the suitable case to perform the database action. In the AJAX success block, I received the response text or HTML to update the user about the database action.

function callCrudAction(action,id) {
$("#loaderIcon").show();
var queryString;
switch(action) {
case "add":
queryString = 'action='+action+'&txtmessage='+ $("#txtmessage").val();
break;
case "edit":
queryString = 'action='+action+'&message_id='+ id + '&txtmessage='+ $("#txtmessage_"+id).val();
break;
case "delete":
queryString = 'action='+action+'&message_id='+ id;
break;}	 
jQuery.ajax({
url: "crud_action.php",
data:queryString,
type: "POST",
success:function(data){
switch(action) {
case "add":
$("#comment-list-box").append(data);
break;
case "edit":
$("#message_" + id + " .message-content").html(data);
$('#frmAdd').show();
break;
case "delete":
$('#message_'+id).fadeOut();
break;
}
$("#txtmessage").val('');
$("#loaderIcon").hide();
},
});}

PHP Script for Database CRUD operations

This PHP code deals with the database CRUD actions using switch cases. The add, update, delete cases performs appropriate CRUD action based on the request raised via an AJAX call. After performing the CRUD action, this code sends the response text or HTML to the AJAX. For example, after performing the database insert, it returns the HTML to display the newly added record in the list. On editing a record this code will respond the edited text content to the AJAX. This content will be updated on the browser with the reference of the record id.

<?php
require_once("DBController.php");
$db_handle = new DBController();

$action = $_POST["action"];
if(!empty($action)) {
	switch($action) {
		case "add":
		    $query = "INSERT INTO comment(message) VALUES('".$_POST["txtmessage"]."')";
		    $insert_id = $db_handle->insert($query);
		    if($insert_id){
				  echo '<div class="message-box"  id="message_' . $insert_id . '">
						<div>
						<button class="btnEditAction" name="edit" onClick="showEditBox(this,' . $insert_id . ')">Edit</button>
<button class="btnDeleteAction" name="delete" onClick="callCrudAction(\'delete\',' . $insert_id . ')">Delete</button>
						</div>
						<div class="message-content">' . $_POST["txtmessage"] . '</div></div>';
			}
			break;
			
		case "edit":
		    $query = "UPDATE comment set message = '".$_POST["txtmessage"]."' WHERE  id=".$_POST["message_id"];
		    $result = $db_handle->execute($query);
			if($result){
				  echo $_POST["txtmessage"];
			}
			break;			
		
		case "delete": 
		    if(!empty($_POST["message_id"])) {
		        $query = "DELETE FROM comment WHERE id=".$_POST["message_id"];
		        $result = $db_handle->execute($query);
			}
			break;
	}
}
?>

DBController.php

This class includes functions to execute the queries to perform CRUD with the database. The runQuery() function reads the database result and returns them as an associative array. The insert() function receives the INSERT query as its argument and executes it. This function will return the newly added record id on the successful database insert. The execute() function receives the update/delete queries based on the request sent via AJAX.

<?php
class DBController {
	private $host = "localhost";
	private $user = "root";
	private $password = "test";
	private $database = "blog_samples";
	
	private static $conn;
	
	function __construct() {
		$this->conn = $this->connectDB();
		if(!empty($this->conn)) {
		    $this->selectDB();
		}
	}
	
	function connectDB() {
		$conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
		return $conn;
	}
	
	function selectDB() {
	    mysqli_select_db($this->conn, $this->database);
	}
	
	function runQuery($query) {
	    $result = mysqli_query($this->conn, $query);
		while($row=mysqli_fetch_assoc($result)) {
			$resultset[] = $row;
		}		
		if(!empty($resultset))
			return $resultset;
	}
	
	function numRows($query) {
	    $result  = mysqli_query($this->conn, $query);
		$rowcount = mysqli_num_rows($result);
		return $rowcount;	
	}
	
	function insert($query) {
	    $insert_id = "";
	    $result  = mysqli_query($this->conn, $query);
	    if(!empty($result)) {
	        $insert_id = mysqli_insert_id($this->conn);
	    }
	    return $insert_id;
	}
	
	function execute($query) {
	    $result  = mysqli_query($this->conn, $query);
	    return $result;
	}
}
?>

AJAX Add Edit Delete Output

This screenshot shows the output of the PHP example for performing add, edit, delete and read actions via AJAX.

ajax-crud

View DemoDownload

↑ Back to Top

Share this Article