如何使用PHP / MySQL实现良好的MVC模式而不会丢失SQL请求时间/服务器内存？ （良好做法）

Question

I want to implement a real pattern MVC for my php controllers. Especially, i want to split Model and API by creating java "bean" equivalent in PHP (objects made for business organization) and an API using these business objects.

For exemple, my basic object is the Member. The question is : where i request my database ? Do I request all the members proprities right at __construct, and i simply access them with the getters OR i do nothing in the __construct and I call the database in every getter function ? People tell me that the 1st solution is better, though, if i want only a specific information in my controller, i will create a Member with all the informations computed right at construct (bad memory management). In the 2nd case, if i want several members proprities, i will do several SQL request which will increase my server execution time.

1st solution :

public function __construct($ID_membre,$sql)
{
    $this->ID_membre = $ID_membre;
    $res = mysql_get("select * from membres where ID_membre = $ID_membre",$sql);
    if(!$res)
        throw new Exceptions\MyDefaultException("no member for this Id");

    $this->firstName = $res['first_name'];
    $this->lastName = $res['last_name'];
    $this->mail = $res['mail'];
    $this->gender = $res['gender'];
    // ....

    $this->sql = $sql;
}
public function getLastName()
{
    return $this->lastName;
}
public function getMail()
{
    return $this->mail;
}
public function getGender()
{
    return $this->gender;
}
// .... 

2nd solution :

public function __construct($ID_membre,$sql)
{
    $this->ID_membre = $ID_membre;
    $res = mysql_get("select count(*) from membres where ID = $ID_membre",$sql);
    if($res == 0)
        throw new Exceptions\MyDefaultException("no member with this id");



    $this->sql = $sql;
}
public function getLastName()
{
    mysql_get("select name from members where ID = {$this->id}",$this->sql);
    return $this->lastName;
}
public function getMail()
{
    mysql_get("select mail from members where ID = {$this->id}",$this->sql);
    return $this->mail;
}
public function getGender()
{
    mysql_get("select gender from members where ID = {$this->id}",$this->sql);
    return $this->gender;
}

In this context, good old SQL custom request within controllers are perfect to not waste time or memory, because they are customs. So, why doing such request are so poorly viewed nowsaday ? And if big organizations such as Fb or Google do MVC with database, how they manage to not waste any time/memory while splitting Model and controllers ?

Answer 1

This is a classic problem, which can even get worse if you want one property of many members.

The standard answer is that solution 1 is better. Requesting one row from a database doesn't take much longer than requesting one value from a database, so it makes sense to ask a whole row at once. That is unless your database rows get very big. That should however not occur in good database design. If your rows get so big that they hamper efficiency then it is probably time to split the table.

Now back to the problem I mentioned at the start of this answer. You haven't solved this. My suggestion would be to make two classes: One with solution 1, dealing with one row, and one with solution 2 dealing with multiple rows.

So both solutions have their place, it just that solution 2 is almost always inefficient for dealing with one row, and I haven't even talked about the amount of extra coding it requires.