article

Friday, December 6, 2019

Create a DataGrid with PHP MySQLi and jQuery EasyUI

Create a DataGrid with PHP MySQLi and jQuery EasyUI
?
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
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Create a DataGrid with PHP MySQLi and jQuery EasyUI</title>
    <link rel="stylesheet" type="text/css" href="https://www.jeasyui.com/easyui/themes/default/easyui.css">
    <link rel="stylesheet" type="text/css" href="https://www.jeasyui.com/easyui/themes/icon.css">
    <link rel="stylesheet" type="text/css" href="https://www.jeasyui.com/easyui/themes/color.css">
    <link rel="stylesheet" type="text/css" href="https://www.jeasyui.com/easyui/demo/demo.css">
    <script type="text/javascript" src="https://code.jquery.com/jquery-1.9.1.min.js"></script>
    <script type="text/javascript" src="https://www.jeasyui.com/easyui/jquery.easyui.min.js"></script>
</head>
<body>
    <h2>Create a DataGrid with PHP MySQLi and jQuery EasyUI</h2>
    <p>Click the buttons on datagrid toolbar to do crud actions.</p>
    <table id="dg" title="Users Management" class="easyui-datagrid" url="getData.php" toolbar="#toolbar" pagination="true" rownumbers="true" fitColumns="true" singleSelect="true" style="width:100%;height:400px;">
  <thead>
   <tr>
    <th field="first_name" width="50">First Name</th>
    <th field="last_name" width="50">Last Name</th>
    <th field="email" width="50">Email</th>
    <th field="phone" width="50">Phone</th>
   </tr>
  </thead>
 </table>
 <div id="toolbar">
  <div id="tb">
   <input id="term" placeholder="Type keywords...">
   <a href="javascript:void(0);" class="easyui-linkbutton" plain="true" onclick="doSearch()">Search</a>
  </div>
  <div id="tb2" style="">
   <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-add" plain="true" onclick="newUser()">New User</a>
   <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-edit" plain="true" onclick="editUser()">Edit User</a>
   <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-remove" plain="true" onclick="destroyUser()">Remove User</a>
  </div>
 </div>
 <div id="dlg" class="easyui-dialog" style="width:450px" data-options="closed:true,modal:true,border:'thin',buttons:'#dlg-buttons'">
  <form id="fm" method="post" novalidate style="margin:0;padding:20px 50px">
   <h3>User Information</h3>
   <div style="margin-bottom:10px">
    <input name="first_name" class="easyui-textbox" required="true" label="First Name:" style="width:100%">
   </div>
   <div style="margin-bottom:10px">
    <input name="last_name" class="easyui-textbox" required="true" label="Last Name:" style="width:100%">
   </div>
   <div style="margin-bottom:10px">
    <input name="email" class="easyui-textbox" required="true" validType="email" label="Email:" style="width:100%">
   </div>
   <div style="margin-bottom:10px">
    <input name="phone" class="easyui-textbox" required="true" label="Phone:" style="width:100%">
   </div>
  </form>
 </div>
 <div id="dlg-buttons">
  <a href="javascript:void(0);" class="easyui-linkbutton c6" iconCls="icon-ok" onclick="saveUser()" style="width:90px;">Save</a>
  <a href="javascript:void(0);" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#dlg').dialog('close');" style="width:90px;">Cancel</a>
 </div>
 <script type="text/javascript">
 function doSearch(){
  $('#dg').datagrid('load', {
   term: $('#term').val()
  });
 }
    
 var url;
 function newUser(){
  $('#dlg').dialog('open').dialog('center').dialog('setTitle','New User');
  $('#fm').form('clear');
  url = 'addData.php';
 }
 function editUser(){
  var row = $('#dg').datagrid('getSelected');
  if (row){
   $('#dlg').dialog('open').dialog('center').dialog('setTitle','Edit User');
   $('#fm').form('load',row);
   url = 'editData.php?id='+row.id;
  }
 }
 function saveUser(){
  $('#fm').form('submit',{
   url: url,
   onSubmit: function(){
    return $(this).form('validate');
   },
   success: function(response){
    var respData = $.parseJSON(response);
    if(respData.status == 0){
     $.messager.show({
      title: 'Error',
      msg: respData.msg
     });
    }else{
     $('#dlg').dialog('close');
     $('#dg').datagrid('reload');
    }
   }
  });
 }
 function destroyUser(){
  var row = $('#dg').datagrid('getSelected');
  if (row){
   $.messager.confirm('Confirm','Are you sure you want to delete this user?',function(r){
    if (r){
     $.post('deleteData.php', {id:row.id}, function(response){
      if(response.status == 1){
       $('#dg').datagrid('reload');
      }else{
       $.messager.show({
        title: 'Error',
        msg: respData.msg
       });
      }
     },'json');
    }
   });
  }
 }
 </script>
