Saturday, October 9, 2021

PHP CRUD Operations without Page Refresh using jQuery, Ajax, and MySQL

 CRUD operation helps to Create, Read, Update and Delete database records. Add, Edit, Update and Delete functionality is commonly used in the data management section of every web application. 

You can easily implement the CRUD operations with MySQL in PHP. Probably, you’ve integrated the PHP CRUD operation many times on the website, but today we’ll show you the user-friendly way to implement CRUD functionality in PHP.

Generally, in PHP CRUD operations the web page is refreshed or redirected each time an action is requested. To make this CRUD process user-friendly, it can be implemented without page refresh using jQuery and Ajax. In this tutorial, we’ll implement PHP CRUD operations without page refresh using jQuery, Ajax, and MySQL. The example PHP CRUD script will help to read, add, update, and delete the records from MySQL database.

The following functionality will be implemented to build PHP CRUD Operations with Bootstrap 4 using jQuery, Ajax, and MySQL.

  • The user’s data will be fetched from the database and listed with the add, edit, and delete link.
  • The add link allows the user to add new data to the database.
  • The edit link allows the user to update previously inserted data.
  • The delete link allows the user to delete the data from the database.
  • All CRUD operations will happen on a single page without page refresh or redirect.

Before getting started to create CRUD application with PHP using jQuery, Ajax, and MySQLi, take a look at the files structure.

php_crud_jquery_ajax_mysql/
├── DB.class.php
├── index.php
├── userAction.php
├── js/
│   └── jquery.min.js
└── bootstrap/
    ├── bootstrap.min.css
    └── bootstrap.min.js

Create Database Table

To store and manage the data a table needs to be created in the database. The following SQL creates a users table with some basic fields in the MySQL database.

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1=Active, 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Class (DB.class.php)

The DB class handles all the database related operations (connect, insert, update, and delete). Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.

  • __construct() – Connect to the database with PHP and MySQLi Extension.
  • getRows() – Fetch records from the database based on the specified conditions.
  • insert() – Insert data into the database.
  • update() – Update data into the database.
  • delete() – Delete data from the database.
<?php
/*
 * DB Class
 * This class is used for database related (connect, insert, update, and delete) operations
 * @author    CodexWorld.com
 * @url        http://www.codexworld.com
 * @license    http://www.codexworld.com/license
 */
class DB{
    private $dbHost     = "localhost";
    private $dbUsername = "root";
    private $dbPassword = "root";
    private $dbName     = "codexworld";
    
    public function __construct(){
        if(!isset($this->db)){
            // Connect to the database
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
            if($conn->connect_error){
                die("Failed to connect with MySQL: " . $conn->connect_error);
            }else{
                $this->db = $conn;
            }
        }
    }
    
    /*
     * Returns rows from the database based on the conditions
     * @param string name of the table
     * @param array select, where, order_by, limit and return_type conditions
     */
    public function getRows($table, $conditions = array()){
        $sql = 'SELECT ';
        $sql .= array_key_exists("select",$conditions)?$conditions['select']:'*';
        $sql .= ' FROM '.$table;
        if(array_key_exists("where",$conditions)){
            $sql .= ' WHERE ';
            $i = 0;
            foreach($conditions['where'] as $key => $value){
                $pre = ($i > 0)?' AND ':'';
                $sql .= $pre.$key." = '".$value."'";
                $i++;
            }
        }
        
        if(array_key_exists("order_by",$conditions)){
            $sql .= ' ORDER BY '.$conditions['order_by']; 
        }else{
            $sql .= ' ORDER BY id DESC '; 
        }
        
        if(array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            $sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit']; 
        }elseif(!array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            $sql .= ' LIMIT '.$conditions['limit']; 
        }
        
        $result = $this->db->query($sql);
        
        if(array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){
            switch($conditions['return_type']){
                case 'count':
                    $data = $result->num_rows;
                    break;
                case 'single':
                    $data = $result->fetch_assoc();
                    break;
                default:
                    $data = '';
            }
        }else{
            if($result->num_rows > 0){
                while($row = $result->fetch_assoc()){
                    $data[] = $row;
                }
            }
        }
        return !empty($data)?$data:false;
    }
    
