PHP CRUD Operations without Page Refresh using jQuery Ajax MySQL


CRUD stands for Create, Read, Update and Delete database records. Add, Edit, Update, and Delete functionality is used almost every web project in PHP. You’ve done it many times, but today we’ll show you the more user-friendly way to implement CRUD functionality in PHP.

Here we’ll implement PHP CRUD operations without page refresh using jQuery, Ajax, and MySQL. The functionality of the example script would be read, add, update, and delete the records from MySQL database.

In this example script, we’ll fetch the users data from the database and display the user data list with add link, edit link, and delete link. By these links user can add new data to the database, update previously inserted data and delete the data from the database. All operations will happen on a single page without page refresh. Also, bootstrap table structure will be used for styling the list, form fields, and links.

Let’s start the step-by-step guide on creating a CRUD application with PHP using jQuery Ajax MySQL. Before you begin, take a look at the files structure of CRUD application.

php-crud-tutorial-files-structure-codexworld

Database Table Creation

For this example script, we’ll create a simple table (users) with some basic columns where users data would be stored.

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(255) 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.php)

DB class handles all the operations related to the database. For example, connect with the database, insert, update and delete record from the database. You need to change the $dbHost, $dbUsername, $dbPassword, and $dbName variables value as per the database credentials.

<?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 "";
    private 
$dbName     "codexworld";
    
    public function 
__construct(){
        if(!
$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']; 
        }
        
        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."'".$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."='".$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;
    }
}

userAction.php

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

  • data returns the single user data based on the id as JSON format.
  • view returns all the users data as HTML format.
  • add insert the record in the database and return the status.
  • edit updates the record in the database and returns the status.
  • delete deletes the record from the database and returns the status.
<?php
include 'DB.php';
$db = new DB();
$tblName 'users';
if(isset(
$_POST['action_type']) && !empty($_POST['action_type'])){
    if(
$_POST['action_type'] == 'data'){
        
$conditions['where'] = array('id'=>$_POST['id']);
        
$conditions['return_type'] = 'single';
        
$user $db->getRows($tblName,$conditions);
        echo 
json_encode($user);
    }elseif(
$_POST['action_type'] == 'view'){
        
$users $db->getRows($tblName,array('order_by'=>'id DESC'));
        if(!empty(
$users)){
            
$count 0;
            foreach(
$users as $user): $count++;
                echo 
'<tr>';
                echo 
'<td>#'.$count.'</td>';
                echo 
'<td>'.$user['name'].'</td>';
                echo 
'<td>'.$user['email'].'</td>';
                echo 
'<td>'.$user['phone'].'</td>';
                echo 
'<td><a href="javascript:void(0);" class="glyphicon glyphicon-edit" onclick="editUser(\''.$user['id'].'\')"></a><a href="javascript:void(0);" class="glyphicon glyphicon-trash" onclick="return confirm(\'Are you sure to delete data?\')?userAction(\'delete\',\''.$user['id'].'\'):false;"></a></td>';
                echo 
'</tr>';
            endforeach;
        }else{
            echo 
'<tr><td colspan="5">No user(s) found......</td></tr>';
        }
    }elseif(
$_POST['action_type'] == 'add'){
        
$userData = array(
            
'name' => $_POST['name'],
            
'email' => $_POST['email'],
            
'phone' => $_POST['phone']
        );
        
$insert $db->insert($tblName,$userData);
        echo 
$insert?'ok':'err';
    }elseif(
$_POST['action_type'] == 'edit'){
        if(!empty(
$_POST['id'])){
            
$userData = array(
                
'name' => $_POST['name'],
                
'email' => $_POST['email'],
                
'phone' => $_POST['phone']
            );
            
$condition = array('id' => $_POST['id']);
            
$update $db->update($tblName,$userData,$condition);
            echo 
$update?'ok':'err';
        }
    }elseif(
$_POST['action_type'] == 'delete'){
        if(!empty(
$_POST['id'])){
            
$condition = array('id' => $_POST['id']);
            
$delete $db->delete($tblName,$condition);
            echo 
$delete?'ok':'err';
        }
    }
    exit;
}

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. For better understanding we’ve divided this script into two parts.

Bootstrap & JavaScript:
Bootstrap CSS & JS library and jQuery library need to be included for work this script smoothly. You can omit Bootstrap CSS & JS library if you don’t want to use bootstrap table structure.

<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.2/jquery.min.js"></script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>

getUsers() function is used to get the users data from the userAction.php file using ajax and render the received HTML in the user data list.

function getUsers(){
    $.ajax({
        type: 'POST',
        url: 'userAction.php',
        data: 'action_type=view&'+$("#userForm").serialize(),
        success:function(html){
            $('#userData').html(html);
        }
    });
}

userAction() function is used to send add, edit, and delete request to the userAction.php file using ajax and give the response to the user.

