My Web Training

Using MySQL Update for Soft Deletes

In a typical workflow, a user clicks a link or button to deactivate their profile. This link passes the user's unique ID through the query string and routes them to a confirmation screen.

Instead of deleting user records, a soft delete updates the user's status in the database — typically by setting an is_blocked column to 1. This preserves the data while disabling the account.

💡 Pro Tip: Use a two-step process: first show the user’s details, then ask for confirmation, and finally submit the change using a secure POST request.

Deactivation Steps

• A user clicks a Deactivate Profile link, which includes their id in the query string (e.g., deactivate.php?id=2).
• The controller reads the id from $_GET, retrieves the matching record from the database, and loads the deactivate.php view with user details.
• The view displays a confirmation message with a POST form and hidden id field.
• When the form is submitted, the controller reads the id from $_POST and passes it to the model's deactivateUser() method.
• The model runs a parameterized UPDATE query that sets is_blocked to 1 for the matching user.
• If successful, the controller redirects to the homepage or a success screen. If not, it redirects back to the profile or shows an error.

📁 Project Structure

This structure outlines how the components of the soft delete system work together:

project-root/
├── controllers/
│   └── UserController.php        ← logic for displaying and processing the form
├── models/
│   └── UserModel.php             ← database interaction for user records
├── views/
│   ├── partials/
│   │   ├── header.php            ← shared HTML header and navigation
│   │   └── footer.php            ← shared footer and closing tags
│   └── profile/
│       ├── create.php            ← displays the registration form
│       ├── deactivate.php        ← *new* confirmation form for deactivation
│       ├── edit.php              ← displays the edit profile form
│       ├── show.php              ← displays the user profile view
│       └── partials/
│           └── form-fields.php   ← shared form fields for create and edit views
├── deactivate.php                ← *new* entry point for soft delete POST requests
├── profile.php                   ← entry point for profile view/edit/update
└── register.php                  ← entry point for user registration

Adding a Deactivate Link

Place this button in your views/profile/show.php file after displaying the user's profile details:

<a class="btn btn-danger" href="deactivate.php?id=<?= htmlspecialchars($user['id']) ?>">
  Deactivate Profile
</a>

This link directs the user to deactivate.php where they can confirm or cancel the action.

⚠️ Important: This link only works if a valid user id exists in your database. Always validate IDs before using them in queries to prevent errors and misuse.

Entry Point: deactivate.php

This file serves as the entry point for handling the soft delete request. It loads the controller and calls the deactivate() method after the form is submitted.

deactivate.php

<?php
require_once 'controllers/UserController.php';
UserController::deactivate();
?>

Place this file in your project root directory. It acts as a lightweight router that safely delegates logic to the controller without exposing internal paths.

Controller Logic

Add the following method to your UserController class. This method handles both the GET request to load the confirmation view and the POST request to perform the soft delete:

public static function deactivate() {
  $id = $_POST['id'] ?? $_GET['id'] ?? null;

  // Check server request method is POST
  if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Check for POST $id and db update success
    if ($id && UserModel::deactivateUser($id)) {
      header("Location: index.php?deactivate=success");
      exit;
    }
    header("Location: index.php?error=failed");
    exit;
  }

  // Check for GET $id, select db record and load the deactivate.php view
  if ($id && $user = UserModel::getUserById($id)) {
    require 'views/profile/deactivate.php';
    return;
  }

  header("Location: index.php?error=notfound");
  exit;
}

🧠 Best Practice: Keeping both GET and POST logic in the same controller method simplifies routing and avoids spreading logic across multiple files.

Deactivation View

Create a new file at views/profile/deactivate.php to display the confirmation form and user info. This prevents accidental or unauthorized deactivations by requiring a POST submission.

<?php include 'views/partials/header.php'; ?>

<h2>Confirm Deactivation</h2>
<p>Are you sure you want to deactivate this account? You must contact the webmaster to reactivate it.</p>

<form method="post" action="deactivate.php">
  <input type="hidden" name="id" value="<?= htmlspecialchars($user['id']) ?>">
  <button type="submit" name="deactivate" class="btn btn-danger">Confirm Deactivation</button>
  <a href="profile.php?id=<?= htmlspecialchars($user['id']) ?>" class="btn btn-secondary">Cancel</a>
</form>

<?php include 'views/partials/footer.php'; ?>

⚠️ Security Tip: Always use a POST request to finalize sensitive changes like deactivation. Avoid relying on GET requests for destructive actions.

Model Logic

Step 1: Update the Database Schema

Add an is_blocked column to the users table to track deactivation status:

ALTER TABLE users ADD COLUMN is_blocked TINYINT(1) NOT NULL DEFAULT 0;

Step 2: Add Model Method

Include this method in your UserModel class to flag a user as deactivated:

public static function deactivateUser($id) {
  $db = static::getDB();
  $stmt = $db->prepare("UPDATE users SET is_blocked = 1 WHERE id = :id LIMIT 1");
  return $stmt->execute([':id' => $id]);
}

🛡️ Security Tip: Always use parameterized queries when updating the database. This protects your app from SQL injection vulnerabilities.

Summary / Takeaways

• Soft deletes preserve data by marking records as inactive rather than removing them
• Route soft delete requests through a dedicated entry point like deactivate.php
• Use a two-step process: confirmation view followed by POST submission
• Handle routing and logic inside the controller for clarity
• Use an is_blocked column to track deactivation status in the database