- Editable HTML Table with jQuery, PHP & MySQL
- Webslesson
- PHP, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial
- Sunday, 14 February 2016
- Live Table Add Edit Delete using Ajax Jquery in PHP Mysql
- Latest Post — Live Add Edit Delete Datatables Records using PHP Ajax
- Complete Source Code
- index.php
- select.php
- insert.php
- edit.php
- delete.php
Editable HTML Table with jQuery, PHP & MySQL
In our previous tutorial, we have explained how to implement Event Calendar with jQuery , PHP and MySQL. In this tutorial, you will learn how to implement Editable HTML Table with jQuery, PHP and MySQL.
Live Editable HTML Table or Inline HTML Table Edit is a feature of web applications to allow users to edit HTML table column value. This is very user friendly feature as makes possible to edit save particular value with reloading page.
So if you’re looking for implementing editable HTML table, then it’s very easy using jQuery TableEdit plugin. In this tutorial you will learn how to implement Editable HTML Table with jQuery, PHP and MYSQL. You would also like to checkout HTML Table Data Export to Excel with jQuery tutorial to export html table data to excel format.
We will use jQuery TableEdit plugin to make HTML table editable and handle edit save when editing done.
So let’s create example to implement inline editable HTML table with jQuery, PHP and MySQL. The file structure of this example is following.
Step1: Create MySQL Database Table
First we will create MySQL database table developers to store employee records.
CREATE TABLE `developers` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `skills` varchar(255) NOT NULL, `address` varchar(255) NOT NULL, `gender` varchar(255) NOT NULL, `designation` varchar(255) NOT NULL, `age` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
We will insert few records into developers table to display in HTML table.
INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`) VALUES (1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34), (2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28), (3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30), (4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25), (5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35), (6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28), (7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26), (8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28), (9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23), (10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28), (11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38), (12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30);
Step2: Inlcude jQuery, Bootstrap and Table Edit jQuery Plugin
As we will implement HTML Table Edit with Bootstrap and jQuery, so in index.php file, we will include files related to jQuery, Bootstrap and jQuery TableEdit Plugin.
Step3: Create Bootstrap HTML and Display Records
Now in .index.php file, we will create Bootstrap HTML table and display records from MySQL database table developers with PHP.
| Id | Name | Gender | Age | Address |
|---|---|---|---|---|
Step4: Implement HTML Table Editable
In editable.js file, we will implement HTML table editable using jQuery Tabledit plugin. We will configure HTML table columns to handle column edit and save. We will also make Ajax request to save_edit.php to save changes to database when table field edited and press Enter key.
$(document).ready(function()< $('#employeeTable').Tabledit(< deleteButton: false, editButton: false, columns: < identifier: [0, 'id'], editable: [[1, 'name'], [2, 'gender'], [3, 'age'], [4, 'address']] >, hideIdentifier: true, url: 'save_edit.php' >); >); Step5: Save Editable Table Changes to Database
Now finally in save_edit.php file, we will handle functionality to save edited column value to database table.
else if(isset($input['gender'])) < $updateField.= "gender='".$input['gender']."'"; >else if(isset($input['address'])) < $updateField.= "address='".$input['address']."'"; >else if(isset($input['age'])) < $updateField.= "age='".$input['age']."'"; >if($updateField && $input['id']) < $sqlQuery = "UPDATE developers SET $updateField WHERE id'] . "'"; mysqli_query($conn, $sqlQuery) or die("database error:". mysqli_error($conn)); >> ?> You may also like:
- Follow and Unfollow System with PHP & MySQL
- GST Billing System with PHP & MySQL
- Restaurant Management System with PHP & MySQL
- Visitor Management System with PHP & MySQL
- Student Attendance System with PHP & MySQL
- Like and Dislike System with Ajax, PHP & MySQL
- Build Star Rating System with jQuery, PHP & MySQL
- User Registration and Login System with PHP & MySQL
- Build Contact Form with PHP and Ajax
- To-do List Application with Ajax, PHP & MySQL
- Build Event Calendar with jQuery , PHP & MySQL
You can view the live demo from the Demo link and can download the source from the Download link below.
Demo Download
Webslesson
PHP, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial
Sunday, 14 February 2016
Live Table Add Edit Delete using Ajax Jquery in PHP Mysql
Latest Post — Live Add Edit Delete Datatables Records using PHP Ajax
Hello Friends, In this tutorial we are going to learn how to live table Insert, Update, Delete and Fetch data from mysql database using Ajax Jquery in PHP programming language. For this feature I am using html5 contenteditable attribute. With the help of this contenteditable attribute table column will be editable and user can change live table data on single click. I am using Ajax call function for Insert data, update data, delete data and Select data from database. This all things are done on user end without page refresh. Ajax get request from user on front-end and send request to database using php language and done operation at back end and send back request to user without page refresh. This feature is very helpful. I hope this tutorial will help to all.
This is main page on which we have load data, so on this page first we have define one div tag with attribute «id=live_data», we will load data under this tag by using Ajax Jquery code and it will use attribute id as selector in Jquery code.
function fetch_data() < $.ajax(< url:"select.php", method:"POST", success:function(data)< $('#live_data').html(data); > >); >
Then after make this jquery function, which fetch data from table and converted in table format and then after display under div tag with attribute «id=live_data», under this function, it use Ajax method for fetch data from server and display on web page. This function send request to this select.php page.
$connect = mysqli_connect("localhost", "root", "", "test_db"); $output = ''; $sql = "SELECT * FROM tbl_sample ORDER BY id DESC"; $result = mysqli_query($connect, $sql); $output .= 'Id First Name Last Name Delete '; if(mysqli_num_rows($result) > 0) < while($row = mysqli_fetch_array($result)) < $output .= ''. $row["id"].' .$row["id"].'" contenteditable>'.$row["first_name"].' .$row["id"].'" contenteditable>'.$row["last_name"].' '; > $output .= ' '; > else < $output .= ' Data not Found '; > $output .= ' '; echo $output; ?>
This php code write on select.php page, because fetch_data() jquery function send request to this page, on this page it will fetch data from table and then convert that data into HTML Table format and send back to fetch_data() function.
Then after in both tag we have also add data attribute tag with two different name. In this tag we have store id of data, value of this data attribute we will use in jquery code while live updating of data.
So In backend our code is ready for fetching data and we have already make jquery function for load data on we page, so we have called this function, so when page load, this function will be called and it will load data on web page in HTML Table format.
$(document).on('click', '#btn_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:, dataType:"text", success:function(data) < alert(data); fetch_data(); >>) >); $connect = mysqli_connect("localhost", "root", "", "test_db"); $sql = "INSERT INTO tbl_sample(first_name, last_name) VALUES('".$_POST["first_name"]."', '".$_POST["last_name"]."')"; if(mysqli_query($connect, $sql)) < echo 'Data Inserted'; > ?> This is php code written on insert.php page, This page will received data from Ajax request and on this page it will make Insert Query for Inserting or Adding data into Mysql Table and it will send respond to Ajax request by write echo statement.
function edit_data(id, text, column_name) < $.ajax(< url:"edit.php", method:"POST", data:, dataType:"text", success:function(data) < alert(data); >>); >
After Successfully Live Insert or Add data, now we want to edit data, so in Jquery code we have make this edit_data(id, text, column_name) function with three argument. Value of All argument data has been send to edit.php page.
$connect = mysqli_connect("localhost", "root", "", "test_db"); $id = $_POST["id"]; $text = $_POST["text"]; $column_name = $_POST["column_name"]; $sql = "UPDATE tbl_sample SET ".$column_name."='".$text."' WHERE "; if(mysqli_query($connect, $sql)) < echo 'Data Updated'; > ?> Above code is written under edit.php page, and this page will received data from Ajax request and then after it will make Update query and execute query and update particular id of data in Mysql Table.
$(document).on('blur', '.first_name', function()< var var first_name = $(this).text(); edit_data(id, first_name, "first_name"); >); $(document).on('blur', '.last_name', function()< var var last_name = $(this).text(); edit_data(id,last_name, "last_name"); >); $(document).on('click', '.btn_delete', function()< var if(confirm("Are you sure you want to delete this?")) < $.ajax(< url:"delete.php", method:"POST", data:, dataType:"text", success:function(data) < alert(data); fetch_data(); >>); > >); Above JQuery code is write for Live Delete or Remove Mysql table data. We have write JQuery code on button with , we have use this class as selector in this Jquery code, so When we have click on delete button then this code will execute. Under this first we have get id from button attribute data-id3. In which we have store unique id. Then after it has send Ajax request to delete.php page. With Ajax request it has been send value of id variable to delete.php page. In Ajax request it will received respond from server and then after it has called fetch_data() functio for refresh table table on web page.
$connect = mysqli_connect("localhost", "root", "", "test_db"); $sql = "DELETE FROM tbl_sample WHERE "; if(mysqli_query($connect, $sql)) < echo 'Data Deleted'; > ?> Above PHP Code is written on delete.php page. This page has been received data from Ajax request and then after it will delete query and remove or delete data from Mysql Table and send respond to Ajax method.
So this is my sample code for making system like Live table Insert Update Delete and Fetch of Mysql Table data by using PHP Script with Ajax JQuery method. This is a single page web application. You can perform all operation on single page without going to another page. If you have any query or inputs, just comment your query or inputs in comment box. We will help you.
Complete Source Code
index.php
Live Table Data Edit rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" /> src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"> src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"> class="container"> /> /> /> class="table-responsive"> align="center">Live Table Add Edit Delete using Ajax Jquery in PHP Mysql /> id="live_data">