How to Make PHP CRUD Application with MySQL DB?

PHP CRUD application is a compulsory project for beginners, that teaches you how PHP can interact with the MySQL database or MariaDB.

So in this tutorial, I will show you how to make a simple CRUD application using PHP with MySQL database.

What does CRUD mean?

CRUD stands for Create (insert), Read, Update, Delete. So here, you are going to learn how to use PHP to insert, read, update, and delete data into MySQL database or MariaDB.

Let’s make the PHP CRUD application

We know that PHP has three database APIs (MySQLi, OOP, and PDO), and you can build a CRUD application using any of them. But obviously, the code syntax would be different.

Don’t worry, here I will share the same CRUD application with three different database APIs.

Let’s start by creating the database –

1. MySQL Database Setup:

First, create a database called “crud“, you can name it whatever you want.

After that, create a table called “users” inside the crud database, which will have three columns – “id“, “name“, and “email“.

Use the following SQL code to create the users table and its structure.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Here is the folder structure of the CRUD application that we are going to build –

PHP MySQL CRUD application folder structure

2. Database connection:

db_connection.php file contains the database connection code.

<?php
$db_host = "localhost";
$db_user = "root";
$db_pass = "";
$db_name = "crud";

$connection = new mysqli($db_host,$db_user,$db_pass,$db_name);

if($connection->connect_errno){
    echo "Connection Failed".mysqli_connect_error();
    exit;
}

Now we will create the nav-links.php that contains two navigation links, and then, we include this file into the create.php, read.php, and update.php.

<li><a href="./create.php">Create</a></li>
<li><a href="./read.php">Read</a></li>

3. Creating or inserting data:

The create.php file contains the code to create or insert data through the HTML form.

<?php
function response(int $success, string $message) : array {
    return ["success" => $success, "msg" => $message];
}

function insertData(string $user_name, string $user_email) : array{
    require './db_connection.php';
    $name = trim(htmlspecialchars($user_name));
    $email = trim(htmlspecialchars($user_email));

    if (empty($name) || empty($email)) {
        return response(0, "Please fill all required fields.");
    }
    elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        return response(0, "Invalid email address.");
    }

    # Checking whether the email already exists in our database.
    $check_email = $connection->prepare("SELECT `email` FROM `users` WHERE `email`=?");
    $check_email->bind_param("s", $email);
    $check_email->execute();
    $check_email->store_result();
    if($check_email->num_rows !== 0){
        return response(0, "This email is already registered. Please try another.");
    }

    # inserting new data
    $query = $connection->prepare("INSERT INTO `users`(`name`,`email`) VALUES(?,?)");
    $query->bind_param("ss", $name, $email);

    if($query->execute()){
        return response(1,"User has been successfully inserted.");
    }

    return response(0,'Opps something is going wrong!');

}

if(isset($_POST['name']) && isset($_POST['email'])):
    $result = insertData($_POST['name'], $_POST['email']); 
    if($result['success']){
        $success = $result['msg'];
    }
    else{
        $error = $result['msg'];
    }
endif;
?>
<!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>PHP CRUD application</title>
    <link rel="stylesheet" href="./style.css">
</head>
<body>
    <main class="container">
        <h1>Create Data</h1>
        <form action="<?= $_SERVER["PHP_SELF"]; ?>" method="POST">
            <div>
                <label for="name">Name:</label>
                <input type="text" name="name" id="name" placeholder="Your name" required>
            </div>
            <div>
                <label for="email">Email:</label>
                <input type="text" name="email" id="email" placeholder="Your email" required>
            </div>
            <?php if(isset($success)){?><p class="success-msg"><?= $success ?></p><?php } ?>
            <?php if(isset($error)){?><p class="err-msg"><?= $error ?></p><?php } ?>
            <div>
                <button type="submit">Insert</button>
            </div>
        </form>
        <ul class="nav-links">
            <?php include_once('./nav-links.php'); ?>
        </ul>
    </main>
<script>
    // Preventing multiple form submissions on Page Refresh
    if(window.history.replaceState){
        window.history.replapceState(null, null, window.location.href);
    }
</script>
</body>
</html>

4. Fetching the inserted data to read:

The read.php contains the code to fetch all the existing users.

<?php
require './db_connection.php';
# Fetching All the users
$query = $connection->query("SELECT * FROM `users`");
$allUsers = $query->fetch_all(MYSQLI_ASSOC);
?><!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>PHP CRUD application - MySQLi OOP</title>
    <link rel="stylesheet" href="./style.css">
