How to Inline Edit Data in Table using PHP and jQuery

Submitted by nurhodelta_17 on Saturday, February 17, 2018 - 18:52.

This tutorial tackles how to inline edit data in a table derived from MySQL Table. This means that you are editing the table within the table itself and you don't have to be redirected to a certain edit page. We have used jQuery to make this possible and also to handle our ajax request.

Getting jQuery and Bootstrap

First, we need the jQuery library and also Bootstrap for a better design to our app. I've included these files in the downloadable of this tutorial but if you want, you can download them yourself using the links below:

Creating our Database

Next, we need to create our MySQL database to handle our data.

I've included a SQL file in the downloadable of this tutorial. If you have no idea on how to import, please visit my tutorial How import .sql file to restore MySQL database.

To create the database, table and insert sample data using the PHPMyAdmin SQL Tab. Create a new database in your PHPMyAdmin naming mydatabase. Then, copy/paste the following code in the SQL Tab.

CREATE TABLE `members` (
  `id` int(11) NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `address` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
INSERT INTO `members` (`id`, `firstname`, `lastname`, `address`) VALUES
(1, 'neovic', 'devierte', 'silay city'),
(2, 'gemalyn', 'cepe', 'carmen, bohol');
 
 
ALTER TABLE `members`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `members`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

Creating our Interface

Next, we create our table which contains data from our database by creating a new file and name it as index.html.

<!DOCTYPE html>
<html>
<head>
        <title></title>
        <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
        <script src="jquery.min.js"></script>
</head>
<body>
<div class="container">
        <h1 class="page-header text-center">How to Inline Edit using jQuery</h1>
        <div class="row">
                <div class="col-sm-8 col-sm-offset-2">
                        <div id="response" class="alert text-center" style="display:none;">
                                <button type="button" class="close" id="clearMsg"><span aria-hidden="true">&times;</span></button>
                                <span id="message"></span>
                        </div>
                        <table class="table table-bordered table-striped">
                                <thead>
                                        <tr>
                                                <th>ID</th>
                                                <th>Firstname</th>
                                                <th>Lastname</th>
                                                <th>Address</th>
                                                <th>Action</th>
                                        </tr>
                                </thead>
                                <tbody id="tbody">
                                </tbody>
                        </table>
                </div>
        </div>
</div>
<script src="app.js"></script>
</body>
</html>

Creating our jQuery Scripts

Next, we create a javascript file which contains our jQuery scripts and name it as app.js. This file is included in our index.html.

$(document).ready(function(){
        //fetch table data
        fetch();
        //clicking edit button
        $(document).on('click', '.editbutton', function(){
        var row = $(this).closest('tr');
        //hide values
        row.find('.editValue').hide();
        //show edit input
        row.find('.editInput').show();
        //show save button
        row.find('.savebutton').show();
        //hide edit button
        $(this).hide();
        });
        //save 
        $(document).on('click', '.savebutton', function(){
                var row = $(this).closest('tr');
                //hide textbox
                row.find('.editInput').hide();
                //show value
                row.find('.editValue').show();
                //show edit button
                row.find('.editbutton').show();
                //hide save button
                $(this).hide();
                var id = row.attr('id');
                var form = row.find('.editInput').serializeArray();
                form.push({ name:'id', value:id });
                $.ajax({
                        method: 'POST',
                        url: 'edit.php',
                        data: form,
                        dataType: 'json',
                        success: function(response){
                                if(response.error){
                                        $('#response').show().removeClass('alert-sucess').addClass('alert-danger');
                                        $('#message').html(response.message);
                                }
                                else{
                                        $('#response').show().removeClass('alert-danger').addClass('alert-success');
                                        $('#message').html(response.message);
 
                                        //populate table with updated row
                                        row.find('.editValue.firstname').html(response.member.firstname);
                                        row.find('.editValue.lastname').html(response.member.lastname);
                                        row.find('.editValue.address').html(response.member.address);
 
                                        row.find('.editInput.firstname').val(response.member.firstname);
                                        row.find('.editInput.lastname').val(response.member.lastname);
                                        row.find('.editInput.address').val(response.member.address);
                                }
                        }
                });
        });
        //clear msg
        $('#clearMsg').click(function(){
                $('#response').hide();
        });
});
 
function fetch(){
        $.ajax({
                method: 'GET',
                url: 'fetch.php',
                success: function(response){
                        $('#tbody').html(response);
                }
        });
}

Fetch API of the table

This PHP file contains our codes that fetch table data from our database and appended to our table view.

fetch.php

<?php
        $conn = new mysqli('localhost', 'root', '', 'mydatabase');
 
        $sql = "SELECT * FROM members";
        $query = $conn->query($sql);
        while($row = $query->fetch_array()){
                ?>
                <tr id="<?php echo $row['id']; ?>">
                        <td>
                                <?php echo $row['id']; ?>
                        </td>
                        <td>
                                <span class="editValue firstname"><?php echo $row['firstname']; ?></span>
                                <input type="text" class="form-control editInput firstname" name="firstname" value="<?php echo $row['firstname']; ?>" style="display:none;">
                        </td>
                        <td>
                                <span class="editValue lastname"><?php echo $row['lastname']; ?></span>
                                <input type="text" class="form-control editInput lastname" name="lastname" value="<?php echo $row['lastname']; ?>" style="display:none;">       
                        </td>
                        <td>
                                <span class="editValue address"><?php echo $row['address']; ?></span>
                                <input type="text" class="form-control editInput address" name="address" value="<?php echo $row['address']; ?>" style="display:none;">  
                        </td>
                        <td>
                                <button class="btn btn-primary editbutton"><span class="glyphicon glyphicon-edit"></span> Edit</button>
                                <button class="btn btn-success savebutton" style="display:none;"><span class="glyphicon glyphicon-floppy-disk"></span> Save</button>
                        </td>
                </tr>
                <?php
        }
 
        
                                
?>

Update Script

Lastly, this is our PHP code that saves the update made to our table view.

edit.php

<?php
        $conn = new mysqli('localhost', 'root', '', 'mydatabase');
 
        $output = array('error' => false);
        
        $id = $_POST['id'];
        $firstname = $_POST['firstname'];
        $lastname = $_POST['lastname'];
        $address = $_POST['address'];
 
        $sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE id = '$id'";
        $query = $conn->query($sql);
 
        if($query){
                $output['message'] = 'Member updated successfully';
                //return the updated member
                $output['member'] = array(
                        'firstname' => $firstname,
                        'lastname' => $lastname,
                        'address' => $address
                );
        }
        else{
                $output['error'] = true;
                $output['message'] = 'Cannot update member';
        }
 
        echo json_encode($output);
 
?>

That ends this tutorial. I hope this tutorial will help you with what your are looking for and you'll find this useful for your future PHP Projects.

Happy Coding :)

Add new comment

1576 views