PHP MySQL CRUD Operations

11/22/2025

PHP MySQL CRUD operations user management example

Go Back

PHP MySQL CRUD Operations: Complete Tutorial for Beginners

When building real-world web applications, you rarely just display static content. You need to Create, Read, Update, and Delete (CRUD) data from a database. In PHP, CRUD operations are usually performed with MySQL using extensions like mysqli or PDO.

In this tutorial, you’ll learn how to build basic PHP MySQL CRUD operations using mysqli with simple, clear examples.

PHP MySQL CRUD operations user management example

What is CRUD?

CRUD stands for:

  • C – Create → Insert new data into the database

  • R – Read → Fetch and display existing data

  • U – Update → Modify existing data

  • D – Delete → Remove data from the database

Almost every module in a web app (users, products, posts, categories, etc.) is built around CRUD.

Prerequisites

Before you start, ensure you have:

  • PHP and MySQL installed (via XAMPP/WAMP/MAMP)

  • A database and table created

  • Basic knowledge of PHP and HTML

🗄 Step 1: Create Database and Table

Open phpMyAdmin and run:

CREATE DATABASE php_crud_demo;
USE php_crud_demo;

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

🔌 Step 2: Database Connection File (db_connect.php)

Create a reusable connection file:

<?php
$host = "localhost";
$user = "root";       // XAMPP default
$pass = "";           // XAMPP default
$dbname = "php_crud_demo";

$conn = mysqli_connect($host, $user, $pass, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

You will include this file in all CRUD scripts.

🟢 C – Create (Insert Data)

HTML Form (create.php)

<?php include 'db_connect.php'; ?>
<!DOCTYPE html>
<html>
<head>
    <title>Create User</title>
</head>
<body>

<h2>Add New User</h2>

<form action="create.php" method="POST">
    <label>Name:</label><br>
    <input type="text" name="name" required><br><br>

    <label>Email:</label><br>
    <input type="email" name="email" required><br><br>

    <label>City:</label><br>
    <input type="text" name="city" required><br><br>

    <button type="submit" name="submit">Save</button>
</form>

</body>
</html>

<?php
if (isset($_POST['submit'])) {
    $name  = mysqli_real_escape_string($conn, $_POST['name']);
    $email = mysqli_real_escape_string($conn, $_POST['email']);
    $city  = mysqli_real_escape_string($conn, $_POST['city']);

    $sql = "INSERT INTO users (name, email, city) VALUES ('$name', '$email', '$city')";

    if (mysqli_query($conn, $sql)) {
        echo "<p>User created successfully!</p>";
    } else {
        echo "<p>Error: " . mysqli_error($conn) . "</p>";
    }
}
?>

R – Read (Display Records)

List Users (index.php)

<?php
include 'db_connect.php';

$sql = "SELECT * FROM users ORDER BY id DESC";
$result = mysqli_query($conn, $sql);
?>

<!DOCTYPE html>
<html>
<head>
    <title>Users List</title>
</head>
<body>

<h2>Users List</h2>
<a href="create.php">Add New User</a>
<br><br>

<table border="1" cellpadding="8" cellspacing="0">
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Email</th>
        <th>City</th>
        <th>Created At</th>
        <th>Actions</th>
    </tr>

    <?php if (mysqli_num_rows($result) > 0): ?>
        <?php while ($row = mysqli_fetch_assoc($result)): ?>
            <tr>
                <td><?php echo $row['id']; ?></td>
                <td><?php echo $row['name']; ?></td>
                <td><?php echo $row['email']; ?></td>
                <td><?php echo $row['city']; ?></td>
                <td><?php echo $row['created_at']; ?></td>
                <td>
                    <a href="edit.php?id=<?php echo $row['id']; ?>">Edit</a> |
                    <a href="delete.php?id=<?php echo $row['id']; ?>" onclick="return confirm('Are you sure?');">Delete</a>
                </td>
            </tr>
        <?php endwhile; ?>
    <?php else: ?>
        <tr><td colspan="6">No users found.</td></tr>
    <?php endif; ?>

</table>

</body>
</html>

U – Update (Edit Existing Data)

Edit Form (edit.php)

<?php
include 'db_connect.php';

// Fetch existing user
if (isset($_GET['id'])) {
    $id = (int) $_GET['id'];
    $sql = "SELECT * FROM users WHERE id = $id";
    $result = mysqli_query($conn, $sql);
    $user = mysqli_fetch_assoc($result);
}

// Handle form submission
if (isset($_POST['update'])) {
    $id    = (int) $_POST['id'];
    $name  = mysqli_real_escape_string($conn, $_POST['name']);
    $email = mysqli_real_escape_string($conn, $_POST['email']);
    $city  = mysqli_real_escape_string($conn, $_POST['city']);

    $sql = "UPDATE users SET name='$name', email='$email', city='$city' WHERE id=$id";

    if (mysqli_query($conn, $sql)) {
        echo "<p>User updated successfully!</p>";
    } else {
        echo "<p>Error: " . mysqli_error($conn) . "</p>";
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Edit User</title>
</head>
<body>

<h2>Edit User</h2>

<form action="edit.php" method="POST">
    <input type="hidden" name="id" value="<?php echo $user['id']; ?>">

    <label>Name:</label><br>
    <input type="text" name="name" value="<?php echo $user['name']; ?>" required><br><br>

    <label>Email:</label><br>
    <input type="email" name="email" value="<?php echo $user['email']; ?>" required><br><br>

    <label>City:</label><br>
    <input type="text" name="city" value="<?php echo $user['city']; ?>" required><br><br>

    <button type="submit" name="update">Update</button>
</form>

</body>
</html>

D – Delete (Remove Records)

Delete Script (delete.php)

<?php
include 'db_connect.php';

if (isset($_GET['id'])) {
    $id = (int) $_GET['id'];

    $sql = "DELETE FROM users WHERE id = $id";

    if (mysqli_query($conn, $sql)) {
        echo "User deleted successfully!";
    } else {
        echo "Error: " . mysqli_error($conn);
    }
}
?>

<a href="index.php">Back to Users List</a>

Best Practices (Important!)

For real applications, you should:

  • Use prepared statements instead of directly embedding variables in SQL.

  • Validate and sanitize all user input.

  • Handle errors gracefully and avoid echoing raw SQL errors in production.

  • Protect CRUD pages with authentication (login system).

  • Implement pagination for large datasets.

Summary

In this tutorial, you built a basic PHP MySQL CRUD system using:

  • CREATE – Insert new users (create.php)

  • READ – List users (index.php)

  • UPDATE – Edit users (edit.php)

  • DELETE – Remove users (delete.php)

This structure is the foundation of most PHP-based admin panels and backend systems. Once you’re comfortable with this, you can enhance it with prepared statements, search filters, pagination, and login protection.

Table of content