PHP CRUD Operations with MySQLi Extension


CRUD stands for Create, Read, Update and Delete. CRUD operations are commonly used to manipulate data in the database. Almost, all web application used Add, Edit, Update and Delete functionality for managing data. In this tutorial, we will create a simple PHP CRUD application with MySQL to perform create (insert), read (select), update, and delete operations.

The MySQLi Extension (MySQL Improved) and Object Oriented Programming (OOP) technique will be used to implement CRUD functionality in PHP. For the demonstration purpose, we will show you the CRUD operations to view, add, edit, and delete user data in PHP using MySQL. The following functionalities will be integrated into PHP CRUD application.

  • Fetch and display user’s data from the MySQL database.
  • Add user data to the MySQL database.
  • Edit and update user data.
  • Delete user data from the MySQL database.

Create Database Table

To store the user’s information a table needs to be created in the MySQL database. The following SQL creates a users table with some basic columns in the MySQL database.

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.class.php)

The DB class handles all the operations related to the database. For example, connect to the MySQL database server, insert, update, and delete records in the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your database server 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(!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']; 
        }
        
        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;
    }
}

CRUD Operations (userAction.php)

The userAction.php file is used to perform the CRUD operations using PHP & MySQL (DB class). The code is executed based on the two conditions:

  • Add / Edit Form Submit – Insert or update records to the database.
  • Query String in URL – Delete records from the database based on the action_type and id given in the query string of the link.

After the data manipulation, the status is stored using PHP SESSION and redirects back to the respective page.

<?php
//start session
session_start();

//load and initialize database class
require_once 'DB.class.php';
$db = new DB();

$tblName 'users';

//set default redirect url
$redirectURL 'index.php';

if(isset(
$_POST['userSubmit'])){
    if(!empty(
$_POST['name']) && !empty($_POST['email']) && !empty($_POST['phone'])){
        if(!empty(
$_POST['id'])){
            
//update data
            
$userData = array(
                
'name' => $_POST['name'],
                
'email' => $_POST['email'],
                
'phone' => $_POST['phone']
            );
            
$condition = array('id' => $_POST['id']);
            
$update $db->update($tblName$userData$condition);
            if(
$update){
                
$sessData['status']['type'] = 'success';
                
$sessData['status']['msg'] = 'User data has been updated successfully.';
            }else{
                
$sessData['status']['type'] = 'error';
                
$sessData['status']['msg'] = 'Some problem occurred, please try again.';
                
                
//set redirect url
                
$redirectURL 'addEdit.php';
            }
        }else{
            
//insert data
            
$userData = array(
                
'name' => $_POST['name'],
                
'email' => $_POST['email'],
                
'phone' => $_POST['phone']
            );
            
$insert $db->insert($tblName$userData);
            if(
$insert){
                
$sessData['status']['type'] = 'success';
                
$sessData['status']['msg'] = 'User data has been added successfully.';
            }else{
                
$sessData['status']['type'] = 'error';
                
$sessData['status']['msg'] = 'Some problem occurred, please try again.';
                
                
//set redirect url
                
$redirectURL 'addEdit.php';
            }
        }
    }else{
        
$sessData['status']['type'] = 'error';
        
$sessData['status']['msg'] = 'All fields are mandatory, please fill all the fields.';
        
        
//set redirect url
        
$redirectURL 'addEdit.php';
    }
    
    
//store status into the session
    
$_SESSION['sessData'] = $sessData;
    
    
//redirect to the list page
    
header("Location:".$redirectURL);
}elseif((
$_REQUEST['action_type'] == 'delete') && !empty($_GET['id'])){
    
//delete data
    
$condition = array('id' => $_GET['id']);
    
$delete $db->delete($tblName$condition);
    if(
$delete){
        
$sessData['status']['type'] = 'success';
        
$sessData['status']['msg'] = 'User data has been deleted successfully.';
    }else{
        
$sessData['status']['type'] = 'error';
        
$sessData['status']['msg'] = 'Some problem occurred, please try again.';
    }
    
    
//store status into the session
    
$_SESSION['sessData'] = $sessData;

    
//redirect to the list page
    
header("Location:".$redirectURL);
}
exit();
?>

Bootstrap Library

The Bootstrap library is used to provide a better UI. If you don’t want to use Bootstrap for styling HTML table and form, you can omit it to include.

<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">

Read & Delete Records (index.php)

