How to Make Editable DataTable in PHP using X-Editable Plugin

Hey Guys, Are you looking for solution of Creating Live Inline Editing in jQuery DataTable with PHP Server-side processing using Ajax, then you have come on the best place because we have make this tutorial in which we have use X-Editable jquery plugin with DataTable for converting simple DataTable into Live editable DataTable, so we can update any cell data on web page without going into another page. In this post, you can find how can we implement jQuery X-editable plugin with jQuery DataTable plugin with PHP Server-side processing for making live inline editing DataTable data without refreshing of web page. Below you can find simple description of both plugin.

jQuery DataTable Plugin

jQuery DataTable plugin is a javascript library for converting simple HTML table into very advance level using different feature like pagination, Instant Search, multi column ordering or sorting etc. So by using this jQuery DataTable plugin we can load data in tabular format. This plugin is also work with bootstrap library so by using this plugin we can also get the benifits of Bootstrap library and it is very easy to integrate in our new project or exisiting project. And by using jQuery plugin we can easily perform client side processing of data and server side processing of data without refresh of web page.

jQuery X-Editable Plugin

By using jQuery X-Editable plugin we can create editable elements on web page. So by using this plugin we can convert simple HTML table into Inline Live HTML table that means we can edit the content of HTML table cells. This plugin is compatible with Bootstrap library, jQuery UI library or even you can also use with simple jQuery also without using Bootstrap or jQuery UI library. And by using this library we can edit table cells data with both Popup and Inline modes also. By using this plugin we can edit table cells data and it will send ajax request to server for perform update database operation.

View Demo

Now we want to use both plugin with each other and we want to convert simple DataTable into inline editable DataTable using X-editable plugin and we want to also perform update data operation also and convert simple DataTable into Inline live table cells editable DataTable with update database operation. Below you can find complete source code for How to make editable DataTable in PHP using X-Editable jQuery plugin.

Source Code

database_connection.php


<?php

//database_connection.php

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

?>

index.php


<html>
	<head>
		<title>In-Place Editing in DataTable with X-Editable using PHP Ajax</title>
		<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
		<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
		<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
		<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
		<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
		<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
		<link href="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/css/bootstrap-editable.css" rel="stylesheet">
  		<script src="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/js/bootstrap-editable.js"></script>
	</head>
	<body>
		<div class="container">
			<h3 align="center">In-Place Editing in DataTable with X-Editable using PHP Ajax</h3>
			<br />
			<div class="panel panel-default">
				<div class="panel-heading">Sample Data</div>
				<div class="panel-body">
					<div class="table-responsive">
						<table id="sample_data" class="table table-bordered table-striped">
							<thead>
								<tr>
									<th>ID</th>
									<th>First Name</th>
									<th>Last Name</th>
									<th>Gender</th>
								</tr>
							</thead>
						</table>
					</div>
				</div>
			</div>
		</div>
		<br />
		<br />
	</body>
</html>

<script type="text/javascript" language="javascript">

$(document).ready(function(){
	var dataTable = $('#sample_data').DataTable({
		"processing": true,
		"serverSide": true,
		"order":[],
		"ajax":{
			url:"fetch.php",
			type:"POST",
		},
		createdRow:function(row, data, rowIndex)
		{
			$.each($('td', row), function(colIndex){
				if(colIndex == 1)
				{
					$(this).attr('data-name', 'first_name');
					$(this).attr('class', 'first_name');
					$(this).attr('data-type', 'text');
					$(this).attr('data-pk', data[0]);
				}
				if(colIndex == 2)
				{
					$(this).attr('data-name', 'last_name');
					$(this).attr('class', 'last_name');
					$(this).attr('data-type', 'text');
					$(this).attr('data-pk', data[0]);
				}
				if(colIndex == 3)
				{
					$(this).attr('data-name', 'gender');
					$(this).attr('class', 'gender');
					$(this).attr('data-type', 'select');
					$(this).attr('data-pk', data[0]);
				}
			});
		}
	});

	$('#sample_data').editable({
		container:'body',
		selector:'td.first_name',
		url:'update.php',
		title:'First Name',
		type:'POST',
		validate:function(value){
			if($.trim(value) == '')
			{
				return 'This field is required';
			}
		}
	});

	$('#sample_data').editable({
		container:'body',
		selector:'td.last_name',
		url:'update.php',
		title:'Last Name',
		type:'POST',
		validate:function(value){
			if($.trim(value) == '')
			{
				return 'This field is required';
			}
		}
	});

	$('#sample_data').editable({
		container:'body',
		selector:'td.gender',
		url:'update.php',
		title:'Gender',
		type:'POST',
		datatype:'json',
		source:[{value: "Male", text: "Male"}, {value: "Female", text: "Female"}],
		validate:function(value){
			if($.trim(value) == '')
			{
				return 'This field is required';
			}
		}
	});
});	
</script>

