article

Saturday, November 7, 2020

DataTable Inline Editing using PHP Mysqli jquery ajax and X-Editable

DataTable InlineEditing using PHP Mysqli jquery ajax and X-Editable 

DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, built upon the foundations of progressive enhancement, that adds all of these advanced features to any HTML table.

https://datatables.net/

X-editable
This library allows you to create editable elements on your page. It can be used with any engine (bootstrap, jquery-ui, jquery only) and includes both popup and inline modes.

https://vitalets.github.io/x-editable/


--
-- Table structure for table `employee`
--

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`id`, `name`, `email`, `phone`) VALUES
(1, 'Caite Ednalan', 'caite@gmail.com', '35465465'),
(2, 'Mark Oto', 'marokoto@gmail.com', '123123123'),
(3, 'Jacob thompson', 'jacobthomson@gmail.com', '13123123'),
(4, 'cylde Ednalan', 'cyledednalan@gmail.com', '313123'),
(5, 'Angelica Ramos', 'AngelicaRamos@gmail.com', '21654654654999999'),
(6, 'Airi Satou', 'AiriSatou@gmail.com', '354646'),
(8, 'Tiger Nixon', 'TigerNixon@gmail.com', '546456'),
(9, 'Airi Satou', 'AiriSatou@gmail.com', '354656'),
(10, 'Angelica Ramos', 'Caite@gmail.com', '465465465465'),
(11, 'Ashton Cox', 'marokoto@gmail.com', '123123123'),
(12, 'Bradley Greer', 'jacobthomson@gmail.com', '13123123'),
(13, 'Brenden Wagner', 'cyledednalan@gmail.com', '313123'),
(14, 'Brielle Williamson', 'AngelicaRamos@gmail.com', '21654654654999999'),
(15, 'Bruno Nash', 'AiriSatou@gmail.com', '354646');

editable_datatable.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
//editable_datatable.php
<html>
  <head>
    <meta name="viewport" content="width=device-width">
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>DataTable Inline Editing using PHP Mysqli jquery ajax and X-Editable</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />       
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
 
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.min.css" />
<script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.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>
 
<script type="text/javascript" language="javascript">
$(document).ready(function(){
    var dataTable = $('#sample_data').DataTable({
        "ajax":{
            url:"fetchrec.php",
            type:"POST",
        },
        createdRow:function(row, data, rowIndex)
        {
            $.each($('td', row), function(colIndex){
                if(colIndex == 1)
                {
                    $(this).attr('data-name', 'name');
                    $(this).attr('class', 'name');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }
                if(colIndex == 2)
                {
                    $(this).attr('data-name', 'email');
                    $(this).attr('class', 'email');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }
                if(colIndex == 3)
                {
                    $(this).attr('data-name', 'phone');
                    $(this).attr('class', 'phone');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }
            });
        }
    });
 
    $('#sample_data').editable({
        container:'body',
        selector:'td.name',
        url:'update_rec.php',
        title:'Name',
        type:'POST',
        validate:function(value){
            if($.trim(value) == '')
            {
                return 'This field is required';
            }
        }
    });
 
    $('#sample_data').editable({
        container:'body',
        selector:'td.email',
        url:'update_rec.php',
        title:'Email',
        type:'POST',
        validate:function(value){
            if($.trim(value) == '')
            {
                return 'This field is required';
            }
        }
    });
 
    $('#sample_data').editable({
        container:'body',
        selector:'td.phone',
        url:'update_rec.php',
        title:'Phone',
        type:'POST',
        validate:function(value){
            if($.trim(value) == '')
            {
                return 'This field is required';
            }
        }
    });
});
</script>
    </head>
    <body>
        <div class="container">
            <h3 align="center">DataTable Inline Editing using PHP Mysqli jquery ajax and X-Editable</h3>
            <br />
            <div class="panel panel-default">
                <div class="panel-heading">DataTable</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>Name</th>
                                    <th>Email</th>
                                    <th>Phone</th>
                                </tr>
                            </thead>
                        </table>
                    </div>
                </div>
            </div>
        </div>
        <br />
        <br />
    </body>
</html>
dbcon.php
?
1
2
3
4
5
6
7
//dbcon.php
<?php
$conn = new mysqli('localhost','root','','testingdb');
if ($conn->connect_error) {
    die('Error : ('. $conn->connect_errno .') '. $conn->connect_error);
}
?>
fetchrec.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
//fetchrec.php
<?php
include"dbcon.php";
$result = $conn->query("SELECT * FROM employee");
$customers = $result->fetch_all(MYSQLI_ASSOC);
$data = array();
foreach($result as $row)
{
    $sub_array = array();
    $sub_array[] = $row['id'];
    $sub_array[] = $row['name'];
    $sub_array[] = $row['email'];
    $sub_array[] = $row['phone'];
    $data[] = $sub_array;
}
$output = array(
    'data'      =>   $data
);
echo json_encode($output);
?>
update_rec.php
?
1
2
3
4
5
6
//update_rec.php
<?php
include"dbcon.php";
$sql = "UPDATE employee SET ".$_POST["name"]." = '".$_POST["value"]."' WHERE id = '".$_POST["pk"]."'";
$update = $conn->query($sql);
?>

Posted by ednalan at 3:59 PM . Under , .

Related Post