Easy CRUD Operations in PHP: A Step-by-Step Guide with Examples
CRUD operations (Create, Read, Update, Delete) are fundamental functions in database management and are essential for web development. This guide will walk you through implementing easy CRUD operations in PHP, focusing on simplicity and practical examples.
Prerequisites
Before we start, ensure you have the following:
- A web server with PHP and MySQL installed (e.g., XAMPP, WAMP, MAMP)
- Basic understanding of PHP and SQL
- A database management tool like phpMyAdmin
Setting Up the Database
First, let’s set up a MySQL database and a table to work with. Open phpMyAdmin or your preferred database management tool and execute the following SQL commands:
CREATE DATABASE crud_example;
USE crud_example;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT NOT NULL
);
This creates a crud_example database and a users table with columns for id, name, email, and age.
Step 1: Database Connection
Create a file named db.php to handle the database connection:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "crud_example";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
Step 2: Create Operation
Create a file named create.php to handle the creation of new records:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Create User</title>
</head>
<body>
<h2>Create User</h2>
<form action="create.php" method="post">
<label for="name">Name:</label>
<input type="text" id="name" name="name" required>
<br>
<label for="email">Email:</label>
<input type="email" id="email" name="email" required>
<br>
<label for="age">Age:</label>
<input type="number" id="age" name="age" required>
<br>
<button type="submit">Create</button>
</form>
<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
require 'db.php';
$name = $_POST['name'];
$email = $_POST['email'];
$age = $_POST['age'];
$sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':age', $age);
if ($stmt->execute()) {
echo "New record created successfully";
} else {
echo "Error: " . $stmt->errorInfo();
}
}
?>
</body>
</html>
Step 3: Read Operation
Create a file named read.php to display the records:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Read Users</title>
</head>
<body>
<h2>Users List</h2>
<table border="1">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Age</th>
<th>Actions</th>
</tr>
<?php
require 'db.php';
$stmt = $conn->prepare("SELECT * FROM users");
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
echo "<tr>";
echo "<td>" . $user['id'] . "</td>";
echo "<td>" . $user['name'] . "</td>";
echo "<td>" . $user['email'] . "</td>";
echo "<td>" . $user['age'] . "</td>";
echo "<td>
<a href='update.php?id=" . $user['id'] . "'>Edit</a> |
<a href='delete.php?id=" . $user['id'] . "'>Delete</a>
</td>";
echo "</tr>";
}
?>
</table>
</body>
</html>
Step 4: Update Operation
Create a file named update.php to handle updating records:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Update User</title>
</head>
<body>
<h2>Update User</h2>
<?php
require 'db.php';
if (isset($_GET['id'])) {
$id = $_GET['id'];
$stmt = $conn->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindParam(':id', $id);
$stmt->execute();
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$user) {
echo "User not found!";
exit;
}
}
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$id = $_POST['id'];
$name = $_POST['name'];
$email = $_POST['email'];
$age = $_POST['age'];
$sql = "UPDATE users SET name = :name, email = :email, age = :age WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':age', $age);
$stmt->bindParam(':id', $id);
if ($stmt->execute()) {
echo "Record updated successfully";
} else {
echo "Error: " . $stmt->errorInfo();
}
}
?>
<form action="update.php" method="post">
<input type="hidden" name="id" value="<?php echo $user['id']; ?>">
<label for="name">Name:</label>
<input type="text" id="name" name="name" value="<?php echo $user['name']; ?>" required>
<br>
<label for="email">Email:</label>
<input type="email" id="email" name="email" value="<?php echo $user['email']; ?>" required>
<br>
<label for="age">Age:</label>
<input type="number" id="age" name="age" value="<?php echo $user['age']; ?>" required>
<br>
<button type="submit">Update</button>
</form>
</body>
</html>
Step 5: Delete Operation
Create a file named delete.php to handle deleting records:
<?php
require 'db.php';
if (isset($_GET['id'])) {
$id = $_GET['id'];
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $id);
if ($stmt->execute()) {
echo "Record deleted successfully";
} else {
echo "Error: " . $stmt->errorInfo();
}
} else {
echo "Invalid request!";
}
?>
Conclusion
In this guide, we’ve covered the basics of implementing CRUD operations in PHP. We created a simple application to manage user records, demonstrating how to create, read, update, and delete data from a MySQL database using PHP.
By following these steps, you can build a basic CRUD application, which can be expanded and customized to meet your specific needs. Whether you’re developing a contact management system, a product catalog, or any other data-driven application, mastering CRUD operations is an essential skill in web development.