In the index.php file, we will retrieve the records from the users table using DB class and list them in tabular format with Add, Edit, and Delete link.

  • The Add link redirects to the addEdit.php page to perform the Create operation.
  • The Edit link redirects to the addEdit.php page to perform the Update operation.
  • The Delete link redirects to the userAction.php file with action_type and id params. In userAction.php file, the record is deleted from the users table based on the id.
<?php
//start session
session_start();

//get session data
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:'';

//load and initialize database class
require_once 'DB.class.php';
$db = new DB();

//get users from database
$users $db->getRows('users',array('order_by'=>'id DESC'));

//get status message from session
if(!empty($sessData['status']['msg'])){
    
$statusMsg $sessData['status']['msg'];
    
$statusMsgType $sessData['status']['type'];
    unset(
$_SESSION['sessData']['status']);
}
?> <div class="container"> <?php if(!empty($statusMsg) && ($statusMsgType == 'success')){ ?> <div class="alert alert-success"><?php echo $statusMsg?></div> <?php }elseif(!empty($statusMsg) && ($statusMsgType == 'error')){ ?> <div class="alert alert-danger"><?php echo $statusMsg?></div> <?php ?> <div class="row"> <div class="panel panel-default users-content"> <div class="panel-heading">Users <a href="addEdit.php" class="glyphicon glyphicon-plus" ></a></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 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="addEdit.php?id=<?php echo $user['id']; ?>" class="glyphicon glyphicon-edit"></a> <a href="userAction.php?action_type=delete&id=<?php echo $user['id']; ?>" class="glyphicon glyphicon-trash" onclick="return confirm('Are you sure to delete?')"></a> </td> </tr> <?php endforeach; else: ?> <tr><td colspan="5">No user(s) found......</td></tr> <?php endif; ?> </tbody> </table> </div> </div> </div>

Create & Update Records (addEdit.php)

In the addEdit.php file, we will implement create and update form functionality.

  • Initially, the form data is submitted to the userAction.php file for inserting records in the users table.
  • If ID parameter exists on the URL, the existing user data will be retrieved from the database based on this ID and the form fields will be pre-filled. The data is submitted to the userAction.php file for updating existing records in the users table.
<?php
//start session
session_start();

//get session data
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:'';

//get user data
if(!empty($_GET['id'])){
    include 
'DB.class.php';
    
$db = new DB();
    
$conditions['where'] = array(
        
'id' => $_GET['id'],
    );
    
$conditions['return_type'] = 'single';
    
$userData $db->getRows('users'$conditions);
}

$actionLabel = !empty($_GET['id'])?'Edit':'Add';

//get status message from session
if(!empty($sessData['status']['msg'])){
    
$statusMsg $sessData['status']['msg'];
    
$statusMsgType $sessData['status']['type'];
    unset(
$_SESSION['sessData']['status']);
}
?> <div class="container"> <?php if(!empty($statusMsg) && ($statusMsgType == 'success')){ ?> <div class="alert alert-success"><?php echo $statusMsg?></div> <?php }elseif(!empty($statusMsg) && ($statusMsgType == 'error')){ ?> <div class="alert alert-danger"><?php echo $statusMsg?></div> <?php ?> <div class="row"> <div class="panel panel-default users-content"> <div class="panel-heading"><?php echo $actionLabel?> User <a href="index.php" class="glyphicon glyphicon-arrow-left"></a></div> <div class="panel-body"> <form method="post" action="userAction.php" class="form"> <div class="form-group"> <label>Name</label> <input type="text" class="form-control" name="name" value="<?php echo !empty($userData['name'])?$userData['name']:''?>"> </div> <div class="form-group"> <label>Email</label> <input type="text" class="form-control" name="email" value="<?php echo !empty($userData['email'])?$userData['email']:''?>"> </div> <div class="form-group"> <label>Phone</label> <input type="text" class="form-control" name="phone" value="<?php echo !empty($userData['phone'])?$userData['phone']:''?>"> </div> <input type="hidden" name="id" value="<?php echo $userData['id']; ?>"> <input type="submit" name="userSubmit" class="btn btn-success" value="SUBMIT"/> </form> </div> </div> </div> </div>

Conclusion

We have tried to show you the simple CRUD operations in PHP using MySQLi. Hope, it will help you to implement select, insert, update, and delete functionality in PHP and MySQLi. You can easily extend this CRUD functionality as per your requirement. We recommend you to check out PHP CRUD Operations without Page Refresh using Ajax tutorial to build CRUD application in a user-friendly way.

Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request

Recommended Tutorials For You

Leave a reply