moviet/tuisql

一个PHP数据库查询构建器库

维护者

详细信息

github.com/moviet/tuisql

主页

源代码

问题

安装: 0

依赖关系: 0

建议者: 0

安全: 0

星标: 1

关注者: 0

分支: 0

开放问题: 0

dev-master 2019-03-06 17:58 UTC

Requires

Requires (Dev)

MIT 63e218c85c1a03ec38413c30971f42b7684e0c36

  • Moviet <snorthfed.woop@gmail.com>

databasemysqlmanagerpostgresqlquerywrapperbuilderhandler

This package is auto-updated.

Last update: 2024-09-07 06:14:18 UTC

README

Build Status License Usage Codacy Badge

Tuisql 是一个快速数据库查询构建器，可扩展且便携，基于PDO（PHP数据对象）作为快速预处理语句，通过最小化源代码实现最丰富的用法，这可能会帮助您进行高效开发并减少任何 复杂性，只需第一次您必须了解我们如何在优秀的 文档 中添加对 SQL注入 的安全防护，您必须 仔细 阅读并使用 yaayy

已经

安装

composer require "moviet/tuisql"

特性

  • 构建连接
  • 简单的C.R.U.D
  • 各种查询
  • 硬编码查询
  • 检索查询

用法

构建连接

  • 您只需简单地编写数据库配置，例如 imho

    require __DIR__ . '/vendor/autoload.php';

use Moviet\Base\Tuisql;
use Moviet\Base\Puppen\Tui;

$connect = Tui::click(
    ['driver','hostname','port','dbname','username','password']
); 

// Or simply like this

$connect = Tui::click(
    ['mysql','localhost','3306','dbname','username','password']
);

  • 对于 sqlite 数据库，您可以使用以下方式

    $connect = Tui::sqlite('folder/folder/folder','mysqlite.db');

    路径位置由您自行决定，在自己的目录中使用上述 简单格式

简单的 CRUD（创建、读取、更新、删除）

  • 要向数据库中 插入 数据，您可以使用以下 添加 命令

    require __DIR__ . '/vendor/autoload.php';

use Moviet\Base\Tuisql;
use Moviet\Base\Puppen\Tui;

$build = Tui::click(
    ['mysql','localhost','3306','dbname','username','password']
);

$connect = new Tuisql($build);

$column = ['category','material','color'];

$values = ['jacket','cotton','brown'];

$connect->from('table')
        ->column($column)
        ->value($values)
        ->add();

    INSERT INTO table (category, material, color) VALUES (:category1, :material2, :color3)

  • 然后，为了简单地 读取 数据库，您可以使用以下 运行 命令

    $values = [$varOne, $varTwo, $varNext];  

$connect->select('*')
        ->from('table')
        ->where('id, product')
        ->value($values)
        ->run();

    SELECT * FROM table WHERE id=:id1 AND product=:product2

  • 我们不希望更新数据库变得复杂，您可以使用 Fresh 命令来更新这些数据

     // use array style
 $connect->select(['table'])
         ->set(['column','column','column','column'])
         ->where(['column'])
         ->value(['work','easy','stay','writable'])
         ->fresh();

  • 最后，要 删除 数据库，您可以使用 Del 命令，yayy

     // use commas style
 $connect->from('table')
         ->where('column, column')
         ->value([$anyId, $anyVariable])
         ->del();

    通过上述一些不同的示例，您可以 完全自由 地表达自己的风格

各种查询

  • 添加单个 Limit 条件

    $select  = ['column','column','column'];
$wheres  = ['notes','usage'];
 
$values  = ['sweet','easy'];  

$connect->select($select)
        ->from('table')
        ->where($wheres)
        ->limit(10) // <= Optional integer/string
        ->value($data)
        ->run();

  • 添加 Offset Or Limits 条件

    $select  = 'column, column, column';
$wheres  = 'type, category';

$values  = ['comma','yippy', 10, 20];  // <= Add binding values

