Lesson 14: CRUD Operations in PHP

—

by

admin
in

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!

Comments

Leave a Reply

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