Friday, 2 October 2020

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.




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);

?>

1 comment:

  1. I want to use WHERE clause in this table?, I tried to use but it's not working.
    please let me know. thnks

    ReplyDelete