crud operations in php using mysqli – PHP Crud Operations – Select, Insert, Update, Delete – PHP Tutorial For Beginners

Ajax, JavaScript, jQuery, Mysql, Mysqli, php Leave a comment

crud operations in php using mysqli : Create, Read, Update, Delete (CRUD) in PHP using mysqli Step by step Example.

crud operations in php using mysqli

PHP CRUD Operations with MySQLi Extension – CRUD is an acronym for Create, Read, Update, and Delete. CRUD operations are basic data manipulation for database.

Create a simple CRUD(Create, Read, Update, and Delete) application using PHP MySQLi with MySQL database.

files structure

create crud operations in php using mysqli files structure.

first_project_in_php/
├── index.php
├── memberCrud.php
├── memberAction.php
├── DB.class.php
├── bootstrap/
│   └── bootstrap.min.css
├── css/
│   └── style.css
└── images/

In this Basic OOP PHP CRUD Operation using PHP/MySQLi with Bootstrap tutorial we will learn about php from Scratch.

Create Database Table

First, we will see the Database Part.
SQL creates a members table

CREATE TABLE `members` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `email_address` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `mobile` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `confirms` 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;

Don’t Miss : CRUD operations in PHP with Bootstrap

Database Class

DB.class.php

<?php
/*
 * Database Class
 * This class is used for database related (connect, insert, update, and delete) operations
 * @author    Pakainfo.com
 * @url        http://www.pakainfo.com
 * @license    http://www.pakainfo.com/license
 */
class DB{
    private $dbHost     = "localhost";
    private $dbUsername = "root";
    private $dbPassword = "member#riu5787";
    private $dbName     = "pakainfo_v1";
    