fetch.php


<?php

//fetch.php

include('database_connection.php');

$column = array("id", "first_name", "last_name", "gender");

$query = "SELECT * FROM tbl_sample ";

if(isset($_POST["search"]["value"]))
{
	$query .= '
	WHERE first_name LIKE "%'.$_POST["search"]["value"].'%" 
	OR last_name LIKE "%'.$_POST["search"]["value"].'%" 
	OR gender LIKE "%'.$_POST["search"]["value"].'%" 
	';
}

if(isset($_POST["order"]))
{
	$query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
	$query .= 'ORDER BY id DESC ';
}

$query1 = '';

if($_POST["length"] != -1)
{
	$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connect->prepare($query);

$statement->execute();

$number_filter_row = $statement->rowCount();

$result = $connect->query($query . $query1);

$data = array();

foreach($result as $row)
{
	$sub_array = array();
	$sub_array[] = $row['id'];
	$sub_array[] = $row['first_name'];
	$sub_array[] = $row['last_name'];
	$sub_array[] = $row['gender'];
	$data[] = $sub_array;
}

function count_all_data($connect)
{
	$query = "SELECT * FROM tbl_sample";

	$statement = $connect->prepare($query);

	$statement->execute();

	return $statement->rowCount();
}

$output = array(
	'draw'		=>	intval($_POST['draw']),
	'recordsTotal'	=>	count_all_data($connect),
	'recordsFiltered'	=>	$number_filter_row,
	'data'		=>	$data
);

echo json_encode($output);

?>

update.php


<?php

//update.php

include('database_connection.php');

$query = "
UPDATE tbl_sample 
SET ".$_POST["name"]." = '".$_POST["value"]."' 
WHERE id = '".$_POST["pk"]."'
";

$connect->query($query);

?>

13 comments:

Vishal Surase8 October 2020 at 04:58
I want to use WHERE clause in this table?, I tried to use but it's not working.
please let me know. thnks
Unknown31 October 2020 at 19:07
Is there other apps to install to get this to work loaded it in my server and it will not start just showing blank screen
Jose Lujan11 December 2020 at 20:46
for those interested in SQL Server with rowCount () problem. take into account the microsoft documentation:

https://docs.microsoft.com/en-us/sql/connect/php/pdostatement-rowcount?redirectedfrom=MSDN&view=sql-server-ver15

Regards,
jlujan
Unknown29 January 2021 at 05:36
Hi, excelent lesson. Can you tell me how can I fill the select column with data from mysql table?
Harris15 February 2021 at 23:37
My table data doesn't show up from the table, anyone can help me?
endorfinas.com25 February 2021 at 05:10
there are dump table:
--
-- Database: `datatable`
--

-- --------------------------------------------------------

--
-- Table structure for table `info`
--

CREATE TABLE `info` (
`id` int(11) NOT NULL,
`first_name` varchar(250) NOT NULL,
`last_name` varchar(255) NOT NULL,
`gender` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `info`
--

INSERT INTO `info` (`id`, `first_name`, `last_name`, `gender`) VALUES
(3805, 'prabhu', 'toy', 'male');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `info`
--
ALTER TABLE `info`
ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `info`
--
ALTER TABLE `info`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3806;
COMMIT;
Unknown10 March 2021 at 22:49
It can use for input data-type date?
and How?
Harris25 March 2021 at 00:35
How do I add and delete data in the existing table? Please help me.
Rick Medlin9 June 2021 at 14:58
Great tutorial. I would like to re-create the same interface on a tabbed control. So far I haven't been able to adapt this code, but any input would be welcome.
Unknown21 June 2021 at 07:41
POST values are not sent to update.php. The three POST parameters ($pk=$_POST['pk'];
$val = $_POST['val']; $name =$_POST['name'];) are correct as I can see them in the inspection console
$('#sample_data').editable({
container:'body',
selector:'td.PartNumber',
url:'test.php',
title:'Part Number',
type:'POST',
url: function(params) {
console.log(params);
},
validate:function(value){
if($.trim(value) == '')
{
return 'This field is required';
}
}
});
Unknown20 January 2022 at 15:17
Thanks so much for this nice code ...

Webslesson

PHP, Node.js, React.js, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial

Friday, 2 October 2020