Inline Editing using PHP MySQL and jQuery AJAX

Hi! Today we will see about ajax inline editing using php and mysql. Inline editing allows users to rapidly modify web page contents in place without the requirement of forms. Popular sites like Facebook, Twitter, etc. employ this technique so that users can change their profile information without having to navigate through separate forms. Using HTML5's 'contenteditable' on page elements will take care of inline editing. For example, you can convert a plain html table into an editable table by setting 'contenteditable = true'.

If you are new to inline editing, take a look at html5 editable table tutorial.

PHP MySQL Inline Editing using jQuery AJAX:

The 'contenteditable' attribute can be used on almost all html elements, but the data edited by user is only temporary. It will be lost when the page is refreshed. Therefore, you must use ajax to send the data back to the server and store it in database for future use.

Here, let's see a demo with editable html table listing records from mysql table. Each time the user makes some changes, the edited data will be sent to the server through an ajax call and will be updated in the database using php.

During editing, the table cell will change to red background and to green when completed.

We'll need the following files for our demo,

1. Bootstrap.css - To design the user interface
2. jQuery.js - To post data to server via ajax
3. dbconnect.php - Takes care of database connection
4. index.php - Main user interface that contains html table, css and javascript functions.
5. savecustomer.php - PHP script to update database table.

STEP-1) Create Database

First we need a mysql database and table to use in our example. Run this below sql file on mysql. It will create a database, a table and add some sample records in it.

CREATE DATABASE `mysite`;
USE `mysite`;

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(60) NOT NULL,
  `location` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO `customers` (`id`, `name`, `email`, `location`) VALUES
(1, 'Jim Connor', 'jimconnor@yahoo.com', 'Las Vegas'),
(2, 'Mark Higgins', 'mark.higgins21@yahoo.com', 'San Francisco'),
(3, 'Austin Joseph', 'austin.joseph.boston@gmail.com', 'Boston'),
(4, 'Sean Kennedy', 'seankennedy01@gmail.com', 'Seattle'),
(5, 'Rose Harris', 'roseharris@gmail.com', 'New York'),
(6, 'Lilly Whites', 'lillywhites@outlook.com', 'New York'),
(7, 'Jennifer Winters', 'jennie.winters001@gmail.com', 'Miami'),
(8, 'Michael Bruce', 'michaelbruce78@yahoo.com', 'Los Angeles'),
(9, 'John Alex', 'johnalex@example.com', 'Chicago'),
(10, 'Demi Milan', 'demimilan@gmail.com', 'Austin');

STEP-2) Open Database Connection

Next, we must establish the connection to database. The following script will open a php-mysql connection.

dbconnect.php

<?php
// connection settings
$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'mysite';

//connect to mysql database
$con = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($con));
?>

STEP-3) Fetch Records from MySQL Table

Next fetch records from the mysql table we have created in step-1.

<?php
include_once 'dbconnect.php';

// fetch records
$sql = "select * from customers order by id";
$result = mysqli_query($con, $sql);
?>

STEP-4) Create HTML5 Editable Table

Then create an html5 table with inline editing enabled. And loop through the mysql resultset and display the records on the table one by one.

<div id="mytable">
    <h3 class="text-center bg-primary">AJAX Inline Editing HTML5 Table - Demo</h3>
    <table class="table table-bordered">
        <tr class="bg-primary">
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Location</th>
        </tr>
        <?php
        while($row = mysqli_fetch_array($result)) { ?>
        <tr>
            <td><?php echo $row['id']; ?></td>
            <td contenteditable="true" onfocus="changeBackground(this);" onblur="saveData(this, '<?php echo $row["id"]; ?>', 'name');"><?php echo $row['name']; ?></td>
            <td contenteditable="true" onfocus="changeBackground(this);" onblur="saveData(this, '<?php echo $row["id"]; ?>', 'email');"><?php echo $row['email']; ?></td>
            <td contenteditable="true" onfocus="changeBackground(this);" onblur="saveData(this, '<?php echo $row["id"]; ?>', 'location');"><?php echo $row['location']; ?></td>
        </tr>
        <?php } ?>
    </table>
</div>

In the above markup, we have added two event functions for each cell in the table. One is to change the background color on focus and the other is to submit edited cell data to the server on blur event.

