README

对于在 PHP 5.x 环境下工作的用户，此包提供了一个便捷的解决方案。它提供了数据库操作的封装工具。

特性

• 易于使用
• 兼容 PHP 5.x

安装

composer require link1515/db-utils-php5

用法

数据库

DB 类可以帮助您管理数据库连接。您可以连接到多个数据库并在它们之间切换。

use Link1515\DbUtilsPhp5\DB;

// set PDO charset. Default value is utf8mb4
DB::setPDOCharset(string $charset);

// set PDO options.
DB::setPDOOptions(array $options);

// Conect to the database. The current connection is used as the default.
DB::connect(string $id, string $drive, string $host, string $database, string $user, string $password): void;

// Use the database with the specified id.
DB::useConnection(string $id): void;

// Get the PDO with the specified id. If no id is passed, the default connection PDO will be returned.
DB::PDO(?string $id): PDO;

BaseORM

BaseORM 类提供了一些创建、查询、更新和删除的常用方法。在简单应用中，可以直接调用静态方法来执行指定表的操作。

use Link1515\DbUtilsPhp5\ORM\BaseORM;

// options
// - page (int)
// - perPage (int)
// - orderBy (string|array)
// default value
// $options = [
//   'page' => 1,
//   'perPage' => 20,
//   'orderBy' => 'id ASC'
// ]
BaseORM::getAllForTable(string $tableName, ?array $columns = null, ?array $options): array;

BaseORM::getCountForTable(string $tableName): int;

BaseORM::getByIdForTable(string $tableName, int|string $id, ?array $columns = null): array|null;

BaseORM::createForTable(string $tableName, array $data): bool;

BaseORM::updateByIdForTable(string $tableName, int|string $id, array $data): bool;

BaseORM::deleteByIdForTable(string $tableName, int|string $id): bool;

示例

单连接

DB::connect('conn', 'mysql', 'localhost:3306', 'shop', 'root', 'root');

BaseORM::updateByIdForTable('orders', 4, ['price' => 300]);

多连接

DB::connect('conn_1', 'mysql', 'localhost:3306', 'shop', 'root', 'root');
DB::connect('conn_2', 'mysql', 'localhost:3306', 'app', 'root', 'root');

DB::useConnection('conn_1');
BaseORM::createForTable('orders', ['user_id' => 1, 'price' => 100]);

DB::useConnection('conn_2');
BaseORM::getAllForTable('user');

BaseORM 内置方法

DB::connect('conn', 'mysql', 'localhost:3306', 'bookstore', 'root', 'root');

// create
BaseORM::createForTable('users', [
  'name' => 'Lynk',
  'phone' => '0922333444',
  'created_at' => date('Y-m-d H:i:s'),
  'updated_at' => date('Y-m-d H:i:s'),
]);

// query all
BaseORM::getAllForTable('users');
// query all and specified field
BaseORM::getAllForTable('users', ['name', 'phone']);
// query all and specified field with alias
BaseORM::getAllForTable('users', ['name' => 'username', 'phone' => 'cellphone']);
// query all and set pagination
BaseORM::getAllForTable('users', null, [
  'page' => 2,
  'perPage' => 30
]);
// query all and order by
BaseORM::getAllForTable('users', null, [
  'orderBy' => ['department_id DESC', 'id ASC'],
]);

BaseORM::getCountForTable('users');

// query by id
BaseORM::getByIdForTable('users', 1);
// query by id and specified field
BaseORM::getByIdForTable('users', 1, ['name', 'created_at']);
// query by id and specified field with alias
BaseORM::getByIdForTable('users', 1, ['name', 'created_at' => 'createdAt']);

// update by id
BaseORM::updateByIdForTable('users', 1, [
  'phone' => '0933555999',
  'updated_at' => date('Y-m-d H:i:s')
]);

// delete by id
BaseORM::deleteByIdForTable('users', 1);

自定义 ORM

对于更复杂的应用，您可以创建自己的 ORM 类来继承 BaseORM。通过编写 SQL 语句来完成复杂的逻辑。

在这种情况下，您需要通过 getInstance() 方法获取实例，并在其上进行操作。以下方法是内置到实例中的

getAll(?array $columns = null, ?array $options): array;

getCount(): int;

getById(int|string $id, ?int $columns = null): array|null;

create(array $data): bool;

updateById(int|string $id, array $data): bool;

deleteById(int|string $id): bool;

示例

use Link1515\DbUtilsPhp5\ORM\BaseORM;
use Link1515\DbUtilsPhp5\DB;

class OrderORM extends BaseORM
{
  // Required. Automatially use the connection with the specified id. Use $this->getPDO() to get connection.
  protected $connectionId = 'conn_1';
  // Required. Automatially pass the tableName pararmeter to build-in methods (getAll(), getById(), create(), deleteById()).
  protected $tableName = 'orders';
  // Optional. Automatially pass the perPage parameter to getAll() method. The default value is 20.
  protected $perPage = 20;

  // your own method
  public function getByIdWithUserInfo($id) {
    $query =
      'SELECT * FROM ' . $this->tableName . '
      LEFT JOIN users ON users.id = orders.user_id
      WHERE orders.id = :id
    ';
    // use getPDO method to make sure we are using correct connection
    $stmt = $this->getPDO()->prepare($query);
    $stmt->execute(['id' => $id]);
    return $stmt->fetch();
  }
}

$orderORM = OrderORM::getInstance();

// build-in method
$orderORM->getById(1);

// customized method
$orderORM->getByIdWithUserInfo(1);