</head>
<body>
    <main class="container">
        <h1>Read Data</h1>
        <section class="user-list">
            <?php if(count($allUsers) > 0):
                foreach($allUsers as $user): ?>
            <ul>
                <li>
                    <span class="list-wrap">
                        <span><?= $user['name'] ?><br><em><?= $user['email'] ?></em></span>
                        <span>
                            <a href="./update.php?id=<?= $user['id'] ?>">Edit</a> 
                            <a href="./delete.php?id=<?= $user['id'] ?>" class="del">Delete</a>
                        </span>
                    </span>
                </li>
            </ul>
            <?php
                endforeach;
            else: ?>
            <p>Please Insert Some Users! 😊</p>
            <?php endif; ?>
        </section>
        <ul class="nav-links">
            <?php include_once('./nav-links.php'); ?>
        </ul>
    </main>
    <script>
        const deleteBtns = document.querySelectorAll('a.del');
        // When user click on the delete button
        function deleteUser(e){
            e.preventDefault();
            // delete confirmation
            if (confirm('Are you sure?')) {
                window.location.href = e.target.href;
            }
        }
        deleteBtns.forEach((el) => {
            el.onclick = (e) => deleteUser(e);
        });
    </script>
</body>
</html>

5. Updating an existing user:

Here is the update.php that contains the code to update an existing user.

<?php
if(!isset($_GET['id'])){
    header("Location: read.php");
    exit;
}
require './db_connection.php';

function response(int $success, string $message) : array {
    return ["success" => $success, "msg" => $message];
}

function updateUser(mysqli $connection, int $id, string $user_name, string $user_email) : array{
    $name = trim(htmlspecialchars($user_name));
    $email = trim(htmlspecialchars($user_email));

    if (empty($name) || empty($email)) {
        return response(0, "Please fill all required fields.");
    }
    elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        return response(0, "Invalid email address.");
    }

    # Checking whether the email already exists in our database.
    $check_email = $connection->prepare("SELECT `email` FROM `users` WHERE `email` = ? and `id` != ?");
    $check_email->bind_param("si", $email, $id);
    $check_email->execute();
    $check_email->store_result();
    if($check_email->num_rows !== 0){
        return response(0, "This email is already registered. Please try another.");
    }

    # Updating the user
    $query = $connection->prepare("UPDATE `users` SET `name`=?, `email`=? WHERE `id`=?");
    $query->bind_param("ssi", $name, $email, $id);

    if($query->execute()){
        return response(1, "User has been successfully updated.");
    }
    return response(0,'Opps something is going wrong!');
}

$userID = trim($_GET['id']);
if(empty($userID) || !is_numeric($userID)){
    header("Location: read.php");
    exit;
}

if(isset($_POST['name']) && isset($_POST['email'])):
    $result = updateUser($connection, $userID, $_POST['name'], $_POST['email']); 
    if($result['success']){
        $success = $result['msg'];
    }
    else{
        $error = $result['msg'];
    }
endif;

# Here you can use query instead of prepare
$query = $connection->prepare("SELECT * FROM `users` WHERE `id`=?");
$query->bind_param('i',$userID);
$query->execute();
$result = $query->get_result();
$user = $result->fetch_assoc();

