Lesson 15: Advanced Database Operations

—

by

admin
in

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!

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *