类之间共享的 PDO 交互

2021-12-26 00:00:00 php pdo

我拥有的每个数据库表都有一个类，例如 events 表的行存储在 Event 类中.对于我编写的每个类，有几个方法完全相同，只是变量或列名不同.例如:

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:

播放器类 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;
}

团队班级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;
 }

正如您所见，方法的重点是相同的，只是将不同的变量绑定到语句(更多方法就是这种情况).有没有一种简单的方法可以让每个类都有相同的基本 update()、insert()、delete()... 方法但有其各自的变量?我曾想过从超类继承基本行为，但从 OO 的角度来看并没有多大意义(我不相信有任何方式可以说对于此类中声明的每个公共变量")，但我不是确定是否有办法使用合成来做到这一点(或者根本没有).

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

推荐答案

是的，这是你所说的一个婴儿 ORM，它很容易实现.

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

首先，创建一个原型类，包含所有常用方法(create()、update()、find() 等).

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 保存特定类中使用的表名.
  • $_primary 保存将在 find() 和 update() 方法中使用的主键的名称，以及id 的默认值.
  • $_fields 是一个非常重要的属性，它应该包含该类所有真实"属性的列表.它将用于 create() 和 update() 方法
  • $_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

然后只需创建实际的类，扩展您的原型类，例如 Team

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'];
}

如您所见，定义相当简单 - 在这里您必须定义表和字段名称，以便我们的自动化工作.现在！您所要做的就是简单地调用 update() 方法！

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.

请注意，永远不要在每个方法内部创建新的数据库连接.创建一次并传递给类的构造函数.这是如何正确连接PDO.

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.

相关文章