Separate model from DB mapper (entity manager) I've read that it is good to separate model from DB mapper (entity manager) (but at the end, it is perhaps personal - just a matter of opinion). Anyway, if I understood correctly, I should not have data models with DB connections, I should not have any methods with SQL (CRUD) in a class, right?

I will give you how my code looks and if you could tell me how to separate - where to put CRUD (for example separate file name), how it would look and how class User should look after that. 


I have a database with a table named users with the following columns: 


id
username
password
first_name
last_name

In the directory includes I have database.php:

$connection = new PDO($dsn, $user, $pass, [ PDO::ATTR_ERRMODE =&gt; PDO::ERRMODE_EXCEPTION]);

Also, in the directory includes I have user.php with a class named User and it has DB connection and methods with CRUD (SQL):

&lt;?php

class User
{
    protected $connection = null;

    protected $statments = [];

    protected static $table_name="users";


    protected $id = null;
    protected $username = null;
    protected $password = null;
    protected $first_name = null;
    protected $last_name = null;

    private $arrayOfUserInstances = array();

    public function __construct(PDO $connection)
    {
        $this-&gt;connection = $connection;
    }



    // SETTERS ...



    // GETTERS ...




    // METHODS:

    private function instantiate($record) {
        $user = new User($this-&gt;connection);
        foreach ($record as $attribute=&gt;$value) {
            if (property_exists('User', $attribute)) {
                $user-&gt;$attribute = $value;
            }
        }
        return $user;
    }

    public function findAllUsers()
    {
        if (!isset($this-&gt;statments[__METHOD__])) {
            $this-&gt;statments[__METHOD__] = $this-&gt;connection-&gt;prepare("SELECT * FROM users");
        }
        $stmt = $this-&gt;statments[__METHOD__];
        if (!($stmt-&gt;execute())) {
            return null;
        }
        while ($record = $stmt-&gt;fetch(PDO::FETCH_ASSOC)) {
            $user = $this-&gt;instantiate($record);
            $this-&gt;arrayOfUserInstances[] = $user;
        }
        $stmt-&gt;closeCursor();
        return $this-&gt;arrayOfUserInstances;
    }

    public function findById($id)
    {
        if (!is_numeric($id) || $id &lt; 1) {
            throw new InvalidArgumentException('ID must be positive integer');
        }
        if (!isset($this-&gt;statements[__METHOD__])) {
            $this-&gt;statements[__METHOD__] = $this-&gt;connection-&gt;prepare('SELECT * FROM users WHERE id = :id');
        }
        $stmt = $this-&gt;statements[__METHOD__];
        $stmt-&gt;execute([':id' =&gt; $id]);
        $record = $stmt-&gt;fetch(PDO::FETCH_ASSOC);
        $stmt-&gt;closeCursor();
        if (!$record) {
            return null;
        }

        return $this-&gt;instantiate($record);
    }

    public function fullName() {
        if (isset($this-&gt;first_name) &amp;&amp; isset($this-&gt;last_name)) {
            return $this-&gt;getFirstName()." ".$this-&gt;getLastName();
        } else {
            return null;
        }
    }
}
The User class is doing too much. It represents a &quot;User&quot; and also handles the data connection and mapping. You have a need for two classes in the very least. The Repository Pattern is a common design solution for this and is quite easy to set up.
You first need to split up your concerns. You have a &quot;User&quot; and a &quot;place users are stored.&quot;
The User class would be called the Domain Model. This represents a row from the &quot;users&quot; table and contains the data and methods that operate on that data. It does not contain data access methods. Rather, it should only contain &quot;business logic.&quot;
From Wikipedia:

A domain model is a system of abstractions that describes select aspects of a sphere of knowledge, influence, or activity (a domain [3]). The model can then be used to solve problems related to that domain. The domain model is a representation of meaningful real-world concepts pertinent to the domain that need to be modeled in software. The concepts include the data involved in the business and rules the business uses in relation to that data.
A domain model generally uses the vocabulary of the domain so that a representation of the model can be used to communicate with non-technical stakeholders.

(emphasis, mine)
class User
{
    private $id;
    private $username;
    private $password;
    private $first_name;
    private $last_name;

