README

SQL 包装器

MySQL 数据库连接简单包装器。

安装

composer require antiquete/sql-wrapper

使用

• 初始化
• 选择
• 连接
• 插入
• 更新
• 删除
• 事务
• 设置
• 日志
• 其他

初始化

namespace Antiquete\SQLWrapper;
use Database;

$mydb = new Database($server, $dbuser, $dbpass, $dbname);

选择

获取整个表

$results = $mydb->select("table");

获取一行

$row = $mydb->getRow("table", [
  "column" => "value"   // WHERE `column` == 'value'
]);

通过 id 获取一行

$row = $mydb->getRowById("table", "id");

获取一个条目

$val = $mydb->getVal("table",
                     [
                       "column" => "value"   // WHERE `column` == 'value'
                     ],
                     "column_name"  // column to get value of
                     );

连接

/**
 * Returns a sql result array with for joined tables
 *
 * @param string $table1
 * @param string $table2
 * @param array $ons - List of all clauses within ON in "column" => "column" format
 * @param array $wheres = [] - List of al clauses within WHERE in "column" => "value" format, defaults to no condition
 * @param string $orderBy = "" - List of all ORDER BY in "column1, column2...." format, defaults to no order
 * @param boolean $orderAsc = TRUE - Whether to order in ascending format, defaults to true
 * @param string $extraConditions = "" - Any extra condition to apply on query in string format, defaults to nothing
 * @param string $joinType = "INNER JOIN" - Type of join to use in string format, defaults to INNER JOIN
 * @return void
 */
function selectJoin2($table1, $table2, $ons, $wheres = [], $orderBy = "", $orderAsc = TRUE, $extraConditions = "", $joinType = "INNER JOIN")

获取连接表

$result = $mydb->selectJoin2("table1", "table2",
                             [
                               "id1" => "id2"   // ON table1.id1 = table2.id2
                             ]);

获取符合连接表条件的行

$result = $mydb->selectJoin2("table1", "table2",
                             [
                               "id1" => "id2"   // ON table1.id1 = table2.id2
                             ],
                             [
                               "column" => "value"  // WHERE `column` = 'value'
                             ])

获取符合连接表条件的行，按降序排列

$result = $mydb->selectJoin2("table1", "table2",
                             [
                               "id1" => "id2"   // ON table1.id1 = table2.id2
                             ],
                             [
                               "column" => "value"  // WHERE `column` = 'value'
                             ],
                             $orderBy = "column",
                             $orderAsc = FALSE)

获取符合连接表条件的行，按降序排列且为右连接

$result = $mydb->selectJoin2("table1", "table2",
                             [
                               "id1" => "id2"   // ON table1.id1 = table2.id2
                             ],
                             [
                               "column" => "value"  // WHERE `column` = 'value'
                             ],
                             $orderBy = "column",
                             $orderAsc = FALSE,
                             $extraConditions = "",
                             $joinType = "RIGHT JOIN")

插入

$success = $mydb->insert("table",
                         [
                           "column1" => "value1",
                           "column2" => "value2",
                           "column3" => "value3"
                         ]);

获取最后插入的 id

$last_insert_id = $mydb->insert_id();

更新

$success = $mydb->update("table",
                         [
                           "column1" => "value1",   // SET `column` = 'value'
                           "column2" => "value2"
                         ],
                         [
                           "column" => "value"      // WHERE `column` = 'value'
                         ]);

删除

$success = $mydb->delete("table",
                         [
                           "column" => "value",     // DELETE FROM `table` WHERE `column` = 'value'
                         ]);

事务

开始事务

$mydb->startTransaction();  // Any command executed after this will be part of transaction.

提交事务

$mydb->commit();

回滚事务

$mydb->rollback();

原子性地执行三个插入操作

try
{
  $mydb->startTransaction();

  if(!$mydb->insert("cars" ["id"=>1, "brand_id"=>5, "series_id"=>2, "name"=>"Veyron"]))
    throw new Exception('Insertion failed.');

  if(!$mydb->insert("series" ["id"=>2, "name"=>"Bugatti"]))
    throw new Exception('Insertion failed.');

  if(!$mydb->insert("brands" ["id"=>5, "name"=>"Volkswagen Group"]))
    throw new Exception('Insertion failed.');

  $mydb->commit();
}
catch (Exception $e)
{
  $mydb->rollback();
}

设置

需求 - 设置表

CREATE TABLE `settings` (
 `skey` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
 `sval` text COLLATE utf8mb4_unicode_ci NOT NULL,
 PRIMARY KEY (`skey`)
);

获取设置

$mydb->getSetting("setting_key");

日志

需求 - 日志表

CREATE TABLE `logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` text NOT NULL,
`content` text NOT NULL,
`log_time` datetime NOT NULL,
PRIMARY KEY (`id`)
);

记录消息

$mydb->log("Access Denied.", "Reason: Go away!");

其他

MySql 真实转义字符串

$escaped_str = $mydb->real_escape($str);

以 mysql 友好格式获取服务器时间

$now = $mydb->phptime();

执行任意 sql 查询

注意：此函数未转义，即任何用户提供的字符串在传递给此函数之前应首先使用 real_escape() 转义，以避免 SQL 注入。一般来说，不建议直接使用此函数。更好的选择是实现一个用于选择任务的功能，该功能负责适当的转义，并发送拉取请求将其合并到主库中。

$result = $mydb->execute("SELECT count(*) FROM `table`");

贡献

欢迎任何贡献或建议。如果您遇到错误或需要新功能，请在 git 仓库中打开一个问题。如果您已经实现了修复或新功能并希望将其合并，请发送拉取请求。谢谢。

许可证

此软件根据 MIT 许可证发布。 https://open-source.org.cn/licenses/MIT