myskewhell/tuple

此包已被放弃，不再维护。作者建议使用 bentools/where 包。

元组管理

维护者

详细信息

github.com/bpolaszek/myskewhell-tuple

主页

源代码

问题

安装: 335

依赖: 0

建议者: 0

安全: 0

星标: 0

关注者: 1

分支: 1

开放问题: 0

1.5 2015-12-10 13:39 UTC

Requires

  • php: >=5.4

MIT 51b70bda056a7e30cf7f307d0ecefc2d0f044170

  • Beno!t POLASZEK

This package is auto-updated.

Last update: 2022-02-01 12:45:36 UTC

README

此包已被放弃。请考虑升级到 bentools/where，一种动态生成条件和完整查询的非常简单的方法。

MySkewHell - Tuple

一种直观的方式来处理 MySql 预处理语句。

使用 PHP 数组语法在 SQL 查询中动态添加条件。

每个值将被转换为占位符；然后您可以将每个占位符绑定到其值。

构建查询：处理占位符和值

位置占位符（默认）

$sql        =   "SELECT * FROM users WHERE 1" . PHP_EOL;
$tuples     =   new TupleANDWrapper();
$tuples[]   =   ['nickname' => 'johndoe'];
$tuples[]   =   ['firstname', '=', 'John'];
$tuples[]   =   ['lastname', 'LIKE', 'Doe'];
$tuples[]   =   new TupleORWrapper([
                                    ['active' => true], 
                                    ['date_registered', 'BETWEEN', ['2015-01-01', '2015-01-30']]
                                    ]);
$tuples[]   =   ['id_group', 'NOT IN', [5, 8, 15]];
$tuples[]   =   ['nb_logins', '>=', 2];
$tuples[]   =   "lastloggedin <= CURRENT_DATE - INTERVAL 1 MONTH";

$sql        .=  'AND ' . $tuples;

var_dump($sql);

输出

SELECT *
    FROM users
WHERE 1
    AND (
          `nickname` = ?
          AND `firstname` = ?
          AND `lastname` LIKE ?
          AND (
                (`active` = ?  
                  OR `date_registered` BETWEEN ? AND ?)
              )
          AND `id_group` NOT IN (?, ?, ?)
          AND `nb_logins` >= ?
          AND lastloggedin <= CURRENT_DATE - INTERVAL 1 MONTH
        )
var_dump($tuples->getPlaceHolders());

输出

array (size=10)
  0 => string '?' (length=1)
  1 => string '?' (length=1)
  2 => string '?' (length=1)
  3 => string '?' (length=1)
  4 => string '?' (length=1)
  5 => string '?' (length=1)
  6 => string '?' (length=1)
  7 => string '?' (length=1)
  8 => string '?' (length=1)
  9 => string '?' (length=1)

var_dump($tuples->getValues());

输出

array (size=10)
  0 => string 'johndoe' (length=7)
  1 => string 'John' (length=4)
  2 => string 'Doe' (length=3)
  3 => boolean true
  4 => string '2015-01-01' (length=10)
  5 => string '2015-01-30' (length=10)
  6 => int 5
  7 => int 8
  8 => int 15
  9 => int 2

查询预览

SELECT *
  FROM users
WHERE 1
    AND (
          `nickname` = 'johndoe'
          AND `firstname` = 'John'
          AND `lastname` LIKE 'Doe'
          AND (
                (`active` = 1
                  OR `date_registered` BETWEEN '2015-01-01' AND '2015-01-30')
                )
          AND `id_group` NOT IN (5, 8, 15)
          AND `nb_logins` >= 2
          AND lastloggedin <= CURRENT_DATE - INTERVAL 1 MONTH
      )

命名占位符

$sql        =   "SELECT * FROM users WHERE 1" . PHP_EOL;
$tuples     =   new TupleANDWrapper();
$tuples[]   =   ['nickname' => 'johndoe'];
$tuples[]   =   ['firstname', '=', 'John'];
$tuples[]   =   ['lastname', 'LIKE', 'Doe'];
$tuples[]   =   new TupleORWrapper([
                                    ['active' => true], 
                                    ['date_registered', 'BETWEEN', ['2015-01-01', '2015-01-30']]]
                );
$tuples[]   =   ['id_group', 'NOT IN', [5, 8, 15]];
$tuples[]   =   ['nb_logins', '>=', 2];
$tuples[]   =   "lastloggedin <= CURRENT_DATE - INTERVAL 1 MONTH";

// Same code here : just specify we want to use named placeholders
$tuples     ->  useNamedPlaceholders(true);

$sql        .=  'AND ' . $tuples;

var_dump($sql);

输出

SELECT *
  FROM users
WHERE 1
  AND (
        `nickname` = :nickname
        AND `firstname` = :firstname
        AND `lastname` LIKE :lastname
        AND (
              (`active` = :active
                OR `date_registered` BETWEEN :date_registered1 AND :date_registered2)
        )
        AND `id_group` NOT IN (:id_group1, :id_group2, :id_group3)
        AND `nb_logins` >= :nb_logins
        AND lastloggedin <= CURRENT_DATE - INTERVAL 1 MONTH
      )
var_dump($tuples->getPlaceHolders());

输出