if(is_null($user)){
    header("Location: read.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>PHP CRUD application</title>
    <link rel="stylesheet" href="./style.css">
</head>
<body>
    <main class="container">
        <h1>Update User</h1>
        <form action="<?= $_SERVER["PHP_SELF"]; ?>?id=<?= $userID ?>" method="POST">
            <div>
                <label for="name">Name:</label>
                <input type="text" name="name" id="name" value="<?= $user['name']; ?>" placeholder="Your name" required>
            </div>
            <div>
                <label for="email">Email:</label>
                <input type="text" name="email" id="email" value="<?= $user['email']; ?>" placeholder="Your email" required>
            </div>
            <?php if(isset($success)){?><p class="success-msg"><?= $success ?></p><?php } ?>
            <?php if(isset($error)){?><p class="err-msg"><?= $error ?></p><?php } ?>
            <div>
                <button type="submit">Update</button>
            </div>
        </form>
        <ul class="nav-links">
            <?php include_once('./nav-links.php'); ?>
        </ul>
    </main>
    <script>
        if(window.history.replaceState){
            window.history.replaceState(null, null, window.location.href);
        }
    </script>
</body>
</html>

6. Deleting an existing user:

<?php
if(isset($_GET['id'])):

    require './db_connection.php';
    
    $userID = trim($_GET['id']);

    if(empty($userID) || !is_numeric($userID)){
        header("Location: read.php");
        exit;
    }

    $query = $connection->query("DELETE FROM `users` WHERE `id`='$userID'");
    
endif;
header("Location: read.php");
exit;

Here is the CSS (style.css)

@charset "UTF-8";
@import url("https://fonts.googleapis.com/css2?family=Noto+Sans:[email protected];700&display=swap");
*,
*::before,
*::after {
  box-sizing: border-box !important;
  line-height: 1.5em;
}

html{
    font-size: 16px;
}

body {
    margin: 0;
    background-color: #f2f2f2;
    font-family: "Noto Sans", sans-serif;
}

h1{
    text-align: center;
    text-decoration: underline wavy;
    margin-bottom: 20px;
    margin-top: 0;

}

a{
    all:unset;
    cursor: pointer;
}
a:hover{
    text-decoration: underline;
}

.container{
    max-width: 450px;
    margin: 50px auto;
    padding: 20px 30px;
    background-color: #ffffff;
    border-radius: 3px;
    border: 1px solid rgba(0, 0, 0, .1);
}
input,button{
    font-size: 1rem;
    font-family: "Noto Sans", sans-serif;
    outline: none;
}

form input{
    width: 100%;
    padding: 10px;
    border-radius: 3px;
    border: 1px solid rgba(0, 0, 0, .4);
}
form input:hover,
form input:focus{
    border-color: #3D4CD9;
}

form label{
    font-weight: bold;
}

form div{
    margin: 7px 0;
}

form [type="submit"]{
    all: unset;
    margin-top: 5px;
    padding: 10px;
    background: #3D4CD9;
    color:white;
    border: 1px solid rgba(0, 0, 0, .1);
    border-radius: 3px;
    cursor: pointer;
    width: 100%;
    text-align: center;
    transition: .05s;
}
form [type="submit"]:hover{
    outline-width: 2px;
    outline:3px solid #7f89e6;
    outline-offset: 2px;
}

.nav-links{
    all: unset;
    margin: 20px 0;
    display: flex;
    flex-wrap: wrap;
    list-style-type: none;
    justify-content: space-around;
    background-color: #111111;
    border-radius: 3px;
}
.nav-links li{
    flex-grow: 1;
}

.nav-links a{
    display: block;
    padding: 10px;   
    text-transform: uppercase; 
    text-align: center;
    color: #e6e6e6;
}

.nav-links a:hover{
    background-color: rgba(0, 0, 0, .9);
}

.success-msg,
.err-msg{
    color: #cc0000;
    border: 1px solid #cc0000;
    border-radius: 3px;
    padding: 10px;
}

.success-msg{
    color: #009900;
    border-color: #009900;
}
.user-list ul{
    padding: 0;
    list-style-type: none;
}
.user-list .list-wrap{
    display: flex;
    align-items: center;
    justify-content: space-between;
    padding: 10px;
}

.user-list em{
    color: #666;
    font-size: 14px;
}

.user-list li:nth-child(odd){
    border: 1px solid rgba(0, 0, 0, .1);
    background-color: #f2f2f2;
    border-radius: 3px;
}

.user-list a{
    display: inline-block;
    border: 1px solid rgba(0, 0, 0, .1);
    border-radius: 3px;
    margin: 0 3px;
    padding: 0 5px;
    font-size: 14px;
    background-color: hsl(141, 53%, 42%);
    color: hsl(0, 0%, 98%);
}

.user-list a.del{
    background-color: hsl(348, 100%, 45%);
    color: hsl(0, 0%, 98%);
}

The above code is written in MySQLi OOP. Here is the Git repository link of other versions (MySQLi, PDO) –

Download

What does CRUD mean?

Let’s make the PHP CRUD application

1. MySQL Database Setup:

2. Database connection:

3. Creating or inserting data:

4. Fetching the inserted data to read:

5. Updating an existing user:

6. Deleting an existing user:

Here is the CSS (style.css)

Related Posts

How to Make Tab using HTML, CSS, and JavaScript?

How to send email from localhost in PHP?

How to Login with Google Account using PHP?