Where do I put a database query in MVC?

Question

The last few days, I have extensively read books and web pages about OOP and MVC in PHP, so that I can become a better programmer. I've come upon a little problem in my understanding of MVC:

Where do I put a mysql_query?

Should I put it in the controller and call a method on a model that returns data based on the provided query? Or should I put it in the model itself? Are both of the options I'm providing total garbage?

Answer 1

Materials on the subject of MVC

You could have listed the books you were reading, because most (if not all) php books, which touch on MVC, are wrong.

If you want to become a better developer, i would recommend for you to start with article by Marting Fowler - GUI Architectures. Followed by book from same author - "Patterns of Enterprise Application Architecture". Then the next step would be for you to research SOLID principles and understand how to write code which follows Law of Demeter. This should cover the basics =]

Can I use MVC with PHP ?

Not really. At least not the classical MVC as it was defined for Smalltalk.

Instead in PHP you have 4 other patterns which aim for the same goal: MVC Model2, MVP, MVVM and HMVC. Again, I am too lazy to write about differences one more time, so I'll just link to an old comment of mine.

What is Model ?

First thing you must understand is that Model in MVC is not a class or an object. It is a layer which contains multitude of classes. Basically model layer is all of the layers combined (though, the second layer there should be called "Domain Object Layer", because it contains "Domain Model Objects"). If you care to read quick summary on what is contained in each part of Model layer, you can try reading this old comment (skip to "side note" section).

                            
The image is taken from Service Layer article on Fowler's site.

What does the Controllers do ?

Controller has one major responsibilities in MVC (I'm gonna talk about Model2 implementation here):

Execute commands on structures from model layer (services or domain objects), which change the state of said structures.

It usually have a secondary responsibility: to bind (or otherwise pass) structures from Model layer to the View, but it becomes a questionable practice, if you follow SRP

Where do I put SQL related code ?

The storage and retrieval of information is handled at the Data Source Layer, and is usually implemented as DataMapper (do not confuse with ORMs, which abuse that name).

Here is how a simplified use of it would look like:

$mapper = $this->mapperFactory->build(Model\Mappers\User::class);
$user = $this->entityFactory->build(Model\Entities\User::class);

$user->setId(42);
$mapper->fetch($user);

if ($user->isBanned() && $user->hasBannExpired()){
    $user->setStatus(Model\Mappers\User::STATUS_ACTIVE);
}

$mapper->store($user);

As you see, at no point the Domain Object is even aware, that the information from it was stored. And neither it cases about where you put the data. It could be stored in MySQL or PostgreSQL or some noSQL database. Or maybe pushed to remote REST API. Or maybe the mapper was a mock for testing. All you would need to do, to replace the mapper, is provide this method with different factory.

Also, please see these related posts:

• understanding MVC Views in PHP
• testable Controllers with dependencies
• how should services communicate between each other?
• MVC for advanced PHP developers

Answer 2

Model and Entity Classes represents the data and the logic of an application, what many calls business logic. Usually, it’s responsible for:

1. Storing, deleting, updating the application data. Generally it includes the database operations, but implementing the same operations invoking external web services or APIs is not an unusual at all.
2. encapsulating the application logic. This is the layer that should implement all the logic of the application

Here is the MVC Sequence Diagram which shows the flow during a http request:

In this case Model is the best place to implement the code realted to access database.

Answer 3

For one, don't use mysql_query() and family; they're being deprecated, so consider also learning about PDO and/or mysqli.

The model takes care of data handling; it provides an interface to the controller by which it retrieves and/or stores information. So this would be a primary place where database actions take place.

Update

To answer a question asked by the OP in the comments: "one generic model for the whole db or a model for each table/action?"

Models are meant to abstract away individual tables (although there are models that exclusively handle a single table); for instance, instead of asking for all articles and then query the usernames for the authors you would have one function like this:

function getArticles()
{
    // query article table and join with user table to get username
}

How many models you will create largely depends on how big the project is and how inter-related the data is. If you can identify independent groups of data, it's likely that you'd create a model for each group; but this is no hard & fast rule.

Data manipulation can be part of the same model, unless you want a clear separation between read-only and write-only models (I wouldn't know of a situation that warrants this, but who knows).

Answer 4

The model contains the domain objects or data structures that represent the application's state. [wikipedia]. So the model would be the place to make the database call.

In the 'classic' (lack of a better word atm) MVC pattern the view would get the current state from the model.

Don't make the mistake by saying that the model is for accessing the database. It's more than just accessing the database.

Answer 5

To go even further, your model should not contain the database access code. This belongs to another layer outside the Model/View/Controller: this is called the persistence layer, which can be implemented using an Object-Relational Mapper such as the popular Doctrine 2 for PHP.

This way, you never touch any (my)SQL code. The persistence layer takes care of this for you. I really advise you to have a look at a Doctrine tutorial, this is a really professional way to create your applications.

Instead of working with raw data loaded from the database, you create objects that hold your data, and the behavior associated with it.

For example, you might have a User class, such as:

class User
{
    protected $id;
    protected $name;
    protected $privileges;

    public function setName($name) { ... }
    public function getName() { ... }

    public function addPrivilege(Privilege $privilege) { ... }
    public function getPrivileges() { ... }
}

You controller will only interact with objects:

class UserController
{
    public function testAction()
    {
        // ...

        $user = $em->getRepository('User')->find(123); // load User with id 123
        $user->setName('John'); // work with your objects,
        echo $user->getName();  // and don't worry about the db!
        $em->flush(); // persist your changes
    }
}

Behind the scenes, the ORM takes care of all the low-level work of issuing a SELECT query, instantiating your object, detecting modifications to your object, and issuing the necessary UPDATE statement!