CRUD Operation in PHP

CRUD stands for Create, Read, Update, and Delete, the four basic operations for managing data in a database.

These operations are fundamental in any dynamic web application, allowing interaction with the database to perform essential tasks.

In this article, we will implement a simple CRUD system in PHP using MySQL as the database. This guide will walk you through setting up and executing each operation.

Prerequisites

1. PHP installed on your system (e.g., XAMPP, WAMP, or LAMP stack).
2. MySQL database set up.
3. A basic understanding of PHP and SQL.

Database Setup

Create a MySQL database and a table to store data.

CREATE DATABASE crud_example;

USE crud_example;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Project Structure

crud/
├── db.php
├── create.php
├── read.php
├── update.php
├── delete.php
└── index.php

Database Connection (db.php)

This file manages the connection to the database.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "crud_example";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Create Operation (create.php)

This file inserts data into the database.

<?php
require 'db.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $name = $_POST['name'];
    $email = $_POST['email'];
    $phone = $_POST['phone'];

    $sql = "INSERT INTO students (name, email, phone) VALUES ('$name', '$email', '$phone')";
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}
?>

<form method="POST" action="">
    Name: <input type="text" name="name" required><br>
    Email: <input type="email" name="email" required><br>
    Phone: <input type="text" name="phone" required><br>
    <button type="submit">Add Student</button>
</form>

Read Operation (read.php)

This file retrieves and displays data from the database.

<?php
require 'db.php';

$sql = "SELECT * FROM students";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table border='1'>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Phone</th>
                <th>Actions</th>
            </tr>";
    while ($row = $result->fetch_assoc()) {
        echo "<tr>
                <td>{$row['id']}</td>
                <td>{$row['name']}</td>
                <td>{$row['email']}</td>
                <td>{$row['phone']}</td>
                <td>
                    <a href='update.php?id={$row['id']}'>Edit</a> |
                    <a href='delete.php?id={$row['id']}'>Delete</a>
                </td>
              </tr>";
    }
    echo "</table>";
} else {
    echo "No records found.";
}
?>

Update Operation (update.php)

This file updates data in the database.

<?php
require 'db.php';

if (isset($_GET['id'])) {
    $id = $_GET['id'];

    $sql = "SELECT * FROM students WHERE id = $id";
    $result = $conn->query($sql);
    $student = $result->fetch_assoc();
}

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $id = $_POST['id'];
    $name = $_POST['name'];
    $email = $_POST['email'];
    $phone = $_POST['phone'];

    $sql = "UPDATE students SET name='$name', email='$email', phone='$phone' WHERE id=$id";
    if ($conn->query($sql) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }
}
?>

<form method="POST" action="">
    <input type="hidden" name="id" value="<?= $student['id'] ?>">
    Name: <input type="text" name="name" value="<?= $student['name'] ?>" required><br>
    Email: <input type="email" name="email" value="<?= $student['email'] ?>" required><br>
    Phone: <input type="text" name="phone" value="<?= $student['phone'] ?>" required><br>
    <button type="submit">Update Student</button>
</form>

Delete Operation (delete.php)

This file deletes data from the database.

<?php
require 'db.php';

if (isset($_GET['id'])) {
    $id = $_GET['id'];

    $sql = "DELETE FROM students WHERE id = $id";
    if ($conn->query($sql) === TRUE) {
        echo "Record deleted successfully";
    } else {
        echo "Error deleting record: " . $conn->error;
    }
}
?>

Entry Point (index.php)

Use this file to navigate between CRUD operations.

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

This example demonstrates how to perform CRUD operations in PHP using MySQL. These operations form the backbone of most web applications, enabling users to manage data dynamically.

You can extend this project with features like authentication, search, pagination, and more for a more robust application.