PHP CRUD Operations

Introduction to CRUD Operations

CRUD operations (Create, Read, Update, Delete) form the foundation of database interactions in PHP applications. These four basic operations allow you to manage data effectively, from inserting new records to querying existing data, updating information, and removing unwanted entries.

Understanding CRUD operations is essential for building data-driven PHP applications that interact with databases securely and efficiently.

Why CRUD Operations Matter

Data Management: CRUD operations provide the fundamental tools for managing application data throughout its lifecycle.

Security: Proper CRUD implementation with prepared statements protects against SQL injection attacks.

Performance: Optimized CRUD operations ensure efficient database interactions and improved application speed.

Maintainability: Well-structured CRUD code is easier to maintain, debug, and extend.

User Experience: Efficient CRUD operations enable responsive user interfaces and smooth data interactions.

CRUD Components

Create: Adding new records to the database with proper validation and error handling.

Read: Retrieving data from the database with filtering, sorting, and pagination.

Update: Modifying existing records while maintaining data integrity.

Delete: Removing records with safety checks and relationship handling.

Create Operations

Inserting New Records

<?php
/**
 * Create Operations - Inserting Data
 * 
 * Demonstrates secure data insertion with validation,
 * error handling, and best practices.
 */

class UserCRUD
{
    private PDO $pdo;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    /**
     * Create single user
     */
    public function createUser(array $userData): array
    {
        // Validate input data
        $validation = $this->validateUserData($userData);
        if (!$validation['valid']) {
            return ['success' => false, 'errors' => $validation['errors']];
        }
        
        try {
            // Prepare data for insertion
            $data = [
                'name' => trim($userData['name']),
                'email' => strtolower(trim($userData['email'])),
                'password' => password_hash($userData['password'], PASSWORD_DEFAULT),
                'role' => $userData['role'] ?? 'user',
                'active' => $userData['active'] ?? 1,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s')
            ];
            
            $sql = "INSERT INTO users (name, email, password, role, active, created_at, updated_at) 
                    VALUES (:name, :email, :password, :role, :active, :created_at, :updated_at)";
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($data);
            
            $userId = $this->pdo->lastInsertId();
            
            return [
                'success' => true,
                'user_id' => $userId,
                'message' => 'User created successfully'
            ];
            
        } catch (PDOException $e) {
            if ($e->getCode() === '23000') {
                return ['success' => false, 'errors' => ['email' => 'Email already exists']];
            }
            
            throw $e;
        }
    }
    
    /**
     * Batch create multiple users
     */
    public function createMultipleUsers(array $users): array
    {
        $results = [];
        $this->pdo->beginTransaction();
        
        try {
            foreach ($users as $index => $userData) {
                $result = $this->createUser($userData);
                $results[$index] = $result;
                
                if (!$result['success']) {
                    $this->pdo->rollback();
                    return ['success' => false, 'results' => $results];
                }
            }
            
            $this->pdo->commit();
            return ['success' => true, 'results' => $results];
            
        } catch (Exception $e) {
            $this->pdo->rollback();
            throw $e;
        }
    }
    
    /**
     * Create user with profile
     */
    public function createUserWithProfile(array $userData, array $profileData): array
    {
        $this->pdo->beginTransaction();
        
        try {
            // Create user
            $userResult = $this->createUser($userData);
            if (!$userResult['success']) {
                $this->pdo->rollback();
                return $userResult;
            }
            
            // Create profile
            $profileData['user_id'] = $userResult['user_id'];
            $profileData['created_at'] = date('Y-m-d H:i:s');
            
            $sql = "INSERT INTO user_profiles (user_id, bio, phone, address, created_at) 
                    VALUES (:user_id, :bio, :phone, :address, :created_at)";
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($profileData);
            
            $this->pdo->commit();
            
            return [
                'success' => true,
                'user_id' => $userResult['user_id'],
                'message' => 'User and profile created successfully'
            ];
            
        } catch (Exception $e) {
            $this->pdo->rollback();
            throw $e;
        }
    }
    