    public function __construct($username, $password, $id = 0) {
        $this-&gt;setUsername($username);
        $this-&gt;setPassword($password);
        $this-&gt;setId($id);
    }

    // Getters

    // Setters

    public function getFullName() {
        if (isset($this-&gt;first_name) &amp;&amp; isset($this-&gt;last_name)) {
            return $this-&gt;getFirstName() . ' ' . $this-&gt;getLastName();
        } else {
            return null;
        }
    }

    public function canChangePassword($oldPassword, $newPassword) {
        // Logic to check if the password can be changed
    }

    public function changePassword($oldPassword, $newPassword) {
        if (!$this-&gt;canChangePassword($oldPassword, $newPassword)) {
            throw new Exception(&quot;Cannot change password&quot;);
        }

        $this-&gt;password = $newPassword;
    }
}

Not every property needs a setter method. In fact, a User is not even a valid object unless it has a username, and password. The constructor is the perfect place to supply those values because the username shouldn't change, and the password can change, but the change in value should be tightly controlled by the User object (hence the canChangePassword and changePassword methods).
The data access code would go into a UserRepository class that specializes in CRUD operations on the &quot;users&quot; table. You can optionally create an interface for the repository class in case you want to unit test your application and provide a mock user repository.
interface IUserRepository
{
    public function add(User $user);
    public function findAll();
    public function find($id);
    public function findByUsername($username);
    public function update(User $user);
    public function remove(User $user);
}

The interface sets up the main operations you can perform on Users in the database. The UserRepository class below is where we actually start the data access code:
class UserRepository implements IUserRepository
{
    private $connection;

    public function __construct(PDO $connection) {
        $this-&gt;connection = $connection;
    }

    public function add(User $user) {
        // Create prepared statement for INSERT
    }

    public function findAll() {
        $results = // Query by prepared statement
        $users = [];

        foreach ($results as $data) {
            $users[] = $this-&gt;createInstance($data);
        }

        return $users;
    }

    public function find($id) {
        $data = // Query by prepared statement

        return $this-&gt;createInstance($data);
    }

    public function findByUsername($username) {
        $data = // Query by prepared statement

        return $this-&gt;createInstance($data);
    }

    public function update(User $user) {
        // Create prepared statement for UPDATE
    }

    public function remove(User $user) {
        // Create prepared statement for DELETE
    }

    private function createInstance($data) {
        return $this-&gt;map($data, new User($data['username'], $data['password'], $data['id']));
    }

    private function map($data, User $user) {
        foreach ($record as $attribute =&gt; $value) {
            $method = 'set' . ucfirst($attribute);

            if (method_exists($user, $method)) {
                call_user_func_array(array($user, $method), array($value));
            } else {
                throw new Exception(&quot;No setter method exists for '$attribute'&quot;);
            }
        }

        return $user;
    }
}

The instantiating and mapping of User objects is kept internal to the repository class. You could split this concern out into its own &quot;mapper&quot; or &quot;factory&quot; class if you wanted, but I generally start out with the mapping code right inside the repository class. Later on if I need to split out the mapping code, doing so is a simple refactoring job because these methods are private.
Now let's explore a sample use case where we change the password of a user:
// Create the &quot;repostory&quot; object
$users = new UserRepository($pdo);

// Fetch the user from the database
$user = $users-&gt;findByUsername('jdoe');

// Modify the User
$user-&gt;changePassword('password1', 'password2');

// Persist the changes back to the database
$users-&gt;update($user);

Clean and simple. That's the goal of splitting out your data access from your domain model. Furthermore, since your User class is devoid of data access code, you can now write unit tests validating the business logic of its methods.
Unit Testing
Earlier I mentioned that defining an interface for the repository can assist unit testing. We'll take a simple &quot;MVC&quot; example to illustrate this point. We have a &quot;users controller&quot; class that handles an HTTP request. It has two arguments in its constructor. The first argument has a Type Hint of IUserRepository and the second argument is the $_GET or $_POST array.
class UsersController
{
    private $params;
    private $users;

    public function __construct(IUserRepository $users, $params) {
        $this-&gt;users = $users;
        $this-&gt;params = $params;
    }

    public function changePassword() {
        $user = $this-&gt;users-&gt;findByUsername($this-&gt;params['username']);
        $user-&gt;changePassword($this-&gt;params['password1'], $this-&gt;params['password2']);
        $this-&gt;users-&gt;update($user);
    }
}

