AllsWeb Blog
No Result
View All Result
  • Home
  • Main Home
  • PHP and MySQL
  • JavaScript
    • jQuery & AJAX
  • WordPress
  • SEO
  • Web Hosting
  • Comparison
Support
Knowledgebase
  • Home
  • Main Home
  • PHP and MySQL
  • JavaScript
    • jQuery & AJAX
  • WordPress
  • SEO
  • Web Hosting
  • Comparison
No Result
View All Result
AllsWeb White Logo
No Result
View All Result
Home JavaScript jQuery & AJAX

Build CRUD DataGrid with jQuery EasyUI using PHP and MySQL

Build CRUD DataGrid with jQuery EasyUI using PHP and MySQL

Lakshika Mathur by Lakshika Mathur
December 25, 2019
Reading Time: 8 mins read
0
Build CRUD DataGrid

DataGrid with CRUD operations is the maximum used functionality for the data management section. Commonly, the web page is reloaded when the CRUD operation happens. Therefore, to create the web application user-friendly, you can assimilate the CRUD functionality without page refresh. So, the EasyUI framework offers an easy way to assimilate DataGrid with CRUD feature in the web application.

RELATED POSTS

What is Application Programming Interface (APIs)?

Like Dislike Rating System with jQuery, Ajax, and PHP

Star Rating System with jQuery, Ajax, PHP, and MySQL

EasyUI is a jQuery framework that helps to create new and interactive DataGrid CRUD applications quickly. You can integrate the DataGrid functionality into the web page in less time by writing lesser code. The jQuery EasyUI permits networking with the server-side script to make the DataGrid more influential. In this tutorial, we will explain to you how to create CRUD with search and pagination by using PHP, EasyUI, and MySQL.

In the EasyUI integration example code, we will implement the following functionality.

  • Firstly, get data from the database and list them in a tabular format.
  • Secondly, add/edit data in the dialog window without page refresh.
  • Thirdly, remove the data from the database without page refresh.
  • At last, add the search and pagination feature to the list.

jQuery EasyUI Integration

The following code shows how to integrate the jQuery EasyUI plugin on the web page to build a CRUD application and make or edit the information of the user with the help of a dialog component.

1. Initially, attach the CSS and JavaScript files of the EasyUI plugin on 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>

2. Add HTML code for the toolbar and data list table.

  • Specify the URL of the server-side script (getData.php) in URL attribute of the <table> tag.
  • Use the pagination attribute and set it TURE (pagination="true") to add pagination links to the data list.
<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. Now, add HTML code for add/edit form dialog.

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

4. Add the JavaScript code for server-side interaction.

  • doSearch() – Send terms to the server-side script (getData.php) and load the filtered data.
  • newUser() – Open popup dialog with the HTML form to add data.
  • editUser() – Open popup dialog with the HTML form to edit data.
  • saveUser() – Send data to the server-side script (addData.php or editData.php) for saving in the database.
  • destroyUser() – Send request to the server-side script (deleteData.php) for remove data.
<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>

SEE ALSO: Ajax File Upload using jQuery and PHP

Server-side Processing

Create Database Table:

We need a table to store the data in the database. The following SQL creates a user table in the MySQL database with some necessary 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 Configuration (dbConnect.php):

Use the dbConnect.php  file to connect with the database. Specify the database host ($dbHost), username  ($dbUsername), password ($dbPassword), and name($dbName) as per your MySQL database credentials.

<?php 
// Database configuration  
$dbHost     = "localhost";  
$dbUsername = "root";  
$dbPassword = "root";  
$dbName     = "allsweb"; 
 
// 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 use of this getData.php file is to fetch data from the database using PHP and MySQL.

  • Based on the search terms and page number, return the records in JSON format.
<?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):

You can use addData.php file to insert data in the database using PHP and MySQL.

<?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):

You can use editData.php to update data based on the row ID using PHP and MySQL.

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

Delete Data (deleteData.php):

You can use deleteData.php to delete data from the database based 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);

SEE ALSO: Convert HTML to MS Word Document using PHP

Conclusion

Firstly, UI is very beneficial when you want to integrate CRUD functionality suddenly without writing much code. Secondly, it benefits you to build a CRUD application with server-side processing using MySQL and PHP. Thirdly, there are several plugins available in EasyUI, which permit you to improve the DataGrid functionality.

Also, read our previous blog- Convert Array to XML and XML to Array in PHP

Tags: CRUD DataGridPHP and MySQL
ShareTweetSendShareSharePinScan
Lakshika Mathur

Lakshika Mathur

Related Posts

What is Application Programming Interface (APIs), Types, and Importance.
PHP and MySQL

What is Application Programming Interface (APIs)?

January 29, 2022
61
Like Dislike Rating System with jQuery, Ajax, and PHP
jQuery & AJAX

Like Dislike Rating System with jQuery, Ajax, and PHP

January 6, 2020
739
Star Rating System with jQuery, Ajax, PHP, and MySQL
jQuery & AJAX

Star Rating System with jQuery, Ajax, PHP, and MySQL

January 6, 2020
162
How to Force Download File in PHP
PHP and MySQL

How to Force Download File in PHP

January 2, 2020
82
Form Validation using jQuery Validation Plugin
jQuery & AJAX

Form Validation using jQuery Validation Plugin

January 2, 2020
34
How to Connect to the Remote MySQL Database using PHP
PHP and MySQL

How to Connect to the Remote MySQL Database using PHP

January 1, 2020
28
Next Post
Convert HTML to MS Word Document using PHP

Convert HTML to MS Word Document using PHP

Convert HTML to PDF using JavaScript

Convert HTML to PDF using JavaScript

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • Comparison (3)
  • HTML & CSS (9)
  • Interesting Facts (1)
  • JavaScript (27)
    • jQuery & AJAX (18)
  • PHP and MySQL (48)
  • Security (10)
  • SEO (2)
  • Trademark (2)
  • Tutorials (5)
  • Uncategorized (1)
  • Web Hosting (19)
    • VPS Server (5)
  • WordPress (8)

Recent Posts

  • Is the Trademark valuable to your Brand or domain?
  • Ideas For Ten Fantastic Online Business From Home
  • Some best free WordPress Themes for Affiliate Marketing Websites
  • Home
  • Posts
  • Privacy Policy
  • Terms and Conditions

Built and Maintained With ♥ by AllsWeb Team

No Result
View All Result
  • Home
  • Main Home
  • PHP and MySQL
  • JavaScript
    • jQuery & AJAX
  • WordPress
  • SEO
  • Web Hosting
  • Comparison

Built and Maintained With ♥ by AllsWeb Team

Go to mobile version