Efficiency of getters/setters with database performance

Question

I'm currently in the process of refactoring a very old personal project of mine, which used procedural PHP-in-HTML code. I'm modernising it as a learning project, using OOP where beneficial. I understand the basics of OOP but I'm wondering if I understand correctly as it would seem my old way of doing this was faster in this case.

On a user's profile page, all of their stats are listed. It's a game so there are lots of individual fields containing stats like wins, losses etc.

Now I've made a User class and given every stat its own getter/setter. A typical pair looks like this:

function getEmoticon() {
    if ($this->emoticon) {
        return $this->emoticon;
    }

    $stmt = $this->conn->prepare('SELECT emoticon FROM profile WHERE id=:userid');
    $stmt->execute(array('userid' => $this->userid));
    $row = $stmt->fetch();

    return $this->emoticon = $row['emoticon'];
}

function setEmoticon($emoticon) {
    $stmt = $this->conn->prepare('UPDATE profile SET emoticon=:emoticon WHERE id= :id');
    $stmt->execute(array('id' => $this->userid, 'emoticon' => $emoticon));
    return $this->emoticon = $emoticon;
}

There used to be one single "GET * FROM profile" SQL query to get all the user's stats in a single query. Now though, I'm resorting to using the getter for every stat, resulting in a database query for every stat. On the profile page it means it has gone from 1 database query to dozens.

Is this the right way to do things? It seems more inefficient but I'm not sure how else to do it, unless I have a User class method like getAllStats() or something.

Answer 1

Yeah, don't do that.

A typical approach would be to have a LoadUser($user_id) function. It will return a user with all its attributes loaded from the database. Hence, load all the attributes of the object when you first create it, don't try to lazily load each attribute one at a time. It will be simpler and more efficient.

When you want to save it, either have a Save(), or keep track of all the objects that have been modified and them save them all at once.

As a general rule, be suspicious of lazy loading like this. I find that it 90% of cases its better just to load the data up front.