The changePassword method queries the repository for the user and calls the changePassword method on the User object. Lastly, it updates the user. Since the $this-&gt;users property was injected into the UsersController object in the constructor with a Type Hint, we can provide our own mock object to unit test the controller with.
class MockUserRepository implements IUserRepository
{
    private $data;

    public function __construct($existingUsers = array()) {
        $this-&gt;data = array(
            'all' =&gt; $existingUsers,
            'added' =&gt; array(),
            'updated' =&gt; array(),
            'removed' =&gt; array()
        );
    }

    public function add(User $user) {
        $this-&gt;data['added'][] = $user;
        $this-&gt;data['all'][] = $user;
    }

    public function findAll() {
        return $this-&gt;data['all'];
    }

    public function find($id) {
        // Find in $this-&gt;data['all']
    }

    public function findByUsername($username) {
        // Find in $this-&gt;data['all']
    }

    public function update(User $user) {
        $this-&gt;data['updated'][] = $user;
    }

    public function remove(User $user) {
        // remove from $this-&gt;data['all']
        $this-&gt;data['removed'][] = $user;
    }

    public function getData() {
        return $this-&gt;data;
    }
}

Now, the simple unit test:
$userToFind = new User('jdoe', 'password1', 123);
$mockUsers = new MockUserRepository(array($userToFind));
$params = array(
    'username' =&gt; 'jdoe',
    'password1' =&gt; 'password1',
    'password2' =&gt; 'abc123'
);
$controller = new UsersController($mockUsers, $params);

$controller-&gt;changePassword();

// Assert that $userToFind has a different password
// Assert that $userToFind was &quot;updated&quot;

Now we can unit test something more complex like a &quot;controller&quot; in an MVC application without requiring a connection to the database.

@PeraMika said:

I hope that it is OK to just have class User and class UserRepository without interface and or other classes that you have created here.

The interface is not a requirement. The most important thing is to separate the Domain Model from the Data Access.
@PeraMika said:

Is it good to have two classes for every table in DB, for example - if we have a table &quot;Comments&quot; - class Comment and class CommentRepository? Or, if we have table categories - class Category and class CategoryRepository?

The answer is two parts: &quot;Yes&quot; and &quot;It depends&quot;.
If you have two tables called &quot;comments&quot; and &quot;categories&quot; you should have two Domain Models: Comment and Category. Yes, you should create one Domain Model class per table in the database.
When it comes to creating the repository classes, it depends. It's not a requirement to create one repository class per database table. If you don't have a large number of tables, one class might suffice. As your application grows, the number of tables grow. As a result, your data access code grows in size and stuffing all of it into one file can become cumbersome to maintain.
Really the decision of how many repository classes you need comes down to good object oriented design. I always approach class design with these in mind first:

Do one thing and do it well
Don't Repeat Yourself (DRY)

Do you need one repository class per table? No. If you start out doing this then you are creating a system that is easily extensible, yet doesn't make any assumptions about future business requirements. This is why I advocate for one repository class per table.
@PeraMika asked:

In methods with SQL (PDO), for example findAll()... is it good to check if the query is executed successfully, and if not - return false; For example:
if (!($stmt-&gt;execute())) {
    return false;
}

Also, in findById($id) is it good to have something like:
$record = $stmt-&gt;fetch(PDO::FETCH_ASSOC); $stmt-&gt; closeCursor();

if (!$record) {
    return false;
}


Many Object Relation/Mapping (ORM) libraries in multiple languages and platforms seem to implement these rules:

If multiple records can be returned, but the query does not return results, an empty array or &quot;collection&quot; is returned. This helps prevents null pointer exceptions in later code

If a single record should be returned, for instance querying by primary key, and the query does not return results, return null instead and let the calling code decide if this is an &quot;exceptional&quot; condition (and then throw an exception).

If a single record should be returned and multiple records are found, throw an exception. You are expecting something to be unique and it is not. Your repository cannot resolve this situation, so failing early and failing loudly is the best option.

If the execute method returns false because of a failure in the database or problem with the SQL, this is an &quot;exceptional&quot; condition. It is appropriate to throw an exception in this scenario with the message being the error message sent back from the database.