PHP code example of a-le / microdbal

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

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

    

a-le / microdbal example snippets

use aLe\MicroDbal;

use aLe\MicroDbal;

// Constructor signature: __construct(string $dsn, ?string $username = null, ?string $password = null)
$db = new MicroDbal('sqlite::memory:');

// Method signature: run(string $sql, array $args = [], ?int &$affectedRows = null): PDOStatement
$db->run('CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, age INTEGER)');
$db->run('INSERT INTO test (name, age) VALUES (?, ?)', ['Pauline', 25]); // question mark placeholder
$db->run('INSERT INTO test (name, age) VALUES (:name, :age)', ['name' => 'Ryan', 'age' => 15]); // named placeholder

// Method signature: getAll(string $sql, array $args = [], ?array &$columnsMeta = null): array
$rows = $db->getAll('SELECT * FROM test WHERE age > ?', [10]);
print_r($rows);

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Pauline
            [age] => 25
        ),
    [1] => Array
        (
            [id] => 2
            [name] => Ryan
            [age] => 15
        )
)

// Method signature: getRow(string $sql, array $args = [], ?array &$columnsMeta = null): array
$row = $db->getRow('SELECT * FROM test WHERE id = ?', [1]);
print_r($row);

Array
(
    [id] => 1
    [name] => Pauline
    [age] => 25
)

// Method signature: getCol(string $sql, array $args = []): array
$col = $db->getCol('SELECT id FROM test WHERE age >= ?', [10]);
print_r($col);

Array
(
    [0] => 1,
    [1] => 2,
)

// Method signature: getOne(string $sql, array $args = []): mixed
$cnt = $db->getOne('SELECT COUNT(*) FROM test WHERE age < ?', [18]);
print_r($cnt);

1

$db->run('INSERT INTO test (name, age) VALUES (?, ?)', ['Marty', 65], $affectedRows);
print_r($affectedRows);

1

print_r($db->getLastInsertedId());

3

$db->getAll('SELECT * FROM test WHERE false', [], $columnsMeta);
print_r($columnsMeta);

Array
(
    [0] => Array
        (
            [name] => id
            (...)
        )
    [1] => Array
        (
            [name] => name
            (...)
        )
    [2] => Array
        (
            [name] => age
            (...)
        )
)

$db->beginTransaction();
$db->run('INSERT INTO test (name, age) VALUES (?, ?)', ['John', 30]);
$db->commit();

$db->beginTransaction();
$db->run('INSERT INTO test (name, age) VALUES (?, ?)', ['Jane', 40]);
$db->rollBack();

if ($db->inTransaction()) {
    echo "Transaction is active.";
}

class Person
{
    public int $id;
    public string $name;
    public int $age;
}

// Method signature: getOneObject(string $sql, array $args = [], string $className): ?object
$person = $db->getOneObject('SELECT * FROM test WHERE id = ?', [1], Person::class);

// Method signature: getAllObjects(string $sql, array $args = [], string $className): array
$peopleAbove18 = $db->getAllObjects('SELECT * FROM test where age > ?', [18], Person::class);

// Method signature: sqlIn(array $values): string
$arr = [1, 2, 3];
$sqlFragment = $db->sqlIn($arr);
$result = $db->getAll('SELECT * FROM test WHERE id IN ' . $sqlFragment, $arr);

// Method signature: sqlLike(string $value, string $escapeChar = '\\'): string
$s = 'P';
$arg = $db->sqlLike($s). '%';
$result = $db->getAll('SELECT * FROM test WHERE name LIKE ?', [$arg]);

$auth = new \Delight\Auth\Auth($db->pdo);

$stmt = $db->run('SELECT * FROM test');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Do something with $row
}

ini
    error_reporting = E_ALL
    display_errors = On
    

bash
   php run-tests.php
   

bash
   php run-tests.php <dsn> <username> <password>
   

bash
   php run-tests.php "pgsql:dbname=postgres;host=localhost" "user" "password"