    /**
     * Validate user data
     */
    private function validateUserData(array $data): array
    {
        $errors = [];
        
        // Name validation
        if (empty($data['name'])) {
            $errors['name'] = 'Name is required';
        } elseif (strlen($data['name']) < 2) {
            $errors['name'] = 'Name must be at least 2 characters';
        }
        
        // Email validation
        if (empty($data['email'])) {
            $errors['email'] = 'Email is required';
        } elseif (!filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
            $errors['email'] = 'Invalid email format';
        }
        
        // Password validation
        if (empty($data['password'])) {
            $errors['password'] = 'Password is required';
        } elseif (strlen($data['password']) < 6) {
            $errors['password'] = 'Password must be at least 6 characters';
        }
        
        // Role validation
        if (isset($data['role']) && !in_array($data['role'], ['user', 'admin', 'moderator'])) {
            $errors['role'] = 'Invalid role specified';
        }
        
        return [
            'valid' => empty($errors),
            'errors' => $errors
        ];
    }
}

// Usage examples
$userCRUD = new UserCRUD($pdo);

// Create single user
$result = $userCRUD->createUser([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => 'secure123',
    'role' => 'user'
]);

if ($result['success']) {
    echo "User created with ID: " . $result['user_id'];
} else {
    echo "Errors: " . implode(', ', $result['errors']);
}
?>

Read Operations

Querying and Retrieving Data

<?php
/**
 * Read Operations - Retrieving Data
 * 
 * Demonstrates data retrieval with filtering, sorting,
 * pagination, and relationship handling.
 */

class UserReader
{
    private PDO $pdo;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    /**
     * Find user by ID
     */
    public function findById(int $id): ?array
    {
        $sql = "SELECT * FROM users WHERE id = :id AND deleted_at IS NULL";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $id]);
        
        $user = $stmt->fetch();
        return $user ?: null;
    }
    
    /**
     * Find user by email
     */
    public function findByEmail(string $email): ?array
    {
        $sql = "SELECT * FROM users WHERE email = :email AND deleted_at IS NULL";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['email' => $email]);
        
        $user = $stmt->fetch();
        return $user ?: null;
    }
    
    /**
     * Get all users with filtering and pagination
     */
    public function getUsers(array $filters = [], int $page = 1, int $limit = 10): array
    {
        $conditions = ['deleted_at IS NULL'];
        $bindings = [];
        
        // Apply filters
        if (!empty($filters['role'])) {
            $conditions[] = 'role = :role';
            $bindings['role'] = $filters['role'];
        }
        
        if (!empty($filters['active'])) {
            $conditions[] = 'active = :active';
            $bindings['active'] = $filters['active'];
        }
        
        if (!empty($filters['search'])) {
            $conditions[] = '(name LIKE :search OR email LIKE :search)';
            $bindings['search'] = '%' . $filters['search'] . '%';
        }
        
        if (!empty($filters['created_after'])) {
            $conditions[] = 'created_at >= :created_after';
            $bindings['created_after'] = $filters['created_after'];
        }
        
        // Build WHERE clause
        $whereClause = 'WHERE ' . implode(' AND ', $conditions);
        
        // Get total count
        $countSql = "SELECT COUNT(*) FROM users $whereClause";
        $stmt = $this->pdo->prepare($countSql);
        $stmt->execute($bindings);
        $totalCount = $stmt->fetchColumn();
        
        // Get paginated results
        $offset = ($page - 1) * $limit;
        $orderBy = $filters['sort'] ?? 'created_at DESC';
        
        $sql = "SELECT * FROM users $whereClause ORDER BY $orderBy LIMIT :limit OFFSET :offset";
        $stmt = $this->pdo->prepare($sql);
        
        // Bind parameters
        foreach ($bindings as $key => $value) {
            $stmt->bindValue(":$key", $value);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        
        $stmt->execute();
        $users = $stmt->fetchAll();
        
        return [
            'data' => $users,
            'pagination' => [
                'page' => $page,
                'limit' => $limit,
                'total' => $totalCount,
                'pages' => ceil($totalCount / $limit),
                'has_next' => $page < ceil($totalCount / $limit),
                'has_prev' => $page > 1
            ]
        ];
    }
    
    /**
     * Get user with profile
     */
    public function getUserWithProfile(int $id): ?array
    {
        $sql = "SELECT u.*, p.bio, p.phone, p.address 
                FROM users u 
                LEFT JOIN user_profiles p ON u.id = p.user_id 
                WHERE u.id = :id AND u.deleted_at IS NULL";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $id]);
        
        $user = $stmt->fetch();
        return $user ?: null;
    }
    
    /**
     * Get users with their posts count
     */
    public function getUsersWithPostCount(): array
    {
        $sql = "SELECT u.*, COUNT(p.id) as post_count 
                FROM users u 
                LEFT JOIN posts p ON u.id = p.author_id 
                WHERE u.deleted_at IS NULL 
                GROUP BY u.id 
                ORDER BY post_count DESC";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        
        return $stmt->fetchAll();
    }
    
    /**
     * Search users with advanced criteria
     */
    public function searchUsers(string $query, array $options = []): array
    {
        $searchFields = $options['fields'] ?? ['name', 'email'];
        $conditions = [];
        $bindings = [];
        
        // Build search conditions
        foreach ($searchFields as $field) {
            $conditions[] = "$field LIKE :query";
        }
        $bindings['query'] = "%$query%";
        
        $whereClause = '(' . implode(' OR ', $conditions) . ') AND deleted_at IS NULL';
        
        // Additional filters
        if (!empty($options['role'])) {
            $whereClause .= ' AND role = :role';
            $bindings['role'] = $options['role'];
        }
        
        $sql = "SELECT * FROM users WHERE $whereClause ORDER BY name";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($bindings);
        
        return $stmt->fetchAll();
    }
    
    /**
     * Get user statistics
     */
    public function getUserStatistics(): array
    {
        $sql = "SELECT 
                    COUNT(*) as total_users,
                    SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) as active_users,
                    SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) as admin_users,
                    COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_users_30_days
                FROM users 
                WHERE deleted_at IS NULL";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        
        return $stmt->fetch();
    }
}

