Connecting Models to the Database: Implementing the MVC Model

Previously in this course, we learned how to perform basic CRUD operations using Connecting to MySQL with PDO: A Secure Beginner’s Guide and Executing Prepared Statements: A Secure PHP Guide. In those lessons, you likely wrote your SQL queries directly inside your page templates. While that works for tiny scripts, it creates a maintenance nightmare as your project grows.

In this lesson, we are introducing the MVC Model — the "M" in Model-View-Controller. By abstracting our database logic into dedicated classes, we separate our data storage concerns from our visual interface.

Why Use an MVC Model?

If your logic to fetch a "Task" or a "User" is scattered across several .php files, changing a table name or a column requires you to hunt through your entire project.

The Model layer acts as a gatekeeper. Your application asks the Model for data, and the Model figures out how to get it from the database. The rest of your app doesn't need to know if you're using MySQL, a JSON file, or an external API; it only cares about the data the Model returns.

The First Principles of Data Abstraction

To move from "scripting" to "engineering," we follow these rules:

1. Never write SQL in a View: Your display files should only loop through arrays, not write SELECT * FROM.
2. Models return data: A Model should return clean arrays or objects, not handle echo or HTML.
3. Centralize connections: The Model should receive a database connection instance rather than creating one every time.

Worked Example: Creating a Task Model

Let's assume our project manages a list of tasks. We will create a TaskModel class to handle the data fetching.

1. Define the Model Class

Create a file named src/Models/TaskModel.php. We will pass the PDO connection into the constructor so the model can reuse it.

PHP
<?php

class TaskModel {
    private $db;

    public function __construct(PDO $db) {
        $this->db = $db;
    }

    public function getAllTasks(): array {
        $stmt = $this->db->query("SELECT * FROM tasks");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function findById(int $id): ?array {
        $stmt = $this->db->prepare("SELECT * FROM tasks WHERE id = :id");
        $stmt->execute(['id' => $id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return $result ?: null;
    }
}

2. Using the Model in your Application

Now, instead of writing SQL in your index file, you instantiate your Model:

PHP
#6A9955">// index.php
require 'db.php'; #6A9955">// Your PDO connection file
require 'src/Models/TaskModel.php';

$taskModel = new TaskModel($pdo);
$tasks = $taskModel->getAllTasks();

#6A9955">// Now you can safely use $tasks in your HTML
foreach ($tasks as $task) {
    echo "<li>" . htmlspecialchars($task['title']) . "</li>";
}

Hands-on Exercise

Refactor one of your existing CRUD scripts. If you have a file that lists items from the database, follow these steps:

1. Create a class file (e.g., ProductModel.php).
2. Move the SELECT query into a method within that class.
3. Update your original file to require the class, create an instance, and call your new method to get the data.

Common Pitfalls

• Hardcoding Connections: Don't put new PDO(...) inside the Model's constructor. Inject the existing connection as shown above. This makes testing easier and prevents opening multiple connections.
• Mixing Logic: Keep your Model focused on data. Don't put header('Location: ...') or die() inside a Model method. Models should return data or throw exceptions.
• Ignoring Return Types: Always define the return type (e.g., : array or : ?array) to keep your code predictable.

FAQ

Q: Do I need a separate Model for every single database table? A: Generally, yes. One Model per entity (User, Task, Product) is the standard approach for keeping your code organized and readable.

Q: Is this the same as an ORM (Object Relational Mapper)? A: Not quite. An ORM (like Eloquent or Doctrine) is a complex library that automates this process. You are currently building a Data Access Object (DAO) pattern, which is the foundation upon which ORMs are built.

Recap

By moving your database logic into a Model, you've successfully decoupled your persistence layer from your UI. Your application is now more modular, easier to test, and significantly cleaner. You’ve taken a major step toward professional architecture by ensuring that your database queries live in one place, while your presentation remains focused on user experience.

Up next: Classes and Objects — we'll dive deeper into how to structure these classes effectively.