Module 9: CRUD Application

Module Overview

This module builds a full CRUD (Create, Read, Update, Delete) application using HTML, CSS, JavaScript, PHP, and MySQL. Students will connect all previous modules into a working web application.

Prerequisites: Modules 1-8 (HTML, CSS, JavaScript, PHP, MySQL)

Estimated Duration: 6-8 hours

Tools: XAMPP, VS Code, Browser, phpMyAdmin

---

Lesson 1: Database Schema and Connection

Duration: 60 minutes

Learning Objectives

• Design a complete database schema for a task management app
• Create tables with proper relationships
• Write a reusable database connection file in PHP
• Test the connection and handle errors
• Understand PDO vs mysqli

Instructor Notes

A database connection file is the backbone of any PHP/MySQL app. PDO is recommended for its security features (prepared statements) and support for multiple databases. Students should verify AI-generated connection code by testing connection failure scenarios.

Presentation Outline

1. Application overview (10 min)
2. Database schema design (15 min)
3. Creating tables (10 min)
4. PHP database connection with PDO (20 min)

Demonstration

In phpMyAdmin, create the task_db database and tables. Create config/database.php with PDO connection. Test the connection by running a simple query and displaying the result.

AI Prompts

1. "Generate a MySQL database schema for a task management app with users and tasks tables"
2. "Create a PHP PDO database connection file with error handling and best practices"
3. "Write PHP code to test database connection and display connection status"

Starter Code

Final Code

Exercises

1. Create an additional tags table and a task_tags pivot table for many-to-many relationships.
2. Modify the Database class to accept configuration from an external file.
3. Add a method to the Database class that returns the last inserted ID.

Homework

1. Draw an ER diagram (Entity-Relationship) for the task management app. Label primary keys, foreign keys, and relationships.
2. Research PDO prepared statements. Write a summary explaining why they prevent SQL injection.

Quiz

1. What does PDO stand for?

A) PHP Data Object

B) Python Database Object

C) PHP Database Orientation

D) Program Data Output

Answer: A

1. Which PDO method executes a prepared statement?

A) execute()

B) run()

C) query()

D) Both A and B

Answer: D

1. What does PDO::ERRMODE_EXCEPTION do?

A) Logs errors silently

B) Throws exceptions on errors

C) Ignores all errors

D) Returns error codes

Answer: B

1. Which SQL clause defines a foreign key?

A) FOREIGN KEY

B) REFERENCES

C) CONSTRAINT

D) All of the above

Answer: D

1. What is the purpose of ON DELETE CASCADE?

A) Prevents deletion

B) Deletes child rows when parent is deleted

C) Archives deleted rows

D) Logs deletions

Answer: B

---

Lesson 2: Create and Read Operations

Duration: 60 minutes

Learning Objectives

• Build a task creation form
• Insert data into the database using PDO prepared statements
• Retrieve and display tasks from the database
• Implement basic search and filtering
• Handle form validation errors

Instructor Notes

Create and Read are the most used CRUD operations. Prepared statements are essential for security. Students should verify AI-generated INSERT/SELECT queries by checking the actual data in phpMyAdmin. Always validate input before inserting.

Presentation Outline

1. Task creation form (15 min)
2. INSERT with prepared statements (15 min)
3. SELECT and display tasks (15 min)
4. Search and filtering (10 min)

Demonstration

Create create.php with a task creation form. Create index.php to display all tasks with filtering by status. Show how data flows from form to database to display.

AI Prompts

1. "Generate PHP PDO prepared statement examples for INSERT and SELECT operations"
2. "Create a task management CRUD app with HTML forms and PHP processing"
3. "Write PHP validation and error handling for a task creation form"

Starter Code

Final Code

Exercises

1. Add a category dropdown to the task creation form.
2. Add pagination to the task list (show 5 tasks per page).
3. Add a "My Tasks" filter that shows only tasks assigned to the current user.

Homework

1. Build a task creation form with client-side validation using JavaScript before form submission.
2. Add a feature to export tasks to a CSV file using PHP.

Quiz

1. Which PHP method executes a prepared statement?

A) query()

B) execute()

C) run()

D) Both B and C

Answer: D

1. What does htmlspecialchars() prevent?

A) SQL injection

B) XSS attacks

C) CSRF attacks

D) File inclusion

Answer: B

1. Which HTTP method is typically used for form submissions that modify data?

A) GET

B) POST

C) PUT

D) DELETE

Answer: B

1. What does $_POST['field'] ?? '' do?

A) Returns an error if field is missing

B) Returns the field value or empty string if not set

C) Deletes the field

D) Sets the field to empty string

Answer: B

1. Which SQL clause sorts results?

A) SORT BY

B) ORDER BY

C) ARRANGE BY

D) GROUP BY

Answer: B

---

Lesson 3: Update and Delete Operations

Duration: 60 minutes

Learning Objectives

• Build an edit form pre-filled with existing data
• Update database records using prepared statements
• Implement delete with confirmation
• Handle cascading deletes
• Add success/error messaging

Instructor Notes

Update and Delete operations require extra caution. Always confirm the record exists before updating. Use confirmation dialogs for deletes. Verify AI-generated update/delete queries by checking the affected row count.

Presentation Outline

1. Update workflow (15 min)
2. Edit form with pre-filled data (15 min)
3. Delete with confirmation (15 min)
4. Success and error messaging (10 min)

Demonstration

Create update.php that fetches a task by ID and pre-fills the form. On submission, update the task in the database. Create delete.php that confirms deletion and removes the task.

AI Prompts

1. "Generate PHP CRUD update and delete operations with confirmation dialogs"
2. "Create a PHP task edit form with pre-filled data and validation"
3. "Write PHP code for soft delete vs hard delete with examples"

Starter Code

Final Code

Exercises

1. Add a "soft delete" feature: add an is_deleted column and filter deleted tasks from the main list.
2. Add bulk delete functionality: allow selecting multiple tasks and deleting them at once.
3. Add an "archive" feature that moves old completed tasks to an archive table.

Homework

1. Add an audit log table that records all create, update, and delete operations with timestamps.
2. Research CSRF protection. Implement a CSRF token in the task creation form.

Quiz

1. Which HTTP method should be used for updates in RESTful APIs?

A) GET

B) POST

C) PUT

D) DELETE

Answer: C

1. What does $_SERVER['REQUEST_METHOD'] return for a form submission?

A) "FORM"

B) "POST" or "GET"

C) "SUBMIT"

D) "SEND"

Answer: B

1. Which PHP function redirects to another page?

A) redirect()

B) header("Location: ...")

C) go_to()

D) navigate()

Answer: B

1. Why should you use exit() after a redirect?

A) It is required by PHP

B) To prevent the rest of the page from executing

C) To close the database connection

D) It is not necessary

Answer: B

1. What is the recommended way to confirm a delete action?

A) Auto-delete without confirmation

B) JavaScript confirm() dialog

C) Ask the user to type "DELETE"

D) Both B and C

Answer: D