// Usage examples
$userReader = new UserReader($pdo);

// Find specific user
$user = $userReader->findById(1);
if ($user) {
    echo "Found user: " . $user['name'];
}

// Get paginated users with filters
$result = $userReader->getUsers([
    'role' => 'user',
    'active' => 1,
    'search' => 'john'
], 1, 10);

echo "Found " . count($result['data']) . " users";
echo "Total pages: " . $result['pagination']['pages'];

// Get statistics
$stats = $userReader->getUserStatistics();
echo "Total users: " . $stats['total_users'];
?>

Update Operations

Modifying Existing Records

<?php
/**
 * Update Operations - Modifying Data
 * 
 * Demonstrates secure data updates with validation,
 * optimistic locking, and audit trails.
 */

class UserUpdater
{
    private PDO $pdo;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    /**
     * Update user information
     */
    public function updateUser(int $id, array $data): array
    {
        // Check if user exists
        $currentUser = $this->getCurrentUser($id);
        if (!$currentUser) {
            return ['success' => false, 'error' => 'User not found'];
        }
        
        // Validate update data
        $validation = $this->validateUpdateData($data, $id);
        if (!$validation['valid']) {
            return ['success' => false, 'errors' => $validation['errors']];
        }
        
        try {
            // Prepare update data
            $updateData = $this->prepareUpdateData($data);
            $updateData['updated_at'] = date('Y-m-d H:i:s');
            
            // Build dynamic UPDATE query
            $setParts = [];
            foreach ($updateData as $field => $value) {
                $setParts[] = "$field = :$field";
            }
            
            $sql = "UPDATE users SET " . implode(', ', $setParts) . " WHERE id = :id";
            $updateData['id'] = $id;
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($updateData);
            
            $rowsAffected = $stmt->rowCount();
            
            if ($rowsAffected > 0) {
                // Log the update
                $this->logUserUpdate($id, $currentUser, $updateData);
                
                return [
                    'success' => true,
                    'message' => 'User updated successfully',
                    'rows_affected' => $rowsAffected
                ];
            } else {
                return ['success' => false, 'error' => 'No changes made'];
            }
            
        } catch (PDOException $e) {
            if ($e->getCode() === '23000') {
                return ['success' => false, 'error' => 'Email already exists'];
            }
            throw $e;
        }
    }
    
