Crud Operations In Php Using Mysqli

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

Contents

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

Free Live Chat for Any Issue

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.

Pakainfo

I am Jaydeep Gondaliya , a software engineer, the founder and the person running Pakainfo. I’m a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, JavaScript, jQuery, Laravel, CodeIgniter, VueJS, AngularJS and Bootstrap from the early stage.

Also Read This 👉 Top 5 Best php PDF Generation libraries