PHP code example of lakshanjs / pdodb

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

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

    

lakshanjs / pdodb example snippets

use LakshanJS\PdoDb\PdoDb;

$db = new PdoDb('host', 'username', 'password', 'databaseName');

$db = new PdoDb([
    'host'     => 'host',
    'username' => 'username',
    'password' => 'password',
    'db'       => 'databaseName',
    'port'     => 3306,
    'prefix'   => 'my_',
    'charset'  => 'utf8mb4',
]);

// Pass driver as the last parameter
$db = new PdoDb('host', 'username', 'password', 'databaseName', 3306, 'utf8mb4', 'pgsql');

// Or define it in the configuration array
$db = new PdoDb([
    'host'     => 'host',
    'username' => 'username',
    'password' => 'password',
    'db'       => 'databaseName',
    'driver'   => 'sqlsrv',
]);

$pdo = new PDO('mysql:host=host;dbname=databaseName', 'username', 'password');
$db  = new PdoDb($pdo);

$db->setPrefix('my_');

$db->autoReconnect = false;

function init()
{
    // db stays private here
    $db = new PdoDb('host', 'user', 'pass', 'db');
}

function myFunc()
{
    // obtain the instance created in init()
    $db = PdoDb::getInstance();
}

$db->addConnection('slave', [
    'host'     => 'host',
    'username' => 'username',
    'password' => 'password',
    'db'       => 'databaseName',
    'port'     => 3306,
    'prefix'   => 'my_',
    'charset'  => 'utf8mb4',
]);

$users = $db->connection('slave')->get('users');

use LakshanJS\PdoDb\PdoDb;

$db = new PdoDb([
    'host'     => 'localhost',
    'db'       => 'pdodb_test',
    'username' => 'root',
    'password' => 'secret',
    'charset'  => 'utf8mb4',
]);

// fetch active users
$activeUsers = $db->where('status', 'active')->get('users');

// join with profiles table
$usersWithProfiles = $db
    ->join('profiles p', 'u.id = p.user_id', 'INNER')
    ->get('users u', null, ['u.email', 'p.full_name']);

// insert a user and update counters
$userId = $db->insert('users', [
    'email'      => '[email protected]',
    'login'      => 'testuser',
    'created_at' => $db->now(),
]);

$db->where('id', $userId)->update('users', [
    'views'      => $db->inc(),
    'last_login' => $db->now(),
]);

// simple transaction
$db->startTransaction();
try {
    $db->insert('log', ['msg' => 'demo', 'created_at' => $db->now()]);
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
}

$db->jsonBuilder()->get('users');
$db->arrayBuilder()->get('users');
$db->objectBuilder()->get('users');

$users = $db->get('users');
$user  = $db->getOne('users');
$count = $db->getValue('users', 'count(*)');
$exists = $db->where('id', 1)->has('users');
$top   = $db->withTotalCount()->get('users', [0, 10]);
echo $db->totalCount; // total rows matching the query

$id = $db->insert('users', ['login' => 'admin']);
$ids = $db->insertMulti('users', [
    ['login' => 'a'],
    ['login' => 'b']
]);
$db->onDuplicate(['lastLogin' => $db->now()]);
$db->insert('users', ['id' => 1, 'login' => 'admin']);
$db->replace('users', ['id' => 1, 'login' => 'admin']);

$db->where('id', 1)->update('users', [
    'views' => $db->inc(),
    'expires' => $db->now('+1 day')
]);

$db->where('id', 1)->delete('users');

$rows  = $db->rawQuery('SELECT * FROM users WHERE id = ?', [1]);
$row   = $db->rawQueryOne('SELECT * FROM users WHERE id = ?', [1]);
$value = $db->rawQueryValue('SELECT count(*) FROM users');
$db->setQueryOption('SQL_NO_CACHE')->query('SELECT * FROM users');

$db->where('age', 18, '>')->orWhere('status', 'guest');
$db->whereRaw('JSON_CONTAINS(tags, ?)', ['"php"']);
$db->having('cnt', 5, '>')->orHaving('cnt', 10, '<');
$db->havingRaw('SUM(score) > ?', [100]);

