Create Inline CRUD Using jQuery and AJAX
These are the four actions that make up the significant part of the actions of a PHP project. By the time developers get to the mid-level, they have actually created dozens of CRUD grids. In many cases, CRUD operations are an important part of CMS, inventory, and accounts management systems.
The idea behind the CRUD operations is to empower the users so that they could use the app to the maximum. All the information generated or modified through CRUD operations is stored in a database (generally MySQL).
To help budding developers, I will demonstrate a simple inline CRUD app using jQuery and AJAX.
Prerequisites
For the purpose of this tutorial, I assume that you have a PHP application installed on a web server. My setup is:
- PHP7
- JQuery
- MYSQL
Create the Database and Tables
The step is the creation of the database and the relevant tables.
Since I am on Cloudways PHP Hosting, I already have a database created for my project. If this is not the case with you, use phpMyAdmin to create the database.
Next, I will create a table named posts_db through the following query:
CREATE TABLE IF NOT EXISTS `posts` (
`id` int(8) NOT NULL,
`post_title` varchar(255) NOT NULL,
`description` text NOT NULL,
`post_at` date DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
Setup Database Connection
To create the database connection, create a file named dbcontroller.php. Paste the following code into it:
class DBController {
private $conn = "";
private $host = "localhost";
private $user = "root";
private $password = "";
private $database = "blog_samples";
function __construct() {
$conn = $this->connectDB();
if(!empty($conn)) {
$this->conn = $conn;
}
}
function connectDB() {
$conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
return $conn;
}
}Insert, Update, and Delete Functions
In the DBController class, there is a connectDB function. This is the perfect place for adding the functions for insert, update and delete functions. Here is the code for these functions:
function runSelectQuery($query) {
$result = mysqli_query($this->conn,$query);
while($row=mysqli_fetch_assoc($result)) {
$resultset[] = $row;
}
if(!empty($resultset))
return $resultset;
}
function executeInsert($query) {
$result = mysqli_query($this->conn,$query);
$insert_id = mysqli_insert_id($this->conn);
return $insert_id;
}
function executeUpdate($query) {
$result = mysqli_query($this->conn,$query);
return $result;
}
function executeQuery($sql) {
$result = mysqli_query($this->conn,$sql);
return $result;
}
function numRows($query) {
$result = mysqli_query($this->conn,$query);
$rowcount = mysqli_num_rows($result);
return $rowcount;
}
Add Information to the Database
To add data to the database, create a file named add.php and add the following code to it:
<?php
require_once("dbcontroller.php");
$db_handle = new DBController();
if(!empty($_POST["title"])) {
$title = mysql_real_escape_string(strip_tags($_POST["title"]));
$description = mysql_real_escape_string(strip_tags($_POST["description"]));
$sql = "INSERT INTO posts (post_title,description) VALUES ('" . $title . "','" . $description . "')";
$faq_id = $db_handle->executeInsert($sql);
if(!empty($faq_id)) {
$sql = "SELECT * from posts WHERE id = '$faq_id' ";
$posts = $db_handle->runSelectQuery($sql);
}
?>
<tr class="table-row" id="table-row-<?php echo $posts[0]["id"]; ?>">
<td contenteditable="true" onBlur="saveToDatabase(this,'post_title','<?php echo $posts[0]["id"]; ?>')" onClick="editRow(this);"><?php echo $posts[0]["post_title"]; ?></td>
<td contenteditable="true" onBlur="saveToDatabase(this,'description','<?php echo $posts[0]["id"]; ?>')" onClick="editRow(this);"><?php echo $posts[0]["description"]; ?></td>
<td><a class="ajax-action-links" onclick="deleteRecord(<?php echo $posts[0]["id"]; ?>);">Delete</a></td>
</tr>
<?php } ?>Edit the Data in the Database
To edit information in the database, create a file named edit.php and add the following code to it:
<?php
require_once("dbcontroller.php");
$db_handle = new DBController();
$sql = "UPDATE posts set " . $_POST["column"] . " = '".$_POST["editval"]."' WHERE id=".$_POST["id"];
$result = $db_handle->executeUpdate($sql);
?>Delete the Data in the Database
In order to delete information from the database, create a file named delete.php and add the following code to it:
<?php
require_once("dbcontroller.php");
$db_handle = new DBController();
if(!empty($_POST['id'])) {
$id = $_POST['id'];
$sql = "DELETE FROM posts WHERE id = '$id' ";
$db_handle->executeQuery($sql);}?>Bringing It All Together
It is time to create the form that will act as the view (front-end) of the app. Create the index.php and add the following code to it:
<?php
require_once("dbcontroller.php");
$db_handle = new DBController();
$sql = "SELECT * from posts";
$posts = $db_handle->runSelectQuery($sql);
?>
<table class="tbl-qa">
<thead>
<tr>
<th class="table-header">Title</th>
<th class="table-header">Description</th>
<th class="table-header">Actions</th>
</tr>
</thead>
<tbody id="table-body">
<?php
if(!empty($posts)) {
foreach($posts as $k=>$v) {
?>
<tr class="table-row" id="table-row-<?php echo $posts[$k]["id"]; ?>">
<td contenteditable="true" onBlur="saveToDatabase(this,'post_title','<?php echo $posts[$k]["id"]; ?>')" onClick="editRow(this);"><?php echo $posts[$k]["post_title"]; ?></td>
<td contenteditable="true" onBlur="saveToDatabase(this,'description','<?php echo $posts[$k]["id"]; ?>')" onClick="editRow(this);"><?php echo $posts[$k]["description"]; ?></td>
<td><a class="ajax-action-links" onclick="deleteRecord(<?php echo $posts[$k]["id"]; ?>);">Delete</a></td>
</tr>
<?php
}
}
?>
</tbody>
</table>AJAX Inline Add Functionality
I will now create the Save and Cancel inline functionality through the native append and remove jQuery functions. The following code will use these functions, save the information in the database and download the loader.gif:
<script>
function createNew() {
$("#add-more").hide();
var data = '<tr class="table-row" id="new_row_ajax">' +
'<td contenteditable="true" id="txt_title" onBlur="addToHiddenField(this,\'title\')" onClick="editRow(this);"></td>' +
'<td contenteditable="true" id="txt_description" onBlur="addToHiddenField(this,\'description\')" onClick="editRow(this);"></td>' +
'<td><input type="hidden" id="title" /><input type="hidden" id="description" /><span id="confirmAdd"><a onClick="addToDatabase()" class="ajax-action-links">Save</a> / <a onclick="cancelAdd();" class="ajax-action-links">Cancel</a></span></td>' +
'</tr>';
$("#table-body").append(data);
}
function cancelAdd() {
$("#add-more").show();
$("#new_row_ajax").remove();
}
function addToDatabase() {
var title = $("#title").val();
var description = $("#description").val();
$("#confirmAdd").html('<img src="loaderIcon.gif" />');
$.ajax({
url: "add.php",
type: "POST",
data:'title='+title+'&description='+description,
success: function(data){
$("#new_row_ajax").remove();
$("#add-more").show();
$("#table-body").append(data);
}
});
}
function addToHiddenField(addColumn,hiddenField) {
var columnValue = $(addColumn).text();
$("#"+hiddenField).val(columnValue);
}
</script>
AJAX Inline Edit & Delete Functionalities
On the blur event of the editable column, create the Edit that removes the row from first the UI and then the database. Here is the code:
<script>
function saveToDatabase(editableObj,column,id) {
$(editableObj).css("background","#FFF url(loaderIcon.gif) no-repeat right");
$.ajax({
url: "edit.php",
type: "POST",
data:'column='+column+'&editval='+$(editableObj).text()+'&id='+id,
success: function(data){
$(editableObj).css("background","#FDFDFD");
}
});
}
function deleteRecord(id) {
if(confirm("Are you sure you want to delete this row?")) {
$.ajax({
url: "delete.php",
type: "POST",
data:'id='+id,
success: function(data){
$("#table-row-"+id).remove();
}
});
}
}
</script>Conclusion
CRUD operations form the backbone of any client-facing app. In many cases, these operations could be easily implemented through inline jQuery and AJAX. Let me know in the comments if you have any issues in implementing the idea.
