How to Create PHP CRUD Application with MySQL DB?

In this tutorial, you will learn how you can create a simple CRUD application using PHP MySQLi with MySQL database. And through this application, you can perform a basic user Insert, Fetch, Update, and Delete operation.

See Demo

Follow the below steps to create this PHP CRUD Application:

1. MySQL Database and table creation.

First, we need to create a database and database table where we save the users’ data or records.

In the database, we will save only two information of a user – name and email.

Database Name – crud_app
Table Name – users

So then, open your phpMyAdmin and create a database called crud_app, and then use the following SQL code to create the users table and structure of the table.

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  email varchar(50) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY email (email)
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

2. Creation of PHP CRUD App.

In this step, we will create the app files. In this step, we will create the app files. So go to your wamp www directory or XAMPP htdocs folder, and here create a new folder called php-crud (this is the main app folder).

If you are curious to know what PHP files we have to create to build this CRUD application, see the following image and folder structure tree.

php-crud/
 ├─ db_connection.php
 ├─ delete.php
 ├─ edit.php
 ├─ fetch-data.php
 ├─ index.php
 ├─ insert-data.php
 ├─ style.css
 ├─ update.php

2.1. db_connection.php

This db_connection.php is for making the database connection.

<?php
 $host = "localhost";
 $db_user = "root";
 $db_password = "";
 $db_name = "crud_app";
 // Create connection
 $conn = mysqli_connect($host, $db_user, $db_password, $db_name);
 // Check connection
 if (!$conn) {
     die("Connection failed: " . mysqli_connect_error());
 }

2.2. insert-data.php

This insert-data.php is for inserting a new user into the database.

<?php
function insertData($conn, $u_name, $u_email)
{
    $u_name = trim(mysqli_real_escape_string($conn, htmlspecialchars($u_name)));
    $u_email = trim(mysqli_real_escape_string($conn, htmlspecialchars($u_email)));

    // IF NAME OR EMAIL IS EMPTY
    if (empty($u_name) || empty($u_email)) {
        return 'Please fill all required fields.';
    }
    //IF EMAIL IS NOT VALID
    elseif (!filter_var($u_email, FILTER_VALIDATE_EMAIL)) {
        return 'Invalid email address.';
    } else {
        $check_email = mysqli_query($conn, "SELECT `email` FROM `users` WHERE `email` = '$u_email'");
        // IF THE EMAIL IS ALREADY IN USE
        if (mysqli_num_rows($check_email) > 0) {
            return 'This email is already registered. Please try another.';
        }

        // INSERTING THE USER DATA
        $query = mysqli_query($conn, "INSERT INTO `users`(`name`,`email`) VALUES('$u_name','$u_email')");
        // IF USER INSERTED
        if ($query) {
            return true;
        }
        return 'Opps something is going wrong!';
    }
}

2.3. fetch-data.php

The fetch-data.php is for fetching users from the database.

In this file you can see two functions –

fetchUsers() – For fetching all users.
fetchUser() – Fetching a single user by the id of the user.

<?php
 // FETCH ALL USERS
 function fetchUsers($conn){
     $query = mysqli_query($conn,"SELECT * FROM `users`");
     return mysqli_fetch_all($query,MYSQLI_ASSOC);
 };
 // FETCH SINGLE USER BY ID
 function fetchUser($conn, $id){
     $id = mysqli_real_escape_string($conn,$id);
     $query = mysqli_query($conn,"SELECT * FROM `users` WHERE ``id`='$id'");
     $data = mysqli_fetch_assoc($query);
     if(!count($data)){
         header('Location: index.php');
         exit;
     }
     return $data;
 }

2.4. index.php

This is the home page of the CRUD app where you can see the users list, insert a new user.

At the bottom of the index.php, you can see a piece of JS code, this JS code is for getting confirmation for deleting the user.

<?php
require_once './db_connection.php';
require_once './fetch-data.php';
require_once './insert-data.php';
$all_user = array_reverse(fetchUsers($conn));

if (isset($_POST['u_name']) && isset($_POST['u_email'])) {
    $insert_data = insertData($conn, $_POST['u_name'], $_POST['u_email']);
    if ($insert_data === true) {
        header('Location: index.php');
        exit;
    }
}
?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Simple PHP CRUD Application</title>
    <link rel="stylesheet" href="./style.css">
