PHP CRUD Operations with MySQLi Extension

By: CodexWorld | In: PHP | Last Updated: May 27, 2019

CRUD stands for Create, Read, Update and Delete. CRUD operations are commonly used to manipulate data in the database. Almost, all the dynamic web application uses Add, Edit, Update and Delete functionality for managing data with the database. 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 the example PHP CRUD application.

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

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

php_crud_with_mysql/
├── index.php
├── addEdit.php
├── userAction.php
├── DB.class.php
├── bootstrap/
│   └── bootstrap.min.css
├── css/
│   └── style.css
└── images/

Create Database Table

To store the user’s information 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(20) 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 MySQL.
getRows() – Fetch records from the database.
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;
    }
}

CRUD Operations (userAction.php)

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

Add / Edit Form Submission – Insert or update records to the database.
Delete Records – Remove data 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();

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

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

// Set default redirect url
$redirectURL = 'index.php';

if(isset($_POST['userSubmit'])){
    // Get form fields value
    $name     = trim(strip_tags($_POST['name']));
    $email    = trim(strip_tags($_POST['email']));
    $phone    = trim(strip_tags($_POST['phone']));
    
    // Fields validation
    $errorMsg = '';
    if(empty($name)){
        $errorMsg .= '<p>Please enter your name.</p>';
    }
    if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){
        $errorMsg .= '<p>Please enter a valid email.</p>';
    }
    if(empty($phone) || !preg_match("/^[-+0-9]{6,20}$/", $phone)){
        $errorMsg .= '<p>Please enter a valid phone number.</p>';
    }
    
    // Submitted form data
    $userData = array(
        'name' => $name,
        'email' => $email,
        'phone' => $phone
    );
    
    // Store the submitted field value in the session
    $sessData['userData'] = $userData;
    
    // Submit the form data
    if(empty($errorMsg)){
        if(!empty($_POST['id'])){
            // Update user data
            $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.';
                
                // Remote submitted fields value from session
                unset($sessData['userData']);
            }else{
                $sessData['status']['type'] = 'error';
                $sessData['status']['msg'] = 'Some problem occurred, please try again.';
                
                // Set redirect url
                $redirectURL = 'addEdit.php';
            }
        }else{
            // Insert user data
            $insert = $db->insert($tblName, $userData);
            
            if($insert){
                $sessData['status']['type'] = 'success';
                $sessData['status']['msg'] = 'User data has been added successfully.';
                
                // Remote submitted fields value from session
                unset($sessData['userData']);
            }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'] = '<p>Please fill all the mandatory fields.</p>'.$errorMsg;
        
        // Set redirect url
        $redirectURL = 'addEdit.php';
    }
    
    // Store status into the session
    $_SESSION['sessData'] = $sessData;
}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 respective page
header("Location:".$redirectURL);
exit();
?>

Bootstrap Library

The Bootstrap library is used to provide a better UI for styling the table, list, form fields, and links. Include the CSS file of the Bootstrap 4 library. If you don’t want to use Bootstrap for styling HTML table and form, you can omit it to include.

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/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 row id.

<?php
// Start session
session_start();

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

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

// Fetch the users data
$users = $db->getRows('users');

// 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">
    <h2>PHP CRUD Operations with MySQL</h2>
	
    <!-- Display status message -->
    <?php if(!empty($statusMsg) && ($statusMsgType == 'success')){ ?>
    <div class="col-xs-12">
        <div class="alert alert-success"><?php echo $statusMsg; ?></div>
    </div>
    <?php }elseif(!empty($statusMsg) && ($statusMsgType == 'error')){ ?>
    <div class="col-xs-12">
        <div class="alert alert-danger"><?php echo $statusMsg; ?></div>
    </div>
    <?php } ?>
	
    <div class="row">
        <div class="col-md-12 head">
            <h5>Users</h5>
            <!-- Add link -->
            <div class="float-right">
                <a href="addEdit.php" class="btn btn-success"><i class="plus"></i> New User</a>
            </div>
        </div>
        
        <!-- List the users -->
        <table class="table table-striped table-bordered">
            <thead class="thead-dark">
                <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 $row){ $count++; ?>
                <tr>
                    <td><?php echo $count; ?></td>
                    <td><?php echo $row['name']; ?></td>
                    <td><?php echo $row['email']; ?></td>
                    <td><?php echo $row['phone']; ?></td>
                    <td>
                        <a href="addEdit.php?id=<?php echo $row['id']; ?>" class="btn btn-warning">edit</a>
                        <a href="userAction.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 user(s) found...</td></tr>
                <?php } ?>
            </tbody>
        </table>
    </div>