    /**
     * Update user password
     */
    public function updatePassword(int $id, string $currentPassword, string $newPassword): array
    {
        $user = $this->getCurrentUser($id);
        if (!$user) {
            return ['success' => false, 'error' => 'User not found'];
        }
        
        // Verify current password
        if (!password_verify($currentPassword, $user['password'])) {
            return ['success' => false, 'error' => 'Current password is incorrect'];
        }
        
        // Validate new password
        if (strlen($newPassword) < 6) {
            return ['success' => false, 'error' => 'New password must be at least 6 characters'];
        }
        
        // Update password
        $hashedPassword = password_hash($newPassword, PASSWORD_DEFAULT);
        
        $sql = "UPDATE users SET password = :password, updated_at = :updated_at WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            'password' => $hashedPassword,
            'updated_at' => date('Y-m-d H:i:s'),
            'id' => $id
        ]);
        
        return ['success' => true, 'message' => 'Password updated successfully'];
    }
    
    /**
     * Batch update multiple users
     */
    public function batchUpdate(array $updates): array
    {
        $results = [];
        $this->pdo->beginTransaction();
        
        try {
            foreach ($updates as $update) {
                $result = $this->updateUser($update['id'], $update['data']);
                $results[] = $result;
                
                if (!$result['success']) {
                    $this->pdo->rollback();
                    return ['success' => false, 'results' => $results];
                }
            }
            
            $this->pdo->commit();
            return ['success' => true, 'results' => $results];
            
        } catch (Exception $e) {
            $this->pdo->rollback();
            throw $e;
        }
    }
    
    /**
     * Toggle user status
     */
    public function toggleUserStatus(int $id): array
    {
        $sql = "UPDATE users SET active = NOT active, updated_at = :updated_at WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            'updated_at' => date('Y-m-d H:i:s'),
            'id' => $id
        ]);
        
        $rowsAffected = $stmt->rowCount();
        
        if ($rowsAffected > 0) {
            $user = $this->getCurrentUser($id);
            $status = $user['active'] ? 'activated' : 'deactivated';
            
            return [
                'success' => true,
                'message' => "User $status successfully",
                'new_status' => $user['active']
            ];
        }
        
        return ['success' => false, 'error' => 'User not found'];
    }
    
    /**
     * Get current user data
     */
    private function getCurrentUser(int $id): ?array
    {
        $sql = "SELECT * FROM users WHERE id = :id AND deleted_at IS NULL";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $id]);
        
        $user = $stmt->fetch();
        return $user ?: null;
    }
    
    /**
     * Validate update data
     */
    private function validateUpdateData(array $data, int $userId): array
    {
        $errors = [];
        
        if (isset($data['name']) && strlen($data['name']) < 2) {
            $errors['name'] = 'Name must be at least 2 characters';
        }
        
        if (isset($data['email'])) {
            if (!filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
                $errors['email'] = 'Invalid email format';
            } else {
                // Check email uniqueness
                $sql = "SELECT id FROM users WHERE email = :email AND id != :user_id AND deleted_at IS NULL";
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute(['email' => $data['email'], 'user_id' => $userId]);
                
                if ($stmt->fetch()) {
                    $errors['email'] = 'Email already exists';
                }
            }
        }
        
        return [
            'valid' => empty($errors),
            'errors' => $errors
        ];
    }
    
    /**
     * Prepare data for update
     */
    private function prepareUpdateData(array $data): array
    {
        $allowed = ['name', 'email', 'role', 'active'];
        $updateData = [];
        
        foreach ($allowed as $field) {
            if (isset($data[$field])) {
                $updateData[$field] = $data[$field];
            }
        }
        
        return $updateData;
    }
    
    /**
     * Log user update for audit trail
     */
    private function logUserUpdate(int $userId, array $oldData, array $newData): void
    {
        $changes = [];
        foreach ($newData as $field => $newValue) {
            if ($field !== 'id' && $field !== 'updated_at' && isset($oldData[$field])) {
                if ($oldData[$field] != $newValue) {
                    $changes[$field] = [
                        'old' => $oldData[$field],
                        'new' => $newValue
                    ];
                }
            }
        }
        
        if (!empty($changes)) {
            $sql = "INSERT INTO user_audit_log (user_id, changes, updated_at) VALUES (:user_id, :changes, :updated_at)";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'user_id' => $userId,
                'changes' => json_encode($changes),
                'updated_at' => date('Y-m-d H:i:s')
            ]);
        }
    }
}