    /*
     * Insert data into the database
     * @param string name of the table
     * @param array the data for inserting into the table
     */
    public function insert($table, $data){
        if(!empty($data) && is_array($data)){
            $columns = '';
            $values  = '';
            $i = 0;
            if(!array_key_exists('created',$data)){
                $data['created'] = date("Y-m-d H:i:s");
            }
            if(!array_key_exists('modified',$data)){
                $data['modified'] = date("Y-m-d H:i:s");
            }
            foreach($data as $key=>$val){
                $pre = ($i > 0)?', ':'';
                $columns .= $pre.$key;
                $values  .= $pre."'".$this->db->real_escape_string($val)."'";
                $i++;
            }
            $query = "INSERT INTO ".$table." (".$columns.") VALUES (".$values.")";
            $insert = $this->db->query($query);
            return $insert?$this->db->insert_id:false;
        }else{
            return false;
        }
    }
    
    /*
     * Update data into the database
     * @param string name of the table
     * @param array the data for updating into the table
     * @param array where condition on updating data
     */
    public function update($table, $data, $conditions){
        if(!empty($data) && is_array($data)){
            $colvalSet = '';
            $whereSql = '';
            $i = 0;
            if(!array_key_exists('modified',$data)){
                $data['modified'] = date("Y-m-d H:i:s");
            }
            foreach($data as $key=>$val){
                $pre = ($i > 0)?', ':'';
                $colvalSet .= $pre.$key."='".$this->db->real_escape_string($val)."'";
                $i++;
            }
            if(!empty($conditions)&& is_array($conditions)){
                $whereSql .= ' WHERE ';
                $i = 0;
                foreach($conditions as $key => $value){
                    $pre = ($i > 0)?' AND ':'';
                    $whereSql .= $pre.$key." = '".$value."'";
                    $i++;
                }
            }
            $query = "UPDATE ".$table." SET ".$colvalSet.$whereSql;
            $update = $this->db->query($query);
            return $update?$this->db->affected_rows:false;
        }else{
            return false;
        }
    }
    
    /*
     * Delete data from the database
     * @param string name of the table
     * @param array where condition on deleting data
     */
    public function delete($table, $conditions){
        $whereSql = '';
        if(!empty($conditions)&& is_array($conditions)){
            $whereSql .= ' WHERE ';
            $i = 0;
            foreach($conditions as $key => $value){
                $pre = ($i > 0)?' AND ':'';
                $whereSql .= $pre.$key." = '".$value."'";
                $i++;
            }
        }
        $query = "DELETE FROM ".$table.$whereSql;
        $delete = $this->db->query($query);
        return $delete?true:false;
    }
}

Data List (index.php)

This is the main view file which is visible to the user. In this single page, the user can do all the CRUD operations without page refresh.

jQuery Library:
The jQuery and Ajax are used to handle CRUD operations without page refresh, so, include the jQuery library.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

Bootstrap Library:
The Bootstrap is used to integrate modal popup and styling the table, list, form fields, and links. Include the CSS & JS files of the Bootstrap 4 library.

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" crossorigin="anonymous">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" crossorigin="anonymous"></script>

