loading

How to Build CRUD DataGrid with jQuery EasyUI using PHP and MySQL

How to Build CRUD DataGrid with jQuery EasyUI using PHP and MySQL

How to Build CRUD DataGrid with jQuery EasyUI using PHP and MySQL

0 Sales

Free

The most often used capability in the data management part is the DataGrid with CRUD operations. When a CRUD action happens, the web page is typically refreshed. You may implement CRUD capabilities without page refresh to make the web application more user-friendly. The EasyUI framework makes it simple to combine DataGrid with the CRUD capability in a web application.

EasyUI is a jQuery framework that allows you to easily create contemporary and interactive 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 provides interaction with the server-side script. We will demonstrate how to create CRUD with search and pagination using EasyUI, PHP, and MySQL in this tutorial.

The following functionality will be provided in the EasyUI integration sample code.

- Retrieve data from the database and display it in a tabular fashion.
- Add/Edit data in the dialogue box without refreshing the page.
- Delete data from the database without refreshing the page.
- Include the search and pagination functions in the list.

jQuery EasyUI Integration

The code below demonstrates how to include the jQuery EasyUI plugin into a web page to construct a CRUD application and create or change user information using the dialogue component.

1. Include the EasyUI plugin's CSS and JavaScript files 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. Insert HTML code into the data list table and toolbar.

- In the url attribute of the table> element, provide the URL of the server-side script (getData.php).
- To add pagination links to the data list, use the pagination property 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. Insert HTML code for the add/edit form dialogue.

<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. Include the JavaScript code required for server-side interaction.

- doSearch() – Send terms to the server-side script (getData.php), which will load the filtered data.
- newUser() – Displays a popup window with an HTML form for entering data.
- editUser() – Displays a popup window with an HTML form for editing data.
- saveUser() – Sends data to the server-side script (addData.php or editData.php) for database storage.
- destroyUser() – Sends a request to the server-side script (deleteData.php) to delete the user's 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>

Server-side Processing

Create Database Table:

A table in the database is required to store the data. The SQL below creates a users table in the MySQL database, along with some basic columns.

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

To connect to the database, use the dbConnect.php file. As per your MySQL database credentials, provide 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):

This file, getData.php, is used to retrieve data from the database using PHP and MySQL.

- The records are provided 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):
The addData.php file is used to put data into the database, which is done with 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):

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

Delete Data (deleteData.php):

The deleteData.php file is used to remove data from the database by 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);

Conclusion

EasyUI is really handy when you need to incorporate CRUD capabilities quickly and without writing a lot of code. It is beneficial to construct a CRUD application with server-side processing using PHP and MySQL. EasyUI has a number of plugins that may be used to expand the DataGrid's capabilities.

LICENSE OF USE

You can use it for personal or commercial projects. You can't resell it partially or in this form.

PRODUCT INFO

Create Date : Jan 25, 2022

Updated Date : Jan 25, 2022

Ratings

Comments : 0

Downloads : 0