adamb/database

一个简单的PDO包装器，用于连接数据库

维护者

详细信息

github.com/adambinnersley/pdo-dbal

主页

源代码

问题

安装次数: 7,453

依赖者: 20

建议者: 0

安全性: 0

星级: 17

关注者: 2

分支: 5

开放问题: 0

1.6.3 2023-09-01 15:10 UTC

Requires (Dev)

MIT d503429c9d78dde0ff2c5b3448b905db64106aec

  • Adam Binnersley <abinnersley.woop@gmail.com>

databasedbpdowrapper

This package is auto-updated.

Last update: 2024-09-30 01:40:20 UTC

README

Build Status Scrutinizer Quality Score Minimum PHP Version Scrutinizer Coverage

PDO数据库抽象层

一个简单的MySQL PDO数据库抽象层

安装

安装可通过 Composer/Packagist 进行，您可以在您的 composer.json 文件中添加以下行

"adamb/database": "^1.0"

或者

composer require adamb/database

类功能

  • 可选的缓存支持，包括Memcache / Memcached / Redis / XCache
  • 可选连接到辅助数据库，以防无法连接到主MySQL服务器
  • 通过PDO连接到MySQL数据库
  • 简化查询为SELECT / INSERT / UPDATE和DELETE
  • 内置预处理语句
  • 兼容PHP5.6及更高版本

许可证

本软件在MIT许可证下分发。请阅读LICENSE以获取有关软件可用性和分发的信息。

使用方法

以下是一个使用示例及其产生的查询（出于安全考虑，所有查询都使用预处理语句运行，在prepare()之后执行()时添加值）

1. 连接

<?php

$hostname = '127.0.0.1';
$username = 'root';
$password = '';
$database = 'my_db';
$backup_server = '127.0.0.2';

// Connect to a single MySQL server
$db = new DBAL\Database($hostname, $username, $password, $database);

// Connect to MySQL server and is primary server is down connect to secondary server
$db = new DBAL\Database($hostname, $username, $password, $database, $backup_server);

2. 选择

$db->select('test_table');
// Query Run = "SELECT * FROM `test_table` LIMIT 1;"

$db->select('test_table', array('id' => 3));
// Query Run = "SELECT * FROM `test_table` WHERE `id` = 3 LIMIT 1;"

$db->select('test_table', array('id' => array('>=', 3)));
// Query Run = "SELECT * FROM `test_table` WHERE `id` >= 3 LIMIT 1;"

$db->select('test_table', array('id' => array('>=', 3)), array('name', 'email'));
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` >= 3 LIMIT 1;"

$db->select('test_table', array('id' => array('>=', 3)), array('name', 'email'), array('id' => 'DESC'));
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` >= 3 ORDER BY `id` DESC LIMIT 1;"

