PHP
JavaScript
Angular AngularJS jQuery Ajax
Linux
.htaccess

May 16, 2016

Using Ajax, jQuery Live Add | Edit | Delete MySQL records using PHP

4154

Hi, Friends In this tutorial we are going to learn how to save, update, display, delete the records live by using Ajax, jQuery in PHP MySQL database. Now we going to use HTML5 “contenteditable” attribute. With the help of this “contenteditable” attribute, table column will be editable and the user can edit live table data on textbox by the single click. I am using Ajax call function for save record, update record, delete the record and fetch the data from MySQL database. These data are edit on user end without page refresh. Ajax gets the request from a user data on the webpage and sends a request to the MySQL database using PHP we work at the back-end and send a request to the user without page refresh.

Ajax jQuery Live Save | Update | Delete The Data In PHP Mysqli (PART – 1)

Ajax jQuery Live Save | Update | Delete The Data In PHP Mysqli (PART – 2)

index.php

<html>

<head>
    <title>Live Table Data Edit</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
</head>

<body>
    <div class="container">
        <h1 align="center">Ajax jQuery Live Add | Edit | Delete MySQL records using PHP</h1>
        <br />
        <div id="disp_data"></div>
    </div>
</body>

</html>
<script>
    $(document).ready(function() {
        function fetch_data() {
            $.ajax({
                url: "select.php",
                method: "POST",
                success: function(data) {
                    $('#disp_data').html(data);
                }
            });
        }
        fetch_data();
        $(document).on('click', '#add', function() {
            var first_name = $('#first_name').text();
            var last_name = $('#last_name').text();
            if (first_name == '') {
                alert("Enter First Name");
                return false;
            }
            if (last_name == '') {
                alert("Enter Last Name");
                return false;
            }
            $.ajax({
                url: "insert.php",
                method: "POST",
                data: {
                    first_name: first_name,
                    last_name: last_name
                },
                dataType: "text",
                success: function(data) {
                    alert(data);
                    fetch_data();
                }
            })
        });

        function edit_data(id, text, column_name) {
            $.ajax({
                url: "edit.php",
                method: "POST",
                data: {
                    id: id,
                    text: text,
                    column_name: column_name
                },
                dataType: "text",
                success: function(data) {
                    alert(data);
                }
            });
        }
        $(document).on('blur', '.first_name', function() {
            var id = $(this).data("id1");
            var first_name = $(this).text();
            edit_data(id, first_name, "first_name");
        });
        $(document).on('blur', '.last_name', function() {
            var id = $(this).data("id2");
            var last_name = $(this).text();
            edit_data(id, last_name, "last_name");
        });
        $(document).on('click', '#delete', function() {
            var id = $(this).data("id3");
            if (confirm("Are you sure you want to delete this?")) {
                $.ajax({
                    url: "delete.php",
                    method: "POST",
                    data: {
                        id: id
                    },
                    dataType: "text",
                    success: function(data) {
                        alert(data);
                        fetch_data();
                    }
                });
            }
        });
    });
</script>

<html>

<head>

<title>Live Table Data Edit</title>

</head>

<body>

<h1 align="center">Ajax jQuery Live Add | Edit | Delete MySQL records using PHP</h1>

<br />

</div>

</body>

</html>

