PHP code example of ndtan / dbf

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

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

    

ndtan / dbf example snippets

ndtan\DBF;

$db = new DBF('sqlite::memory:');
$db->execute('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE, status TEXT)');

$id = $db->tx(function (DBF $tx): int {
    return $tx->table('users')->insert([
        'email' => '[email protected]',
        'status' => 'active',
    ]);
});

$user = $db->table('users')
    ->select(['id', 'email'])
    ->where('id', '=', $id)
    ->first();

echo $user['email']; // [email protected]

$db = new ndtan\DBF('sqlite::memory:');

$db = new ndtan\DBF('mysql://user:[email protected]/app?charset=utf8mb4');

$db = new ndtan\DBF([
    'type' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'app',
    'username' => 'app_user',
    'password' => 'secret',
    'charset' => 'utf8mb4',
    'prefix' => 'app_',
    'options' => [PDO::ATTR_TIMEOUT => 5],
    'features' => [
        'max_in_params' => 1000,
        'soft_delete' => ['enabled' => true, 'column' => 'deleted_at', 'mode' => 'timestamp'],
    ],
]);

$db = new ndtan\DBF([
    'write' => 'mysql://writer:secret@primary/app',
    'read' => 'mysql://reader:secret@replica/app',
    'routing' => 'auto',
]);

$rows = $db->table('users')
    ->select(['id', 'email AS address'])
    ->where('status', '=', 'active')
    ->whereIn('id', [1, 2, 3])
    ->orderBy('id', 'desc')
    ->limit(20)
    ->get();

$row = $db->table('users')->where('id', '=', 1)->first();
$exists = $db->table('users')->where('id', '=', 1)->exists();

$rows = $db->table('orders')
    ->select(['orders.user_id', 'SUM(orders.total) AS total'])
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->groupBy(['orders.user_id'])
    ->having('SUM(orders.total)', '>', 100)
    ->get();

$count = $db->table('orders')->count();
$sum = $db->table('orders')->sum('total');
$avg = $db->table('orders')->avg('total');
$emails = $db->table('users')->pluck('email');
$map = $db->table('users')->pluck('email', 'id');

$id = $db->table('users')->insert(['email' => '[email protected]']);
$ids = $db->table('users')->insertMany([
    ['email' => '[email protected]'],
    ['email' => '[email protected]'],
]);
$row = $db->table('users')->insertGet(['email' => '[email protected]'], ['id', 'email']);
$affected = $db->table('users')->where('id', '=', $id)->update(['status' => 'vip']);
$affected = $db->table('users')->where('id', '=', $id)->delete();

$db->table('users')->upsert(
    ['email' => '[email protected]', 'status' => 'vip'],
    ['email'],
    ['status']
);

$tenantDb = $db->withScope(['tenant_id' => 7]);
$users = $tenantDb->table('users')->get();
$tenantDb->table('users')->where('id', '=', 1)->restore();

$db->tx(function (ndtan\DBF $tx) {
    $row = $tx->table('accounts')->where('id', '=', 1)->forUpdate()->first();
    $tx->table('accounts')->where('id', '=', 1)->update(['balance' => 100]);
}, attempts: 3);

$query = $db->table('users')->orderBy('id', 'desc')->limit(50);
$page = $query->getKeyset(null, 'id');
$next = $page['next'] === null ? null : $query->getKeyset($page['next'], 'id');

foreach ($db->table('users')->orderBy('id')->stream() as $row) {
    processRow($row);
}
$db->table('users')->chunkById(100, function (array $rows) {
    processBatch($rows);
});

$row = $db->table('users')->whereJson('data->profile->name', '=', 'Tony')->first();
$db->table('users')->where('id', '=', 1)->jsonSet('data', [
    'profile.name' => 'Tony',
    'active' => true,
    'roles' => ['editor'],
]);

$rows = $db->selectRaw('SELECT email FROM users WHERE id = :id', ['id' => 1]);
$affected = $db->execute('UPDATE users SET status = ? WHERE id = ?', ['vip', 1]);
$result = $db->raw('INSERT INTO users(email) VALUES (?) RETURNING id', ['[email protected]']);

$db->setLogger(function (string $sql, array $params, float $ms) {
    error_log(sprintf('[%.2fms] %s', $ms, $sql));
});
$db->setMetrics(function (array $metrics) {
    recordMetrics($metrics);
});
$db = $db->policy(function (array $ctx) {
    if (($ctx['type'] ?? '') === 'delete') throw new RuntimeException('Deletion denied');
});
$db = $db->use(fn(array $ctx, callable $next) => $next($ctx));

$db->setTestMode(true);
$db->table('users')->where('id', '=', 1)->get();
echo $db->queryString();
$params = $db->queryParams();