</head>

<body>

    <div class="container">
        <header class="header">
            <h1 class="title">PHP CRUD Application</h1>
            <p>By <a href="//www.w3jar.com">w3jar.com</a></p>
        </header>
        <div class="wrapper">
            <div class="form">
                <form method="POST">
                    <label for="userName">Full Name</label>
                    <input type="text" name="u_name" id="userName" placeholder="Name" autocomplete="off" required>
                    <label for="userEmail">Email</label>
                    <input type="email" name="u_email" id="userEmail" placeholder="Email" autocomplete="off" required>
                    <?php if (isset($insert_data) && $insert_data !== true) {
                        echo '<p class="msg err-msg">' . $insert_data . '</p>';
                    }
                    ?>
                    <input type="submit" value="Submit">
                </form>
            </div>
            <div class="user-list">
                <?php if (count($all_user) > 0) : ?>
                    <table>
                        <tbody>
                            <tr>
                                <th>Name</th>
                                <th>Email</th>
                                <th>Action</th>
                            </tr>
                            <?php foreach ($all_user as $user) :
                                $id = $user['id'];
                                $name = $user['name'];
                                $email = $user['email'];
                            ?>
                                <tr>
                                    <td><?php echo $name; ?></td>
                                    <td><?php echo $email; ?></td>
                                    <td>
                                        <a href="edit.php?id=<?php echo $id; ?>" class="edit">Edit</a>&nbsp;|
                                        <a href="delete.php?id=<?php echo $id; ?>" class="delete delete-action">Delete</a>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                <?php else : ?>
                    <h2>No records found. Please insert some records.</h2>
                <?php endif; ?>
            </div>
        </div>
    </div>

    <script>
        var delteAction = document.querySelectorAll('.delete-action');
        delteAction.forEach((el) => {
            el.onclick = function(e) {
                e.preventDefault();
                if (confirm('Are you sure?')) {
                    window.location.href = e.target.href;
                }
            }
        });
    </script>

</body>

</html>

2.5. update.php

The update.php is for updating an existing user.

<?php
function updateUser($conn, $id, $u_name, $u_email)
{

    $id = trim(mysqli_real_escape_string($conn, $id));
    $u_name = trim(mysqli_real_escape_string($conn, htmlspecialchars($u_name)));
    $u_email = trim(mysqli_real_escape_string($conn, htmlspecialchars($u_email)));

    // IF NAME OR EMAIL IS EMPTY
    if (empty($u_name) || empty($u_email)) {
        return 'Please fill all required fields.';
    }
    //IF EMAIL IS NOT VALID
    elseif (!filter_var($u_email, FILTER_VALIDATE_EMAIL)) {
        return 'Invalid email address.';
    } else {
        $check_email = mysqli_query($conn, "SELECT `email` FROM `users` WHERE `email` = '$u_email' AND `id`!='$id'");
        // IF THE EMAIL IS ALREADY IN USE
        if (mysqli_num_rows($check_email) > 0) {
            return 'This email is already registered. Please try another.';
        }

        // UPDATE USER DATA
        $query = mysqli_query($conn, "UPDATE `users` SET `name`='$u_name', `email`='$u_email' WHERE `id`='$id'");
        // IF USER UPDATED
        if ($query) {
            return true;
        }
        return 'Opps something is going wrong!';
    }
}

2.6. edit.php

The edit.php provides a user interface for updating an existing user.

<?php
require_once './db_connection.php';
require_once './fetch-data.php';
require_once './update.php';
if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
    header('Location: index.php');
    exit;
}

if (isset($_POST['u_name']) && isset($_POST['u_email'])) {

    $update_data = updateUser($conn, $_GET['id'], $_POST['u_name'], $_POST['u_email']);

    if ($update_data === true) {
        header('Location: index.php');
        exit;
    }
}

$theUser = fetchUser($conn, $_GET['id']);
?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Simple PHP CRUD Application</title>
    <link rel="stylesheet" href="./style.css">
</head>

