Create a PHP Application Interacting with MySQL Database for CRUD Operations
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:
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:
$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:
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:
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
Leave a Comment or Ask for Help
If you have any questions or need help with this lab, please leave a comment below.
Images
Comments