Web20 University

Easy CRUD Operations in PHP: A Step-by-Step Guide with Examples

Get up to 65% Off Hosting with FREE SSL & FREE Domains!

* Web 2.0 University is supported by it's audience. If you purchase through links on our site, we may earn an affiliate commision.

CRUD operations (Create, Read, Update, Delete) are fundamental functions in database management and are essential for web development. This guide will walk you through implementing easy CRUD operations in PHP, focusing on simplicity and practical examples.

Prerequisites

Before we start, ensure you have the following:

  • A web server with PHP and MySQL installed (e.g., XAMPP, WAMP, MAMP)
  • Basic understanding of PHP and SQL
  • A database management tool like phpMyAdmin

Setting Up the Database

First, let’s set up a MySQL database and a table to work with. Open phpMyAdmin or your preferred database management tool and execute the following SQL commands:

CREATE DATABASE crud_example;

USE crud_example;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT NOT NULL
);

This creates a crud_example database and a users table with columns for id, name, email, and age.

Step 1: Database Connection

Create a file named db.php to handle the database connection:

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

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Step 2: Create Operation

Create a file named create.php to handle the creation of new records:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Create User</title>
</head>
<body>
    <h2>Create User</h2>
    <form action="create.php" method="post">
        <label for="name">Name:</label>
        <input type="text" id="name" name="name" required>
        <br>
        <label for="email">Email:</label>
        <input type="email" id="email" name="email" required>
        <br>
        <label for="age">Age:</label>
        <input type="number" id="age" name="age" required>
        <br>
        <button type="submit">Create</button>
    </form>

    <?php
    if ($_SERVER["REQUEST_METHOD"] == "POST") {
        require 'db.php';

        $name = $_POST['name'];
        $email = $_POST['email'];
        $age = $_POST['age'];

        $sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':age', $age);

        if ($stmt->execute()) {
            echo "New record created successfully";
        } else {
            echo "Error: " . $stmt->errorInfo();
        }
    }
    ?>
</body>
</html>

Step 3: Read Operation

Create a file named read.php to display the records:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Read Users</title>
</head>
<body>
    <h2>Users List</h2>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Age</th>
            <th>Actions</th>
        </tr>

        <?php
        require 'db.php';

        $stmt = $conn->prepare("SELECT * FROM users");
        $stmt->execute();
        $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($users as $user) {
            echo "<tr>";
            echo "<td>" . $user['id'] . "</td>";
            echo "<td>" . $user['name'] . "</td>";
            echo "<td>" . $user['email'] . "</td>";
            echo "<td>" . $user['age'] . "</td>";
            echo "<td>
                    <a href='update.php?id=" . $user['id'] . "'>Edit</a> |
                    <a href='delete.php?id=" . $user['id'] . "'>Delete</a>
                  </td>";
            echo "</tr>";
        }
        ?>
    </table>
</body>
</html>

Step 4: Update Operation

Create a file named update.php to handle updating records:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Update User</title>
</head>
<body>
    <h2>Update User</h2>

    <?php
    require 'db.php';

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

        $stmt = $conn->prepare("SELECT * FROM users WHERE id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        $user = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$user) {
            echo "User not found!";
            exit;
        }
    }

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

        $sql = "UPDATE users SET name = :name, email = :email, age = :age WHERE id = :id";
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':age', $age);
        $stmt->bindParam(':id', $id);

        if ($stmt->execute()) {
            echo "Record updated successfully";
        } else {
            echo "Error: " . $stmt->errorInfo();
        }
    }
    ?>

    <form action="update.php" method="post">
        <input type="hidden" name="id" value="<?php echo $user['id']; ?>">
        <label for="name">Name:</label>
        <input type="text" id="name" name="name" value="<?php echo $user['name']; ?>" required>
        <br>
        <label for="email">Email:</label>
        <input type="email" id="email" name="email" value="<?php echo $user['email']; ?>" required>
        <br>
        <label for="age">Age:</label>
        <input type="number" id="age" name="age" value="<?php echo $user['age']; ?>" required>
        <br>
        <button type="submit">Update</button>
    </form>
</body>
</html>

Step 5: Delete Operation

Create a file named delete.php to handle deleting records:

<?php
require 'db.php';

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

    $sql = "DELETE FROM users WHERE id = :id";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':id', $id);

    if ($stmt->execute()) {
        echo "Record deleted successfully";
    } else {
        echo "Error: " . $stmt->errorInfo();
    }
} else {
    echo "Invalid request!";
}
?>

Conclusion

In this guide, we’ve covered the basics of implementing CRUD operations in PHP. We created a simple application to manage user records, demonstrating how to create, read, update, and delete data from a MySQL database using PHP.

By following these steps, you can build a basic CRUD application, which can be expanded and customized to meet your specific needs. Whether you’re developing a contact management system, a product catalog, or any other data-driven application, mastering CRUD operations is an essential skill in web development.

Get up to 65% Off Hosting with FREE SSL & FREE Domains!