$db->join('profiles p', 'u.id = p.user_id', 'LEFT');
$db->joinWhere('profiles p', 'p.active', 1);
$users = $db->get('users u');

$db->joinWithAlias('profiles', 'u.id = p.user_id', 'LEFT', 'p');

$db->orderBy('createdAt', 'DESC');
$db->groupBy('status');

$sub = PdoDb::subQuery('u');
$sub->where('active', 1)->get('users');
$posts = $db->join($sub, 'p.userId=u.id', 'LEFT')->get('posts p');

$page = 1;
$users = $db->paginate('users', $page);
$total = $db->totalPages;

$mapped = $db->map('id')->get('users');
$copy = $db->where('active', 1)->copy()->get('users');

$db->startTransaction();
if (!$db->insert('log', ['msg' => 'test'])) {
    $db->rollback();
} else {
    $db->commit();
}

$db->setLockMethod('WRITE')->lock('users');
// ... queries ...
$db->unlock();

$db->disconnect('slave');
$db->disconnectAll();
$db->ping();
$exists = $db->tableExists('users');
$escaped = $db->escape("' and 1=1");
$id = $db->getInsertId();
$error = $db->getLastError();
$query = $db->getLastQuery();
$db->clearStmtCache();
print_r($db->getCacheStats());
$db->setTrace(true);
print_r($db->getTrace());

$db->setSecurityLogCallback(function($type, $msg) {
    error_log("[$type] $msg");
});
$db->setSecurityLogging(false); // disable
$status = $db->getSecurityStatus();

$db->update('users', [
    'visits' => $db->inc(),
    'quota'  => $db->dec(5),
    'note'   => $db->not('note'),
    'hash'   => $db->func('SHA1(?)', ['secret'])
]);

$users = $db
    ->where('status', 'active')
    ->where('created_at', '2025-01-01', '>=')
    ->orderBy('createdAt', 'DESC')
    ->get('users', [20, 10], ['id', 'email']);

$rows = $db
    ->where('role', 'admin')
    ->orWhere('role', 'manager')
    ->get('users');

$rows = $db
    ->join('profiles p', 'u.id = p.user_id', 'LEFT')
    ->joinWhere('profiles p', 'p.active', 1)
    ->orderBy('u.id', 'ASC')
    ->get('users u', null, ['u.id', 'u.email', 'p.full_name']);

$rows = $db
    ->joinWithAlias('profiles', 'u.id = p.user_id', 'LEFT', 'p')
    ->joinWhere('profiles p', 'p.active', 1)
    ->get('users u');

$rows = $db
    ->groupBy('status')
    ->having('COUNT(*)', 5, '>')
    ->orderBy('cnt', 'DESC')
    ->get('users', null, ['status', 'COUNT(*) AS cnt']);

$db->onDuplicate([
    'login'     => $db->func('VALUES(login)'),
    'lastLogin' => $db->func('VALUES(last_login)')
]);

$db->insert('users', [
    'id'        => 1,
    'login'     => 'admin',
    'last_login'=> $db->now()
]);

$db->insertMulti('tags', [
    ['name' => 'php'],
    ['name' => 'pdo'],
    ['name' => 'security'],
]);

$db->where('id', 42)->update('users', [
    'views'   => $db->inc(),
    'quota'   => $db->dec(5),
    'expires' => $db->now('+1 day'),
    'note'    => $db->not('note'),
]);

$db->where('user_id', 7)
   ->where('last_seen', '2025-01-01', '<')
   ->delete('sessions');

$sub = PdoDb::subQuery('u');
$sub->where('active', 1)->get('users', null, ['id', 'email']);

$rows = $db
    ->join($sub, 'p.user_id = u.id', 'LEFT')
    ->get('posts p', null, ['p.id', 'p.title', 'u.email']);

$page  = 3;
$users = $db->orderBy('id', 'ASC')->paginate('users', $page, 10);
$total = $db->totalPages; // provided by withTotalCount logic inside paginate

$row = $db->rawQueryOne('SELECT * FROM users WHERE id = ?', [1]);

