Visual representation of CRUD operations: Create, Read, Update, and Delete functionalities in a basic application.

7 min read

2 hours ago

Listen

CRUD operations (Create, Read, Update, Delete) are the foundation of most dynamic web applications. Learning how to implement these operations will give you a solid understanding of how to manipulate databases and create dynamic websites.

In this tutorial, I will guide you step-by-step to create a basic CRUD application using PHP, HTML, CSS, Vanilla JavaScript, and MySQL. We will use XAMPP, a local web server, to run our application. By the end of this tutorial, you will have a working web application that allows users to create, read, update, and delete student records.

Prerequisites

Before we begin, make sure you have the following tools:

XAMPP installed on your computer. Download XAMPP here.
A basic understanding of PHP, HTML, CSS, and JavaScript.
A code editor like Visual Studio Code, Web Storm, or Notepad++.

What You’ll Learn:

Setting up a local web server using XAMPP.
Creating a MySQL database and table for storing student data.
Building PHP scripts to handle CRUD operations.
Structuring a front-end interface with HTML and CSS.
Adding JavaScript for form validation and user interaction.

Step 1: Setting Up XAMPP

To run your PHP scripts locally, you need a web server. XAMPP is a free, easy-to-use server stack that includes Apache (web server), MySQL (database), and PHP (scripting language).

Installing XAMPP

Download XAMPP: Go to XAMPP’s official website and download the version that matches your operating system.
Install XAMPP: Follow the installation instructions. After installation, open the XAMPP control panel.
Start Apache and MySQL: From the XAMPP control panel, click on the Start buttons for Apache (the web server) and MySQL (the database).

Setting Up Your Project Directory

Navigate to your XAMPP installation directory (usually C:\xampp\htdocs on Windows or /Applications/XAMPP/htdocs on macOS).
Inside the htdocs folder, create a new folder named crud-app. This folder will hold all your project files.

Step 2: Creating the Database in phpMyAdmin

We need a database to store the student information. We’ll use MySQL, and XAMPP provides a handy tool called phpMyAdmin to manage MySQL databases easily.

Open your browser and go to phpMyAdmin

2. In phpMyAdmin, click on the Databases tab and create a new database. Name it crud_app.

3. After creating the database, go to the SQL tab and create a new table by executing the following SQL query:

CREATE TABLE students (  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,  name VARCHAR(100) NOT NULL,  email VARCHAR(100) NOT NULL,  course VARCHAR(100) NOT NULL);

This SQL statement creates a table called students with the following fields:

id: an auto-incrementing primary key.
name: stores the student's name.
email: stores the student’s email.
course: stores the course the student is enrolled in.

Step 3: Creating PHP Files for CRUD Operations

Now that our database is set up, let’s write the PHP code to interact with it. In your crud-app folder, create the following PHP files: config.php, create.php, read.php, update.php, and delete.php.

1. Setting Up Database Connection (config.php)

The first step is to set up a connection to our MySQL database. Create a file named config.php inside the crud-app folder.

<?php$servername = "localhost";$username = "root";  // Default MySQL username in XAMPP$password = "";  // No password by default in XAMPP$dbname = "crud_app";// Create connection$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}?>

This script connects to the MySQL database using mysqli. If the connection fails, an error message is displayed.

2. Create Operation (create.php)

The create operation allows us to add new student records. In this file, we’ll create an HTML form where users can input the student’s name, email, and course, and submit the data to be saved in the database.

<?phpinclude 'config.php';if ($_SERVER['REQUEST_METHOD'] === 'POST') {    $name = $_POST['name'];    $email = $_POST['email'];    $course = $_POST['course'];    $sql = "INSERT INTO students (name, email, course) VALUES ('$name', '$email', '$course')";    if ($conn->query($sql) === TRUE) {        echo "New record created successfully";    } else {        echo "Error: " . $sql . "<br>" . $conn->error;    }}?><!DOCTYPE html><html lang="en"><head>    <meta charset="UTF-8">    <meta name="viewport" content="width=device-width, initial-scale=1.0">    <title>Add Student</title>    <link rel="stylesheet" href="style.css"></head><body>    <h1>Add New Student</h1>    <form action="create.php" method="POST">        <label for="name">Name:</label><br>        <input type="text" name="name" required><br><br>        <label for="email">Email:</label><br>        <input type="email" name="email" required><br><br>        <label for="course">Course:</label><br>        <input type="text" name="course" required><br><br>        <button type="submit">Submit</button>    </form></body></html>

The form sends a POST request to create.php when submitted.
The PHP script checks if the form has been submitted, and if so, it retrieves the data from the form fields and inserts it into the students table using an SQL INSERT statement.

3. Read Operation (index.php)

The read operation retrieves and displays student records from the database. We’ll create a table in HTML to show the records.