JavaScript Code:
The following JavaScript code handles the CRUD request using jQuery and Ajax.

  • getUsers() – This function is used to retrieve the user’s data from the server-side script (userAction.php) using jQuery and Ajax. On success, the response HTML is rendered in the user data list table.
  • userAction() – This function is used to send add, edit, and delete request to the server-side script (userAction.php) using jQuery and Ajax. Based on the response, the message is shown to the user.
  • editUser() – This function is used to get a specific user’s data from the server-side script (userAction.php) and set the respective value in the form fields.
  • shown.bs.modal – When the modal is visible to the user, the request type is identified based on the value of the data-type attribute.
    • If Add request is initiated, userAction() function with add param is set in the onclick attribute of the submit button (#userSubmit).
    • If Edit request is initiated, userAction() function with edit param is set in the onclick attribute of the submit button (#userSubmit). Also, the editUser() function is called to pre-filled the user’s data in the HTML form based on the rowId.
  • hidden.bs.modal – When the modal is hidden from the user, the onclick attribute is removed from the submit button (#userSubmit). Also, the pre-filled form data and the status message is removed from the HTML elements.
<script>
// Update the users data list
function getUsers(){
    $.ajax({
        type: 'POST',
        url: 'userAction.php',
        data: 'action_type=view',
        success:function(html){
            $('#userData').html(html);
        }
    });
}

// Send CRUD requests to the server-side script
function userAction(type, id){
    id = (typeof id == "undefined")?'':id;
    var userData = '', frmElement = '';
    if(type == 'add'){
        frmElement = $("#modalUserAddEdit");
        userData = frmElement.find('form').serialize()+'&action_type='+type+'&id='+id;
    }else if (type == 'edit'){
        frmElement = $("#modalUserAddEdit");
        userData = frmElement.find('form').serialize()+'&action_type='+type;
    }else{
        frmElement = $(".row");
        userData = 'action_type='+type+'&id='+id;
    }
    frmElement.find('.statusMsg').html('');
    $.ajax({
        type: 'POST',
        url: 'userAction.php',
        dataType: 'JSON',
        data: userData,
        beforeSend: function(){
            frmElement.find('form').css("opacity", "0.5");
        },
        success:function(resp){
            frmElement.find('.statusMsg').html(resp.msg);
            if(resp.status == 1){
                if(type == 'add'){
                    frmElement.find('form')[0].reset();
                }
                getUsers();
            }
            frmElement.find('form').css("opacity", "");
        }
    });
}

// Fill the user's data in the edit form
function editUser(id){
    $.ajax({
        type: 'POST',
        url: 'userAction.php',
        dataType: 'JSON',
        data: 'action_type=data&id='+id,
        success:function(data){
            $('#id').val(data.id);
            $('#name').val(data.name);
            $('#email').val(data.email);
            $('#phone').val(data.phone);
        }
    });
}

// Actions on modal show and hidden events
$(function(){
    $('#modalUserAddEdit').on('show.bs.modal', function(e){
        var type = $(e.relatedTarget).attr('data-type');
        var userFunc = "userAction('add');";
        if(type == 'edit'){
            userFunc = "userAction('edit');";
            var rowId = $(e.relatedTarget).attr('rowID');
            editUser(rowId);
        }
        $('#userSubmit').attr("onclick", userFunc);
    });
    
    $('#modalUserAddEdit').on('hidden.bs.modal', function(){
        $('#userSubmit').attr("onclick", "");
        $(this).find('form')[0].reset();
        $(this).find('.statusMsg').html('');
    });
});
</script>

PHP & HTML Code:
The following HTML is used to build the data list table and form dialog.

  • Initially, the user’s data is fetched from the database and listed with Edit & Delete links in the HTML table.
  • At the top of the data list, an Add link is placed to initiate the create request.
  • On clicking the Add button, the Bootstrap modal dialog appears with the HTML form to add a new user.
  • On clicking the Edit button, the Bootstrap modal dialog appears with the HTML form and pre-filled data to update user’s data.
  • On clicking the Delete button, a confirmation dialog appears. After the confirmation, userAction() function is called with delete and row ID params to remove the record from the database.
<?php
// Include and initialize DB class
require_once 'DB.class.php';
$db = new DB();

// Fetch the users data
$users = $db->getRows('users');
?>
 <div class="container">
    <div class="row">
        <div class="col-md-12 head">
            <h5>Users</h5>
            <!-- Add link -->
            <div class="float-right">
                <a href="javascript:void(0);" class="btn btn-success" data-type="add" data-toggle="modal" data-target="#modalUserAddEdit"><i class="plus"></i> New User</a>
            </div>
        </div>
        <div class="statusMsg"></div>
        <!-- List the users -->
        <table class="table table-striped table-bordered">
            <thead class="thead-dark">
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Phone</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody id="userData">
                <?php if(!empty($users)){ foreach($users as $row){ ?>
                <tr>
                    <td><?php echo '#'.$row['id']; ?></td>
                    <td><?php echo $row['name']; ?></td>
                    <td><?php echo $row['email']; ?></td>
                    <td><?php echo $row['phone']; ?></td>
                    <td>
                        <a href="javascript:void(0);" class="btn btn-warning" rowID="<?php echo $row['id']; ?>" data-type="edit" data-toggle="modal" data-target="#modalUserAddEdit">edit</a>
                        <a href="javascript:void(0);" class="btn btn-danger" onclick="return confirm('Are you sure to delete data?')?userAction('delete', '<?php echo $row['id']; ?>'):false;">delete</a>
                    </td>
                </tr>
                <?php } }else{ ?>
                <tr><td colspan="5">No user(s) found...</td></tr>
                <?php } ?>
            </tbody>
        </table>
    </div>
</div>



<!-- Modal Add and Edit Form -->
<div class="modal fade" id="modalUserAddEdit" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">
            <!-- Modal Header -->
            <div class="modal-header">
              <h4 class="modal-title">Add New User</h4>
              <button type="button" class="close" data-dismiss="modal">&times;</button>
            </div>
            
            <!-- Modal Body -->
            <div class="modal-body">
                <div class="statusMsg"></div>
                <form role="form">
                    <div class="form-group">
                        <label for="name">Name</label>
                        <input type="text" class="form-control" name="name" id="name" placeholder="Enter your name">
                    </div>
                    <div class="form-group">
                        <label for="email">Email</label>
                        <input type="email" class="form-control" name="email" id="email" placeholder="Enter your email">
                    </div>
                    <div class="form-group">
                        <label for="phone">Phone</label>
                        <input type="text" class="form-control" name="phone" id="phone" placeholder="Enter phone no">
                    </div>
                    <input type="hidden" class="form-control" name="id" id="id"/>
                </form>
            </div>
            
            <!-- Modal Footer -->
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                <button type="button" class="btn btn-success" id="userSubmit">SUBMIT</button>
            </div>
        </div>
    </div>
</div>

Add, Edit, and Delete Records (userAction.php)

This file handles the CRUD requests coming from the Ajax request of the view file (index.php) and returns the respective requested data. Here the code is executed based on the action_type parameter. The action_type can be five types, data, view, add, edit, and delete. The following operations will happen based on the action_type request.

  • data – Fetch a single record based on the id from the database using getRows() function of DB class. Returns the user data as JSON format.
  • view – Fetch all records from the database and returns the users data as HTML format.
  • add – Retrieve form fields data using $_POST in PHP, validate the input values, and insert the data in the database. Returns the response as JSON format.
  • edit – Retrieve form fields data using $_POST in PHP, validate the input values, and update the data in the database. Returns the response as JSON format.
  • delete – Deletes the record from the database based on the ID and returns the status as JSON format.
<?php
// Include and initialize DB class
require_once 'DB.class.php';
$db = new DB();

// Database table name
$tblName = 'users';

// If the form is submitted
if(!empty($_POST['action_type'])){
    if($_POST['action_type'] == 'data'){
        // Fetch data based on row ID
        $conditions['where'] = array('id' => $_POST['id']);
        $conditions['return_type'] = 'single';
        $user = $db->getRows($tblName, $conditions);
        
        // Return data as JSON format
        echo json_encode($user);
    }elseif($_POST['action_type'] == 'view'){
        // Fetch all records
        $users = $db->getRows($tblName);
        
        // Render data as HTML format
        if(!empty($users)){
            foreach($users as $row){
                echo '<tr>';
                echo '<td>#'.$row['id'].'</td>';
                echo '<td>'.$row['name'].'</td>';
                echo '<td>'.$row['email'].'</td>';
                echo '<td>'.$row['phone'].'</td>';
                echo '<td><a href="javascript:void(0);" class="btn btn-warning" rowID="'.$row['id'].'" data-type="edit" data-toggle="modal" data-target="#modalUserAddEdit">edit</a>
                <a href="javascript:void(0);" class="btn btn-danger" onclick="return confirm(\'Are you sure to delete data?\')?userAction(\'delete\', \''.$row['id'].'\'):false;">delete</a></td>';
                echo '</tr>';
            }
        }else{
            echo '<tr><td colspan="5">No user(s) found...</td></tr>';
        }
    }elseif($_POST['action_type'] == 'add'){
        $msg = '';
        $status = $verr = 0;
        
        // Get user's input
        $name = $_POST['name'];
        $email = $_POST['email'];
        $phone = $_POST['phone'];
        
        // Validate form fields
        if(empty($name)){
            $verr = 1;
            $msg .= 'Please enter your name.<br/>';
        }
        if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){
            $verr = 1;
            $msg .= 'Please enter a valid email.<br/>';
        }
        if(empty($phone)){
            $verr = 1;
            $msg .= 'Please enter your phone no.<br/>';
        }
        
        if($verr == 0){
            // Insert data in the database
            $userData = array(
                'name'  => $name,
                'email' => $email,
                'phone' => $phone
            );
            $insert = $db->insert($tblName, $userData);
            
            if($insert){
                $status = 1;
                $msg .= 'User data has been inserted successfully.';
            }else{
                $msg .= 'Some problem occurred, please try again.';
            }
        }
        
        // Return response as JSON format
        $alertType = ($status == 1)?'alert-success':'alert-danger';
        $statusMsg = '<p class="alert '.$alertType.'">'.$msg.'</p>';
        $response = array(
            'status' => $status,
            'msg' => $statusMsg
        );
        echo json_encode($response);
    }elseif($_POST['action_type'] == 'edit'){
        $msg = '';
        $status = $verr = 0;
        
        if(!empty($_POST['id'])){
            // Get user's input
            $name = $_POST['name'];
            $email = $_POST['email'];
            $phone = $_POST['phone'];
            
            // Validate form fields
            if(empty($name)){
                $verr = 1;
                $msg .= 'Please enter your name.<br/>';
            }
            if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){
                $verr = 1;
                $msg .= 'Please enter a valid email.<br/>';
            }
            if(empty($phone)){
                $verr = 1;
                $msg .= 'Please enter your phone no.<br/>';
            }
            
            if($verr == 0){
                // Update data in the database
                $userData = array(
                    'name'  => $name,
                    'email' => $email,
                    'phone' => $phone
                );
                $condition = array('id' => $_POST['id']);
                $update = $db->update($tblName, $userData, $condition);
                
                if($update){
                    $status = 1;
                    $msg .= 'User data has been updated successfully.';
                }else{
                    $msg .= 'Some problem occurred, please try again.';
                }
            }
        }else{
            $msg .= 'Some problem occurred, please try again.';
        }
        
        // Return response as JSON format
        $alertType = ($status == 1)?'alert-success':'alert-danger';
        $statusMsg = '<p class="alert '.$alertType.'">'.$msg.'</p>';
        $response = array(
            'status' => $status,
            'msg' => $statusMsg
        );
        echo json_encode($response);
    }elseif($_POST['action_type'] == 'delete'){
        $msg = '';
        $status = 0;
        
        if(!empty($_POST['id'])){
            // Delate data from the database
            $condition = array('id' => $_POST['id']);
            $delete = $db->delete($tblName, $condition);
            
            if($delete){
                $status = 1;
                $msg .= 'User data has been deleted successfully.';
            }else{
                $msg .= 'Some problem occurred, please try again.';
            }
        }else{
            $msg .= 'Some problem occurred, please try again.';
        }  

        // Return response as JSON format
        $alertType = ($status == 1)?'alert-success':'alert-danger';
        $statusMsg = '<p class="alert '.$alertType.'">'.$msg.'</p>';
        $response = array(
            'status' => $status,
            'msg' => $statusMsg
        );
        echo json_encode($response);
    }
}

exit;
?>


at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

How to register multiple implementations of the same interface in Asp.Net Core?

 Problem: I have services that are derived from the same interface. public interface IService { } public class ServiceA : IService { ...

  • What is a synonym in SQL Server
    In SQL Server, a synonym is an alias or alternative name for a database object such as a table,  view ,  stored procedure ,  user-defined fu...
  • What is Microservices?
    Microservice architecture, or simply microservices , is a distinctive method of developing software systems that tries to focus on buildi...