software-development-company-logo

Tag: Lesson 14: CRUD Operations in PHP

  • Lesson 14: CRUD Operations in PHP

    Section 1: Introduction to PHP
    Section 2: PHP Fundamentals
    Section 3: Working with Forms and User Input
    Section 4: Object-Oriented PHP
    Section 5: PHP and Databases
    Section 6: Advanced PHP Concepts
    Section 7: Real-World Projects
    Section 8: PHP Best Practices

    CRUD stands for Create, Read, Update, and Delete, the fundamental operations of managing data in a database. This lesson will guide you through implementing these operations in PHP using MySQL.

    Lesson Outline

    1. Introduction to CRUD Operations
    2. Setting Up the Environment
    3. Creating Data (INSERT)
    4. Reading Data (SELECT)
    5. Updating Data (UPDATE)
    6. Deleting Data (DELETE)
    7. Practical CRUD Example with a “Users” Table
    8. Activities and Assignments

    14.1 Introduction to CRUD Operations

    What are CRUD Operations?

    CRUD operations form the core functionality of any database-driven application:

    • Create: Add new records to the database.
    • Read: Retrieve and display data from the database.
    • Update: Modify existing records.
    • Delete: Remove records from the database.

    14.2 Setting Up the Environment

    Step 1: Database Setup

    Create a database crud_demo with a users table:

    sql

    CREATE DATABASE crud_demo;

    USE crud_demo;

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

    Step 2: Insert Sample Data

    sql
    INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

    Step 3: Connection Script

    Create a file db.php for reusable database connection code:

    php
    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "crud_demo";    try {
    $conn = new PDO(“mysql:host=$host;dbname=$database“, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
    die(“Connection failed: “ . $e->getMessage());
    }
    ?>

    14.3 Creating Data (INSERT)

    Insert Operation

    Use the INSERT INTO SQL statement to add new records.

    Example: Insert User

    Create a file create.php:

    php
    <?php
    require 'db.php';    if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
    $name = $_POST[‘name’];
    $email = $_POST[’email’];

    $stmt = $conn->prepare(“INSERT INTO users (name, email) VALUES (:name, :email)”);
    $stmt->bindParam(‘:name’, $name);
    $stmt->bindParam(‘:email’, $email);

    if ($stmt->execute()) {
    echo “User added successfully!”;
    } else {
    echo “Error adding user.”;
    }
    }
    ?>

    <form method=“POST” action=“”>
    Name: <input type=“text” name=“name” required><br>
    Email: <input type=“email” name=“email” required><br>
    <button type=“submit”>Add User</button>
    </form>

    14.4 Reading Data (SELECT)

    Read Operation

    Use the SELECT SQL statement to retrieve data.

    Example: Display Users

    Create a file read.php:

    php
    <?php
    require 'db.php';    $stmt = $conn->query(“SELECT * FROM users”);
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ?>

    <table border=“1”>
    <tr>
    <th>ID</th>
    <th>Name</th>
    <th>Email</th>
    <th>Created At</th>
    </tr>
    <?php foreach ($users as $user): ?>
    <tr>
    <td><?= $user[‘id’]; ?></td>
    <td><?= $user[‘name’]; ?></td>
    <td><?= $user[’email’]; ?></td>
    <td><?= $user[‘created_at’]; ?></td>
    </tr>
    <?php endforeach; ?>
    </table>

    14.5 Updating Data (UPDATE)

    Update Operation

    Use the UPDATE SQL statement to modify existing records.

    Example: Update User

    Create a file update.php:

    php
    <?php
    require 'db.php';    if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
    $id = $_POST[‘id’];
    $name = $_POST[‘name’];
    $email = $_POST[’email’];

    $stmt = $conn->prepare(“UPDATE users SET name = :name, email = :email WHERE id = :id”);
    $stmt->bindParam(‘:id’, $id);
    $stmt->bindParam(‘:name’, $name);
    $stmt->bindParam(‘:email’, $email);

    if ($stmt->execute()) {
    echo “User updated successfully!”;
    } else {
    echo “Error updating user.”;
    }
    }

    // Fetch the user details for editing
    $id = $_GET[‘id’];
    $stmt = $conn->prepare(“SELECT * FROM users WHERE id = :id”);
    $stmt->bindParam(‘:id’, $id);
    $stmt->execute();
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    ?>

    <form method=“POST” action=“”>
    <input type=“hidden” name=“id” value=“<?= $user[‘id’]; ?>”>
    Name: <input type=“text” name=“name” value=“<?= $user[‘name’]; ?>” required><br>
    Email: <input type=“email” name=“email” value=“<?= $user[’email’]; ?>” required><br>
    <button type=“submit”>Update User</button>
    </form>

    14.6 Deleting Data (DELETE)

    Delete Operation

    Use the DELETE SQL statement to remove records.

    Example: Delete User

    Create a file delete.php:

    php
    <?php
    require 'db.php';    if ($_SERVER[“REQUEST_METHOD”] == “GET” && isset($_GET[‘id’])) {
    $id = $_GET[‘id’];

    $stmt = $conn->prepare(“DELETE FROM users WHERE id = :id”);
    $stmt->bindParam(‘:id’, $id);

    if ($stmt->execute()) {
    echo “User deleted successfully!”;
    } else {
    echo “Error deleting user.”;
    }
    }
    ?>

    14.7 Practical CRUD Example

    Create a file index.php that ties all operations together:

    php
    <?php
    require 'db.php';    $stmt = $conn->query(“SELECT * FROM users”);
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ?>

    <a href=“create.php”>Add User</a>
    <table border=“1”>
    <tr>
    <th>ID</th>
    <th>Name</th>
    <th>Email</th>
    <th>Actions</th>
    </tr>
    <?php foreach ($users as $user): ?>
    <tr>
    <td><?= $user[‘id’]; ?></td>
    <td><?= $user[‘name’]; ?></td>
    <td><?= $user[’email’]; ?></td>
    <td>
    <a href=“update.php?id=<?= $user[‘id’]; ?>”>Edit</a>
    <a href=“delete.php?id=<?= $user[‘id’]; ?>” onclick=“return confirm(‘Are you sure?’)”>Delete</a>
    </td>
    </tr>
    <?php endforeach; ?>
    </table>

    Activities and Exercises

    1. Create a Products Table:
      • Columns: id, name, price, quantity, created_at.
      • Implement CRUD operations for managing products.
    2. User Management:
      • Add functionality to search users by name or email.
    3. Validation:
      • Add validation to ensure that all fields are filled before inserting or updating data.

    Assignment

    1. Create a tasks table with the following columns:
      • id (Primary Key)
      • task_name (VARCHAR)
      • status (ENUM: pending, completed)
    2. Build a CRUD application:
      • Create tasks.
      • Display all tasks.
      • Update the status of tasks.
      • Delete tasks.

    Summary

    In this lesson, you learned:

    1. How to perform CRUD operations using PHP and MySQL.
    2. The importance of using prepared statements for secure database interactions.
    3. How to tie together Create, Read, Update, and Delete operations in a dynamic web application.

    These skills are essential for developing database-driven applications. Let me know if you need additional guidance!