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.