DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Better Scaffolding with jQuery - Part I
  • How to Call Rest API by Using jQuery AJAX in Spring Boot? [Video]
  • Open Source: A Pathway To Personal and Professional Growth
  • Enhancing Software Quality with Checkstyle and PMD: A Practical Guide

Trending

  • AI, ML, and Data Science: Shaping the Future of Automation
  • Java's Quiet Revolution: Thriving in the Serverless Kubernetes Era
  • Building Scalable and Resilient Data Pipelines With Apache Airflow
  • Microsoft Azure Synapse Analytics: Scaling Hurdles and Limitations
  1. DZone
  2. Coding
  3. Frameworks
  4. Create Inline CRUD Using jQuery and AJAX

Create Inline CRUD Using jQuery and AJAX

Read this article in order to view a tutorial on how to create inline CRUD using jQuery and AJAX.

By 
Pardeep Kumar user avatar
Pardeep Kumar
·
May. 26, 18 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
25.6K Views

Join the DZone community and get the full member experience.

Join For Free

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.

AJAX Database connection JQuery code style

Opinions expressed by DZone contributors are their own.

Related

  • Better Scaffolding with jQuery - Part I
  • How to Call Rest API by Using jQuery AJAX in Spring Boot? [Video]
  • Open Source: A Pathway To Personal and Professional Growth
  • Enhancing Software Quality with Checkstyle and PMD: A Practical Guide

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!