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

?>

13 comments:

  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
    Replies
    1. hallo sir..i have same problems,do you already have the solution?

      Delete
    2. in order to do that you have to remove the search part I guess. not sure

      Delete
  2. 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

    ReplyDelete
  3. 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

    ReplyDelete
  4. Hi, excelent lesson. Can you tell me how can I fill the select column with data from mysql table?

    ReplyDelete
  5. My table data doesn't show up from the table, anyone can help me?

    ReplyDelete
  6. 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;

    ReplyDelete
  7. It can use for input data-type date?
    and How?

    ReplyDelete
  8. How do I add and delete data in the existing table? Please help me.

    ReplyDelete
  9. 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.

    ReplyDelete
  10. 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';
    }
    }
    });

    ReplyDelete
  11. Thanks so much for this nice code ...

    ReplyDelete