STEP-5) Change Table Cell Background On Focus

Now it's time to add javascript functions. First let's create the changeBackground() method which will change the background color of the cell to red when it gets focus.

function changeBackground(obj) {
    $(obj).removeClass("bg-success");
    $(obj).addClass("bg-danger");
}

STEP-6) Make AJAX Call to Server

Next is the ajax function saveData(). This function will post the edited data, record id and column name to the server as a json string. Once the database update is complete, the background color of the <td> element will change to green.

function saveData(obj, id, column) {
    var customer = {
        id: id,
        column: column,
        value: obj.innerHTML
    }
    $.ajax({
        type: "POST",
        url: "savecustomer.php",
        data: customer,
        dataType: 'json',
        success: function(data){
            if (data) {
                $(obj).removeClass("bg-danger");
                $(obj).addClass("bg-success");
            }
        }
   });
}

Following is the complete script for the index.php file.

index.php

<?php
include_once 'dbconnect.php';
// fetch records
$sql = "select * from customers order by id";
$result = mysqli_query($con, $sql);
?>

<!DOCTYPE html>
<html>
<head>
    <title>Inline Editing in PHP MySQL</title>
    <meta charset="utf-8"> 
    <meta content="width=device-width, initial-scale=1.0" name="viewport" >
    <link rel="stylesheet" href="css/bootstrap.css" type="text/css" />
    <style type="text/css">
    #mytable {
        margin: 0 auto;
        width: 60%;
    }
    </style>
</head>
<body>
<br/>
    <div id="mytable">
        <h3 class="text-center bg-primary">AJAX Inline Editing HTML5 Table - Demo</h3>
        <table class="table table-bordered">
            <tr class="bg-primary">
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Location</th>
            </tr>
            <?php
            while($row = mysqli_fetch_array($result)) { ?>
            <tr>
                <td><?php echo $row['id']; ?></td>
                <td contenteditable="true" onfocus="changeBackground(this);" onblur="saveData(this, '<?php echo $row["id"]; ?>', 'name');"><?php echo $row['name']; ?></td>
                <td contenteditable="true" onfocus="changeBackground(this);" onblur="saveData(this, '<?php echo $row["id"]; ?>', 'email');"><?php echo $row['email']; ?></td>
                <td contenteditable="true" onfocus="changeBackground(this);" onblur="saveData(this, '<?php echo $row["id"]; ?>', 'location');"><?php echo $row['location']; ?></td>
            </tr>
            <?php } ?>
        </table>
    </div>
    <script src="js/jquery-1.10.2.js"></script>
    <script type="text/javascript">
    function changeBackground(obj) {
        $(obj).removeClass("bg-success");
        $(obj).addClass("bg-danger");
    }

    function saveData(obj, id, column) {
        var customer = {
            id: id,
            column: column,
            value: obj.innerHTML
        }
        $.ajax({
            type: "POST",
            url: "savecustomer.php",
            data: customer,
            dataType: 'json',
            success: function(data){
                if (data) {
                    $(obj).removeClass("bg-danger");
                    $(obj).addClass("bg-success");
                }
            }
       });
    }
    </script>
</body>
</html>

STEP-7) Save Edited Data into MySQL Database

Finally, we need to store the data edited by the user on the database. Here is the php code to do it.

savecustomer.php

<?php
include_once "dbconnect.php";

$sql = "update customers set " . $_POST["column"] . "='" . $_POST["value"] . "' where id=" . $_POST["id"];
if (mysqli_query($con, $sql))
    echo "true";
else
    echo "false";
?>

Done! Now we have all the necessary files in place. Run index.php and you will see a nice table that lists the records from the database. Edit some table cells and the cell color will change to red during editing.

Once you finish editing, the table cell turns green as soon as it lost focus.

Refresh the page and the data you have modified will remain there. It means that we have saved the edited data on the database successfully.

Read Also:

• How to Create URL Shortener using PHP and Google API
• How to Send SMS in PHP - SMS Gateway Integration

That explains about inline editing using php, mysql and ajax. Inline editing will enhance user experience and also save you from creating multiple web forms. You can apply the technique with any html element. I hope you like this tutorial. Please don't forget to share it on social media. Good day!!!