PDO interaction shared among classes

Question

I have a class for each database table I have, for example events table's rows are stored in Event classes. For each class I write, there are several methods that are exactly the same just with different variables or column names. For example:

Player class update()

public function update() {
    $conn = new PDO( db_host, db_user, db_pw );
    $sql = "UPDATE players SET name=:name, picture=:picture, position=:position, num=:num, team=:team, description=:description WHERE id = :id";
    $st = $conn->prepare ( $sql );
    $st->bindValue( ":name", $this->name, PDO::PARAM_STR );
    $st->bindValue( ":picture", $this->picture, PDO::PARAM_STR );
    $st->bindValue( ":position", $this->position, PDO::PARAM_STR );
    $st->bindValue( ":num", $this->num, PDO::PARAM_INT );
    $st->bindValue( ":team", $this->team, PDO::PARAM_INT );
    $st->bindValue( ":description", $this->description, PDO::PARAM_STR);
    $st->bindValue( ":id", $this->id, PDO::PARAM_INT );
    $st->execute();
    $conn = null;
}

Team class update()

public function update() {
    $conn = new PDO( db_host, db_user, db_pw );
    $sql = "UPDATE teams SET name=:name, sname=:sname, logo=:logo, sport=:sport WHERE id = :id";
    $st = $conn->prepare ( $sql );
    $st->bindValue( ":name", $this->name, PDO::PARAM_STR );
    $st->bindValue( ":sname", $this->sname, PDO::PARAM_STR );
    $st->bindValue( ":logo", $this->logo, PDO::PARAM_STR );
    $st->bindValue( ":sport", $this->sport, PDO::PARAM_STR );
    $st->bindValue( ":id", $this->id, PDO::PARAM_INT );
    $st->execute();
    $conn = null;
 }

As you can see the point of the methods are the same it's just different variables being bound to the statement (this is the case with more methods). Is there an easy way to do it so that each class has the same basic update(), insert(), delete()... methods but with its respective variables? I have thought of inheriting basic behaviour from a super class but from OO point of view doesn't make much sense (and im not convinced there is any way of saying "for each public variable declared in this class") but I'm not sure if there is a way to do this using compositions (or at all, really).

You'd actually need a query builder for that one tbh. Some methods like `findAll` or `findBy(property)` may work. But CRUD can't work because no table is guaranteed to have the same columns. With that said you could benefit from a proper ORM like [Doctrine](https://www.doctrine-project.org/) or [Propel](http://propelorm.org/) or any other ORM that you find to your liking. — Andrei, Oct 03 '19 at 12:53
I was hoping there was a simpler/more native way of doing this :( Thank you for your answer though — peterxz, Oct 03 '19 at 13:01
With a proper ORM it's incredibly simple. Not to mention you benefit from a fully fledged tried and tested package that millions use. There's no going wrong with that. Admittedly it takes a little while to setup but it compensates in time. — Andrei, Oct 03 '19 at 13:05
I would name those classes a "PlayerRepository", "TeamRepository" and leave them alone. It's not that bad, you have a 100% control of what is happening. — yergo, Oct 03 '19 at 13:21

Your Common Sense · Accepted Answer · 2019-12-16T11:17:11.297

Yes, this a baby ORM you are talking about and it is quite easy to implement.

First of all, create a prototype class, with all the common methods (create(), update(), find() etc).

abstract class BaseActiveRecord
{
    protected $_db;
    protected $_table;
    protected $_primary = 'id';
    protected $_fields = [];

    public function __construct($db)
    {
        $this->_db = $db;
    }
    public function read($id)
    {
        $sql = "SELECT * FROM `$this->_table` WHERE `$this->_primary` = ?";
        $stmt = $this->_db->prepare($sql);
        $stmt->execute([$id]);
        $stmt->setFetchMode(PDO::FETCH_INTO, $this);
        $stmt->fetch();
    }
    public function update()
    {
        $data = [];
        $set = "";
        foreach($this->_fields as $key)
        {
            $set .= "`$key` = :$key,";
            $data[$key]  = $this->{$key};
        }
        $set = rtrim($set, ",");
        $data[$this->_primary] = $this->{$this->_primary};
        $where = "$this->_primary = :$this->_primary";
        $sql = "UPDATE {$this->_table} SET $set WHERE $where";
        $this->_db->prepare($sql)->execute($data);
    }
}

As you can see, there are some new variables:

$_table holds the table name used in the particular class.
$_primary holds the name of the primary key that will be used in the find() and update() methods, with the default value of id.
$_fields is a very important property, it should contain the list of all "real" properties of this class. It will be used for create() and update() methods

Then just create actual classes, extending your prototype class, such as Team

class Team extends BaseActiveRecord
{
    protected $_table = "teams";
    protected $_fields = ['name', 'sname', 'logo', 'sport'];
}

As you can see, the definition is fairly simple - here you have to define the table and field names in order to make our automation work. And now! All you have to do is to simply call update() method!

include 'pdo.php';
$team = new Team($pdo);
$team->read(1); // get the record from the database
$user->name = "Boston Celtics";
$user->sname = "Celtics";
$user->update(); // save the updated record.

Note that you should never create a new database connection inside of each method. Create it once and pass into constructor of your classes. Here is how to connect with PDO properly.

PDO interaction shared among classes

1 Answers1

Linked

Related