Performing CRUD Operations

Welcome to the next lesson! In this lesson, you’ll learn how to perform the four basic database operations: Create, Read, Update, and Delete (CRUD). These operations are the foundation of dynamic web applications. Let’s get started!

What Are CRUD Operations?

CRUD stands for:

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

Key Point: CRUD operations allow you to manage data dynamically in your applications.

Create: Adding Data

Use the INSERT INTO statement to add new records. Example:

Tip: Always use prepared statements to prevent SQL injection.

<?php
  $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(["name" => "Alice", "email" => "alice@mail.com"]);

  echo "Record added successfully!";
?>

Read: Retrieving Data

Use the SELECT statement to fetch data from the database. Example:

Key Point: Use loops to process and display multiple rows of data.

<?php
  $sql = "SELECT * FROM users";
  $stmt = $pdo->query($sql);

  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: " . $row["id"] . ", Name: " . $row["name"] . ", Email: " . $row["email"] . "<br>";
  }
?>

Update: Modifying Data

Use the UPDATE statement to modify existing records. Example:

Tip: Be careful when using UPDATE without a WHERE clause, as it will update all records.

<?php
  $sql = "UPDATE users SET email = :email WHERE name = :name";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(["email" => "newalice@mail.com", "name" => "Alice"]);

  echo "Record updated successfully!";
?>

Delete: Removing Data

Use the DELETE statement to remove records. Example:

Key Point: Use the WHERE clause to target specific records for deletion.

<?php
  $sql = "DELETE FROM users WHERE name = :name";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(["name" => "Alice"]);

  echo "Record deleted successfully!";
?>

Best Practices for CRUD Operations

Follow these best practices when performing CRUD operations:

• Always validate and sanitize user inputs.
• Use prepared statements to prevent SQL injection.
• Back up your database before performing large updates or deletions.

Tip: Log all changes to the database for easier debugging and auditing.

Combining CRUD Operations in a Single Application

CRUD operations can be combined in a single application to manage data dynamically. Example:

• Create: Add new users via a form.
• Read: Display user data in a table.
• Update: Edit user information through an interface.
• Delete: Remove users with a delete button.

Key Point: CRUD operations form the backbone of many web applications, including CMS and e-commerce platforms.

Great Job!

Congratulations! You’ve learned how to perform CRUD operations in PHP using MySQL. These skills are essential for managing data in any web application. In the next lesson, we’ll explore advanced querying techniques with SQL. Let’s keep coding!