PHP CRUD Application: Step-by-Step Guide

Suman KhatriSuman Khatri
11 min read

In this blog, we’ll build a simple PHP CRUD application for managing employee records. CRUD stands for Create, Read, Update, and Delete, and this application will demonstrate all these operations using MySQL.

project Structure

config.php
create.php
delete.php
index.php
read.php
update.php

Setting Up the Database

create database:

CREATE DATABASE crud_db;

use database:

USE crud_db;

Create a MySQL database and a table named employees using the following structure:

CREATE TABLE employees (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(255) NOT NULL,
    salary INT(10) NOT NULL
);

Sample Data

Insert sample employee records into the database:

INSERT INTO employees (name, address, salary) 
VALUES ('Ram Bahadur', 'Kathmandu', 40000);

INSERT INTO employees (name, address, salary) 
VALUES ('Sita Kumari', 'Pokhara', 45000);

INSERT INTO employees (name, address, salary) 
VALUES ('Bikash Shrestha', 'Lalitpur', 60000);

INSERT INTO employees (name, address, salary) 
VALUES ('Puja Thapa', 'Bhaktapur', 55000);

INSERT INTO employees (name, address, salary) 
VALUES ('Ramesh Adhikari', 'Biratnagar', 50000);

Configuration File: config.php

Create a config.php file for database connection:

<?php
/* Database credentials */
$host = 'localhost';
$username = 'root';
$password = '';
$dbname = 'crud_db';

/* Attempt to connect to MySQL database */
$link = mysqli_connect($host, $username, $password, $dbname);