<?phpinclude 'config.php';$sql = "SELECT * FROM students";$result = $conn->query($sql);?><!DOCTYPE html><html lang="en"><head>    <meta charset="UTF-8">    <meta name="viewport" content="width=device-width, initial-scale=1.0">    <title>Student Records</title>    <link rel="stylesheet" href="style.css"></head><body>    <h1>Student Records</h1>    <a href="create.php">Add New Student</a>    <table>        <thead>            <tr>                <th>Name</th>                <th>Email</th>                <th>Course</th>                <th>Actions</th>            </tr>        </thead>        <tbody>            <?php while($row = $result->fetch_assoc()) { ?>                <tr>                    <td><?php echo $row['name']; ?></td>                    <td><?php echo $row['email']; ?></td>                    <td><?php echo $row['course']; ?></td>                    <td>                        <a href="update.php?id=<?php echo $row['id']; ?>">Edit</a> |                         <a href="delete.php?id=<?php echo $row['id']; ?>">Delete</a>                    </td>                </tr>            <?php } ?>        </tbody>    </table></body></html>

The script fetches all student records from the students table using the SELECT * query.
The results are displayed in a table, with each student having options to edit or delete their record.

4. Update Operation (update.php)

The update operation allows us to edit existing records. When a user clicks the “Edit” link next to a student record, they will be taken to an edit form where they can update the student’s details.

<?phpinclude 'config.php';if (isset($_GET['id'])) {    $id = $_GET['id'];    $sql = "SELECT * FROM students WHERE id=$id";    $result = $conn->query($sql);    $row = $result->fetch_assoc();}if ($_SERVER['REQUEST_METHOD'] === 'POST') {    $name = $_POST['name'];    $email = $_POST['email'];    $course = $_POST['course'];    $sql = "UPDATE students SET name='$name', email='$email', course='$course' WHERE id=$id";    if ($conn->query($sql) === TRUE) {        header("Location: index.php");    } else {        echo "Error: " . $conn->error;    }}?><!DOCTYPE html><html lang="en"><head>    <meta charset="UTF-8">    <meta name="viewport" content="width=device-width, initial-scale=1.0">    <title>Update Student</title></head><body>    <h1>Edit Student</h1>    <form method="POST">        <label for="name">Name:</label><br>        <input type="text" name="name" value="<?php echo $row['name']; ?>" required><br><br>        <label for="email">Email:</label><br>        <input type="email" name="email" value="<?php echo $row['email']; ?>" required><br><br>        <label for="course">Course:</label><br>        <input type="text" name="course" value="<?php echo $row['course']; ?>" required><br><br>        <button type="submit">Update</button>    </form></body></html>

The script fetches the record based on the id parameter passed in the URL.
The form is pre-filled with the existing data, allowing the user to modify it.
After submitting the updated information, an UPDATE SQL query is executed to modify the record in the database.

5. Delete Operation (delete.php)

The delete operation allows us to remove student records from the database. This script is called when a user clicks the “Delete” link next to a student record.

<?phpinclude 'config.php';if (isset($_GET['id'])) {    $id = $_GET['id'];    $sql = "DELETE FROM students WHERE id=$id";    if ($conn->query($sql) === TRUE) {        header("Location: index.php");    } else {        echo "Error deleting record: " . $conn->error;    }}?>

The script takes the id parameter from the URL and deletes the corresponding record from the students table.
After deletion, the user is redirected back to the main index.php page.

Step 4: Designing the User Interface with HTML & CSS

To make the application visually appealing, we’ll style the user interface using CSS. Create a file named style.css inside the crud-app folder.

body {    font-family: Arial, sans-serif;    background-color: #f4f4f4;    margin: 0;    padding: 20px;}h1 {    text-align: center;    color: #1f1f1f;}table {    width: 100%;    border-collapse: collapse;    margin-top: 20px;}table, th, td {    border: 1px solid #ddd;}th, td {    padding: 12px;    text-align: left;}th {    background-color: #f4f4f4;}a {    color: #1f4ea5;    text-decoration: none;}a:hover {    text-decoration: underline;}button {    background-color: #1f4ea5;    color: #fff;    border: none;    padding: 10px 20px;    cursor: pointer;}button:hover {    background-color: #133a83;}

Step 5: Adding JavaScript for Client-Side Validation

Although we’re using HTML5 form validation attributes, adding a bit of JavaScript can improve the user experience. For example, before deleting a record, we can ask the user to confirm their action.

document.querySelectorAll('a.delete').forEach(function(link) {    link.addEventListener('click', function(event) {        if (!confirm('Are you sure you want to delete this record?')) {            event.preventDefault();        }    });});

Step 6: Running the Application

Start the XAMPP Apache server if it’s not already running.
Open your browser and go to http://localhost/crud-app/index.php.
You should now see a table with student records (if any exist) and options to create, read, update, and delete records.

Conclusion

In this tutorial, we’ve built a simple CRUD application using PHP, MySQL, HTML, CSS, and JavaScript. We learned how to:

Set up a local development environment using XAMPP.
Connect to a MySQL database using PHP.
Perform CRUD operations using SQL queries and PHP.
Create a basic user interface with HTML and CSS.
Add basic JavaScript interactivity.

This project serves as a strong foundation for more advanced web development. You can enhance this application by adding features such as input validation, better security practices (e.g., prepared statements to prevent SQL injection), and user authentication.

#css

Frontender`s Spectre

A Comprehensive Guide to Building a Basic CRUD Application Using PHP, HTML, CSS, and Vanilla JavaScript with XAMPP