// Usage examples
$userUpdater = new UserUpdater($pdo);

// Update user
$result = $userUpdater->updateUser(1, [
    'name' => 'John Smith',
    'email' => '[email protected]'
]);

if ($result['success']) {
    echo $result['message'];
} else {
    echo "Update failed: " . ($result['error'] ?? implode(', ', $result['errors']));
}

// Update password
$passwordResult = $userUpdater->updatePassword(1, 'oldpass', 'newpass123');
echo $passwordResult['message'];
?>

Delete Operations

Removing Records Safely

<?php
/**
 * Delete Operations - Removing Data
 * 
 * Demonstrates safe data deletion with soft deletes,
 * cascade handling, and data preservation.
 */

class UserDeleter
{
    private PDO $pdo;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    /**
     * Soft delete user
     */
    public function softDeleteUser(int $id): array
    {
        // Check if user exists
        $user = $this->getUser($id);
        if (!$user) {
            return ['success' => false, 'error' => 'User not found'];
        }
        
        if ($user['deleted_at']) {
            return ['success' => false, 'error' => 'User already deleted'];
        }
        
        try {
            $sql = "UPDATE users SET deleted_at = :deleted_at, active = 0 WHERE id = :id";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'deleted_at' => date('Y-m-d H:i:s'),
                'id' => $id
            ]);
            
            // Archive user data
            $this->archiveUserData($id);
            
            return [
                'success' => true,
                'message' => 'User deleted successfully'
            ];
            
        } catch (Exception $e) {
            throw $e;
        }
    }
    
    /**
     * Hard delete user (permanent)
     */
    public function hardDeleteUser(int $id): array
    {
        $user = $this->getUser($id);
        if (!$user) {
            return ['success' => false, 'error' => 'User not found'];
        }
        
        $this->pdo->beginTransaction();
        
        try {
            // Delete related data first
            $this->deleteUserRelatedData($id);
            
            // Delete user
            $sql = "DELETE FROM users WHERE id = :id";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['id' => $id]);
            
            $this->pdo->commit();
            
            return [
                'success' => true,
                'message' => 'User permanently deleted'
            ];
            
        } catch (Exception $e) {
            $this->pdo->rollback();
            throw $e;
        }
    }
    
    /**
     * Restore soft deleted user
     */
    public function restoreUser(int $id): array
    {
        $sql = "UPDATE users SET deleted_at = NULL, active = 1 WHERE id = :id AND deleted_at IS NOT NULL";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $id]);
        
        $rowsAffected = $stmt->rowCount();
        
        if ($rowsAffected > 0) {
            return [
                'success' => true,
                'message' => 'User restored successfully'
            ];
        }
        
        return ['success' => false, 'error' => 'User not found or not deleted'];
    }
    
    /**
     * Batch delete multiple users
     */
    public function batchDelete(array $userIds, bool $hard = false): array
    {
        $results = [];
        $this->pdo->beginTransaction();
        
        try {
            foreach ($userIds as $id) {
                if ($hard) {
                    $result = $this->hardDeleteUser($id);
                } else {
                    $result = $this->softDeleteUser($id);
                }
                
                $results[$id] = $result;
                
                if (!$result['success']) {
                    $this->pdo->rollback();
                    return ['success' => false, 'results' => $results];
                }
            }
            
            $this->pdo->commit();
            return ['success' => true, 'results' => $results];
            
        } catch (Exception $e) {
            $this->pdo->rollback();
            throw $e;
        }
    }
    
    /**
     * Clean up old soft deleted users
     */
    public function cleanupDeletedUsers(int $daysOld = 30): array
    {
        $cutoffDate = date('Y-m-d H:i:s', strtotime("-$daysOld days"));
        
        // Get users to be permanently deleted
        $sql = "SELECT id, name, email FROM users WHERE deleted_at IS NOT NULL AND deleted_at < :cutoff";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['cutoff' => $cutoffDate]);
        $usersToDelete = $stmt->fetchAll();
        
        if (empty($usersToDelete)) {
            return ['success' => true, 'message' => 'No users to clean up', 'deleted_count' => 0];
        }
        
        $this->pdo->beginTransaction();
        
        try {
            foreach ($usersToDelete as $user) {
                $this->deleteUserRelatedData($user['id']);
            }
            
            // Permanently delete users
            $sql = "DELETE FROM users WHERE deleted_at IS NOT NULL AND deleted_at < :cutoff";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['cutoff' => $cutoffDate]);
            
            $deletedCount = $stmt->rowCount();
            
            $this->pdo->commit();
            
            return [
                'success' => true,
                'message' => "Permanently deleted $deletedCount users",
                'deleted_count' => $deletedCount,
                'deleted_users' => $usersToDelete
            ];
            
        } catch (Exception $e) {
            $this->pdo->rollback();
            throw $e;
        }
    }
    
    /**
     * Get user data
     */
    private function getUser(int $id): ?array
    {
        $sql = "SELECT * FROM users WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $id]);
        
        $user = $stmt->fetch();
        return $user ?: null;
    }
    
    /**
     * Delete user related data
     */
    private function deleteUserRelatedData(int $userId): void
    {
        $tables = [
            'user_profiles' => 'user_id',
            'user_sessions' => 'user_id',
            'posts' => 'author_id',
            'comments' => 'user_id'
        ];
        
        foreach ($tables as $table => $column) {
            $sql = "DELETE FROM $table WHERE $column = :user_id";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['user_id' => $userId]);
        }
    }
    
    /**
     * Archive user data before deletion
     */
    private function archiveUserData(int $userId): void
    {
        $user = $this->getUser($userId);
        if (!$user) return;
        
        $archiveData = [
            'user_id' => $userId,
            'user_data' => json_encode($user),
            'archived_at' => date('Y-m-d H:i:s')
        ];
        
        $sql = "INSERT INTO deleted_users_archive (user_id, user_data, archived_at) VALUES (:user_id, :user_data, :archived_at)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($archiveData);
    }
}

