In our previous article, we explored how to establish a connection between PHP and MySQL, and how to retrieve data from the database and display it on a webpage. We covered the fundamental concepts of setting up a local server environment, writing a basic connection script, and executing a simple SELECT query.
In this article, we will take things a step further by implementing full CRUD operations — Create, Read, Update, and Delete — using PHP and MySQL. We will build a practical user management system that includes a registration form with validation, a login system using sessions and cookies, and a dynamic user listing table with edit and delete functionality. By the end of this article, you will have a fully functional mini-application demonstrating all four CRUD operations in action.
Table of Contents:
- Database and Table Setup
- Database Connection
- User Registration Form with Validation
- User Login Form
- Dashboard page with Session data and Users table
- Edit User Data
- Delete User Data
- Conclusion
1 – Database and Table Setup
First, open phpMyAdmin or your MySQL client and create a new database named Customers. Inside this database, we will create table named users.
CREATE DATABASE Customers;
USE Customers;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
gender ENUM('Male', 'Female') NOT NULL,
city VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL
);The users table stores complete user profile data like first name, last name, gender, city, email, and password.
2 – Database Connection
Create a file named db_connection.php in the project directory. This file will be included in every PHP file that requires database access.
<?php
$host = "localhost";
$username = "root";
$password = "root";
$database = "Customers";
$conn = mysqli_connect( $host, $username, $password, $database );
if( ! $conn ) {
die( "Connection failed: " . mysqli_connect_error() );
}
?>3 – User Registration Form with Validation
Create a file named register.php in the project directory. This file contains the HTML registration form along with PHP-based server-side validation.
Validation Rules:
- First Name, Last Name, City: Must start with a letter (a–z or A–Z). No numbers or special characters allowed.
- Email: Must follow a valid email pattern.
- Gender: Selected via radio buttons (Male / Female).
- Password: Must be alphanumeric with at least one special character, minimum 8 characters, maximum 15 characters.
First, we will set up the user interface by creating the form structure and applying visual styles. Open register.php and add the HTML form structure.
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>User Registration</title>
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
<!-- Registration Form -->
<div class="container">
<h2>User Registration</h2>
<form method="POST" action="register.php">
<div class="form-group">
<label for="first_name">First Name <span style="color:red">*</span></label>
<input type="text" id="first_name" name="first_name" placeholder="Enter your first name" value="<?= isset( $_POST['first_name'] ) ? htmlspecialchars( $_POST['first_name'] ) : '' ?>">
</div>
<div class="form-group">
<label for="last_name">Last Name <span style="color:red">*</span></label>
<input type="text" id="last_name" name="last_name" placeholder="Enter your last name" value="<?= isset( $_POST['last_name'] ) ? htmlspecialchars( $_POST['last_name'] ) : '' ?>">
</div>
<div class="form-group">
<label>Gender <span style="color:red">*</span></label>
<div class="radio-group">
<label>
<input type="radio" name="gender" value="Male" <?= ( isset( $_POST['gender'] ) && $_POST['gender'] == 'Male' ) ? 'checked' : ' ' ?>>
Male
</label>
<label>
<input type="radio" name="gender" value="Female" <?= ( isset( $_POST['gender'] ) && $_POST['gender'] == 'Female' ) ? 'checked' : ' ' ?>>
Female
</label>
</div>
</div>
<div class="form-group">
<label for="city">City <span style="color:red">*</span></label>
<input type="text" id="city" name="city" placeholder="Enter your city" value="<?= isset( $_POST['city'] ) ? htmlspecialchars( $_POST['city'] ) : ' ' ?>">
</div>
<div class="form-group">
<label for="email">Email <span style="color:red">*</span></label>
<input type="email" id="email" name="email" placeholder="Enter your email" value="<?= isset( $_POST['email'] ) ? htmlspecialchars( $_POST['email'] ) : ' ' ?>">
</div>
<div class="form-group">
<label for="password">Password <span style="color:red">*</span></label>
<input type="password" id="password" name="password" placeholder="Min 8, Max 15 chars with letter, number & special char">
</div>
<div class="form-group">
<button type="submit" class="btn-register">Register</button>
</div>
</form>
<div class="login-link">
Already have an account? <a href="login.php">Login here</a>
</div>
</div>
</body>
</html>Open style.css and add custom styles to design the form.
* {
margin: 0;
padding: 0;
box-sizing: border-box;
}
body {
font-family: 'verdana', sans-serif;
background: #f4f4f4;
margin: 0;
padding: 20px;
}
.container {
max-width: 800px;
margin: 0 auto;
background: #fff;
padding: 30px;
border-radius: 8px;
box-shadow: 0 2px 8px rgba(0,0,0,0.1);
}
h2 {
text-align: center;
color: #333;
}
.form-group {
margin-bottom: 15px;
}
label {
display: block;
margin-bottom: 5px;
font-weight: bold;
color: #555;
}
input[type="text"],
input[type="email"],
input[type="password"] {
width: 100%;
padding: 10px;
border: 1px solid #ccc;
border-radius: 5px;
box-sizing: border-box;
font-size: 14px;
}
input[type="text"]:focus,
input[type="email"]:focus,
input[type="password"]:focus {
border-color: #4a90e2;
outline: none;
}
.radio-group {
display: flex;
gap: 20px;
margin-top: 5px;
}
.radio-group label {
font-weight: normal;
}
.btn-register,
.btn-login {
width: 100%;
padding: 12px;
background: #4a90e2;
color: #fff;
border: none;
border-radius: 5px;
font-size: 16px;
cursor: pointer;
}
.btn-register:hover {
background: #357abd;
}
.error-list {
background: #ffe0e0;
border: 1px solid #f5c6cb;
padding: 10px 15px;
border-radius: 5px;
margin-bottom: 15px;
color: #721c24;
}
.error-list ul {
margin: 5px 0;
padding-left: 20px;
}
.success-msg {
background: #d4edda;
border: 1px solid #c3e6cb;
padding: 10px 15px;
border-radius: 5px;
margin-bottom: 15px;
color: #155724;
}
.table-container {
max-width: 900px;
margin: 30px auto;
background: #fff;
padding: 20px;
border-radius: 8px;
box-shadow: 0 2px 8px rgba(0,0,0,0.1);
}
table {
width: 100%;
border-collapse: collapse;
margin-top: 10px;
}
th, td {
padding: 10px 12px;
border: 1px solid #ddd;
text-align: left;
font-size: 14px;
}
th {
background: #4a90e2;
color: #fff;
}
tr:nth-child(even) {
background: #f9f9f9;
}
.btn-edit {
color: #fff;
background: #28a745;
padding: 5px 10px;
border-radius: 4px;
text-decoration: none;
font-size: 13px;
}
.btn-delete {
color: #fff;
background: #dc3545;
padding: 5px 10px;
border-radius: 4px;
text-decoration: none;
font-size: 13px;
margin-left: 5px;
}
.btn-edit:hover {
background: #218838;
}
.btn-delete:hover {
background: #c82333;
}
.login-link {
text-align: center;
margin-top: 15px;
}
.login-link a {
color: #4a90e2;
text-decoration: none;
}Next, we will handle user input securely by implementing server-side logic. Open register.php again. Insert the PHP validation and submission code at the very top of the file.
<?php
include 'db_connection.php';
$errors = [];
$success = " ";
if( $_SERVER["REQUEST_METHOD"] == "POST" ) {
// Get form data
$first_name = trim( $_POST['first_name'] );
$last_name = trim( $_POST['last_name'] );
$gender = $_POST['gender'] ?? '';
$city = trim( $_POST['city'] );
$email = trim( $_POST['email'] );
$password = trim( $_POST['password'] );
// Validate First Name
if( empty( $first_name ) ) {
$errors[] = "First Name is required.";
} elseif( ! preg_match( "/^[a-zA-Z][a-zA-Z\s]*$/", $first_name ) ) {
$errors[] = "First Name must start with a letter (a-z or A-Z).";
}
// Validate Last Name
if( empty( $last_name ) ) {
$errors[] = "Last Name is required.";
} elseif( ! preg_match( "/^[a-zA-Z][a-zA-Z\s]*$/", $last_name ) ) {
$errors[] = "Last Name must start with a letter (a-z or A-Z).";
}
// Validate Gender
if( empty( $gender ) ) {
$errors[] = "Please select a gender.";
}
// Validate City
if (empty($city)) {
$errors[] = "City is required.";
} elseif (!preg_match("/^[a-zA-Z][a-zA-Z\s]*$/", $city)) {
$errors[] = "City must start with a letter (a-z or A-Z).";
}
// Validate Email
if (empty($email)) {
$errors[] = "Email is required.";
} elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$errors[] = "Invalid email format.";
} else {
// Check if email already exists
$check = mysqli_query($conn, "SELECT id FROM users WHERE email='$email'");
if (mysqli_num_rows($check) > 0) {
$errors[] = "Email already registered. Please use a different email.";
}
}
// Validate Password
if (empty($password)) {
$errors[] = "Password is required.";
} elseif (!preg_match("/^(?=.*[a-zA-Z])(?=.*[0-9])(?=.*[\W_]).{8,15}$/", $password)) {
$errors[] = "Password must be 8-15 characters and include letters, numbers, and a special character.";
}
// If no errors, insert into database
if (empty($errors)) {
$hashed_password = password_hash($password, PASSWORD_DEFAULT);
$sql = "INSERT INTO users (first_name, last_name, gender, city, email, password)
VALUES ('$first_name', '$last_name', '$gender', '$city', '$email', '$hashed_password')";
if (mysqli_query($conn, $sql)) {
$success = "User registered successfully!";
header("Location: login.php");
exit;
} else {
$errors[] = "Something went wrong. Please try again.";
}
}
}
?>Note: For production applications, always use prepared statements to prevent SQL injection.
4 – User Login Form
After a successful registration, the user is redirected to the login page. We will now build the login interface and its authentication logic. Create a new file named login.php. Add the HTML login form markup into this file.
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>User Registration</title>
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
<div class="container">
<h2>Login Form</h2>
<?php if (!empty($errors)): ?>
<div class="error-list">
<ul>
<?php foreach ($errors as $error): ?>
<li><?= htmlspecialchars($error) ?></li>
<?php endforeach; ?>
</ul>
</div>
<?php endif; ?>
<form method="POST" action="login.php">
<div class="form-group">
<label for="email">Email <span style="color:red">*</span></label>
<input type="email" id="email" name="email" placeholder="Enter your email" value="<?= isset($_POST['email']) ? htmlspecialchars($_POST['email']) : '' ?>">
</div>
<div class="form-group">
<label for="password">Password <span style="color:red">*</span></label>
<input type="password" id="password" name="password" placeholder="Enter your password">
</div>
<div class="form-group">
<button type="submit" class="btn-login">Login</button>
</div>
</form>
<div class="login-link">
If you have not an account? <a href="register.php">Register </a>here
</div>
</body>
</html>Next, we will connect the form to the database to validate credentials and securely log the user in. Open login.php and insert the PHP authentication code at the very top, before the HTML markup.
<?php
session_start();
require_once 'db_connection.php';
$errors = [];
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$email = trim($_POST['email']);
$password = trim($_POST['password']);
// Validation
if (empty($email)) {
$errors[] = "Email is required.";
}
if (empty($password)) {
$errors[] = "Password is required.";
}
// If no errors → check user
if (empty($errors)) {
// Get user by email
$stmt = $conn->prepare("SELECT id, first_name, email, password FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows === 1) {
$user = $result->fetch_assoc();
// Verify password
if (password_verify($password, $user['password'])) {
// Store session
$_SESSION['user_id'] = $user['id'];
$_SESSION['user_name'] = $user['first_name'];
// Redirect to dashboard
header("Location: dashboard.php");
exit;
} else {
$errors[] = "Invalid email or password.";
}
} else {
$errors[] = "Invalid email or password.";
}
}
}
?>This PHP script will check the entered email and password against the users table. Redirect the user to the Dashboard page upon a successful match.
5 – Dashboard page with Session data and Users table
We will create a dashboard.php page that greets the logged-in user and displays all users from the database in a table.
At the very top of dashboard.php, start the session and include the database connection file.
<?php
session_start();
include 'db_connection.php';session_start() must be called before any output is sent to the browser. The include 'db_connection.php' line loads the database connection so we can query the users table.
Only logged-in users should be able to access the dashboard. We check whether the session has a user_id value. If not, the user is redirected back to the login page.
// Protect page (only logged-in users)
if (!isset($_SESSION['user_id'])) {
header("Location: login.php");
exit;
}The exit after header() is important — it stops the rest of the script from running after the redirect.
Now we run a query to fetch all records from the users table.
// Fetch users
$result = mysqli_query($conn, "SELECT * FROM users");
?>We store the result in $result, which we will loop through later in the HTML.
Inside the HTML, we greet the logged-in user by name using the value stored in the session. We use htmlspecialchars() to prevent XSS (Cross-Site Scripting) attacks.
<h2>Welcome <?= htmlspecialchars($_SESSION['user_name']) ?> 🎉</h2>You can place a Logout link next to the welcome message so the user can easily sign out.
<a href="logout.php">Logout</a>We create an HTML table with columns for ID, Name, Gender, City, Email, and Actions.
<table border="1">
<tr>
<td>ID</td>
<td>Name</td>
<td>Gender</td>
<td>City</td>
<td>Email</td>
<td>Action</td>
</tr>We use a while loop with mysqli_fetch_assoc() to go through each row returned from the database and print it as a table row.
<?php while($row = mysqli_fetch_assoc($result)): ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= htmlspecialchars($row['first_name'] . ' ' . $row['last_name']) ?></td>
<td><?= $row['gender'] ?></td>
<td><?= $row['city'] ?></td>
<td><?= $row['email'] ?></td>
<td>
<a href="edit.php?id=<?= $row['id'] ?>">Edit</a>
<a href="delete.php?id=<?= $row['id'] ?>"
onclick="return confirm('Are you sure you want to delete this user?')">Delete</a>
</td>
</tr>
<?php endwhile; ?>Each row has an Edit link that goes to edit.php and a Delete link that goes to delete.php, both passing the user’s id as a URL parameter. The delete link includes a JavaScript confirmation dialog so the user does not accidentally delete a record.
6 – Edit User Data
When a user clicks the Edit button on the dashboard, the browser redirects them to edit.php with the URL carrying the user’s ID like this:
edit.php?id=3Now let’s build the edit.php page step by step.
Just like on the dashboard page, we start the session and include the database connection at the very top.
We make sure only logged-in users can access the edit page. If PHP finds no session, the browser sends the user back to the login page.
if( ! isset( $_SESSION['user_id'] ) ) {
header( "Location: login.php" );
exit;
}When the user clicks the Edit button on the dashboard, PHP passes the user’s ID through the URL as a query parameter. We read that value using $_GET['id'].
$id = (int) $_GET['id'];We cast it to an integer with (int) to make sure no harmful input can be passed through the URL.
Using the ID from the URL, we query the database to fetch that specific user’s current details. This data will be used to pre-fill the edit form so the user can see the existing values before making changes.
$result = mysqli_query( $conn, "SELECT * FROM users WHERE id = $id" );
$row = mysqli_fetch_assoc( $result );When the user updates the form and clicks the submit button, the form data is sent via POST. We check for the form submission and run an UPDATE query to save the changes to the database.
if( $_SERVER['REQUEST_METHOD'] == 'POST' ) {
$first_name = htmlspecialchars( $_POST['first_name'] );
$last_name = htmlspecialchars( $_POST['last_name'] );
$gender = htmlspecialchars( $_POST['gender'] );
$city = htmlspecialchars( $_POST['city'] );
$email = htmlspecialchars( $_POST['email'] );
mysqli_query( $conn, "UPDATE users SET
first_name = '$first_name', last_name='$last_name',
gender='$gender', city='$city', email='$email'
WHERE id=$id" );
header( "Location: dashboard.php" );
exit;
}
?>After a successful update, the browser redirects the user back to the dashboard.
In the HTML, we display a form pre-filled with the user’s current data using the $row values we fetched. This way the user can see what is already saved and only change what they need to.
<form method="POST">
<input type="text" name="first_name" value="<?= htmlspecialchars( $row['first_name'] ) ?>">
<input type="text" name="last_name" value="<?= htmlspecialchars( $row['last_name'] ) ?>">
<input type="text" name="gender" value="<?= htmlspecialchars( $row['gender'] ) ?>">
<input type="text" name="city" value="<?= htmlspecialchars( $row['city'] ) ?>">
<input type="email" name="email" value="<?= htmlspecialchars( $row['email'] ) ?>">
<button type="submit">Update</button>
</form>It is good practice to give the user a way to cancel and go back to the dashboard without making any changes.
<a href="dashboard.php">Back to Dashboard</a>That’s it! With these steps, edit.php page will read the user ID from the URL, load that user’s current data into a form, and save the updated values back to the database when the user submits the form.
7 – Delete User Data
When a user clicks the Delete link on the dashboard, a JavaScript confirmation alert appears first asking the user to confirm the action. The onclick attribute on the delete link handles this behavior directly on the dashboard.
<a href="delete.php?id=<?= $row['id'] ?>"
onclick="return confirm( 'Are you sure you want to delete this user?' )">Delete</a>If the user clicks OK, the browser redirects them to delete.php with the user’s ID in the URL. If they click Cancel, nothing happens and they stay on the dashboard.
Now let’s build the delete.php file step by step.
We start the session and include the database connection at the top of the file.
We check whether the user is logged in. If PHP finds no session, the browser redirects them back to the login page so unauthorized users cannot directly access this file via the URL.
if( ! isset( $_SESSION['user_id'] ) ) {
header( "Location: login.php" );
exit;
}We read the id value passed through the URL. The ?? null coalescing operator sets a default value of 0 if no ID is provided in the URL.
$id = $_GET['id'] ?? 0;Before running any database query, we validate the ID to make sure it is a positive number. If the ID is missing, non-numeric, or zero, the user is sent back to the dashboard and no delete operation takes place.
if( ! is_numeric( $id ) || $id <= 0 ) {
header( "Location: dashboard.php" );
exit;
}This is an important security step that prevents invalid or malicious values from reaching the database.
Instead of inserting the ID directly into the query string, we use a prepared statement. This protects the database from SQL injection attacks.
$stmt = $conn->prepare( "DELETE FROM users WHERE id = ?" );
$stmt->bind_param( "i", $id );The ? is a placeholder for the ID value. The bind_param( “i”, $id ) line binds the actual ID to that placeholder, where "i" tells PHP the value is an integer.
We execute the prepared statement to delete the user from the database. Once done, the user is immediately redirected back to the dashboard where the updated users list will no longer include the deleted record.
$stmt->execute();
header("Location: dashboard.php");
exit;That’s it! With these steps, your delete.php page will safely read the user ID from the URL, validate it, and permanently remove that user from the database using a prepared statement before sending the user back to the dashboard.
8 – Conclusion
In this article, we successfully implemented all four CRUD operations — Create, Read, Update, and Delete — using PHP and MySQL. We built a complete user management system that includes a validated registration form, a secure login system using sessions, a dynamic user listing table, and fully functional edit and delete capabilities. These are the core building blocks of virtually every web application. In our upcoming articles, we will explore how to make this system more secure using prepared statements and how to enhance the UI using Bootstrap. Stay tuned!
If you like this article, then you can write your words in the comments section. Or if you want to know more about PHP & MySQL then don’t hesitate to reach out us. Not least but last, you can also follow us on X.com.