$(document).ready(function() {

function fetch_data() {

$.ajax({

url: "select.php",

method: "POST",

success: function(data) {

$('#disp_data').html(data);

}

});

}

fetch_data();

$(document).on('click', '#add', function() {

var first_name = $('#first_name').text();

var last_name = $('#last_name').text();

if (first_name == '') {

alert("Enter First Name");

return false;

}

if (last_name == '') {

alert("Enter Last Name");

return false;

}

$.ajax({

url: "insert.php",

method: "POST",

data: {

first_name: first_name,

last_name: last_name

dataType: "text",

success: function(data) {

alert(data);

fetch_data();

}

})

});

function edit_data(id, text, column_name) {

$.ajax({

url: "edit.php",

method: "POST",

data: {

id: id,

text: text,

column_name: column_name

dataType: "text",

success: function(data) {

alert(data);

}

});

}

$(document).on('blur', '.first_name', function() {

var id = $(this).data("id1");

var first_name = $(this).text();

edit_data(id, first_name, "first_name");

});

$(document).on('blur', '.last_name', function() {

var id = $(this).data("id2");

var last_name = $(this).text();

edit_data(id, last_name, "last_name");

});

$(document).on('click', '#delete', function() {

var id = $(this).data("id3");

if (confirm("Are you sure you want to delete this?")) {

$.ajax({

url: "delete.php",

method: "POST",

data: {

id: id

dataType: "text",

success: function(data) {

alert(data);

fetch_data();

}

});

}

});

</script>

select.php

<?php 
 $conn = mysqli_connect("localhost", "root", "", "test"); 
 $output = ''; 
 $sql = "SELECT * FROM empinfo ORDER BY id DESC"; 
 $result = mysqli_query($conn, $sql); 
 $output .= ' 
 <div align="center"> 
 <table border="1" bordercolor="#00CCCC"> 
 <tr> 
 <th width="10%">Id</th> 
 <th width="40%">First Name</th> 
 <th width="40%">Last Name</th> 
 <th width="10%">Delete</th> 
 </tr>'; 
 if(mysqli_num_rows($result) > 0) 
 { 
 while($row = mysqli_fetch_array($result)) 
 { 
 $output .= ' 
 <tr> 
 <td>'.$row["id"].'</td> 
 <td class="first_name" data-id1="'.$row["id"].'" contenteditable>'.$row["first_name"].'</td> 
 <td class="last_name" data-id2="'.$row["id"].'" contenteditable>'.$row["last_name"].'</td> 
 <td><button type="button" name="delete_btn" data-id3="'.$row["id"].'" id="delete">Delete</button></td> 
 </tr> 
 '; 
 } 
 $output .= ' 
 <tr> 
 <td></td> 
 <td id="first_name" contenteditable></td> 
 <td id="last_name" contenteditable></td> 
 <td><button type="button" name="add" id="add">Add</button></td> 
 </tr> 
 '; 
 } 
 else 
 { 
 $output .= '<tr> 
 <td colspan="4">Data not Found</td> 
 </tr>'; 
 } 
 $output .= '</table> 
 </div>'; 
 echo $output; 
 ?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test");

$output = '';

$sql = "SELECT * FROM empinfo ORDER BY id DESC";

$result = mysqli_query($conn, $sql);

$output .= '

<tr>

<th width="40%">First Name</th>

<th width="10%">Delete</th>

</tr>';

if(mysqli_num_rows($result) > 0)

{

while($row = mysqli_fetch_array($result))

{

$output .= '

<tr>

<td class="first_name" data-id1="'.$row["id"].'" contenteditable>'.$row["first_name"].'</td>

<td><button type="button" name="delete_btn" data-id3="'.$row["id"].'" id="delete">Delete</button></td>

</tr>

}

$output .= '

<tr>

</tr>

}

else

{

$output .= '<tr>

<td colspan="4">Data not Found</td>

</tr>';

}

$output .= '</table>

</div>';

echo $output;

insert.php

<?php 
 $conn = mysqli_connect("localhost", "root", "", "test"); 
 $sql = "INSERT INTO empinfo(first_name, last_name) VALUES('".$_POST["first_name"]."', '".$_POST["last_name"]."')"; 
 if(mysqli_query($conn, $sql)) 
 { 
 echo 'Record Inserted Successfully!'; 
 } 
 ?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test");

$sql = "INSERT INTO empinfo(first_name, last_name) VALUES('".$_POST["first_name"]."', '".$_POST["last_name"]."')";

if(mysqli_query($conn, $sql))

{

echo 'Record Inserted Successfully!';

}

edit.php

<?php 
 $conn = mysqli_connect("localhost", "root", "", "test"); 
 $id = $_POST["id"]; 
 $text = $_POST["text"]; 
 $column_name = $_POST["column_name"]; 
 $sql = "UPDATE empinfo SET ".$column_name."='".$text."' WHERE id='".$id."'"; 
 if(mysqli_query($conn, $sql)) 
 { 
 echo 'Data Updated'; 
 } 
 ?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test");

$id = $_POST["id"];

$text = $_POST["text"];

$column_name = $_POST["column_name"];

$sql = "UPDATE empinfo SET ".$column_name."='".$text."' WHERE id='".$id."'";

if(mysqli_query($conn, $sql))

{

echo 'Data Updated';

}

delete.php

<?php 
 $conn = mysqli_connect("localhost", "root", "", "test"); 
 $sql = "DELETE FROM empinfo WHERE id = '".$_POST["id"]."'"; 
 if(mysqli_query($conn, $sql)) 
 { 
 echo 'Data Deleted Successufully!'; 
 } 
 ?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test");

$sql = "DELETE FROM empinfo WHERE id = '".$_POST["id"]."'";

if(mysqli_query($conn, $sql))

{

echo 'Data Deleted Successufully!';

}

Download

Checkout Added to cart

Mraj

Creative Designer & Developer specialist by the spirit and a loving blogger by thoughts. If you have any questions let me drop an email with the article name to the following email id: [email protected]