PHP & MySQL CRUD: Create, Read, Update, and Delete

PHP

"PHP is a server-side programming language that is flexible, easy to learn, and still the backbone of many websites, even though it is often thought to be outdated"

Hey, PHP champion!  You’ve learned how to connect PHP with MySQL—great! But what good is a database if you can’t add, view, edit, or delete data? Today, we’ll master CRUD (Create, Read, Update, Delete) with PHP and MySQL. Let’s get coding! 

Why Learn CRUD?

Imagine running a website where users can: Register & log in, Post, edit, and delete content, Manage customer orders, products, or messages.

CRUD operations power almost every web app! Let’s build a basic user management system.

Setting Up the Database

We need a users table to perform CRUD operations. Open phpMyAdmin or use the MySQL CLI and run:

CREATE DATABASE my_database;
USE my_database;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

Database ready! Now let’s do some CRUD magic!

CREATE (Insert Data)

Using MySQLi

$conn = new mysqli("localhost", "root", "", "my_database");
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New user added successfully!";
} else {
    echo "Error: " . $conn->error;
}

User added!

Using PDO (Prepared Statements)

$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->execute(["jane_doe", "jane@example.com"]);

More secure!

READ (Fetch Data)

Using MySQLi

$result = $conn->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
    echo "ID: " . $row["id"] . " - Username: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
}

Data retrieved!

Using PDO

$stmt = $conn->query("SELECT * FROM users");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: " . $row["id"] . " - Username: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
}

Same, but using PDO!

UPDATE (Modify Data)

Using MySQLi

$sql = "UPDATE users SET email='newemail@example.com' WHERE username='john_doe'";
if ($conn->query($sql) === TRUE) {
    echo "User updated successfully!";
} else {
    echo "Error: " . $conn->error;
}

User info updated!

Using PDO (Prepared Statements)

$stmt = $conn->prepare("UPDATE users SET email=? WHERE username=?");
$stmt->execute(["newemail@example.com", "jane_doe"]);

More secure updates!

DELETE (Remove Data)

Using MySQLi

$sql = "DELETE FROM users WHERE username='john_doe'";
if ($conn->query($sql) === TRUE) {
    echo "User deleted successfully! 🗑️";
} else {
    echo "Error: " . $conn->error;
}

User removed!

Using PDO (Prepared Statements)

$stmt = $conn->prepare("DELETE FROM users WHERE username=?");
$stmt->execute(["jane_doe"]);

Bye-bye, user!

Congrats, coding warrior!  You’ve mastered: Creating (Inserting) data, Reading (Fetching) data, Updating data, Deleting data.