<body>

    <div class="container">
        <header class="header">
            <h1 class="title">PHP CRUD Application</h1>
            <p>By <a href="//www.w3jar.com">w3jar.com</a></p>
        </header>
        <div class="wrapper edit-wrapper">
            <div class="form">
                <form method="POST">
                    <label for="userName">Full Name</label>
                    <input type="text" name="u_name" value="<?php echo htmlspecialchars($theUser['name']); ?>" id="userName" placeholder="Name" autocomplete="off" required>
                    <label for="userEmail">Email</label>
                    <input type="email" name="u_email" value="<?php echo htmlspecialchars($theUser['email']); ?>" id="userEmail" placeholder="Email" autocomplete="off" required>
                    <?php if (isset($update_data) && $update_data !== true) {
                        echo '<p class="msg err-msg">' . $update_data . '</p>';
                    }
                    ?>
                    <input type="submit" value="Update">
                </form>
            </div>
        </div>
    </div>

</body>

</html>

2.7. style.css

CSS stylesheet for beautifying the CRUD app.

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

body {
    font-family: system-ui, -apple-system, "Segoe UI", Roboto, "Helvetica Neue", Arial, "Noto Sans", "Liberation Sans", sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol", "Noto Color Emoji";
    margin: 0;
    padding: 50px;
    background-color: #f7f7f7;
    color: #222222;
}

input {
    font-size: 15px;
}


.container {
    max-width: 900px;
    margin: 0 auto;
    padding: 20px;
    background-color: white;
    box-shadow: 0 1px 2px 0 rgba(0, 0, 0, 0.05);
    border-radius: 3px;
}

.header {
    text-align: center;
}

.container .wrapper,
.form form {
    display: flex;
    flex-wrap: wrap;
}

.container .wrapper {
    margin-top: 30px;
    align-items: flex-start;
}

.container .edit-wrapper {
    justify-content: center;
}

.form {
    border: 1px solid #cccccc;
    flex-basis: 40%;
    padding: 20px;
    background-color: #f7f7f7;
}

.form form label {
    font-weight: bold;
}


.form form [type="text"],
.form form [type="email"] {
    width: 100%;
    padding: 10px;
    border: 1px solid #666666;
    border-radius: 2px;
}

.form form [type="text"] {
    margin-bottom: 5px;
}

.form form [type="submit"] {
    margin-top: 10px;
    padding: 7px 10px;
    cursor: pointer;
    border-radius: 2px;
    background: #0d6efd;
    color: white;
    border: 1px solid rgba(0, 0, 0, 0.1);
    box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1), 0 1px 2px 0 rgba(0, 0, 0, 0.06);
}

.msg {
    width: 100%;
    color: #dc3545;
    border: 1px solid #dc3545;
    border-radius: 3px;
    padding: 10px;
    margin-bottom: 0;

}

.user-list {
    padding-left: 20px;
    flex: 1;
}


table {
    border-collapse: collapse;
    width: 100%;
}

table th {
    background-color: #f7f7f7;
}

table th,
table td {
    border: 1px solid #cccccc;
    padding: 10px;
    text-align: center;
}

a.edit,
a.delete {
    background-color: #dc3545;
    color: white;
    text-decoration: none;
    font-size: 14px;
    padding: 3px 5px;
    border-radius: 2px;
    border: 1px solid rgba(0, 0, 0, 0.1);
}

a.edit {
    background-color: #0d6efd;
}

3. PHP CRUD application DEMO.

Download (GitHub)

How to create PHP CRUD application with MySQL DB?

Follow the below steps to create this PHP CRUD Application:

1. MySQL Database and table creation.

2. Creation of PHP CRUD App.

2.1. db_connection.php

2.2. insert-data.php

2.3. fetch-data.php

2.4. index.php

2.5. update.php

2.6. edit.php

2.7. style.css

3. PHP CRUD application DEMO.

Leave a ReplyCancel Reply

Follow the below steps to create this PHP CRUD Application:

1. MySQL Database and table creation.

2. Creation of PHP CRUD App.

2.1. db_connection.php

2.2. insert-data.php

2.3. fetch-data.php

2.4. index.php

2.5. update.php

2.6. edit.php

2.7. style.css

3. PHP CRUD application DEMO.

Related Posts

PHP Loops: While, Do While, For, ForEach Loop

What is PHP Array? Explained for Beginners

What is PHP Function? Explained for Beginners

Leave a ReplyCancel Reply