array (size=10)
  0 => string ':nickname' (length=9)
  1 => string ':firstname' (length=10)
  2 => string ':lastname' (length=9)
  3 => string ':active' (length=7)
  4 => string ':date_registered1' (length=17)
  5 => string ':date_registered2' (length=17)
  6 => string ':id_group1' (length=10)
  7 => string ':id_group2' (length=10)
  8 => string ':id_group3' (length=10)
  9 => string ':nb_logins' (length=10)

var_dump($tuples->getValues());

输出

array (size=10)
  'nickname' => string 'johndoe' (length=7)
  'firstname' => string 'John' (length=4)
  'lastname' => string 'Doe' (length=3)
  'active' => boolean true
  'date_registered1' => string '2015-01-01' (length=10)
  'date_registered2' => string '2015-01-30' (length=10)
  'id_group1' => int 5
  'id_group2' => int 8
  'id_group3' => int 15
  'nb_logins' => int 2

查询预览

SELECT *
  FROM users
WHERE 1
    AND (
          `nickname` = 'johndoe'
          AND `firstname` = 'John'
          AND `lastname` LIKE 'Doe'
          AND (
                (`active` = 1
                  OR `date_registered` BETWEEN '2015-01-01' AND '2015-01-30')
                )
          AND `id_group` NOT IN (5, 8, 15)
          AND `nb_logins` >= 2
          AND lastloggedin <= CURRENT_DATE - INTERVAL 1 MONTH
      )

使用 PDO 的示例

使用位置占位符

$pdo        =   new PDO("mysql:host=localhost;dbname=mydb", "username", "password");
$stmt       =   $pdo->prepare($sql);

foreach ($tuples AS $key => $value)
    $stmt->bindValue(++$key, $value);

$stmt->execute();
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

使用命名占位符

$pdo        =   new PDO("mysql:host=localhost;dbname=mydb", "username", "password");
$stmt       =   $pdo->prepare($sql);

foreach ($tuples AS $key => $value)
    $stmt->bindValue(sprintf(':%s', $key), $value);

$stmt->execute();
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

使用 PDOExtended 的示例

使用位置占位符

$pdo        =   new PDOExtended("mysql:host=localhost;dbname=mydb", "username", "password");
$stmt       =   $pdo->prepare($sql);
var_dump($stmt->sqlArray($tuples->getValues()));

使用命名占位符

$pdo        =   new PDOExtended("mysql:host=localhost;dbname=mydb", "username", "password");
$stmt       =   $pdo->prepare($sql);
var_dump($stmt->sqlArray($tuples->getValues()));

运算符

比较运算符

['my_column', '=', 'my_string']; // will output "`my_column` = ?"
['my_column', 'LIKE', 'my_string%']; // will output "`my_column` LIKE ?"
['my_column', '>=', 2]; // will output "`my_column` >= ?"
['my_column', '>', 2]; // will output "`my_column` > ?"
['my_column', '<=', 2]; // will output "`my_column` <= ?"
['my_column', '<', 2]; // will output "`my_column` < ?"
['my_column', '<>', 2]; // will output "`my_column` <> ?"
['my_column', '!=', 'my_string]; // will output "`my_column` != ?"

简单范围运算符

['my_column', 'BETWEEN', ['2015-01-01', '2015-01-30']]; // will output "`my_column` BETWEEN ? AND ?"

多个范围运算符

['my_column', 'IN', [5, 3]]; // will output "`my_column` IN (?, ?)"
['my_column', 'NOT IN', [8, 6, 9, 12]]; // will output "`my_column` NOT IN (?, ?, ?, ?)"

快捷方式

['my_column' => 'my_string']; // will output "`my_column` = ?"

包装器

"AND" 包装器

$tuples     =   new TupleANDWrapper();
$tuples[]   =   ['my_column', 'IN', [5, 3]]; // will output "`my_column` IN (?, ?)"
$tuples[]   =   ['my_column', 'NOT IN', [8, 6, 9, 12]]; // will output "`my_column` NOT IN (?, ?, ?, ?)"
var_dump((string) $tuples)); // will output "(`my_column` IN (?, ?) AND `my_column` NOT IN (?, ?, ?, ?))"

"OR" 包装器

$tuples     =   new TupleORWrapper();
$tuples[]   =   ['my_column', 'IN', [5, 3]]; // will output "`my_column` IN (?, ?)"
$tuples[]   =   ['my_column', 'NOT IN', [8, 6, 9, 12]]; // will output "`my_column` NOT IN (?, ?, ?, ?)"
var_dump((string) $tuples)); // will output "(`my_column` IN (?, ?) OR `my_column` NOT IN (?, ?, ?, ?))"

"String" 包装器

$tuples     =   new TupleORWrapper();
$tuples[]   =   ['my_column', 'BETWEEN', ['2015-01-01', '2015-01-30']]
$tuples[]   =   "`another_column` >= CURRENT_DATE - INTERVAL 3 DAY"
var_dump((string) $tuples)); // will output "(`my_column` BETWEEN ? AND ? OR `another_column` >= CURRENT_DATE - INTERVAL 3 DAY"

安装

将以下行添加到您的 composer.json 中

{
    "require": {
        "myskewhell/tuple": "dev-master"
    }
}

享受。