A class for working with a specific table?

Question

I'm writing a class to work with a specific table, but it seems to me that I'm somehow writing it incorrectly or something, redneck code) Are there examples of how to write such a class correctly? For example, I can't imagine how to get multiple records + add a save. For example, so that when passing through the loop, I can tweak the value and call save.

How the code looks now:

class Account {
        private $database;

        private $properties = [];
        private $updated = [];

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

        public function __get($name) {
            if(isset($this->properties[$name])) {
                return $this->properties[$name];
            }
        }

        public function __set($name, $message) {
            if(isset($this->properties[$name])) {
                if((string) $name != 'id') {
                    $this->updated[$name] = $message;
                    $this->properties[$name] = $message;
                }
            }
        }

        public function getById($id) {
            $object = $this->database->query('SELECT * FROM `ACCOUNTS` WHERE `id` = ?', [ $id ])->fetch();

            if(isset($object->id)) {
                $this->properties = json_decode(json_encode($object), true);
            }
        }

        public function save() {
            if(isset($this->properties['id'])) {
                if(!empty($this->updated)) {
                    $prepare  = 'UPDATE `ACCOUNTS` SET ';
                    $prepare .= '`' . implode('` = ?, `', array_keys($this->updated)) . '` = ? ';
                    $prepare .= 'WHERE `id` = ?';
    
                    $this->updated += [ 'id' => $this->properties['id'] ];
                    $this->database->query($prepare, array_values($this->updated));
                    $this->updated = [];
                }
            }
        }
    }

$account = new Account($database);
    $account->getById(1);
    $account->username = '@tests';
    $account->save();

Answer 1

What you are trying to implement is the ActiveRecord pattern, google the implementations.
And the nuances

• why the class is called Account and at the same time you use the magic get and set here, in principle, everything is clear and you know which fields you need to network, so you can simply declare them as public fields
  
• the save method is rather strange how you will distinguish between an update and an insert (you can make the isInsert flag) and if the model was received through your search methods, then set it to false which means update
  
• For searching, make your methods static, and always return a new instance (collection, array) of your class, then you will solve the problem with the loop
  
• According to modern design rules, this pattern is an anti-pattern, as it takes on too much functionality and is tied to a specific database.
  

See the Yii2 and Laravel frameworks for good implementations of this pattern.

Answer 2

Instead

public function getById($id) {
            $object = $this->database->query('SELECT * FROM `ACCOUNTS` WHERE `id` = ?', [ $id ])->fetch();

            if(isset($object->id)) {
                $this->properties = json_decode(json_encode($object), true);
            }
        }

it should be like this (I don’t know the notation of your wrapper for the database, I write at random):

public function getById($id) {
            $object = $this->database->query('SELECT * FROM ? WHERE `id` = ?', [ $this->table, $id ])->fetch();

            if(isset($object->id)) {
                $this->properties = json_decode(json_encode($object), true);
            }
        }

those. if you make a class a la ActiveRecord, then make inheritance from the base class, in which there will be all methods of the highest level of the getById type (and not get, but findById). And specific classes should simply inherit from it and have knowledge about $this->table - either define them manually for each specific class, or with the help of some kind of magic.
In general, everything is not bad and correct, you even introduced the updated property, for this a separate respect.
Only I didn't understand this:

if(isset($object->id)) {
                $this->properties = json_decode(json_encode($object), true);
            }

I can’t imagine how to get multiple records + add the save method to each of these records

findCollectionById method, for example. Returns an array of objects to you. You loop through this array and call save() for each object.