PHP crud operation using MySQLi Method

By Chandan Tudu

October 23, 2019PHP

In this tutorial, you will learn how to perform PHP CRUD Operation using MySQLi.

Basically, in this tutorial, we are going to create a PHP CRUD (Create Read Update Delete) Application.

In this crud application, we will be able to insert data, read the data, update the data and delete the data.

If you want to see a DEMO of this PHP CRUD application then watch the below video.

DEMO

Follow the below steps to create PHP CRUD Application

1 – MySQL Database and table creation

First, we will create a database where we save our data or records, in this project we save only two records username and user email.

Database Name: crud_app
Table Name: `users`

After creating the Database use the below SQL code to create `users` table and the structure of the `users` table.

CREATE TABLE `users` (
  `id` int(11) UNSIGNED NOT NULL,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_email` (`user_email`);
  
ALTER TABLE `users`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

Tip:

If you are using the phpMyAdmin, then select the newly created Database and then go to the SQL tab above.

After that, paste the above SQL code in the Run SQL query box and then click on the go button.

2 – Files creation

First go to your wamp www directory or XAMPP htdocs folder.

And here you create a new folder and name it whatever you want, I named this folder crud_app.

After that go inside the newly created folder, and here you create some files.

Structure of the crud_app folder. — **Structure of the crud_app folde**r

db_connection.php

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db_name = "crud_app";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $db_name);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

index.php

<?php
require 'db_connection.php';
// function for getting data from database
function get_all_data($conn){
    $get_data = mysqli_query($conn,"SELECT * FROM `users`");
    if(mysqli_num_rows($get_data) > 0){
        echo '<table>
              <tr>
                <th>Username</th>
                <th>Email</th> 
                <th>Action</th> 
              </tr>';
        while($row = mysqli_fetch_assoc($get_data)){
           
            echo '<tr>
            <td>'.$row['username'].'</td>
            <td>'.$row['user_email'].'</td>
            <td>
            <a href="update.php?id='.$row['id'].'">Edit</a> |
            <a href="delete.php?id='.$row['id'].'">Delete</a>
            </td>
            </tr>';

        }
        echo '</table>';
    }else{
        echo "<h3>No records found. Please insert some records</h3>";
    }
}
?>
<!DOCTYPE html>
<html lang="">

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CRUD Application</title>
    <link rel="stylesheet" href="style.css">
</head>

<body>
    <div class="container">
      
       <!-- INSERT DATA -->
        <div class="form">
            <h2>Insert Data</h2>
            <form action="insert.php" method="post">
                <strong>Username</strong><br>
                <input type="text" name="username" placeholder="Enter your full name" required><br>
                <strong>Email</strong><br>
                <input type="email" name="email" placeholder="Enter your email" required><br>
                <input type="submit" value="Insert">
            </form>
        </div>
        <!-- END OF INSERT DATA SECTION -->
        <hr>
        <!-- SHOW DATA -->
        <h2>Show Data</h2>
        <?php 
        // calling get_all_data function
        get_all_data($conn); 
        ?>
        <!-- END OF SHOW DATA SECTION -->
    </div>
</body>

</html>

insert.php

<?php
require 'db_connection.php';

if(isset($_POST['username']) && isset($_POST['email'])){
    
    // check username and email empty or not
    if(!empty($_POST['username']) && !empty($_POST['email'])){
        
        // Escape special characters.
        $username = mysqli_real_escape_string($conn, htmlspecialchars($_POST['username']));
        $user_email = mysqli_real_escape_string($conn, htmlspecialchars($_POST['email']));
        
        //CHECK EMAIL IS VALID OR NOT
        if (filter_var($user_email, FILTER_VALIDATE_EMAIL)) {
            
            // CHECK IF EMAIL IS ALREADY INSERTED OR NOT
            $check_email = mysqli_query($conn, "SELECT `user_email` FROM `users` WHERE user_email = '$user_email'");
            
            if(mysqli_num_rows($check_email) > 0){    
                
                echo "<h3>This Email Address is already registered. Please Try another.</h3>";
                
            }else{
                
                // INSERT USERS DATA INTO THE DATABASE
                $insert_query = mysqli_query($conn,"INSERT INTO `users`(username,user_email) VALUES('$username','$user_email')");

                //CHECK DATA INSERTED OR NOT
                if($insert_query){
                    echo "<script>
                    alert('Data inserted');
                    window.location.href = 'index.php';
                    </script>";
                    exit;
                }else{
                    echo "<h3>Opps something wrong!</h3>";
                }
                
                
            }
            
            
        }else{
            echo "Invalid email address. Please enter a valid email address";
        }
        
    }else{
        echo "<h4>Please fill all fields</h4>";
    }
    
}else{
    // set header response code
    http_response_code(404);
    echo "<h1>404 Page Not Found!</h1>";
}
?>

update.php

<?php
require 'db_connection.php';
if(isset($_GET['id']) && is_numeric($_GET['id'])){
    
    $userid = $_GET['id'];
    $get_user = mysqli_query($conn,"SELECT * FROM `users` WHERE id='$userid'");
    
    if(mysqli_num_rows($get_user) === 1){
        
        $row = mysqli_fetch_assoc($get_user);
    
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Update data</title>
    <link rel="stylesheet" href="style.css">
</head>

<body>
     <div class="container">
      
       <!-- UPDATE DATA -->
        <div class="form">
            <h2>Update Data</h2>
            <form action="" method="post">
                <strong>Username</strong><br>
                <input type="text" autocomplete="off" name="username" placeholder="Enter your full name" value="<?php echo $row['username'];?>" required><br>
                <strong>Email</strong><br>
                <input type="email" autocomplete="off" name="email" placeholder="Enter your email" value="<?php echo $row['user_email'];?>" required><br>
                <input type="submit" value="Update">
            </form>
        </div>
        <!-- END OF UPDATE DATA SECTION -->
    </div>
</body>
</html>
<?php

    }else{
        // set header response code
        http_response_code(404);
        echo "<h1>404 Page Not Found!</h1>";
    }
    
}else{
    // set header response code
    http_response_code(404);
    echo "<h1>404 Page Not Found!</h1>";
}


/* ---------------------------------------------------------------------------
------------------------------------------------------------------------------ */


// UPDATING DATA

if(isset($_POST['username']) && isset($_POST['email'])){
    
    // check username and email empty or not
    if(!empty($_POST['username']) && !empty($_POST['email'])){
        
        // Escape special characters.
        $username = mysqli_real_escape_string($conn, htmlspecialchars($_POST['username']));
        $user_email = mysqli_real_escape_string($conn, htmlspecialchars($_POST['email']));
        
        //CHECK EMAIL IS VALID OR NOT
        if (filter_var($user_email, FILTER_VALIDATE_EMAIL)) {
            $user_id = $_GET['id'];
            // CHECK IF EMAIL IS ALREADY INSERTED OR NOT
            $check_email = mysqli_query($conn, "SELECT `user_email` FROM `users` WHERE user_email = '$user_email' AND id != '$user_id'");
            
            if(mysqli_num_rows($check_email) > 0){    
                
                echo "<h3>This Email Address is already registered. Please Try another.</h3>";
            }else{
                
                // UPDATE USER DATA               
                $update_query = mysqli_query($conn,"UPDATE `users` SET username='$username',user_email='$user_email' WHERE id=$user_id");

                //CHECK DATA UPDATED OR NOT
                if($update_query){
                    echo "<script>
                    alert('Data Updated');
                    window.location.href = 'index.php';
                    </script>";
                    exit;
                }else{
                    echo "<h3>Opps something wrong!</h3>";
                }
            }
        }else{
            echo "Invalid email address. Please enter a valid email address";
        }
        
    }else{
        echo "<h4>Please fill all fields</h4>";
    }   
}

// END OF UPDATING DATA

?>

delete.php

<?php
require 'db_connection.php';
if(isset($_GET['id']) && is_numeric($_GET['id'])){
    
    $userid = $_GET['id'];
    $delete_user = mysqli_query($conn,"DELETE FROM `users` WHERE id='$userid'");
    
    if($delete_user){
        echo "<script>
        alert('Data Deleted');
        window.location.href = 'index.php';
        </script>";
        exit;
    }else{
       echo "Opps something wrong!"; 
    }
}else{
    // set header response code
    http_response_code(404);
    echo "<h1>404 Page Not Found!</h1>";
}
?>

style.css

*,
 *::before,
 *::after {
     box-sizing: border-box;
     -webkit-box-sizing: border-box;
 }

 body {
     font-family: sans-serif;
     margin: 0;
     padding: 20px;
 }

 .container {
     max-width: 700px;
     margin: 0 auto;
     border: 3px solid #f2f2f2;
     padding: 10px;

 }

 h2 {
     text-transform: uppercase;
     margin: 10px 0;
     padding: 0;
     text-align: center;

 }

 input[type="text"],
 input[type="email"] {
     width: 100%;
     padding: 10px;
     font-size: 16px;
     margin: 3px 0;
     border: 1px solid #ccc;
 }

 input[type="submit"] {
     margin-top: 5px;
     padding: 6px 12px;
     font-size: 16px;
     cursor: pointer;
     background-color: dodgerblue;
     color: #FFF;
     outline: none;
     border: 1px solid rgba(0, 0, 0, .1);

 }

 table {
     font-family: arial, sans-serif;
     border-collapse: collapse;
     width: 100%;
 }

 td,
 th {
     border: 1px solid #dddddd;
     text-align: left;
     padding: 8px;
     color: #333;
 }

 tr:nth-child(even) {
     background-color: #f6f7f9;
 }

 a,
 a:visited {
     color: #0077c8;
     text-decoration: none;
 }

 a:hover {
     text-decoration: underline;
     color: red;
 }

Download the PHP crud operation application.

Download

PHP code for a login and registration page

Friend request system in PHP PDO

PHP File Upload Tutorial With Example

PHP Captcha code verification tutorial

2 Comments

phptechie says:

August 3, 2020 at 5:25 PM

This is very helpful article for me.I have been looking for such a content that helpful to me.

Reply
Akachi says:

October 15, 2020 at 3:26 AM

This is pretty cool helps me work on a big project i am making thanks so so much!!!!! 🙂

Reply

PHP crud operation using MySQLi Method

Related Tutorials

Follow the below steps to create PHP CRUD Application

1 – MySQL Database and table creation

2 – Files creation

2 Comments

Leave a Comment Cancel reply

Related Tutorials

Follow the below steps to create PHP CRUD Application

1 – MySQL Database and table creation

2 – Files creation

You May Also Like

2 Comments

Leave a Comment Cancel reply