software-development-company-logo

Tag: Lesson 15: Advanced Database Operations

  • Lesson 15: Advanced Database Operations

    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

    In this lesson, you’ll learn advanced techniques for working with databases in PHP, including prepared statements for security, transactions for managing complex operations, and joins for querying related data from multiple tables.

    15.1 Prepared Statements

    What are Prepared Statements?

    • A prepared statement is a precompiled SQL query that separates query structure from data values.
    • Advantages:
      • Prevents SQL injection.
      • Improves performance when running the same query multiple times.

    Syntax for Prepared Statements

    php
    $stmt = $conn->prepare("SQL_QUERY");
    $stmt->bindParam(':param', $value);
    $stmt->execute();

    Example: Prepared Statement with PDO

    Insert Data

    php
    <?php
    require 'db.php';    $name = “Alice”;
    $email = “alice@example.com”;

    $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.”;
    }
    ?>

    Select Data

    php
    <?php
    $stmt = $conn->prepare("SELECT * FROM users WHERE email = :email");
    $email = "alice@example.com";
    $stmt->bindParam(':email', $email);
    $stmt->execute();    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($user) {
    echo “Name: “ . $user[‘name’] . “, Email: “ . $user[’email’];
    } else {
    echo “No user found.”;
    }
    ?>

    Update Data

    php
    <?php
    $stmt = $conn->prepare("UPDATE users SET name = :name WHERE email = :email");
    $name = "Alice Updated";
    $email = "alice@example.com";
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':email', $email);    if ($stmt->execute()) {
    echo “User updated successfully.”;
    } else {
    echo “Error updating user.”;
    }
    ?>

    Delete Data

    php
    <?php
    $stmt = $conn->prepare("DELETE FROM users WHERE email = :email");
    $email = "alice@example.com";
    $stmt->bindParam(':email', $email);    if ($stmt->execute()) {
    echo “User deleted successfully.”;
    } else {
    echo “Error deleting user.”;
    }
    ?>

    15.2 Transactions

    What is a Transaction?

    • A transaction is a series of SQL queries executed as a single unit of work.
    • Transactions ensure data integrity by following ACID principles:
      • Atomicity: All operations succeed or none.
      • Consistency: Database remains in a valid state.
      • Isolation: Transactions do not interfere with each other.
      • Durability: Changes persist even after a crash.

    Transaction Control Commands

    • Begin Transaction: Starts a transaction.
    • Commit: Saves all changes.
    • Rollback: Reverts changes if an error occurs.

    Example: Using Transactions in PDO

    Transfer Funds

    php
    <?php
    require 'db.php';    try {
    $conn->beginTransaction();

    // Deduct from sender’s account
    $stmt = $conn->prepare(“UPDATE accounts SET balance = balance – :amount WHERE id = :sender_id”);
    $stmt->execute([‘:amount’ => 100, ‘:sender_id’ => 1]);

    // Add to receiver’s account
    $stmt = $conn->prepare(“UPDATE accounts SET balance = balance + :amount WHERE id = :receiver_id”);
    $stmt->execute([‘:amount’ => 100, ‘:receiver_id’ => 2]);

    $conn->commit();
    echo “Transaction completed successfully.”;
    } catch (Exception $e) {
    $conn->rollBack();
    echo “Transaction failed: “ . $e->getMessage();
    }
    ?>

    15.3 Joins

    What are Joins?

    • Joins combine rows from two or more tables based on related columns.
    • Types of Joins:
      1. Inner Join: Returns matching rows from both tables.
      2. Left Join: Returns all rows from the left table and matching rows from the right table.
      3. Right Join: Returns all rows from the right table and matching rows from the left table.
      4. Full Outer Join: Returns all rows from both tables.

    Example: Joins

    Database Setup

    Create two tables, users and orders:

    sql
    CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
    );    CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product VARCHAR(50),
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
    );

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

    INSERT INTO orders (user_id, product, amount) VALUES
    (1, ‘Laptop’, 1500),
    (1, ‘Mouse’, 20),
    (2, ‘Keyboard’, 50);

    Inner Join

    Retrieve all users with their orders:

    php
    <?php
    $stmt = $conn->query("
    SELECT users.name, orders.product, orders.amount
    FROM users
    INNER JOIN orders ON users.id = orders.user_id
    "    );    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo “Name: “ . $row[‘name’] . “, Product: “ . $row[‘product’] . “, Amount: “ . $row[‘amount’] . “<br>”;
    }
    ?>

    Output:

    yaml
    Name: Alice, Product: Laptop, Amount: 1500
    Name: Alice, Product: Mouse, Amount: 20
    Name: Bob, Product: Keyboard, Amount: 50

    Left Join

    Retrieve all users, including those without orders:

    php
    <?php
    $stmt = $conn->query("
    SELECT users.name, orders.product, orders.amount
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id
    "    );    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo “Name: “ . $row[‘name’] . “, Product: “ . ($row[‘product’] ?? ‘None’) . “<br>”;
    }
    ?>

    Right Join

    Retrieve all orders, including those without matching users:

    php
    <?php
    $stmt = $conn->query("
    SELECT users.name, orders.product, orders.amount
    FROM users
    RIGHT JOIN orders ON users.id = orders.user_id
    "    );    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo “Name: “ . ($row[‘name’] ?? ‘Unknown’) . “, Product: “ . $row[‘product’] . “<br>”;
    }
    ?>

    15.4 Practical Example

    Task Management System

    1. Create a tasks table:
      sql
      CREATE TABLE tasks (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT,
      description VARCHAR(255),
      status ENUM('pending', 'completed'),
      FOREIGN KEY (user_id) REFERENCES users(id)
      );
    2. Implement the following:
      • Prepared Statements:
        • Add a new task for a user.
        • Update the status of a task.
      • Transactions:
        • Deduct a task’s cost from a user’s balance only if the task is added successfully.
      • Joins:
        • Display all tasks with user details.

    Activities and Exercises

    1. Prepared Statements:
      • Write a script to securely insert and retrieve data from a products table.
    2. Transactions:
      • Simulate a banking system where users can transfer money between accounts.
    3. Joins:
      • Create a report showing users and their orders, including users with no orders.

    Assignment

    1. Create two tables: students and courses with a many-to-many relationship using a student_courses table.
    2. Implement the following:
      • Add a new course for a student using prepared statements.
      • Display all courses a student is enrolled in using joins.
      • Use transactions to ensure course enrollment is recorded correctly.

    Summary

    In this lesson, you learned:

    1. How to use prepared statements to prevent SQL injection and enhance security.
    2. How to manage complex database operations with transactions.
    3. How to retrieve related data using joins.

    These skills are critical for building secure and efficient database-driven applications. Let me know if you need further clarification or examples!