</div>

Create & Update Records (addEdit.php)

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

Initially, an HTML form is displayed to provide the data fields value.
If the 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.
After the form submission, the form data is posted to the userAction.php file to insert/update record in the users table.

<?php
// Start session
session_start();

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

// Get user data
$userData = array();
if(!empty($_GET['id'])){
    // Include and initialize DB class
    include 'DB.class.php';
    $db = new DB();
    
    // Fetch the user data
    $conditions['where'] = array(
        'id' => $_GET['id'],
    );
    $conditions['return_type'] = 'single';
    $userData = $db->getRows('users', $conditions);
}
$userData = !empty($sessData['userData'])?$sessData['userData']:$userData;
unset($_SESSION['sessData']['userData']);

$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">
    <h2><?php echo $actionLabel; ?> User</h2>
    
    <!-- Display status message -->
    <?php if(!empty($statusMsg) && ($statusMsgType == 'success')){ ?>
    <div class="col-xs-12">
        <div class="alert alert-success"><?php echo $statusMsg; ?></div>
    </div>
    <?php }elseif(!empty($statusMsg) && ($statusMsgType == 'error')){ ?>
    <div class="col-xs-12">
        <div class="alert alert-danger"><?php echo $statusMsg; ?></div>
    </div>
    <?php } ?>
    
    <div class="row">
         <div class="col-md-6">
             <form method="post" action="userAction.php">
                <div class="form-group">
                    <label>Name</label>
                    <input type="text" class="form-control" name="name" placeholder="Enter 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" placeholder="Enter 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" placeholder="Enter contact number" value="<?php echo !empty($userData['phone'])?$userData['phone']:''; ?>" >
                </div>
                
                <a href="index.php" class="btn btn-secondary">Back</a>
                <input type="hidden" name="id" value="<?php echo !empty($userData['id'])?$userData['id']:''; ?>">
                <input type="submit" name="userSubmit" class="btn btn-success" value="Submit">
            </form>
        </div>
    </div>
</div>

PHP CRUD Operations with Search and Pagination

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 MySQL. 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.

Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request

Download Source Code

Send Email with Attachment in PHP

Pagination in PHP with MySQL

8 Comments

Newbie Said...

I tired. Update and insert not working for me :(.
I change this script.
public function change($table, $coloumn, $conditions){
$queryClass = “UPDATE “.$table.” SET “.$coloumn.” WHERE $conditions”;
$changeClass = $this->db->query($queryClass);
return $changeClass?$this->db->affected_rows:false;
}

#===
$changed = “product_status=’$getChange'”;
$whereId = “id = $postId “;
$update = $db->change(‘products’, $changed, $whereId);
if($update > 0){
echo alert(‘Change status is Successfully‘, ‘success’);
}else{
echo alert(‘Somethink went wrong to change status.’, ‘danger’);
}

June 3, 2023 at 6:25 AM
Tom King Said...

Its great code that I’ve been using for a couple of years. Licensed user. My question is PHP 8.2 is giving a warning about line 22 in the DBclass.php file.
“Deprecated: Creation of dynamic property DB::$db is deprecated”

Whenever PHP 9 comes out it will throw a fatal error. The fix for now is to add this line above the DBclass
#[AllowDynamicProperties]
class DB{

March 21, 2023 at 8:30 PM
Addisu Said...

Nice tutor, Thanks

November 4, 2020 at 8:17 AM
Kowai Said...

how can select multi tables

December 23, 2019 at 4:58 AM
Vinod Verma Said...

How can show mysqli data show randomly

June 24, 2019 at 12:07 AM
Alvaro Fuenzalida Said...

has anyone worked around this:
when we do the update or insert, how do we treat numbers or null values? does don’t go without quotes, same as values that can’t be empty, but rather null. thanks!

April 19, 2019 at 3:48 PM
Alonso Sirenio Said...

how can i do if i want to search a user for name and show in the table

January 26, 2018 at 8:46 PM

CodexWorld Said...

For search functionality, see this tutorial – https://www.codexworld.com/server-side-filtering-jquery-ajax-php-mysql/

January 28, 2018 at 8:47 PM

PHP CRUD Operations with MySQLi Extension

Create Database Table

Database Class (DB.class.php)

CRUD Operations (userAction.php)

Bootstrap Library

Read & Delete Records (index.php)

Create & Update Records (addEdit.php)

Conclusion

RELATED TUTORIALS

8 Comments

Leave a reply

Cancel

TRENDING TUTORIALS

TOPICSall topics

LATEST HOW TO GUIDES

ABOUT US

Contact

Terms

Extra Links