AJAX Add Edit Delete Records in Database using PHP and jQuery
by Vincy. Last modified on August 29th, 2022.
The Database add, modify, delete, list, and 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.
I have created a database table comment to store the users’ comments. I have used jQuery handlers to generate AJAX requests 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 the MySQL database using PHP via AJAX. If you are looking for a non-AJAX plain PHP CRUD, go through my previous tutorial.
This screenshot shows the output of the PHP example for performing add, edit, delete and read actions via AJAX.
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
CREATE TABLE `comment` (
`id` int(11) NOT NULL,
`message` text NOT NULL
);
ALTER TABLE `comment`
ADD PRIMARY KEY (`id`);
ALTER TABLE `comment`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
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 into the browser. After successful insert, the AJAX response will update the newly added record in the browser.
Each record in the comments list view contains the Edit and the Delete buttons to trigger respective database actions. We have form controls to enter user data and trigger AJAX events to perform the database action.
This code initially displays the list of records from a 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) {
?>
<div class="message-box"
id="message_<?php echo $comments[$k]["id"];?>">
<div id="link-space">
<a class="btn-action"
onClick="showEditBox(this,<?php echo $comments[$k]["id"]; ?>)">Edit</a>
<a class="btn-action"
onClick="callCrudAction('delete',<?php echo $comments[$k]["id"]; ?>)">Delete</a>
</div>
<div class="message-content"><?php echo $comments[$k]["message"]; ?></div>
</div>
<?php
}
}
?>
</div>
<div class="comment-form-container">
<form id="frm-comment">
<div id="frmAdd">
<textarea name="txtmessage" id="txtmessage"
class="txtmessage" rows="5"></textarea>
<p>
<button name="submit" onClick="callCrudAction('add','')"
type="submit" name="submit" id="submit"
class="btn-submit">Add</button>
<span id="txtmessage-info"></span>
</p>
</div>
<img src="LoaderIcon.gif" id="loaderIcon" style="display: none" />
</form>
</div>
Once the add form is submitted then the form data is sent to the PHP page via an AJAX call. On a 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) {
var queryString;
valid = true;
if (action != "delete") {
if ($(".txtmessage").val() == '') {
valid = false;
}
}
if (valid == true) {
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;
}
$("#loaderIcon").show();
jQuery.ajax({
url: "crud_action.php",
data: queryString,
type: "POST",
success: function(data) {
switch (action) {
case "add":
$("#comment-list-box").append(data);
$("#loaderIcon").hide();
$("#txtmessage").val('');
break;
case "edit":
alert(data);
$("#message_" + id + " .message-content").html(data);
$('.comment-form-container').show();
$("#message_" + id + " .btnEditAction").prop('disabled', '');
$("#loaderIcon").hide();
$("#txtmessage").val('');
break;
case "delete":
var deleteConfirmation = confirm('Are you sure you want delete');
if (deleteConfirmation) {
$('#message_' + id).fadeOut();
$("#loaderIcon").hide();
}
break;
}
},
error: function() { }
});
}
}
PHP Script for Database CRUD operations
This PHP code deals with the database CRUD actions using switch cases. The add, update and delete cases perform 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 completing the database insert, it returns the HTML to display the newly added record in the list.
On editing a record this code will respond to 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 __DIR__ . '/DataSource.php';
$database = new DataSource();
$action = $_POST["action"];
if (! empty($action)) {
switch ($action) {
case "add":
$sql = "INSERT INTO comment(message) VALUES (?)";
$paramType = 's';
$paramValue = array(
$_POST["txtmessage"]
);
$insert_id = $database->insert($sql, $paramType, $paramValue);
if ($insert_id) {
echo '<div class="message-box" id="message_' . $insert_id . '">
<div>
<a class="btn-reply" onClick="showEditBox(this,' . $insert_id . ')">Edit</a>
<a class="btn-reply" onClick="callCrudAction(\'delete\',' . $insert_id . ')">Delete</a>
</div>
<div class="message-content">' . $_POST["txtmessage"] . '</div></div>';
}
break;
case "edit":
$sql = "UPDATE comment set message =? WHERE id=?";
$paramType = 'si';
$paramValue = array(
$_POST["txtmessage"],
$_POST["message_id"]
);
$result = $database->update($sql, $paramType, $paramValue);
echo $_POST["txtmessage"];
break;
case "delete":
if (! empty($_POST["message_id"])) {
$sql = "DELETE FROM comment WHERE id=?";
$paramType = 'i';
$paramValue = array(
$_POST["message_id"]
);
$result = $database->delete($sql, $paramType, $paramValue);
}
break;
}
}
?>
DataSource.php
This class includes functions to execute the queries to perform CRUD with the database. It uses prepared statement to run the database operations.
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.
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.