php - 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).

Answer 1

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.