GeekLab\GLPDO2

Easy to use PDO Wrapper for PHP >= 8.1, _(*2)

Latest

2022-11-25 (4.0.9) * Style fixes., _(*3)

Features

Bind value by the correct type. E.g. Don't bind as a string where an integer bind should be.
Bindings are injected, so you can create your own!
Help prevent injections.
PSR1/2/4 Compliant.

Installation

composer require geeklab/glpdo2, _(*4)

Todo

More tests, since we can test at many levels and implementations.
Better schema for testing.
Better docs.
More of a real wold example.
Reduce the complexity?

Basic Usage (Quick-N-Dirty)

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

define('DS', DIRECTORY_SEPARATOR);
require_once '..' . DS . '..' . DS . 'vendor' . DS . 'autoload.php';

$dbConn    = new PDO('mysql:host=localhost;dbname=playground', 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$db        = new \GeekLab\GLPDO2\GLPDO2($dbConn);
$Statement = new \GeekLab\GLPDO2\Statement(GLPDO2\Bindings\MySQL\MySQLBindingFactory::build());
$start     = 0;
$limit     = 5;

$Statement->sql('SELECT *')
          ->sql('FROM (')
          ->sql('          SELECT *')
          ->sql('          FROM   `mock_data`')
          ->sql('          LIMIT  ?, ?')->bInt($start)->bInt($limit)
          ->sql('     ) SUBQ')
          ->sql('ORDER BY `id` DESC;');

// Show computedSQL statement
print_r($Statement->getComputed());

$res = $db->selectRows($Statement);

print_r($res);

Data Binding Methods

Statement->bBool($value, optional boolean $null)br/ Binds a value as bool(true, false), with optional NULL value return., _(*5)

Statement->bBoolInt($value, optional boolean $null)br/ Binds a value as int(0, 1), with optional NULL value return., _(*6)

Statement->bDate($value, optional boolean $null)br/ Binds a value as a date (string - validated for YYYY-MM-DD), with optional NULL return., _(*7)

Statement->bDateTime($value, optional boolean $null)br/ Binds a value as a date time (string - validated for YYYY-MM-DD HH:MM:SS), with optional NULL return., _(*8)

Statement->bFloat($value, optional integer $decimals, optional boolean $null)br/ Binds a value aa a float, with decimal place (default of 3) and optional NULL return. Use '%%' instead of '?'., _(*9)

Statement->bInt($value, optional boolean $null)br/ Bind a value as an integer, whith optional NULL return., _(*10)

Statement->bIntArray(array $data, integer $default)br/ Converts an array of integers to a comma separated values. Will output $default (which is 0) if $data is not an array. Used with IN() statements. Use '%%' instead of '?'., _(*11)

Statement->bJSON($data, optional boolean $null)br/ Binds a JSON object or string as a string, with optional NULL value. Throws JsonException., _(*12)

Statement->bLike($value, boolean $ends, boolean $starts)br/ Binds a value as a string for LIKE queries. $ends = "ends with", $starts = "starts with", _(*13)

Statement->bStr($value, optional boolean $null, optional \PDO::PARAM_* $type)br/ Binds a value as a string, with optional NULL value return and optional PDO binding type (default \PDO::PARAM_STR)., _(*14)

Statement->bStrArr(array $values, optional string $default)br/ Binds a string converted array for use with IN statements. $default is used when value isn't an array, which the default is NULL. Use '%%' instead of '?'., _(*15)

Statement->bind($name, $value, \PDO::PARAM_* $type)br/ Binds a value to a named parameter with option PDO binding type (default \PDO::PARAM_STR), _(*16)

Statement->bRaw($value)br/ Binds a raw value to '%%' in the sql statement. This is unquoted and unescaped. Good for tables names and functions. Can be dangerous if not handled correctly., _(*17)

Query Methods

GLPDO->queryDelete(Statement $SQL)br/ Runs a delete query and returns numbers of affected rows., _(*18)

GLPDO->queryInsert(Statement $SQL)br/ Runs an insert query and returns the primary ID., _(*19)

GLPDO->queryUpdate(Statement $SQL)br/ Runs an update query and returns number of affect rows, _(*20)

GLPDO->selectRows(Statement $SQL)br/ Run a normal query, returns multiple rows as an array of associative arrays, or false., _(*21)

GLPDO->selectRow(Statement $SQL)br/ Runs a normal query, returns a single row as an array, or false., _(*22)

GLPDO->selectValue(Statement $SQL, $column, $default)br/ Runs a normal query, returns a single column ($column) and can return a default (mixed $default = null) value is no value is in the column., _(*23)

GLPDO->selectRow(Statement $SQL)br/ Runs a normal query, returns a single row as an array., _(*24)

GLPDO->beginTransaction()br/ Begins an SQL Transaction., _(*25)

GLPDO->commit()br/ Commits an SQL Transaction., _(*26)

GLPDO->inTransaction()br/ Is there a transaction in progress, returns bool., _(*27)

Misc Methods

Statement->sql(string $text)br/ Used to build up the SQL parameterized statement., _(*28)

Statement->reset()br/ Used to reset Statement private variables. Usefully for creating multiple queries without having to create a new Statement object., _(*29)

Statement->execute(\PDO $PDO)br/ Prepares and executes the statement, _(*30)

Statement->getComputed()br/ Returns what the compiled SQL query string might look like for debugging purposes., _(*31)