function userAction(type,id){
    id = (typeof id == "undefined")?'':id;
    var statusArr = {add:"added",edit:"updated",delete:"deleted"};
    var userData = '';
    if (type == 'add') {
        userData = $("#addForm").find('.form').serialize()+'&action_type='+type+'&id='+id;
    }else if (type == 'edit'){
        userData = $("#editForm").find('.form').serialize()+'&action_type='+type;
    }else{
        userData = 'action_type='+type+'&id='+id;
    }
    $.ajax({
        type: 'POST',
        url: 'userAction.php',
        data: userData,
        success:function(msg){
            if(msg == 'ok'){
                alert('User data has been '+statusArr[type]+' successfully.');
                getUsers();
                $('.form')[0].reset();
                $('.formData').slideUp();
            }else{
                alert('Some problem occurred, please try again.');
            }
        }
    });
}

editUser() function is used to get the particular user data from the userAction.php file and set the respective value in the edit form fields.

function editUser(id){
    $.ajax({
        type: 'POST',
        dataType:'JSON',
        url: 'userAction.php',
        data: 'action_type=data&id='+id,
        success:function(data){
            $('#idEdit').val(data.id);
            $('#nameEdit').val(data.name);
            $('#emailEdit').val(data.email);
            $('#phoneEdit').val(data.phone);
            $('#editForm').slideDown();
        }
    });
}

HTML:
Initially already inserted users data is fetched from the users table and listed with edit & delete links. At the top of the user list, add link would be displayed. The add and edit form HTMl would be visible if add or edit link is clicked.

<div class="container">
    <div class="row">
        <div class="panel panel-default users-content">
            <div class="panel-heading">Users <a href="javascript:void(0);" class="glyphicon glyphicon-plus" id="addLink" onclick="javascript:$('#addForm').slideToggle();">Add</a></div>
            <div class="panel-body none formData" id="addForm">
                <h2 id="actionLabel">Add User</h2>
                <form class="form" id="userForm">
                    <div class="form-group">
                        <label>Name</label>
                        <input type="text" class="form-control" name="name" id="name"/>
                    </div>
                    <div class="form-group">
                        <label>Email</label>
                        <input type="text" class="form-control" name="email" id="email"/>
                    </div>
                    <div class="form-group">
                        <label>Phone</label>
                        <input type="text" class="form-control" name="phone" id="phone"/>
                    </div>
                    <a href="javascript:void(0);" class="btn btn-warning" onclick="$('#addForm').slideUp();">Cancel</a>
                    <a href="javascript:void(0);" class="btn btn-success" onclick="userAction('add')">Add User</a>
                </form>
            </div>
            <div class="panel-body none formData" id="editForm">
                <h2 id="actionLabel">Edit User</h2>
                <form class="form" id="userForm">
                    <div class="form-group">
                        <label>Name</label>
                        <input type="text" class="form-control" name="name" id="nameEdit"/>
                    </div>
                    <div class="form-group">
                        <label>Email</label>
                        <input type="text" class="form-control" name="email" id="emailEdit"/>
                    </div>
                    <div class="form-group">
                        <label>Phone</label>
                        <input type="text" class="form-control" name="phone" id="phoneEdit"/>
                    </div>
                    <input type="hidden" class="form-control" name="id" id="idEdit"/>
                    <a href="javascript:void(0);" class="btn btn-warning" onclick="$('#editForm').slideUp();">Cancel</a>
                    <a href="javascript:void(0);" class="btn btn-success" onclick="userAction('edit')">Update User</a>
                </form>
            </div>
            <table class="table table-striped">
                <thead>
                    <tr>
                        <th></th>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Phone</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody id="userData">
                    <?php
                        
include 'DB.php';
                        
$db = new DB();
                        
$users $db->getRows('users',array('order_by'=>'id DESC'));
                        if(!empty(
$users)): $count 0; foreach($users as $user): $count++;
                    
?> <tr> <td><?php echo '#'.$count?></td> <td><?php echo $user['name']; ?></td> <td><?php echo $user['email']; ?></td> <td><?php echo $user['phone']; ?></td> <td> <a href="javascript:void(0);" class="glyphicon glyphicon-edit" onclick="editUser('<?php echo $user['id']; ?>')"></a> <a href="javascript:void(0);" class="glyphicon glyphicon-trash" onclick="return confirm('Are you sure to delete data?')?userAction('delete','<?php echo $user['id']; ?>'):false;"></a> </td> </tr>                     <?php endforeach; else: ?> <tr><td colspan="5">No user(s) found......</td></tr>                     <?php endif; ?> </tbody> </table> </div> </div> </div>

CSS:
CSS for .none class is required and the other CSS is optional.

/* required style*/ 
.none{display: none;}

/* optional styles */
table tr th, table tr td{font-size: 1.2rem;}
.row{ margin:20px 20px 20px 20px;width: 100%;}
.glyphicon{font-size: 20px;}
.glyphicon-plus{float: right;}
a.glyphicon{text-decoration: none;}
a.glyphicon-trash{margin-left: 10px;}

Conclusion

In this tutorial, we’re trying to make PHP CRUD operations more simple and user-friendly. You can share your thought to improve this script or the DB class.

7 Comments

  1. Saif Ansari Said...
  2. King Mharkley Baga Said...
  3. Kel Said...
  4. Jerzy Said...
  5. Jeb Bush Said...
  6. Gautam Said...

Leave a reply

Connect With CodexWorld