How to separate query logic in DBMS and PHP?

Question

PHP

Previously, when there were small projects and there was no time to devote proper time to learning MySQL, I did a simple or a couple of simple queries in MySQL, then I processed the result in PHP, perhaps making a couple more queries in a loop. Naturally, this is wrong. Began to study denser DBMS. The new versions have a lot of features, including conditions and more.
So, how not to overload the database query logic? How to properly separate the work of DBMS and PHP?

Answer 1

1. Controller or analogues.
2. Service level.
3. Business logic
layer 4. Data storage layer
Layers 2-4 are the model, layers 3-4 communicate through requests and commands.

Answer 2

How to properly separate the work of DBMS and PHP?

There is a very simple principle.
The DBMS is a subsystem that stores and retrieves data, and the application (in PHP or whatever) processes and modifies them, if necessary.
The DBMS is architecturally (and physically) "closer" to the data. One of the most important tasks of the DBMS is to select from the entire volume of the database only the data that is currently needed . It is at the DBMS level that maximum filtering should be performed. She has everything you need for this - indexes and other tricky data structures.
However, in order for the DBMS to perform its main tasks better, you should not load it with unnecessary computational work. Everything that is not related to maintaining the integrity and consistency of data is given to the application level - then these calculations (which include, for example, preparing JSON responses or assembling a web page according to a template) can be performed logically and physically on other nodes, which entails provides obvious benefits.
Therefore, the conclusion is this: choose as few records and columns/attributes as possible in a database query (i.e., for example, no * in planned queries), and do as much work with them at the application level (as far as possible to comply with the required integrity assurance).
By "as few records as possible" I mean keep the query as specific and narrow as possible, but sufficient to accomplish the current task.

Answer 3

I all the same would try to give the process of data sampling to the DBMS as much as possible. This option is definitely faster and more reliable.

Answer 4

Everyone has a different definition of "correct". In essence, it all boils down to "either all or nothing."
If you have an understanding of how the DBMS works and its capabilities, you will most likely put everything into it: the application will only "go there for data" (pull one "storage" of all the heaps of requests), and write data, again through the "storage" and one request instead of a heap. We even have "schedulers" in the database itself (if they have to change the data or do something with it).
Or the option when the DBMS acts as a string store with advanced features such as secondary keys and indexes + more spreading syntax for "select", this is usually with MySql in view of its "features"

Answer 5

In addition to what Vyacheslav said:
1. If the calculations are carried out at the database level, then this introduces additional time and complexity costs in the development of unit tests.
2. Theoretically - it will also be problematic to migrate to another database, say MongoDB, for example. After all, the calculations are hardcoded in a certain engine. And so you have to duplicate the logic of calculations in the new engine, which is not very correct.
3. There is such a principle - it is called SoC. So, he says that responsibilities should be shared. Business logic and calculations are one responsibility, but data storage is another.