$connect->select($select)
        ->from('table')
        ->where($wheres)
        ->limits('one, two') // <= add uniques :bind (implicitely)
        ->value($values)
        ->run();

    SELECT column, column, column FROM table WHERE type=:type1 AND category=:category2 LIMIT :one, :two

  • 添加 Count 条件

    $connect->select('column, column, column')
        ->count('column')
        ->from('table')
        ->where('column')
        ->limits('11, 12') // must be unique :naming
        ->value(['something', 11, 12])
        ->run();

  • 添加 Distinct 条件

    $connect->distinct('column AS price')
        ->from('table')
        ->where('something')
        ->limit(22)
        ->value(['something'])
        ->run();

  • 添加 Group By 条件

    $select  = ['column','column','column'];
$wheres  = ['column','column','column'];

$values  = ['make','ease','readable']; 

$connect->select($select)
        ->from('table')
        ->where($wheres)
        ->groupBy('hashtable')
        ->value($values)
        ->run();

  • 添加 Order By 条件

    $select  = ['column','column','column'];
$wheres  = ['column','column','column'];

$values  = ['free','styles','whatever']; 

$connect->select($select)
        ->from('table')
        ->where($wheres)
        ->groupBy('column')
        ->OrderBy('date ASC') // <= sorting
        ->limit('15')
        ->value($values)
        ->run();

  • 添加 Like 条件

    $select  = 'column, column, column';
$likes   = 'chocolato, coffee';

$values  = ['delicious','groovy'];

$connect->select($select)
        ->from('table')
        ->isWhere()  // <= require Where condition
        ->like($likes)
        ->value($values)
        ->run();

    上述操作将添加带有 AND 条件的 LIKE，您可以将 ->orLike() 用于创建 OR 条件

  • 添加 Having 条件

    $connect->select('column, column, column')
        ->from('table')
        ->having('count(column) > :ten') // <= add optional :bind
        ->groupBy('column count(a, b, c)')
        ->value([10]) // <= get :bind value
        ->run();

  • 添加 Between 条件

    $select  = 'column, column, column';  
$where   = 'column, column';
$between = 'column, column'; // <= add between column name

$values  = ['distract','damages','between_val','between_val'];

$connect->select($select)
        ->from('table')
        ->where($where)
        ->between($between)
        ->value($values)
        ->run();

  • 添加与 Where 条件一起的 OR

    $select  = 'column, column, column';  
$where   = 'column, column';

$values  = ['magnum','doritos'];

$connect->select($select)
        ->from('table')
        ->or()
        ->where($where)
        ->value($values)
        ->run();

  • 添加 Where In 条件

    $select  = ['column AS A','column'];  
$whereIn = ['column']; // add where in column
$addIn   = ['ca, ca, ca, ca']; // add uniques :binding

$values  = ['cheese','sauce','salt','sugar'];

$connect->select($select)
        ->from('table')
        ->isWhere()  // <= must be add where condition
        ->whereIn($whereIn, $addIn)
        ->value($values)
        ->run();

  • 添加 Not In 条件

    $select  = ['column','column'];
$notIn   = ['column NOT']; // add Not explicitely
$addIn   = ['id, id, id, id'];

$values  = ['homies','sweet','home','selfie'];

$connect->select($select)
        ->from('table')
        ->isWhere()
        ->whereIn($notIn, $addIn)
        ->value($values)
        ->run();

  • 无属性 表的 Join

    $select  = ['column.a','column.b','column.id as col'];
$wheres  = ['column.a','column.b'];

$values  = ['yayy','mooo'];

$connect->select($select)
        ->from('table.a')
        ->where($wheres)
        ->value($values)
        ->run();

  • 内连接 表

    $select  = ['column.a*','column.b*'];
$table   = ['mytable'];
$join    = ['inner','column.b'];
$addOn   = ['column.a=column.id','column.b=column.id']; 
$wheres  = ['column.aid','column.bid']; // <= add 2 :binds column

$values  = [11, 12]; // <= send bind with 2 values