</body>
</html>
?
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
//getData.php
<?php
include"dbcon.php";
$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
  
$searchTerm = isset($_POST['term']) ? $conn->real_escape_string($_POST['term']) : '';
  
$offset = ($page-1)*$rows;
  
$result = array();
 
$whereSQL = "first_name LIKE '$searchTerm%' OR last_name LIKE '$searchTerm%' OR email LIKE '$searchTerm%' OR phone LIKE '$searchTerm%'";
$result = $conn->query("SELECT COUNT(*) FROM tbl_users WHERE $whereSQL");
$row = $result->fetch_row();
$response["total"] = $row[0];
 
$result = $conn->query( "SELECT * FROM tbl_users WHERE $whereSQL ORDER BY id DESC LIMIT $offset,$rows");
  
$users = array();
while($row = $result->fetch_assoc()){
    array_push($users, $row);
}
$response["rows"] = $users;
 
echo json_encode($response);
?>
?
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);
}
?>
?
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
//addData.php
<?php
include"dbcon.php";
$response = array(
    'status' => 0,
    'msg' => 'Some problems occurred, please try again.'
);
if(!empty($_REQUEST['first_name']) && !empty($_REQUEST['last_name']) && !empty( $_REQUEST['email']) && !empty($_REQUEST['phone'])){
    $first_name = $_REQUEST['first_name'];
    $last_name = $_REQUEST['last_name'];
    $email = $_REQUEST['email'];
    $phone = $_REQUEST['phone'];
  
    $sql = "INSERT INTO tbl_users(first_name,last_name,email,phone) VALUES ('$first_name','$last_name','$email','$phone')";
    $insert = $conn->query($sql);
  
 if($insert){
        $response['status'] = 1;
        $response['msg'] = 'User data has been added successfully!';
    }
}else{
    $response['msg'] = 'Please fill all the mandatory fields.';
}
echo json_encode($response);
?>
?
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
//editData.php
<?php
include"dbcon.php";
$response = array(
    'status' => 0,
    'msg' => 'Some problems occurred, please try again.'
);
if(!empty($_REQUEST['first_name']) && !empty($_REQUEST['last_name']) && !empty( $_REQUEST['email']) && !empty($_REQUEST['phone'])){
    $first_name = $_REQUEST['first_name'];
    $last_name = $_REQUEST['last_name'];
    $email = $_REQUEST['email'];
    $phone = $_REQUEST['phone'];
      
    if(!empty($_REQUEST['id'])){
        $id = intval($_REQUEST['id']);
          
     
        $sql = "UPDATE tbl_users SET first_name='$first_name', last_name='$last_name', email='$email', phone='$phone' WHERE id = $id";
        $update = $conn->query($sql);
          
        if($update){
            $response['status'] = 1;
            $response['msg'] = 'User data has been updated successfully!';
        }
    }
}else{
    $response['msg'] = 'Please fill all the mandatory fields.';
}
echo json_encode($response);
?>
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//deleteData.php
<?php
include"dbcon.php";
$response = array(
    'status' => 0,
    'msg' => 'Some problems occurred, please try again.'
);
if(!empty($_REQUEST['id'])){
    $id = intval($_REQUEST['id']);
      
 
    $sql = "DELETE FROM tbl_users WHERE id = $id";
    $delete = $conn->query($sql);
      
    if($delete){
        $response['status'] = 1;
        $response['msg'] = 'User data has been deleted successfully!';
    }
}
echo json_encode($response);
?>
Posted by ednalan at 7:13 PM . Under , .

Related Post