The most often used data management functionality is DataGrid with CRUD operations. When a CRUD operation is performed, the web page is usually reloaded. You may implement CRUD functionality without refreshing the page to make the web application more user-friendly. The EasyUI framework makes it simple to incorporate DataGrid into a web application’s CRUD functionality.
EasyUI is a jQuery framework that makes it simple to create modern DataGrid CRUD applications. By developing less code, the DataGrid capability may be added into the web page in less time. To make the DataGrid more powerful, the jQuery EasyUI allows you to interact with the server-side script. We’ll show you how to use EasyUI, PHP, and MySQL to create CRUD with search and pagination in this tutorial.
The following functionality will be implemented in the EasyUI integration example code.
Data from the database should be fetched and listed in a tabular format.
Without refreshing the website, you can add/edit data in the dialogue window.
Remove data from the database without refreshing the page.
To the list, add the search and pagination options.
Integration of jQuery EasyUI
The following code demonstrates how to use the jQuery EasyUI plugin to create a CRUD application and create or change user information using the dialogue component by integrating it into the web page.
1. Add the EasyUI plugin’s CSS and JavaScript files to the web page.
<link rel="stylesheet" type="text/css" href="easyui/themes/default/easyui.css">
<link rel="stylesheet" type="text/css" href="easyui/themes/icon.css">
<script type="text/javascript" src="easyui/jquery.min.js"></script>
<script type="text/javascript" src="easyui/jquery.easyui.min.js"></script>/* Your code... */For the data list table and toolbar, add HTML code.
In the url attribute of the table> tag, specify the URL of the server-side script (getData.php).
To add pagination links to the data list, use the pagination attribute and set it to TURE (pagination=”true”).
<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:350px;">
<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>
3. Add HTML code to the dialogue box for adding/editing forms.
<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>For server-side interactivity, add the JavaScript code.
Send terms to the server-side script (getData.php) and load the filtered data with doSearch().
newUser() – Displays a popup dialogue with an HTML form for entering data.
editUser() – Displays a popup dialogue with an HTML form for data editing.
saveUser() – Sends data to a server-side script (addData.php or editData.php) for database saving.
destroyUser() – Sends a request to delete data to the server-side script (deleteData.php).
<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>Processing on the server
Create a table in the database:
A table in the database is required to store the data. In the MySQL database, the following SQL creates a users table with some basic fields.
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;Database Connection (dbConnect.php):
To connect to the database, use the dbConnect.php file. According to your MySQL database credentials, specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName).
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName = "codexworld";
// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}Fetch Data (getData.php):
The getData.php file is used to retrieve information from a database using PHP and MySQL.
The records are returned in JSON format based on the search criteria and page number.
<?php
// Include the database config file
require_once 'dbConnect.php';
$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
$searchTerm = isset($_POST['term']) ? $db->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 = $db->query("SELECT COUNT(*) FROM users WHERE $whereSQL");
$row = $result->fetch_row();
$response["total"] = $row[0];
$result = $db->query( "SELECT * FROM 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);Add Data (addData.php):
Using PHP and MySQL, the addData.php file is used to insert data into the database.
<?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'];
// Include the database config file
require_once 'dbConnect.php';
$sql = "INSERT INTO users(first_name,last_name,email,phone) VALUES ('$first_name','$last_name','$email','$phone')";
$insert = $db->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); Update Data (editData.php):
The editData.php file is used to update data using PHP and MySQL based on the row ID.
<?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']);
// Include the database config file
require_once 'dbConnect.php';
$sql = "UPDATE users SET first_name='$first_name', last_name='$last_name', email='$email', phone='$phone' WHERE id = $id";
$update = $db->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);The deleteData.php
file is used to delete data from the database depending on the row ID.
<?php
$response = array(
'status' => 0,
'msg' => 'Some problems occurred, please try again.'
);
if(!empty($_REQUEST['id'])){
$id = intval($_REQUEST['id']);
// Include the database config file
require_once 'dbConnect.php';
$sql = "DELETE FROM users WHERE id = $id";
$delete = $db->query($sql);
if($delete){
$response['status'] = 1;
$response['msg'] = 'User data has been deleted successfully!';
}
}
echo json_encode($response);Note :
When you need to quickly integrate CRUD functionality without writing a lot of code, EasyUI comes in handy. It aids in the development of a CRUD application that uses PHP and MySQL for server-side processing. EasyUI has a number of plugins that can be used to improve the DataGrid’s capabilities.