$connect->select($select)
        ->from($table)
        ->join($join)
        ->on($addOn)
        ->where($where)
        ->value($values)
        ->run();

    您可以使用参数和添加属性（例如 [join, inner, left, cross, right]）来连接多个表

  • 使用 Where In Join 多个表

    $select  = ['column.a*','column.b*'];
$table   = ['mytable'];
$join    = ['left','column.b']; 
$addOn   = ['column.a=column.id','column.b=column.id','column.b=column.a']; 
$whereIn = ['column.a']; // <= add column
$inValue = ['id','id','id','id']; // <= add uniques :binds
$order   = ['date'];

$values  = [5, 20, 40, 48]; // <= add values

$connect->select($select)
        ->from($table)
        ->join($join)
        ->on($addOn)
        ->isWhere()
        ->whereIn($whereIn, $inValue)
        ->orderBy($order)
        ->value($values)
        ->run();

    引号 :

    通过上述各种示例，您可以自行表达许多风格

硬编码查询

  • 如果您不希望任何人知道您需要什么，则可以创建一个 硬编码 的查询，如下所示

    $select  = ['column','column'];
$table   = ['tableA']
$join1   = ['join','column.b']; 
$On_1    = ['column.a=column.id','column.a=column.id','column.a=column.e']; 
$join2   = ['inner','column.b']; 
$On_2    = ['column.b=column.id','column.b=column.id','column.b=column.a']; 
$join3   = ['left','column.b']; 
$On_3    = ['column.c=column.id','column.c=column.id','column.c=column.b']; 
$join4   = ['cross','column.c']; 
$On_4    = ['column.d=column.id','column.d=column.id','column.d=column.c']; 
$join5   = ['right','column.c']; 
$On_5    = ['column.e=column.id','column.e=column.id','column.e=column.d']; 
$wereIn1 = ['column.a'];
$withIn1 = ['a','a','a']; // <= add unique string binds => a = :ina1,..
$wereIn2 = ['column.b'];
$withIn2 = ['b','b','b']; // <= add unique string binds => b = :inb1,..
$group   = ['date.a'];
$order   = ['date.a ASC'];
$limit   = [20];

$values  = [1, 2, 3, 4, 8, 16, 24, 32]; // <= depends on total parameter :binding

$connect->select($select)
        ->from($table)
        ->join($join1)
        ->on($On_1)
        ->join($join2)
        ->on($On_2)
        ->join($join3)
        ->on($On_3)
        ->join($join4)
        ->on($On_4)
        ->join($join5)
        ->on($On_5)
        ->isWhere()
        ->or()
        ->whereIn($wereIn1, $withIn1)
        ->whereIn($wereIn1, $withIn2)
        ->groupBy($group)
        ->orderBy($order)
        ->limit($limit)
        ->value($values)
        ->run();

  • 您可以使用 Badass 查询，并使用 DRAW 与硬编码的 手动 查询一起使用

    $colom   = ['columnA','columnB AS Badass'];
$count   = ['columnD'];
$table   = ['columnA'];
$draw1   = ['INNER JOIN column.b AS Bad ON column.id, column.di WHERE columnA.id=:param']; // <= add binding ":" name
$draw2   = ['UNION ALL'];
$draw3   = ['(SELECT column.id FROM columnC'];
$join1   = ['left','column.b']; 
$addOn1  = ['column.b=column.id','column.b=column.id','column.b=column.a']; 
$join2   = ['cross','column.c']; 
$addOn2  = ['column.c=column.id','column.c=column.id','column.c=column.a']; 
$join3   = ['right','column.d']; 
$addOn3  = ['column.d=column.id','column.d=column.id','column.d=column.a']; 
$where   = ['column.a','column.b','column.c'];
$among   = ['a.date','b.date'];
$whereIn = ['column.a'];
$withIn  = ['id','id','id'];
$likes   = ['column.b', 'column.c'];
$groups  = ['columnA.date','columnB.date','columnC.date'];
$orders  = ['column.a ASC','column.b ASC','column.c ASC'];
$limit   = [':hundred)']; // <= add limit binding ":" name

