[php] How should a model be structured in MVC?

I am just getting a grasp on the MVC framework and I often wonder how much code should go in the model. I tend to have a data access class that has methods like this:

public function CheckUsername($connection, $username)
{
    try
    {
        $data = array();
        $data['Username'] = $username;

        //// SQL
        $sql = "SELECT Username FROM" . $this->usersTableName . " WHERE Username = :Username";

        //// Execute statement
        return $this->ExecuteObject($connection, $sql, $data);
    }
    catch(Exception $e)
    {
        throw $e;
    }
}

My models tend to be an entity class that is mapped to the database table.

Should the model object have all the database mapped properties as well as the code above or is it OK to separate that code out that actually does the database work?

Will I end up having four layers?

More oftenly most of the applications will have data,display and processing part and we just put all those in the letters M,V and C.

Model(M)-->Has the attributes that holds state of application and it dont know any thing about V and C.

View(V)-->Has displaying format for the application and and only knows about how-to-digest model on it and does not bother about C.

Controller(C)---->Has processing part of application and acts as wiring between M and V and it depends on both M,V unlike M and V.

Altogether there is separation of concern between each. In future any change or enhancements can be added very easily.

In my case I have a database class that handle all the direct database interaction such as querying, fetching, and such. So if I had to change my database from MySQL to PostgreSQL there won't be any problem. So adding that extra layer can be useful.

Each table can have its own class and have its specific methods, but to actually get the data, it lets the database class handle it:

File Database.php

class Database {
    private static $connection;
    private static $current_query;
    ...

    public static function query($sql) {
        if (!self::$connection){
            self::open_connection();
        }
        self::$current_query = $sql;
        $result = mysql_query($sql,self::$connection);

        if (!$result){
            self::close_connection();
            // throw custom error
            // The query failed for some reason. here is query :: self::$current_query
            $error = new Error(2,"There is an Error in the query.\n<b>Query:</b>\n{$sql}\n");
            $error->handleError();
        }
        return $result;
    }
 ....

    public static function find_by_sql($sql){
        if (!is_string($sql))
            return false;

        $result_set = self::query($sql);
        $obj_arr = array();
        while ($row = self::fetch_array($result_set))
        {
            $obj_arr[] = self::instantiate($row);
        }
        return $obj_arr;
    }
}

Table object classL

class DomainPeer extends Database {

    public static function getDomainInfoList() {
        $sql = 'SELECT ';
        $sql .='d.`id`,';
        $sql .='d.`name`,';
        $sql .='d.`shortName`,';
        $sql .='d.`created_at`,';
        $sql .='d.`updated_at`,';
        $sql .='count(q.id) as queries ';
        $sql .='FROM `domains` d ';
        $sql .='LEFT JOIN queries q on q.domainId = d.id ';
        $sql .='GROUP BY d.id';
        return self::find_by_sql($sql);
    }

    ....
}

I hope this example helps you create a good structure.

Everything that is business logic belongs in a model, whether it is a database query, calculations, a REST call, etc.

You can have the data access in the model itself, the MVC pattern doesn't restrict you from doing that. You can sugar coat it with services, mappers and what not, but the actual definition of a model is a layer that handles business logic, nothing more, nothing less. It can be a class, a function, or a complete module with a gazillion objects if that's what you want.

It's always easier to have a separate object that actually executes the database queries instead of having them being executed in the model directly: this will especially come in handy when unit testing (because of the easiness of injecting a mock database dependency in your model):

class Database {
   protected $_conn;

   public function __construct($connection) {
       $this->_conn = $connection;
   }

   public function ExecuteObject($sql, $data) {
       // stuff
   }
}

abstract class Model {
   protected $_db;

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

class User extends Model {
   public function CheckUsername($username) {
       // ...
       $sql = "SELECT Username FROM" . $this->usersTableName . " WHERE ...";
       return $this->_db->ExecuteObject($sql, $data);
   }
}

$db = new Database($conn);
$model = new User($db);
$model->CheckUsername('foo');

Also, in PHP, you rarely need to catch/rethrow exceptions because the backtrace is preserved, especially in a case like your example. Just let the exception be thrown and catch it in the controller instead.

In Web-"MVC" you can do whatever you please.

The original concept ⁽¹⁾ described the model as the business logic. It should represent the application state and enforce some data consistency. That approach is often described as "fat model".

Most PHP frameworks follow a more shallow approach, where the model is just a database interface. But at the very least these models should still validate the incoming data and relations.

Either way, you're not very far off if you separate the SQL stuff or database calls into another layer. This way you only need to concern yourself with the real data/behaviour, not with the actual storage API. (It's however unreasonable to overdo it. You'll e.g. never be able to replace a database backend with a filestorage if that wasn't designed ahead.)