How To Make A Simple CRUD Application Using PHP

Tutorial to Create PHP CRUD application

CRUD applications are fundamental in web development and are used for managing data in databases. In this step-by-step tutorial you will learn how to create a basic CRUD (Create, Read, Update, Delete) application using PHP with MySQL Database.

Steps to Create the PHP CRUD Application With MySQL DB

Here we will use PHP MySQLi OOP to interact with the database. At the end other two versions of code (MySQLi and PDO) are provided.

Step 1: Setting Up the Database

Start by creating a MySQL database where we will store the users name and email. You can use phpMyAdmin or any other MySQL client to create a new database. Let’s name our database crud_demo, and within this database, create a table named users with the following structure:

  • Database name: crud_demo
  • Table name: users

Use the following SQL code to create the users table and its columns:

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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Step 2: Setup the Project Directory

Go to your localhost directory which is htdocs or www depending on what software you are using. Now inside localhost directory create a folder called php-crud (this is our project folder).

In the “php-crud” folder we need to create the following files:

php-crud/
├── db_connection.php
├── nav-links.php
├── create.php
├── read.php
├── update.php
├── delete.php
└── style.css

Step 3: Make the Database Connection “db_connection.php”

In the db_connection.php file, establish a connection to the MySQL database using PHP’s mysqli extension. Here’s an example code snippet:

<?php
// Database configuration
$db_host = "localhost"; // Hostname where the database server is running
$db_user = "root";      // Database username
$db_pass = "";          // Database password
$db_name = "crud_demo";      // Database name

// Attempt to establish a connection to the database
$connection = new mysqli($db_host, $db_user, $db_pass, $db_name);

// Check if connection was successful
if ($connection->connect_errno) {
    // If connection failed, print an error message and exit the script
    echo "Connection Failed" . mysqli_connect_error();
    exit;
}

Step 4: Implement Page Navigation “nav-links.php”

For implementing navigation between the pages we create the nav-links.php that contains two navigation links, and then later 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>

Step 5: “create.php” For Inserting a New User in the Database

The following create.php code handles inserting new user data into the users database and provides feedback to the user based on the success or failure of the operation.

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

// Function to insert data into the database
function insertData(string $user_name, string $user_email): array
{
    require './db_connection.php'; // Including database connection file
    $name = trim(htmlspecialchars($user_name)); // Sanitizing and trimming input name
    $email = trim(htmlspecialchars($user_email)); // Sanitizing and trimming input email

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

    // Checking if the email already exists in the 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 into the database
    $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, 'Oops something is going wrong!');
}

// Handling form submission
if (isset($_POST['name']) && isset($_POST['email'])) {
    $result = insertData($_POST['name'], $_POST['email']);
    if ($result['success']) {
        $success = $result['msg']; // Success message
    } else {
        $error = $result['msg']; // Error message
    }
}
?>
<!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.replaceState(null, null, window.location.href);
        }
    </script>
</body>

</html>

Step 6: “read.php” Fetching and Displaying the Users

The read.php code provides a simple interface for reading user data from the database and offers options to edit or delete each user entry.

<?php
// Include the database connection file
require './db_connection.php';

// Fetching All the users from the database
$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>
    <!-- Include CSS stylesheet -->
    <link rel="stylesheet" href="./style.css">
</head>