// Usage of IN or NOT IN operator
$db->select('test_table', array('id' => array('IN' => array(1, 2, 3))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` IN (1,2,3) LIMIT 1;"

$db->select('test_table', array('id' => array('NOT IN' => array(2, 3))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` NOT IN (2,3) LIMIT 1;"

// Usage of BETWEEN or NOT BETWEEN operator
$db->select('test_table', array('id' => array('BETWEEN' => array(1, 3))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` BETWEEN 1 AND 3 LIMIT 1;"

$db->select('test_table', array('id' => array('NOT BETWEEN' => array(2, 10))));
// Query Run = "SELECT * FROM `test_table` WHERE `id` NOT BETWEEN 2 AND 10 LIMIT 1;"

// The same functions can be run using selectAll() rather than select()

$db->selectAll('test_table', array('id' => array('>=', 3)), array('name', 'email'), array('id' => 'DESC'), 150);
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` >= 3 ORDER BY `id` DESC LIMIT 150;"

// Usage
// select($table, $where = array('field_name' => $value), $selectfield = array('field_1', 'field_2'), $order = array('field_name' => 'ASC' or 'DESC') or RAND());
// selectAll($table, $where = array('field_name' => $value), $selectfield = array('field_1', 'field_2'), $order = array('field_name' => 'ASC' or 'DESC') or RAND(), $limit(default = no limit));

3. 插入

$db->insert('test_table', array('name' => 'Bob', 'email' => 'bob@gmail.com'));
// Query Run = "INSERT INTO `test_table` (`name`, `email`) VALUES ('Bob', 'bob@gmail.com');"

// Usage
// insert($table, array('field_name' => $value));

4. 更新

$db->update('test_table', array('name' => 'Ken', 'email' => 'ken@gmail.com'), array('id' => 12345));
// Query Run = "UPDATE `test_table` SET (`name` => 'Ken', `email` => 'ken@gmail.com') WHERE `id` = 12345;"

$db->update('test_table', array('name' => 'Ken'), array('name' => 'Bob'), 50);
// Query Run = "UPDATE `test_table` SET (`name` => 'Ken') WHERE `name` = 'Bob' LIMIT 50;"

// Usage
// update($table, $updatevalues = array('field_name' => $value), $where = array('field' => $value));

5. 删除

$db->delete('test_table', array('id' => 1));
// Query Run = "DELETE FROM `test_table` WHERE `id` = 1;"

$db->delete('test_table', array('name' => 'Ted'), 50);
// Query Run = "DELETE FROM `test_table` WHERE `name` = 'Ted' LIMIT 50;"

// Usage
// delete($table, $where = array('field' => $value), $limit);

6. 计数

$db->count('test_table');
// Query Run = "SELECT COUNT(*) FROM `test_table`;";

$db->count('test_table', array('name' => 'Bob'));
// Query Run = "SELECT COUNT(*) FROM `test_table` WHERE `name` => 'Bob';";

// Usage
// count($table, $where = array('field' => $value));

7. 查询

可以通过传递SQL查询和值使用查询命令运行其他查询

$db->query("SELECT * FROM `test_table` WHERE `name` = ? OR `name` => ?;", array('John', 'Phil'));
// Query Run = "SELECT * FROM `test_table` WHERE `name` = 'John' OR `name` => 'Phil';";

$db->query("UPDATE `test_table` SET `name` = 'Karl' WHERE `name` = ? OR `name` => ?;", array('John', 'Phil'));
// Query Run = "UPDATE `test_table` SET `name` = 'Karl' WHERE `name` = 'John' OR `name` => 'Phil';";

$db->query("SELECT * FROM `test_table` WHERE `field1` = ? AND (`field2` => ? OR `field3` => ?);", array('value1', 'value2', 'value3'));
// Query Run = "SELECT * FROM `test_table` WHERE `field1` = 'value1' AND (`field2` => 'value2' OR `field3` => 'value3');";

# UNION
$db->query("SELECT * FROM `test_table` WHERE `field1` = ? UNION SELECT * FROM `another_table` WHERE `another_field` = ?;", array('value1', 'value2'));
// Query Run = SELECT * FROM `test_table` WHERE `field1` = 'value1' UNION SELECT * FROM `another_table` WHERE `another_field` = 'value2';";

$db->query("SELECT * FROM `test_table` WHERE `field1` = :search UNION SELECT * FROM `another_table` WHERE `another_field` = :search;", array(':search' => 'value1'));
// Query Run = SELECT * FROM `test_table` WHERE `field1` = 'value1' UNION SELECT * FROM `another_table` WHERE `another_field` = 'value1';";

# JOIN
$db->query("SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field`;");
// Query Run = SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field`;";

$db->query("SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field` AND `test_table`.`field1` <= ? LIMIT 100;", array('value'));
// Query Run = SELECT `test_table`.`field1`, `test_table`.`field2`, `another_table`.`field1` FROM `test_table` INNER JOIN `another_table` ON `test_table`.`reference_field` = `another_table`.`some_field` AND `test_table`.`field1` <= 'value' LIMIT 100;";

8. FetchColumn

// This is similar to the select method except return the column value rather than an array of all of the fields 

$column = $db->fetchColumn('test_table', array('id' => 3), array('name', 'email'));
// Query Run = "SELECT `name`, `email` FROM `test_table` WHERE `id` = 3 LIMIT 1;"
echo($column[0]); // will return the name field
echo($column[1]); // will return the email field

$column = $db->fetchColumn('test_table', array('id' => 3), array('name', 'email'), 1);
echo($column); // will return email as the field number of 1 has been set

9. NumRows

$db->numRows();
$db->rowCount();

// Running either of these methods after executing a query will show you how many rows have been affected e.g.
$db->update('test_table', array('name' => 'Ken'), array('name' => 'Bob'));
echo($db->numRows()); // Returns number of results updated e.g. 12

10. LastInsertId

$db->insert('test_table', array('name' => 'Bob', 'email' => 'bob@gmail.com'));
echo($db->lastInsertId()); // Returns the key field value number normally the the auto increment field value

11. isConnected

$db->isConnected(); // Returns true of false depending on if the connection is active to the server

12. 缓存

可以通过支持Memcache / Memcached / Redis和XCache将数据库缓存添加到查询中

$caching = new DBAL\Caching\Memcached();
$db = new DBAL\Database($hostname, $username, $password, $database, $backup_server, $caching);

// OR

$caching = new DBAL\Caching\Memcached();
$db = new DBAL\Database($hostname, $username, $password, $database);
$db->setCaching($caching);