❮ Previous

CRUD Operations in PHP with Database

CRUD is an acronym for Create, Read, Update, and Delete—the four basic operations for managing data in a database. These operations are fundamental in developing dynamic web applications, and PHP provides an easy way to perform them using SQL.

In this article, we'll create a simple CRUD application using PHP and a MySQL database.

Prerequisites

Before we start, ensure you have:

A local server environment like XAMPP, WAMP, or MAMP installed.
Basic knowledge of PHP, MySQL, and HTML.
A database tool like phpMyAdmin (commonly included in server packages).

Step-by-Step Guide

1. Setting Up the Database

Open your database management tool (e.g., phpMyAdmin).
Create a database called crud_app.
Run the following SQL query to create a users table:

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

2. Connecting PHP to the Database

Create a file named db.php to establish a connection.

<?php
$servername = "localhost";
$username = "root"; // Default for local servers
$password = "";
$database = "crud_app";

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

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

3. The CRUD Operations

3.1 Create Operation (Insert Data)

Create a form to collect user data. Save this as create.php.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Create User</title>
</head>
<body>
    <h1>Add New User</h1>
    <form action="insert.php" method="POST">
        <label for="name">Name:</label>
        <input type="text" name="name" required><br>
        <label for="email">Email:</label>
        <input type="email" name="email" required><br>
        <button type="submit">Submit</button>
    </form>
</body>
</html>

The insert.php script will process the form and insert data into the database.

<?php
include 'db.php';

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

    $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}
?>

3.2 Read Operation (Retrieve Data)

Create a script to display all users. Save this as read.php.

<?php
include 'db.php';

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

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Users</title>
</head>
<body>
    <h1>All Users</h1>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Created At</th>
            <th>Actions</th>
        </tr>
        <?php while ($row = $result->fetch_assoc()): ?>
            <tr>
                <td><?= $row['id'] ?></td>
                <td><?= $row['name'] ?></td>
                <td><?= $row['email'] ?></td>
                <td><?= $row['created_at'] ?></td>
                <td>
                    <a href="update.php?id=<?= $row['id'] ?>">Edit</a>
                    <a href="delete.php?id=<?= $row['id'] ?>">Delete</a>
                </td>
            </tr>
        <?php endwhile; ?>
    </table>
</body>
</html>

3.3 Update Operation (Edit Data)

Create a script to update a user’s details. Save this as update.php.

<?php
include 'db.php';

$id = $_GET['id'];
$sql = "SELECT * FROM users WHERE id=$id";
$result = $conn->query($sql);
$user = $result->fetch_assoc();

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

    $sql = "UPDATE users SET name='$name', email='$email' WHERE id=$id";

    if ($conn->query($sql) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Update User</title>
</head>
<body>
    <h1>Edit User</h1>
    <form method="POST">
        <label> for="name">Name:</label>
        <input type="text" name="name" value="<?= $user['name'] ?>" required><br>
        <label> for="email">Email:</label>
        <input type="email" name="email" value="<?= $user['email'] ?>" required><br>
        <button> type="submit">Update</button>
    </form>
</body>
</html>

3.4 Delete Operation (Remove Data)

Create a script to delete a user. Save this as delete.php.

<?php
include 'db.php';

$id = $_GET['id'];

$sql = "DELETE FROM users WHERE id=$id";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

Conclusion

In this tutorial, we built a simple CRUD application using PHP and MySQL. Here's a summary of what we achieved:

Create: Inserted data into the database.
Read: Retrieved and displayed data.
Update: Edited and updated records.
Delete: Removed records.

This basic structure can be extended with features like form validation, user authentication, and enhanced UI for a full-fledged application.

Next Article ❯

❮ Previous