    public function __construct(){
        if(!isset($this->db)){
            $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;
            }
        }
    }
    
    public function getRows($table, $controls = array()){
        $sql = 'SELECT ';
        $sql .= array_key_exists("select",$controls)?$controls['select']:'*';
        $sql .= ' FROM '.$table;
        if(array_key_exists("where",$controls)){
            $sql .= ' WHERE ';
            $i = 0;
            foreach($controls['where'] as $key => $value){
                $pre = ($i > 0)?' AND ':'';
                $sql .= $pre.$key." = '".$value."'";
                $i++;
            }
        }
        
        if(array_key_exists("order_by",$controls)){
            $sql .= ' ORDER BY '.$controls['order_by']; 
        }else{
            $sql .= ' ORDER BY id DESC '; 
        }
        
        if(array_key_exists("start",$controls) && array_key_exists("limit",$controls)){
            $sql .= ' LIMIT '.$controls['start'].','.$controls['limit']; 
        }elseif(!array_key_exists("start",$controls) && array_key_exists("limit",$controls)){
            $sql .= ' LIMIT '.$controls['limit']; 
        }
        
        $result = $this->db->query($sql);
        
        if(array_key_exists("return_type",$controls) && $controls['return_type'] != 'all'){
            switch($controls['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;
    }
    
    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('updated_at',$data)){
                $data['updated_at'] = 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;
        }
    }
    
    public function update($table, $data, $controls){
        if(!empty($data) && is_array($data)){
            $colvalSet = '';
            $whereSql = '';
            $i = 0;
            if(!array_key_exists('updated_at',$data)){
                $data['updated_at'] = 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($controls)&& is_array($controls)){
                $whereSql .= ' WHERE ';
                $i = 0;
                foreach($controls 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;
        }
    }
    
    public function delete($table, $controls){
        $whereSql = '';
        if(!empty($controls)&& is_array($controls)){
            $whereSql .= ' WHERE ';
            $i = 0;
            foreach($controls 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

memberAction.php

<?php
session_start();

require_once 'DB.class.php';
$db = new DB();

$tblName = 'members';

$redirectURL = 'index.php';

if(isset($_POST['memberSubmit'])){
    $name     = trim(strip_tags($_POST['name']));
    $email_address    = trim(strip_tags($_POST['email_address']));
    $mobile    = trim(strip_tags($_POST['mobile']));
    
    $errorMsg = '';
    if(empty($name)){
        $errorMsg .= '<p>Please enter your name.</p>';
    }
    if(empty($email_address) || !filter_var($email_address, FILTER_VALIDATE_EMAIL)){
        $errorMsg .= '<p>Please enter a valid email_address.</p>';
    }
    if(empty($mobile) || !preg_match("/^[-+0-9]{6,20}$/", $mobile)){
        $errorMsg .= '<p>Please enter a valid mobile number.</p>';
    }
    
    $memberDetails = array(
        'name' => $name,
        'email_address' => $email_address,
        'mobile' => $mobile
    );
    
    $membersInfo['memberDetails'] = $memberDetails;
    
    if(empty($errorMsg)){
        if(!empty($_POST['id'])){
            $condition = array('id' => $_POST['id']);
            $update = $db->update($tblName, $memberDetails, $condition);
            
            if($update){
                $membersInfo['confirms']['type'] = 'success';
                $membersInfo['confirms']['msg'] = 'Member data has been updated successfully.';
                
                unset($membersInfo['memberDetails']);
            }else{
                $membersInfo['confirms']['type'] = 'error';
                $membersInfo['confirms']['msg'] = 'Some problem occurred, please try again.';
                
                $redirectURL = 'memberCrud.php';
            }
        }else{
            $insert = $db->insert($tblName, $memberDetails);
            
            if($insert){
                $membersInfo['confirms']['type'] = 'success';
                $membersInfo['confirms']['msg'] = 'Member data has been added successfully.';
                
                unset($membersInfo['memberDetails']);
            }else{
                $membersInfo['confirms']['type'] = 'error';
                $membersInfo['confirms']['msg'] = 'Some problem occurred, please try again.';
                
                $redirectURL = 'memberCrud.php';
            }
        }
    }else{
        $membersInfo['confirms']['type'] = 'error';
        $membersInfo['confirms']['msg'] = '<p>Please fill all the mandatory fields.</p>'.$errorMsg;
        
        $redirectURL = 'memberCrud.php';
    }
    
    $_SESSION['membersInfo'] = $membersInfo;
}elseif(($_REQUEST['action_type'] == 'delete') && !empty($_GET['id'])){

    $condition = array('id' => $_GET['id']);
    $delete = $db->delete($tblName, $condition);
    
    if($delete){
        $membersInfo['confirms']['type'] = 'success';
        $membersInfo['confirms']['msg'] = 'Member data has been deleted successfully.';
    }else{
        $membersInfo['confirms']['type'] = 'error';
        $membersInfo['confirms']['msg'] = 'Some problem occurred, please try again.';
    }
    
    $_SESSION['membersInfo'] = $membersInfo;
}

header("Location:".$redirectURL);
exit();
?>

Bootstrap Library

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">

Read & Delete Records

index.php

<?php
session_start();

$membersInfo = !empty($_SESSION['membersInfo'])?$_SESSION['membersInfo']:'';

require_once 'DB.class.php';
$db = new DB();

$members = $db->getRows('members');

if(!empty($membersInfo['confirms']['msg'])){
    $confirmsMsg = $membersInfo['confirms']['msg'];
    $confirmsMsgType = $membersInfo['confirms']['type'];
    unset($_SESSION['membersInfo']['confirms']);
}
?>

<div class="container">
    <h2>crud operations in php using mysqli</h2>
	
    <!-- Display confirms message -->
    <?php if(!empty($confirmsMsg) && ($confirmsMsgType == 'success')){ ?>
    <div class="col-xs-12">
        <div class="alert alert-success"><?php echo $confirmsMsg; ?></div>
    </div>
    <?php }elseif(!empty($confirmsMsg) && ($confirmsMsgType == 'error')){ ?>
    <div class="col-xs-12">
        <div class="alert alert-danger"><?php echo $confirmsMsg; ?></div>
    </div>
    <?php } ?>
	
    <div class="row">
        <div class="col-md-12 head">
            <h5>Member</h5>
            <!-- Add link -->
            <div class="float-right">
                <a href="memberCrud.php" class="btn btn-success"><i class="plus"></i> New Member</a>
            </div>
        </div>
        
        <!-- List the members -->
        <table class="table table-striped table-bordered">
            <thead class="thead-dark">
                <tr>
                    <th>#</th>
                    <th>Name</th>
                    <th>Email Address</th>
                    <th>Mobile</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody id="memberDetails">
                <?php if(!empty($members)){ $count = 0; foreach($members as $row){ $count++; ?>
                <tr>
                    <td><?php echo $count; ?></td>
                    <td><?php echo $row['name']; ?></td>
                    <td><?php echo $row['email_address']; ?></td>
                    <td><?php echo $row['mobile']; ?></td>
                    <td>
                        <a href="memberCrud.php?id=<?php echo $row['id']; ?>" class="btn btn-warning">edit</a>
                        <a href="memberAction.php?action_type=delete&id=<?php echo $row['id']; ?>" class="btn btn-danger" onclick="return confirm('Are you sure to delete?');">delete</a>
                    </td>
                </tr>
                <?php } }else{ ?>
                <tr><td colspan="5">No member(s) found...</td></tr>
                <?php } ?>
            </tbody>
        </table>
    </div>
</div>

Create & Update Records

memberCrud.php

<?php
session_start();

$membersInfo = !empty($_SESSION['membersInfo'])?$_SESSION['membersInfo']:'';

$memberDetails = array();
if(!empty($_GET['id'])){
    include 'DB.class.php';
    $db = new DB();
    
    $controls['where'] = array(
        'id' => $_GET['id'],
    );
    $controls['return_type'] = 'single';
    $memberDetails = $db->getRows('members', $controls);
}
$memberDetails = !empty($membersInfo['memberDetails'])?$membersInfo['memberDetails']:$memberDetails;
unset($_SESSION['membersInfo']['memberDetails']);

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

if(!empty($membersInfo['confirms']['msg'])){
    $confirmsMsg = $membersInfo['confirms']['msg'];
    $confirmsMsgType = $membersInfo['confirms']['type'];
    unset($_SESSION['membersInfo']['confirms']);
}

?>

<div class="container">
    <h2><?php echo $actionLabel; ?> Member</h2>
    
    <?php if(!empty($confirmsMsg) && ($confirmsMsgType == 'success')){ ?>
    <div class="col-xs-12">
        <div class="alert alert-success"><?php echo $confirmsMsg; ?></div>
    </div>
    <?php }elseif(!empty($confirmsMsg) && ($confirmsMsgType == 'error')){ ?>
    <div class="col-xs-12">
        <div class="alert alert-danger"><?php echo $confirmsMsg; ?></div>
    </div>
    <?php } ?>
    
    <div class="row">
         <div class="col-md-6">
             <form method="post" action="memberAction.php">
                <div class="form-group">
                    <label>Name</label>
                    <input type="text" class="form-control" name="name" placeholder="Enter name" value="<?php echo !empty($memberDetails['name'])?$memberDetails['name']:''; ?>" >
                </div>
                <div class="form-group">
                    <label>Email</label>
                    <input type="text" class="form-control" name="email_address" placeholder="Enter email address" value="<?php echo !empty($memberDetails['email_address'])?$memberDetails['email_address']:''; ?>" >
                </div>
                <div class="form-group">
                    <label>Mobile</label>
                    <input type="text" class="form-control" name="mobile" placeholder="Enter mobile number" value="<?php echo !empty($memberDetails['mobile'])?$memberDetails['mobile']:''; ?>" >
                </div>
                
                <a href="index.php" class="btn btn-secondary">Back</a>
                <input type="hidden" name="id" value="<?php echo !empty($memberDetails['id'])?$memberDetails['id']:''; ?>">
                <input type="submit" name="memberSubmit" class="btn btn-success" value="Submit">
            </form>
        </div>
    </div>
</div>

I hope you get an idea about crud operations in php using mysqli.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

Read Also:  PHP codeigniter form validation Example

Related posts:

  1. PHP crud Mysqli Select Insert Update and Delete query
  2. PHP MySQL CRUD Create, Insert, Update and Delete operations
  3. PHP – MySQLi Insert Update Delete CRUD Operation using AngularJS
  4. PHP MySQLi CRUD Insert Update Delete
  5. PHP mysqli insert update delete for Dynemic query
  6. Laravel 6 crud Insert Update Delete operations Example
  7. vuejs crud Insert Update Delete operations Example and demo
  8. Laravel 5.7 Jquery Ajax CRUD(insert update delete)
  9. PHP OOPS – MySQL connecting Insert update delete select Fetch Data
  10. Insert Update Delete using PHP and mysql for Frontaccounting – crud
Read Also:  javascript countdown timer minutes seconds