**Course Title:** Modern PHP Development: Best Practices and Advanced Techniques **Section Title:** Working with Databases (MySQL/MariaDB) **Topic:** Create a PHP application that interacts with a MySQL database to perform CRUD operations. (Lab topic) **Overview** In this lab, you'll learn how to create a PHP application that interacts with a MySQL database to perform CRUD (Create, Read, Update, Delete) operations. You'll use PDO (PHP Data Objects) to establish a connection to the database, and then use prepared statements and parameterized queries to execute CRUD operations. **Prerequisites** Before starting this lab, make sure you have: * A PHP development environment set up with a MySQL database (e.g., XAMPP, MAMP, LAMP, or Docker) * Basic knowledge of PHP, MySQL, and CRUD operations * Completed the previous topics on database integration in PHP using PDO **Step 1: Create a Database and Table** Create a new database and table in your MySQL database using the following SQL commands: ```sql CREATE DATABASE crud_example; USE crud_example; CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), email VARCHAR(255) ); ``` You can execute these commands using a tool like phpMyAdmin or the MySQL command-line interface. **Step 2: Connect to the Database** Create a new PHP file (e.g., `db.php`) and establish a connection to the database using PDO: ```php $host = 'localhost'; $dbname = 'crud_example'; $user = 'your_username'; $password = 'your_password'; $dsn = "mysql:host=$host;dbname=$dbname"; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]; try { $pdo = new PDO($dsn, $user, $password); } catch (PDOException $e) { echo 'Error: ' . $e->getMessage(); exit; } ``` Replace `your_username` and `your_password` with your actual MySQL username and password. **Step 3: Create a User Class** Create a new PHP file (e.g., `User.php`) and define a `User` class that will encapsulate the CRUD operations: ```php class User { private $pdo; public function __construct(PDO $pdo) { $this->pdo = $pdo; } public function createUser($name, $email) { $stmt = $this->pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)"); $stmt->bindParam(':name', $name); $stmt->bindParam(':email', $email); $stmt->execute(); return $this->pdo->lastInsertId(); } public function readUser($id) { $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = :id"); $stmt->bindParam(':id', $id); $stmt->execute(); return $stmt->fetch(); } public function updateUser($id, $name, $email) { $stmt = $this->pdo->prepare("UPDATE users SET name = :name, email = :email WHERE id = :id"); $stmt->bindParam(':name', $name); $stmt->bindParam(':email', $email); $stmt->bindParam(':id', $id); $stmt->execute(); return $stmt->rowCount(); } public function deleteUser($id) { $stmt = $this->pdo->prepare("DELETE FROM users WHERE id = :id"); $stmt->bindParam(':id', $id); $stmt->execute(); return $stmt->rowCount(); } } ``` **Step 4: Test the CRUD Operations** Create a new PHP file (e.g., `index.php`) and test the CRUD operations using the `User` class: ```php require_once 'db.php'; require_once 'User.php'; $user = new User($pdo); // Create a new user $name = 'John Doe'; $email = 'john@example.com'; $id = $user->createUser($name, $email); echo "User created with ID: $id" . PHP_EOL; // Read the user $readUser = $user->readUser($id); echo "Read user: " . print_r($readUser, true) . PHP_EOL; // Update the user $name = 'Jane Doe'; $email = 'jane@example.com'; $updated = $user->updateUser($id, $name, $email); echo "User updated: $updated" . PHP_EOL; // Delete the user $deleted = $user->deleteUser($id); echo "User deleted: $deleted" . PHP_EOL; ``` Run the `index.php` file and observe the output. **Conclusion** In this lab, you created a PHP application that interacts with a MySQL database to perform CRUD operations using PDO and prepared statements. You defined a `User` class that encapsulates the CRUD operations and tested the operations using a sample scenario. **Additional Resources** * [PDO official documentation](https://www.php.net/manual/en/book.pdo.php) * [MySQL official documentation](https://dev.mysql.com/doc/refman/8.0/en/) * [SQL CRUD operations tutorial](https://www.w3schools.com/sql/sql_crud.asp) **Leave a Comment or Ask for Help** If you have any questions or need help with this lab, please leave a comment below.