<body>
    <main class="container">
        <h1>Read Data</h1>
        <section class="user-list">
            <!-- Display users if there are any -->
            <?php if (count($allUsers) > 0) :
                foreach ($allUsers as $user) : ?>
                    <ul>
                        <li>
                            <span class="list-wrap">
                                <!-- Display user's name and email -->
                                <span><?= $user['name'] ?><br><em><?= $user['email'] ?></em></span>
                                <!-- Links to edit and delete user -->
                                <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 : ?>
                <!-- Display message if no users found -->
                <p>Please Insert Some Users! 😊</p>
            <?php endif; ?>
        </section>
        <!-- Include navigation links -->
        <ul class="nav-links">
            <?php include_once('./nav-links.php'); ?>
        </ul>
    </main>
    <!-- JavaScript for handling delete confirmation -->
    <script>
        const deleteBtns = document.querySelectorAll('a.del');
        // Function to handle delete confirmation
        function deleteUser(e) {
            e.preventDefault();
            // Ask for delete confirmation
            if (confirm('Are you sure?')) {
                window.location.href = e.target.href;
            }
        }
        // Attach event listeners to delete buttons
        deleteBtns.forEach((el) => {
            el.onclick = (e) => deleteUser(e);
        });
    </script>
</body>

</html>

Step 7: “update.php” Update Info of an Existing User

The update.php allows users to update their information in the database and provides feedback on the success or failure of the update operation.

<?php
// Check if ID is set in the URL parameters
if (!isset($_GET['id'])) {
    // Redirect to the read.php page if ID is not set
    header("Location: read.php");
    exit;
}

// Include the database connection file
require './db_connection.php';

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

// Function to update user information
function updateUser(mysqli $connection, int $id, string $user_name, string $user_email): array
{
    // Sanitize and validate input data
    $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.");
    }

    // Check if the email already exists in the 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.");
    }

    // Update user information in the database
    $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!');
}

// Get the user ID from the URL and validate it
$userID = trim($_GET['id']);
if (empty($userID) || !is_numeric($userID)) {
    header("Location: read.php");
    exit;
}

// If the form is submitted, update the user
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'];
    }
}

// Fetch the user details from the database
$query = $connection->prepare("SELECT * FROM `users` WHERE `id`=?");
$query->bind_param('i', $userID);
$query->execute();
$result = $query->get_result();
$user = $result->fetch_assoc();

// If user is not found, redirect to read.php
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>
    <!-- Prevent form resubmission on page refresh -->
    <script>
        if (window.history.replaceState) {
            window.history.replaceState(null, null, window.location.href);
        }
    </script>
</body>

</html>

Step 8: “delete.php” Delete a User from the Database by ID

The delete.php serves the purpose of deleting a user from the database based on the “id” parameter provided in the URL, ensuring that the operation is executed securely and redirecting the user appropriately after deletion.

<?php
// Check if the 'id' parameter is set in the URL
if (isset($_GET['id'])) :

    // Include the database connection file
    require './db_connection.php';

    // Get and sanitize the user ID from the URL
    $userID = trim($_GET['id']);

    // Validate the user ID
    if (empty($userID) || !is_numeric($userID)) {
        // Redirect to read.php if the ID is invalid
        header("Location: read.php");
        exit;
    }

    // Delete the user from the database using the provided ID
    $query = $connection->query("DELETE FROM `users` WHERE `id`='$userID'");

endif;

// Redirect to read.php after deleting the user
header("Location: read.php");
exit;

Step 9: “style.css” CSS Stylesheet for this PHP CRUD Application

@charset "UTF-8";
@import url("https://fonts.googleapis.com/css2?family=Noto+Sans:wght@400;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, 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, 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, 0.1);
    border-radius: 3px;
    cursor: pointer;
    width: 100%;
    text-align: center;
    transition: 0.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, 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, 0.1);
    background-color: #f2f2f2;
    border-radius: 3px;
}

.user-list a {
    display: inline-block;
    border: 1px solid rgba(0, 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%);
}

Step 10: Test this PHP CRUD App

Now open http://localhost/php-crud/create.php to test your application, make sure your server and database is running. If you have done all the steps correctly you should see the following result:

PHP MySQL CRUD application

Download the Source Code:

Here is the Git repository link of the php-crud app written in all versions (MySQLi, MySQLi OOP, and PDO).

Download the Code

Leave a Reply

Your email address will not be published. Required fields are marked *

We use cookies to ensure that we give you the best experience on our website. Privacy Policy