// Usage examples
$userDeleter = new UserDeleter($pdo);

// Soft delete user
$result = $userDeleter->softDeleteUser(1);
if ($result['success']) {
    echo $result['message'];
}

// Restore user
$restoreResult = $userDeleter->restoreUser(1);
echo $restoreResult['message'];

// Clean up old deleted users
$cleanupResult = $userDeleter->cleanupDeletedUsers(30);
echo "Cleaned up " . $cleanupResult['deleted_count'] . " users";
?>

For more PHP database topics:

PHP PDO and MySQL - Database connections and PDO basics
PHP Prepared Statements - Advanced prepared statement usage
PHP Transactions - Database transaction management
PHP Query Builder - Advanced query building
PHP Database Migrations - Schema management

Summary

CRUD operations form the backbone of database interactions in PHP applications:

Create Operations: Secure data insertion with validation, error handling, and relationship management ensures data integrity from the start.

Read Operations: Efficient data retrieval with filtering, pagination, and relationship loading provides flexible data access patterns.

Update Operations: Safe data modification with validation, audit trails, and optimistic locking maintains data consistency.

Delete Operations: Careful data removal with soft deletes, cascade handling, and archiving preserves data integrity and enables recovery.

Security Best Practices: Prepared statements, input validation, and proper error handling protect against SQL injection and data corruption.

Performance Optimization: Efficient queries, proper indexing, and batch operations ensure responsive application performance.

Data Integrity: Transaction handling, foreign key constraints, and validation rules maintain consistent, reliable data.

Mastering CRUD operations enables you to build robust, secure database-driven PHP applications with proper data management capabilities.

Introduction to CRUD Operations

Why CRUD Operations Matter

CRUD Components

Create Operations

Inserting New Records

Read Operations

Querying and Retrieving Data

Update Operations

Modifying Existing Records

Delete Operations

Removing Records Safely

Summary

On this page

Introduction to CRUD Operations

Create Operations

Read Operations

Update Operations

Delete Operations

Related Topics

Summary