PHP code example of myskewhell / tuple

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

myskewhell / tuple example snippets

$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);

var_dump($tuples->getPlaceHolders());

var_dump($tuples->getValues());

$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);

var_dump($tuples->getPlaceHolders());

var_dump($tuples->getValues());

$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));

$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` = ?"

$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 (?, ?, ?, ?))"

$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 (?, ?, ?, ?))"

$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"

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
        )

sql
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
    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
      )