Create a DataGrid with PHP MySQLi and jQuery EasyUI
<!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>
//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);
?>
//dbcon.php
<?php
$conn = new mysqli('localhost','root','','testingdb');
if ($conn->connect_error) {
die('Error : ('. $conn->connect_errno .') '. $conn->connect_error);
}
?>
//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);
?>
//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);
?>
//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);
?>
VIDEO