$values  = ['home','sweet','home','makes', date('Y-m-d'), date('Y-m-d'), 1, 2, 3, 'badass','speed', 100]; // in sequence 

$connect->select($colom)
        ->count($count)
        ->from($table)
        ->draw($draw1)
        ->draw($draw2)
        ->draw($draw3)
        ->join($join1)
        ->on($addOn1)
        ->join($join2)
        ->on($addOn2)
        ->join($join3)
        ->on($addOn3)
        ->where($where)
        ->between($among)
        ->whereIn($whereIn, $withIn)
        ->orLike()
        ->like($likes)
        ->groupBy($groups)
        ->orderBy($orders)
        ->limit($limit)
        ->value($values)
        ->run();

    注意 :

    当您从 外部 获取 值 时，必须添加唯一的 :name 绑定，以防止 SQL注入

    上述示例将生成以下硬编码查询

    SELECT columnA, columnB AS Badass, 
COUNT(columnD)
FROM columnA
INNER JOIN column.b AS Bad
ON column.id, column.date 
WHERE columnA.id=:bindparam
UNION ALL
      (SELECT column.id 
       FROM columnC
       LEFT JOIN column.b 
       ON column.b=column.id, column.b=column.id, column.b=column.a
       CROSS JOIN column.c
       ON column.c=column.id, column.c=column.id, column.c=column.a
       RIGHT JOIN column.d
       ON column.d=column.id, column.d=column.id, column.d=column.a
       WHERE column.a=:columna1 AND column.b=:columnb2 AND column.c=:columnc3 
       AND a.date BETWEEN :bdate AND b.date BETWEEN :bdate 
       AND column.a IN(:inid1, :inid2, :inid3)
       AND column.b LIKE :columnb1 OR column.c LIKE :columnc2
       GROUP BY columnA.date, columnB.date, columnC.date
       ORDER BY column.a ASC, column.b ASC, column.c ASC
       LIMIT :hundred)

连接

检索查询

  • 您可以从 Tuisql 中简单地检索一行，如下所示

    require __DIR__ . '/vendor/autoload.php';

use Moviet\Base\Tuisql;
use Moviet\Base\Puppen\Tui;
use Moviet\Base\Fetchs\Rtui;

$database = Tui::click(
    ['mysql','localhost','3306','dbname','username','password']
);

$connect = new Tuisql($database);

$column = ['myid','type','color'];
$values = [123456];

$query  = $connect->select($column)
                  ->from('house')
                  ->where('id')
                  ->value($values)
                  ->run();

$getRow = Rtui::oneRow($query);

$myid   = $getRow['myid'];
$type   = $getRow['type'];
$color  = $getRow['color'];

$direct = Rtui::notFound($getRow, '404.shtml'); // Direct url eg. url/404.shtml

  • 要检索更多数据，您可以使用如下方式

    $database = Tui::click(
    ['mysql','localhost','3306','dbname','username','password']
);

$connect = new Tuisql($database);

$column = ['code','shoes','color'];
$values = [123456];

$query  = $connect->select($column)
                  ->from('house')
                  ->where('code')
                  ->value($values)
                  ->run();

$allRow = Rtui::allRow($query); // Equivalent like fetchAll

foreach ($allRow as $row => $all) {

    $code   = $all['code'];
    $shoes  = $all['shoes'];
    $color  = $all['color'];
}

$direct = Rtui::notFound($allRow, '404.shtml'); // Direct url eg. url/404.shtml

  • 检索查询的一列

    $allRow = Rtui::oneColumn($query);

  • 检索查询的行数

    $allRow = Rtui::count($query);

  • 检索查询的行号

    $allRow = Rtui::oneNum($query);

  • 检索查询的行懒加载

    $allRow = Rtui::oneLazy($query);

  • 检索查询的行对象

    $allRow = Rtui::allObj($query);

许可协议

Moviet/tuisql 采用 MIT 公共许可证发布。