$db->startTransaction();
if (!$db->insert('log', ['msg' => 'test'])) {
    $db->rollback();
} else {
    $db->commit();
}

$json  = $db->jsonBuilder()->get('users');
$array = $db->arrayBuilder()->get('users');
$obj   = $db->objectBuilder()->get('users');

$top = $db->withTotalCount()->get('users', [0, 10]);
$total = $db->totalCount;

$db->connect();

$db->disconnect('slave');

$db->disconnectAll();

$db->connection('slave')->get('users');

$db->addConnection('slave', [...]);

$pdo = $db->pdo();

$db->jsonBuilder()->get('users');

$db->arrayBuilder()->get('users');

$db->objectBuilder()->get('users');

$db->setPrefix('my_');

$db->rawQuery('SELECT 1');

$db->rawQueryOne('SELECT * FROM users WHERE id = ?', [1]);

$db->rawQueryValue('SELECT COUNT(*) FROM users');

$db->query('SELECT * FROM users WHERE id = :id', [':id' => 1]);

$db->setQueryOption('SQL_NO_CACHE');

$db->withTotalCount()->get('users');

$users = $db->get('users');

$user = $db->getOne('users');

$count = $db->getValue('users', 'COUNT(*)');

$exists = $db->where('id',1)->has('users');

$id = $db->insert('users', ['login' => 'admin']);

$db->insertMulti('users', [['login' => 'a'], ['login' => 'b']]);

$db->replace('users', ['id' => 1, 'login' => 'admin']);

$db->onDuplicate(['login' => 'new']);

$db->where('id',1)->update('users',['login'=>'u']);

$db->where('id',1)->delete('users');

$db->where('id',1);

$db->orWhere('status','active');

$db->whereRaw('id > ?', [10]);

$db->having('COUNT(id)', 1);

$db->orHaving('SUM(score)', '>', 10);

$db->havingRaw('SUM(score) > ?', [10]);

$db->join('profiles p', 'u.id = p.user_id', 'LEFT');

$db->joinWithAlias('profiles', 'u.id = p.user_id', 'LEFT', 'p');

$db->joinWhere('profiles p', 'p.active', 1);

$db->joinOrWhere('profiles p', 'p.active', 0);

$db->orderBy('createdAt', 'DESC');

$db->groupBy('status');

$sub = PdoDb::subQuery('u');

$sql = $sub->getSubQuery();

$copy = $db->copy();

$mapped = $db->map('id')->get('users');

$users = $db->paginate('users', 1);

$db->startTransaction();

$db->commit();

$db->rollback();

$db->setLockMethod('WRITE');

$db->lock('users');

$db->unlock();

$escaped = $db->escape("' and 1=1");

$id = $db->getInsertId();

$error = $db->getLastError();

$errno = $db->getLastErrno();

$query = $db->getLastQuery();

$db->setTrace(true);

$trace = $db->getTrace();

$last = $db->getLastTrace();

$db->clearTrace();

$db->update('users',['visits'=>$db->inc()]);

$db->update('users',['quota'=>$db->dec(5)]);

sql
INSERT INTO tags (name) VALUES ('php'), ('pdo'), ('security');

sql
DELETE FROM sessions
WHERE user_id = 7 AND last_seen < '2025-01-01';

php
$db->update('users',['active'=>$db->not('active')]);

php
$db->update('users',['hash'=>$db->func('SHA1(?)',['secret'])]);

php
$db->insert('log',['created'=>$db->now()]);

php
$db->where('created_at', $db->interval('-1', 'DAY'), '>');

php
$db->ping();

php
$db->clearStmtCache();

php
$stats = $db->getCacheStats();

php
$exists = $db->tableExists('users');

php
$valid = $db->isValidIdentifier('users');

php
$db->setSecurityLogCallback(fn($t,$m)=>error_log("[$t] $m"));

php
$db->setSecurityLogging(false);

php
$status = $db->getSecurityStatus();

php
$version = $db->getVersion();

php
$mysql = $db->getMysqlVersion();

php
$db = PdoDb::getInstance();