Working with the Provided BaseModel class

What is the BaseModel class?

The BaseModel class provides a set of convenient methods for database operations using prepared statements. All methods use named or positional parameters for security against SQL injection.

This guide demonstrates how to use the BaseModel class and its PDO wrapper methods in your application.

---

Basic Model Creation

Create a model class by extending BaseModel. The constructor should call the parent constructor with the PDOService instance.

<?php

namespace App\Domain\Models;

use App\Helpers\Core\PDOService;

class UserModel extends BaseModel
{
    public function __construct(PDOService $db_service)
    {
        parent::__construct($db_service);
    }

    // Your custom methods here...
}

---

Using Models in Controllers

Use in your controller:

class UserController extends BaseController
{
    public function __construct(Container $container, private UserModel $userModel)
    {
        parent::__construct($container);
    }

    public function show(Request $request, Response $response, array $args): Response
    {
        $userId = (int) $args['id'];
        $user = $this->userModel->findById($userId);

        if (!$user) {
            throw new HttpNotFoundException($request, 'User not found');
        }

        return $this->render($response, 'user/show.php', ['user' => $user]);
    }
}

---

Available Methods

selectAll() - Retrieve Multiple Records

// Get all users.
public function getAllUsers(): array
{
    return $this->selectAll('SELECT * FROM users ORDER BY created_at DESC');
}

// Get users with conditions using named parameters.
public function getUsersByStatus(string $status): array
{
    return $this->selectAll(
        'SELECT * FROM users WHERE status = :status ORDER BY name',
        ['status' => $status]
    );
}

// Get users with multiple conditions.
public function getUsersByRoleAndStatus(string $role, string $status): array
{
    return $this->selectAll(
        'SELECT * FROM users WHERE role = :role AND status = :status',
        ['role' => $role, 'status' => $status]
    );
}

---

selectOne() - Retrieve Single Record

// Find user by ID using positional parameter.
public function findById(int $id): array|false
{
    return $this->selectOne('SELECT * FROM users WHERE id = ?', [$id]);
}

// Find user by email using named parameter.
public function findByEmail(string $email): array|false
{
    return $this->selectOne(
        'SELECT * FROM users WHERE email = :email',
        ['email' => $email]
    );
}

// Get user with related data.
public function getUserWithProfile(int $userId): array|false
{
    return $this->selectOne(
        'SELECT u.*, p.bio, p.avatar
         FROM users u
         LEFT JOIN profiles p ON u.id = p.user_id
         WHERE u.id = :user_id',
        ['user_id' => $userId]
    );
}

---

count() - Count Records

// Count all users.
public function getTotalUsers(): int
{
    return $this->count('SELECT COUNT(*) FROM users');
}

// Count users by status.
public function countUsersByStatus(string $status): int
{
    return $this->count(
        'SELECT COUNT(*) FROM users WHERE status = :status',
        ['status' => $status]
    );
}

---

execute() - Insert, Update, Delete Operations

// Insert new user.
public function createUser(array $userData): int
{
    $rowsAffected = $this->execute(
        'INSERT INTO users (name, email, password, status, created_at)
         VALUES (:name, :email, :password, :status, :created_at)',
        [
            'name' => $userData['name'],
            'email' => $userData['email'],
            'password' => password_hash($userData['password'], PASSWORD_DEFAULT),
            'status' => 'active',
            'created_at' => date('Y-m-d H:i:s')
        ]
    );

    return $rowsAffected;
}

// Update user information.
public function updateUser(int $id, array $userData): int
{
    return $this->execute(
        'UPDATE users
         SET name = :name, email = :email, updated_at = :updated_at
         WHERE id = :id',
        [
            'id' => $id,
            'name' => $userData['name'],
            'email' => $userData['email'],
            'updated_at' => date('Y-m-d H:i:s')
        ]
    );
}

// Soft delete user.
public function deleteUser(int $id): int
{
    return $this->execute(
        'UPDATE users SET status = :status, deleted_at = :deleted_at WHERE id = :id',
        [
            'id' => $id,
            'status' => 'deleted',
            'deleted_at' => date('Y-m-d H:i:s')
        ]
    );
}

---

lastInsertId() - Get Last Inserted ID

public function createUserAndGetId(array $userData): string
{
    $this->execute(
        'INSERT INTO users (name, email, password, created_at)
         VALUES (:name, :email, :password, :created_at)',
        [
            'name' => $userData['name'],
            'email' => $userData['email'],
            'password' => password_hash($userData['password'], PASSWORD_DEFAULT),
            'created_at' => date('Y-m-d H:i:s')
        ]
    );

    return $this->lastInsertId();
}

---

Best Practices

1. Use Named Parameters

Prefer named parameters over positional parameters for better readability:

// Good - clear and readable.
$this->selectOne('SELECT * FROM users WHERE email = :email AND status = :status', [
    'email' => $email,
    'status' => 'active'
]);

// Avoid - harder to read and maintain.
$this->selectOne('SELECT * FROM users WHERE email = ? AND status = ?', [$email, 'active']);

---

2. Validate Input

Always validate input before using in queries:

public function findById(int $id): array|false
{
    if ($id <= 0) {
        throw new InvalidArgumentException('User ID must be positive');
    }

    return $this->selectOne('SELECT * FROM users WHERE id = :id', ['id' => $id]);
}

---

4. Handle Errors Gracefully

Always handle potential database errors:

public function safeCreateUser(array $userData): array
{
    try {
        $userId = $this->createUser($userData);
        return ['success' => true, 'user_id' => $userId];
    } catch (Exception $e) {
        error_log('User creation failed: ' . $e->getMessage());
        return ['success' => false, 'error' => 'Failed to create user'];
    }
}