Introduction to MySQL with PHP: CRUD Operations for Beginners 🚀

If you’re building a PHP web application, chances are you’ll need to store and retrieve data—whether it’s users, orders, blog posts, or anything else. That’s where MySQL comes in!

In this beginner-friendly guide, you’ll learn how to:
✅ Connect PHP to MySQL
✅ Perform CRUD operations (Create, Read, Update, Delete)
✅ Prevent SQL injection attacks
✅ Build a mini CRUD app

Let’s dive in! 🚀

---

🎯 Step 1: Setting Up MySQL with PHP

Before we start, ensure you have:
✔️ PHP & MySQL installed (or use XAMPP, MAMP, Docker)
✔️ A MySQL database (you can create one using phpMyAdmin or MySQL CLI)

Creating a Database & Table

Run this SQL script to create a database and a users table:

CREATE DATABASE zeroexp_dev;
USE zeroexp_dev;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

🔥 What happens?

• We create a zeroexp_dev database.
• The users table stores user data with id, name, and email.

---

Step 2: Connecting PHP to MySQL

We’ll use PDO (PHP Data Objects)—a secure, flexible way to interact with MySQL.

db.php (Database Connection File)

<?php
$host = "localhost";
$dbname = "zeroexp_dev";
$username = "root";
$password = "";

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}
?>

🔥 Why use PDO?

• More secure than mysqli_connect().
• Supports prepared statements (prevents SQL injection).

---

Step 3: Performing CRUD Operations in PHP

CRUD = Create, Read, Update, Delete

1️⃣ Create (Insert Data)

<?php
require "db.php";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $name = htmlspecialchars($_POST["name"]);
    $email = htmlspecialchars($_POST["email"]);

    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    if ($stmt->execute([$name, $email])) {
        echo "User added successfully!";
    } else {
        echo "Error adding user.";
    }
}
?>

<form method="post">
    Name: <input type="text" name="name"><br>
    Email: <input type="email" name="email"><br>
    <input type="submit" value="Add User">
</form>

🔥 What happens?

• The form sends data to the script via POST.
• The script inserts data into the MySQL database.
• htmlspecialchars() prevents XSS attacks.

---

2️⃣ Read (Fetch Data)

<?php
require "db.php";

$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<h3>Users List</h3>";
foreach ($users as $user) {
    echo "{$user['id']}: {$user['name']} ({$user['email']}) <br>";
}
?>

🔥 What happens?

• Fetches all users from users table.
• Displays them in a simple list format.

---

3️⃣ Update (Modify Data)

<?php
require "db.php";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $id = $_POST["id"];
    $name = htmlspecialchars($_POST["name"]);
    $email = htmlspecialchars($_POST["email"]);

    $stmt = $pdo->prepare("UPDATE users SET name = ?, email = ? WHERE id = ?");
    if ($stmt->execute([$name, $email, $id])) {
        echo "User updated successfully!";
    } else {
        echo "Error updating user.";
    }
}
?>

<form method="post">
    ID: <input type="text" name="id"><br>
    Name: <input type="text" name="name"><br>
    Email: <input type="email" name="email"><br>
    <input type="submit" value="Update User">
</form>

🔥 What happens?

• The form lets you edit user data.
• The UPDATE query modifies an existing user based on id.

---

4️⃣ Delete (Remove Data)

<?php
require "db.php";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $id = $_POST["id"];

    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    if ($stmt->execute([$id])) {
        echo "User deleted!";
    } else {
        echo "Error deleting user.";
    }
}
?>

<form method="post">
    ID: <input type="text" name="id"><br>
    <input type="submit" value="Delete User">
</form>

🔥 What happens?

• Deletes a user based on id.
• Prevents accidental deletion using prepared statements.

---

🎯 Mini Project: Simple User Management System

Now, let’s put everything together into a mini CRUD app.

1️⃣ user_list.php (List Users & Actions)

<?php
require "db.php";
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<h3>User List</h3>";
foreach ($users as $user) {
    echo "{$user['id']}: {$user['name']} ({$user['email']}) 
          <a href='edit_user.php?id={$user['id']}'>Edit</a> | 
          <a href='delete_user.php?id={$user['id']}'>Delete</a><br>";
}
echo "<a href='add_user.php'>Add New User</a>";
?>

2️⃣ add_user.php (Add User Form)

<form method="post" action="insert.php">
    Name: <input type="text" name="name"><br>
    Email: <input type="email" name="email"><br>
    <input type="submit" value="Add User">
</form>

3️⃣ insert.php (Handles User Insertion)

<?php
require "db.php";
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute([$_POST['name'], $_POST['email']]);
header("Location: user_list.php");
?>

4️⃣ delete_user.php (Delete a User)

<?php
require "db.php";
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([$_GET['id']]);
header("Location: user_list.php");
?>

✅ What’s Happening?

• user_list.php lists all users with Edit/Delete links.
• add_user.php provides a form to add a new user.
• insert.php processes the form and redirects back to user_list.php.
• delete_user.php removes a user from the database.

🔥 Boom! You now have a basic user management system! 🚀

---

🚀 Final Thoughts

Now you can:
✅ Connect PHP to MySQL using PDO
✅ Perform CRUD operations (Create, Read, Update, Delete)
✅ Prevent SQL injection with prepared statements
✅ Build a simple user management system

👉 Next: Connecting PHP to MySQL Using PDO and MySQLi

Happy coding! 🎉🚀