Creating a PHP application that performs CRUD operations (Create, Read, Update, Delete) with MySQL is a fundamental skill for web developers. In this guide, we’ll take you through the process of building a simple PHP CRUD application.
1. Set Up Your Environment
Before you start, ensure that you have PHP and MySQL installed on your development machine. You can use tools like XAMPP or MAMP to set up a local server environment.
2. Create a MySQL Database
CREATE DATABASE mycrudapp;
USE mycrudapp;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT
);
These SQL statements create a MySQL database named mycrudapp and a table named users with columns for id, name, email, and age.
3. Set Up Your Project Structure
Create a project directory for your CRUD application and organize it as follows:
mycrudapp/ │── index.php │── create.php │── read.php │── update.php │── delete.php │── db.php │── .htaccess
4. Connect to MySQL
db.php – Database Connection
<?php
$host = 'localhost';
$username = 'root';
$password = '';
$database = 'mycrudapp';
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
This file establishes a connection to your MySQL database. Replace the connection details with your own if they differ.
5. Create Records (Create – create.php)
create.php – Create Form
<?php include 'db.php'; ?>
<!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" name="name" required>
<label for="email">Email:</label>
<input type="email" name="email" required>
<label for="age">Age:</label>
<input type="number" name="age">
<button type="submit" name="submit">Create User</button>
</form>
<?php
if (isset($_POST['submit'])) {
$name = $_POST['name'];
$email = $_POST['email'];
$age = $_POST['age'];
$sql = "INSERT INTO users (name, email, age) VALUES ('$name', '$email', '$age')";
if ($conn->query($sql) === TRUE) {
echo "User created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
?>
</body>
</html>
This file displays a form to create a new user. Upon submission, it inserts the user data into the MySQL database.
6. Read Records (Read – read.php)
read.php – Display Users
<?php include 'db.php'; ?>
<!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>
<?php
$result = $conn->query("SELECT * FROM users");
if ($result->num_rows > 0) {
echo "<ul>";
while ($row = $result->fetch_assoc()) {
echo "<li>{$row['name']} - {$row['email']} (Age: {$row['age']})</li>";
}
echo "</ul>";
} else {
echo "No users found.";
}
?>
</body>
</html>
This file queries the database to fetch all users and displays them in an unordered list.
7. Update Records (Update – update.php)
update.php – Update Form
<?php include 'db.php'; ?>
<!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
if (isset($_GET['id'])) {
$id = $_GET['id'];
$result = $conn->query("SELECT * FROM users WHERE id = $id");
if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
?>
<form action="update.php" method="post">
<input type="hidden" name="id" value="<?php echo $row['id']; ?>">
<label for="name">Name:</label>
<input type="text" name="name" value="<?php echo $row['name']; ?>" required>
<label for="email">Email:</label>
<input type="email" name="email" value="<?php echo $row['email']; ?>" required>
<label for="age">Age:</label>
<input type="number" name="age" value="<?php echo $row['age']; ?>">
<button type="submit" name="submit">Update User</button>
</form>
<?php
} else {
echo "User not found.";
}
} elseif (isset($_POST['submit'])) {
$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";
if ($conn->query($sql) === TRUE) {
echo "User updated successfully";
} else {
echo "Error updating user: " . $conn->error;
}
} else {
echo "Invalid request.";
}
?>
</body>
</html>
This file displays a form to update user information. If a user ID is provided, it pre-fills the form with the user’s existing details.
8. Delete Records (Delete – delete.php)
delete.php – Delete User
<?php include 'db.php'; ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Delete User</title>
</head