// Check connection
if (!$link) {
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>

Code Breakdown:

  1. Database Credentials:

    • $host: Specifies the database server (usually localhost for local development).

    • $username: Username for the MySQL database.

    • $password: Password for the MySQL user.

    • $dbname: Name of the database you want to connect to.

  2. Database Connection:

    • mysqli_connect(): Attempts to establish a connection to the MySQL database using the provided credentials.

  3. Error Handling:

    • If the connection fails (!$link), the script stops execution (die()) and outputs the error message from mysqli_connect_error().

Create Employee: create.php

The create.php file handles the creation of new employee records:

<?php
include "config.php";

$name = $address = $salary = ""; 

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $name = $_POST["name"];
    $address = $_POST["address"];
    $salary = $_POST["salary"];

    $sql = "INSERT INTO employees (name, address, salary) VALUES ('$name', '$address', '$salary')";

    if (mysqli_query($link, $sql)) {
        header("location: index.php");
        exit();
    } else {
        echo "Something went wrong. Please try again later.";
    }
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>Create Record</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>

<body>
    <div class="container my-5">
        <h2>Create Record</h2>
        <form action="<?= $_SERVER["PHP_SELF"]; ?>" method="post">
            <div class="mb-3">
                <label for="name" class="form-label">Name</label>
                <input type="text" id="name" name="name" class="form-control" value="<?= $name; ?>">
            </div>
            <div class="mb-3">
                <label for="address" class="form-label">Address</label>
                <textarea id="address" name="address" class="form-control"><?= $address; ?></textarea>
            </div>
            <div class="mb-3">
                <label for="salary" class="form-label">Salary</label>
                <input type="text" id="salary" name="salary" class="form-control" value="<?= $salary; ?>">
            </div>
            <button type="submit" class="btn btn-primary">Submit</button>
            <a href="index.php" class="btn btn-secondary ms-2">Cancel</a>
        </form>
    </div>
</body>

</html>

Explanation of the above script:

  1. Configuration File Inclusion:
    The code includes a separate file (config.php) where the database connection is defined. This helps maintain cleaner code and allows for reusability across multiple files.

  2. Variable Initialization:
    Variables are initialized to store user inputs for name, address, and salary. Initially, they are set to empty values.

  3. Handling Form Submission:
    The script checks if the form was submitted using the POST method. If submitted:

    • The input values from the form (name, address, salary) are retrieved and stored in variables.

    • An SQL query is constructed to insert the data into the employees table in the database.

    • The query is executed, and if successful, the user is redirected to the main page (index.php). If there’s an error, a message is displayed.

  4. HTML Form:
    The HTML form contains input fields for entering employee details (name, address, and salary). These fields are styled using Bootstrap for a clean and responsive design.

  5. Form Submission and Pre-filled Inputs:

    • The form is submitted to the same page using the POST method.

    • If there’s an error, the previously entered data remains visible in the input fields for convenience.

  6. Buttons:

    • A "Submit" button allows users to submit the form.

    • A "Cancel" button redirects users back to the main page without submitting the form.

Read Employee Records: read.php

The read.php file displays the details of a single employee record:

<?php
if (isset($_GET["id"]) && !empty($_GET["id"])) { 
    include "config.php";
    $id = $_GET["id"];
    $sql = "SELECT * FROM employees WHERE id = '$id'";
    $result = mysqli_query($link, $sql);

    if ($result && mysqli_num_rows($result) == 1) {
        $row = mysqli_fetch_assoc($result);
        $name = $row["name"];
        $address = $row["address"];
        $salary = $row["salary"];
    } else {
        die("Error: Record not found. <a href='index.php'>Go back</a>");
    }
} else {
    die("Error: Invalid request. <a href='index.php'>Go back</a>");
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>View Record</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container my-5">
        <h1 class="mb-4">View Record</h1>
        <div class="mb-3">
            <label class="form-label">Name</label>
            <p><strong><?= $name; ?></strong></p>
        </div>
        <div class="mb-3">
            <label class="form-label">Address</label>
            <p><strong><?= $address; ?></strong></p>
        </div>
        <div class="mb-3">
            <label class="form-label">Salary</label>
            <p><strong><?= $salary; ?></strong></p>
        </div>
        <a href="index.php" class="btn btn-primary">Back</a>
    </div>
</body>
</html>

Explanation of the above script:

  1. Check for id parameter:

    • The code checks if the id is present in the URL and not empty using isset() and !empty().

  2. Include database connection:

    • If id is valid, it includes the config.php file (which presumably contains the database connection).

  3. Prepare and execute query:

    • It fetches the employee details by querying the employees table using the id from the URL.

  4. Check if record exists:

    • If a record is found (mysqli_num_rows($result) == 1), it fetches the employee data (name, address, salary) into variables.

  5. Error handling:

    • If no record is found or id is missing, it shows an error message with a link to go back to the index page.

  6. Display data:

    • The HTML part displays the fetched employee details (name, address, salary) in a structured format.

  7. Back button:

    • A button is provided to go back to the index.php page.

Update Employee: update.php

The update.php file allows us to update employee information:

<?php
include "config.php";

if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST["id"])) {
    $id = $_POST["id"];
    $name = $_POST["name"];
    $address = $_POST["address"];
    $salary = $_POST["salary"];

    $sql = "UPDATE employees SET name='$name', address='$address', salary='$salary' WHERE id=$id";

    if (mysqli_query($link, $sql)) {
        header("location: index.php");
        exit();
    } else {
        echo "Something went wrong. Please try again later.";
    }
} elseif (isset($_GET["id"])) {
    $id = $_GET["id"];
    $sql = "SELECT * FROM employees WHERE id = $id";
    $result = mysqli_query($link, $sql);

    if ($result && mysqli_num_rows($result) == 1) {
        $row = mysqli_fetch_assoc($result);
        $name = $row["name"];
        $address = $row["address"];
        $salary = $row["salary"];
    } else {
        echo "Record not found.";
        exit();
    }
} else {
    echo "Invalid request.";
    exit();
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Update Record</title>
    <!-- Bootstrap 5 CDN -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        .wrapper {
            width: 600px;
            margin: 0 auto;
        }
    </style>
</head>
<body>
    <div class="wrapper">
        <h2 class="mt-5">Update Record</h2>
        <p>Edit the values and submit to update the employee record.</p>
        <form action="" method="post">
            <div class="mb-3">
                <label for="name" class="form-label">Name</label>
                <input type="text" id="name" name="name" class="form-control" value="<?= $name; ?>">
            </div>
            <div class="mb-3">
                <label for="address" class="form-label">Address</label>
                <textarea id="address" name="address" class="form-control"><?= $address; ?></textarea>
            </div>
            <div class="mb-3">
                <label for="salary" class="form-label">Salary</label>
                <input type="text" id="salary" name="salary" class="form-control" value="<?= $salary; ?>">
            </div>
            <input type="hidden" name="id" value="<?= $id; ?>"/>
            <button type="submit" class="btn btn-primary">Submit</button>
            <a href="index.php" class="btn btn-secondary ms-2">Cancel</a>
        </form>
    </div>
</body>
</html>

Explanation of the above script:

1. Database Connection:

  • The code starts by including the config.php file, which likely contains the database connection.

2. Handling POST Request:

  • Check if the form was submitted via POST:

    • It checks if the form is submitted using the POST method and if the id field is set.

  • Update Query:

    • If the form is submitted, it retrieves the form data (name, address, salary, and id).

    • It then executes an UPDATE SQL query to update the employee's record in the database.

  • Redirect After Successful Update:

    • If the query executes successfully, the user is redirected to index.php using the header() function.

3. Handling GET Request:

  • Fetch Record:

    • If the id is provided via the URL (GET method), it fetches the employee's details from the database.

  • Display Existing Data:

    • If a record is found, it populates the form with the current values for the employee's name, address, and salary.

  • Error Handling:

    • If no record is found, or if the id is missing, it displays an error message.

4. HTML Form:

  • Form Structure:

    • A form is presented to the user with fields to update the employee's name, address, and salary.

    • The form is pre-populated with the current values of the employee record fetched from the database.

  • Submit and Cancel:

    • The form has a "Submit" button to save changes, and a "Cancel" button to go back to index.php.

5. Hidden Field:

  • Pass id with the Form:

    • A hidden input field is included to pass the id of the employee with the form so the server knows which record to update.

Delete Employee: delete.php

The delete.php file handles the deletion of an employee record:

<?php
if(isset($_GET["id"]) && !empty($_GET["id"])){
    require_once "config.php";
    $id = $_GET["id"];
    $sql = "DELETE FROM employees WHERE id = $id";

    if(mysqli_query($link, $sql)){
        header("location: index.php");
        exit();
    } else {
        echo "Oops! Something went wrong. Please try again later.";
    }
    mysqli_close($link);
} else {
    header("location: index.php");
    exit();
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Delete Record</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-5">
        <div class="alert alert-danger">
            <p>The employee record has been successfully deleted.</p>
            <a href="index.php" class="btn btn-secondary">Go Back</a>
        </div>
    </div>
</body>
</html>

Explanation of the above script:

. Check for id Parameter in URL:

  • The code first checks if the id parameter is provided in the URL using isset() and !empty().

  • If the id is valid, it proceeds to delete the corresponding employee record from the database.

2. Delete Query:

  • The DELETE SQL query is prepared, where it deletes the employee record from the employees table based on the id provided in the URL.

  • The query is executed using mysqli_query($link, $sql).

3. Successful Deletion:

  • If the query executes successfully, the user is redirected to the index.php page using header("location: index.php"); and the script ends with exit() to stop further execution.

4. Error Handling:

  • If the query fails for any reason, an error message is displayed: "Oops! Something went wrong. Please try again later.".

5. Close Database Connection:

  • The database connection is closed using mysqli_close($link) after the query execution.

6. Redirect if No id:

  • If the id parameter is not provided in the URL, the user is redirected to index.php.

7. HTML Confirmation:

  • After successful deletion, an HTML page is displayed with a message: "The employee record has been successfully deleted."

  • The user can click a "Go Back" button to return to the main page (index.php).

Main Page: index.php

The index.php file displays all employee records and provides links to CRUD operations:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Dashboard</title>
    <!-- Bootstrap 5 CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        .wrapper {
            width: 600px;
            margin: 0 auto;
        }
    </style>
</head>
<body>
    <div class="wrapper">
        <div class="container-fluid">
            <div class="row">
                <div class="col-md-12">
                    <div class="mt-5 mb-3 clearfix">
                        <h2 class="float-start">Employees Details</h2>
                        <a href="create.php" class="btn btn-success float-end">Add New Employee</a>
                    </div>
                    <?php
                    include "config.php";

                    $sql = "SELECT * FROM employees";
                    $result = mysqli_query($link, $sql);
                    echo '<table class="table table-bordered table-striped">';
                    echo "<thead><tr><th>#</th><th>Name</th><th>Address</th><th>Salary</th><th>Action</th></tr></thead><tbody>";
                    while ($row = mysqli_fetch_array($result)) {
                        echo "<tr>";
                        echo "<td>" . $row['id'] . "</td>";
                        echo "<td>" . $row['name'] . "</td>";
                        echo "<td>" . $row['address'] . "</td>";
                        echo "<td>" . $row['salary'] . "</td>";
                        echo "<td>";
                        echo '<a href="read.php?id=' . $row['id'] . '" class="btn btn-sm btn-info">View</a> ';
                        echo '<a href="update.php?id=' . $row['id'] . '" class="btn btn-sm m-2 btn-warning">Update</a> ';
                        echo '<a href="delete.php?id=' . $row['id'] . '" class="btn btn-sm btn-danger">Delete</a>';
                        echo "</td>";
                        echo "</tr>";
                    }
                    echo "</tbody></table>";

                    mysqli_close($link);
                    ?>
                </div>
            </div>        
        </div>
    </div>
    <!-- Bootstrap 5 JS -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

Explanation of the above script:

1. HTML Structure:

  • Document Setup:

    • The document starts with a standard HTML structure, including a meta tag for character encoding (UTF-8), a title ("Dashboard"), and the Bootstrap CSS for styling.

  • Styling:

    • Custom styles are applied through a <style> tag. The .wrapper class is used to center the content and give it a width of 600px.

2. Bootstrap Integration:

  • The page uses Bootstrap 5 for responsive design and styling. This includes table styling and button styles (such as success, warning, danger).

  • The page also includes the Bootstrap JS at the end for any interactivity like dropdowns or modals (though it’s not specifically used in this code).

3. PHP Code for Fetching Data:

  • Database Connection:

    • The config.php file is included at the beginning to establish a connection to the database.

  • SQL Query:

    • An SQL query (SELECT * FROM employees) is executed to fetch all employee records from the database.

  • Displaying Data in Table:

    • A table is created with column headers: # (for the employee ID), Name, Address, Salary, and Action.

    • The while loop fetches each row from the result set ($result) and populates the table with employee data.

    • For each employee, the following information is displayed:

      • Employee ID: Displayed in the first column.

      • Name, Address, Salary: Displayed in subsequent columns.

      • Action: Provides links for actions on each employee (view, update, delete) as buttons.

        • The View button redirects to the read.php page.

        • The Update button redirects to the update.php page.

        • The Delete button redirects to the delete.php page.

  • Table Structure:

    • The table is styled using the table, table-bordered, and table-striped Bootstrap classes to make it look more readable and clean.

4. Close Database Connection:

  • After the data is fetched and displayed, the database connection is closed using mysqli_close($link).

SQL Queries for Testing

Here are some SQL queries you can use to test your database:

Select Queries

SELECT * FROM employees;
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees ORDER BY salary ASC;

Update Queries

UPDATE employees SET salary = 60000 WHERE id = 1;
UPDATE employees SET address = 'Lalitpur' WHERE name = 'Ram Bahadur';

Delete Queries

DELETE FROM employees WHERE id = 2;
DELETE FROM employees WHERE salary < 50000;

Conclusion

With this PHP CRUD application, you’ve learned how to:

  • Connect to a database.

  • Perform basic CRUD operations.

  • Use a structured PHP file system.

Feel free to modify and enhance this application for your specific needs!

⚠️ Note:
The code provided in this article is not secure and is intended for learning purposes only.

  • It does not prevent SQL Injection, as queries are directly interpolated with user input.
0
Subscribe to my newsletter

Read articles from Suman Khatri directly inside your inbox. Subscribe to the newsletter, and don't miss out.

PHPcrudbasicsMySQLeasy

Written by

Suman Khatri
Suman Khatri

I am Suman Khatri, a passionate full-stack developer specializing in React, Node.js, TypeScript, Prisma, and NestJS. With a strong foundation in modern web technologies, I craft dynamic and responsive applications that deliver seamless user experiences.