PHP code example of tommyknocker / pdo-database-class

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

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

    

tommyknocker / pdo-database-class example snippets

use tommyknocker\pdodb\PdoDb;

// Connect (SQLite - no setup needed!)
$db = new PdoDb('sqlite', ['path' => ':memory:']);

// Create table
$db->rawQuery('CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT,
    age INTEGER
)');

// Insert
$id = $db->find()->table('users')->insert([
    'name' => 'John',
    'email' => '[email protected]',
    'age' => 30
]);

// Query
$users = $db->find()
    ->from('users')
    ->where('age', 18, '>')
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->get();

// Update
$db->find()
    ->table('users')
    ->where('id', $id)
    ->update(['age' => 31]);

use tommyknocker\pdodb\PdoDb;

// SQLite (easiest - no database server needed)
$db = new PdoDb('sqlite', ['path' => ':memory:']);

// Or MySQL/PostgreSQL
$db = new PdoDb('mysql', [
    'host' => 'localhost',
    'dbname' => 'mydb',
    'username' => 'user',
    'password' => 'pass'
]);

// Simple approach (raw SQL)
$db->rawQuery('CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT,
    age INTEGER
)');

// Or use DDL Query Builder
$db->schema()->createTable('products', [
    'id' => $db->schema()->primaryKey(),
    'name' => $db->schema()->string(255)->notNull(),
    'status' => $db->schema()->enum(['draft', 'published', 'archived'])
        ->defaultValue('draft'),
    'created_at' => $db->schema()->timestamp()->defaultExpression('CURRENT_TIMESTAMP')
]);

// Create
$id = $db->find()->table('users')->insert([
    'name' => 'Alice',
    'email' => '[email protected]',
    'age' => 30
]);

// Read
$users = $db->find()->from('users')->get();
$user = $db->find()->from('users')->where('id', $id)->getOne();

// Update
$db->find()->table('users')
    ->where('id', $id)
    ->update(['name' => 'Bob']);

// Delete
$db->find()->table('users')->where('id', $id)->delete();

use tommyknocker\pdodb\PdoDb;

// MySQL
$db = new PdoDb('mysql', [
    'host' => '127.0.0.1',
    'username' => 'testuser',
    'password' => 'testpass',
    'dbname' => 'testdb',
    'port' => 3306,
    'charset' => 'utf8mb4',
]);

// PostgreSQL
$db = new PdoDb('pgsql', [
    'host' => '127.0.0.1',
    'username' => 'testuser',
    'password' => 'testpass',
    'dbname' => 'testdb',
    'port' => 5432,
]);

// SQLite
$db = new PdoDb('sqlite', [
    'path' => '/path/to/database.sqlite',  // or ':memory:' for in-memory
]);

// MSSQL
$db = new PdoDb('sqlsrv', [
    'host' => 'localhost',
    'username' => 'testuser',
    'password' => 'testpass',
    'dbname' => 'testdb',
    'port' => 1433,
]);

$db = new PdoDb();
$db->addConnection('mysql_main', ['driver' => 'mysql', ...]);
$db->addConnection('pgsql_analytics', ['driver' => 'pgsql', ...]);
$db->connection('mysql_main')->find()->from('users')->get();

$db->enableReadWriteSplitting(new RoundRobinLoadBalancer());
$db->addConnection('master', [...], ['type' => 'write']);
$db->addConnection('replica-1', [...], ['type' => 'read']);
// SELECTs automatically go to replicas, DML to master

$cache = CacheFactory::create(['type' => 'filesystem', 'directory' => '/var/cache']);
$db = new PdoDb('mysql', $config, [], null, $cache);
$users = $db->find()->from('users')->cache(3600)->get();

use tommyknocker\pdodb\PdoDb;

$db = PdoDb::fromEnv();

// Configure AI (or use environment variables: PDODB_AI_OPENAI_KEY, etc.)
$db = new PdoDb('mysql', [
    'host' => 'localhost',
    'dbname' => 'mydb',
    'username' => 'user',
    'password' => 'pass',
    'ai' => [
        'provider' => 'openai',
        'openai_key' => 'sk-...',
    ],
]);

// Get AI-enhanced analysis
$result = $db->find()
    ->from('users')
    ->where('email', '[email protected]')
    ->explainAiAdvice(tableName: 'users', provider: 'openai');

// Access base analysis (traditional EXPLAIN)
foreach ($result->baseAnalysis->issues as $issue) {
    echo "Issue: {$issue->message}\n";
}

// Access AI recommendations
echo "AI Analysis:\n";
echo $result->aiAnalysis . "\n";

// SELECT
$user = $db->find()
    ->from('users')
    ->where('id', 10)
    ->getOne();

$users = $db->find()
    ->from('users')
    ->where('age', 18, '>=')
    ->get();

// INSERT
$id = $db->find()->table('users')->insert([
    'name' => 'Alice',
    'email' => '[email protected]',
    'age' => 30
]);

// UPDATE
$db->find()
    ->table('users')
    ->where('id', $id)
    ->update(['age' => 31]);

// DELETE
$db->find()
    ->table('users')
    ->where('id', $id)
    ->delete();

use tommyknocker\pdodb\helpers\Db;

// WHERE conditions
$users = $db->find()
    ->from('users')
    ->where('status', 'active')
    ->andWhere('age', 18, '>')
    ->andWhere(Db::like('email', '%@example.com'))
    ->get();

// JOIN and GROUP BY
$stats = $db->find()
    ->from('users AS u')
    ->select(['u.id', 'u.name', 'total' => Db::sum('o.amount')])
    ->leftJoin('orders AS o', 'o.user_id = u.id')
    ->groupBy('u.id')
    ->having(Db::sum('o.amount'), 1000, '>')
    ->get();

$db->startTransaction();
try {
    $userId = $db->find()->table('users')->insert(['name' => 'Alice']);
    $db->find()->table('orders')->insert(['user_id' => $userId, 'total' => 100]);
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
}

use tommyknocker\pdodb\helpers\Db;

// Create JSON data
$db->find()->table('users')->insert([
    'name' => 'John',
    'meta' => Db::jsonObject(['city' => 'NYC', 'age' => 30]),
    'tags' => Db::jsonArray('php', 'mysql', 'docker')
]);

// Query JSON
$adults = $db->find()
    ->from('users')
    ->where(Db::jsonPath('meta', ['age'], '>', 25))
    ->get();

// Extract JSON values
$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'name',
        'city' => Db::jsonGet('meta', ['city'])
    ])
    ->get();

// Update JSON
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => Db::jsonSet('meta', ['city'], 'London')
    ]);

$users = $db->rawQuery(
    'SELECT * FROM users WHERE age > :age',
    ['age' => 18]
);

$users = $db->find()
    ->from('users')
    ->whereIn('id', function($query) {
        $query->from('orders')
            ->select('user_id')
            ->where('total', 1000, '>');
    })
    ->get();

// Full pagination (with total count)
$result = $db->find()
    ->from('posts')
    ->orderBy('created_at', 'DESC')
    ->paginate(20, 1);

// Cursor pagination (most efficient)
$result = $db->find()
    ->from('posts')
    ->orderBy('id', 'DESC')
    ->cursorPaginate(20);

// Process in batches
foreach ($db->find()->from('users')->batch(100) as $batch) {
    foreach ($batch as $user) {
        processUser($user);
    }
}

// Stream results (minimal memory)
foreach ($db->find()->from('users')->stream() as $user) {
    processUser($user);
}

// Cache for 1 hour
$products = $db->find()
    ->from('products')
    ->where('category', 'Electronics')
    ->cache(3600)
    ->get();

use tommyknocker\pdodb\exceptions\{
    DatabaseException,
    ConnectionException,
    QueryException,
    ConstraintViolationException,
    TransactionException
};

try {
    $users = $db->find()->from('users')->get();
} catch (ConnectionException $e) {
    // Handle connection errors
    if ($e->isRetryable()) {
        // Implement retry logic
    }
} catch (QueryException $e) {
    // Handle query errors
    error_log("Query: " . $e->getQuery());
    error_log("Context: " . $e->getDescription());
} catch (ConstraintViolationException $e) {
    // Handle constraint violations
    error_log("Constraint: " . $e->getConstraintName());
}

$cache = new Psr16Cache(new FilesystemAdapter());
$db = new PdoDb('mysql', $config, [], null, $cache);

$products = $db->find()
    ->from('products')
    ->where('category', 'Electronics')
    ->cache(3600)
    ->get();

// ❌ Slow: Multiple single inserts
foreach ($users as $user) {
    $db->find()->table('users')->insert($user);
}

// ✅ Fast: Single batch insert
$db->find()->table('users')->insertMulti($users);

// ✅ Safe: Limited results
$users = $db->find()->from('users')->limit(1000)->get();

// Process in chunks
foreach ($db->find()->from('users')->batch(100) as $batch) {
    processBatch($batch);
}

$db->find()->table('users')->onDuplicate([
    'age' => Db::inc()
])->insert(['email' => '[email protected]', 'age' => 25]);

$users = $db->rawQuery('SELECT * FROM users WHERE age > :age', ['age' => 18]);

$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$db = new PdoDb('mysql', ['pdo' => $pdo]);

$db = new PdoDb('sqlite', ['path' => ':memory:']);

$db->startTransaction();
try {
    // Your operations
    $db->commit();
} catch (\Exception $e) {
    $db->rollBack();
}

$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$stmt = $pdo->prepare('SELECT * FROM users WHERE age > :age');
$stmt->execute(['age' => 18]);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

$db = new PdoDb('mysql', [
    'host' => 'localhost',
    'dbname' => 'test',
    'username' => 'user',
    'password' => 'pass'
]);
$users = $db->find()->from('users')->where('age', 18, '>')->get();

User::where('active', 1)
    ->where('age', '>', 18)
    ->orderBy('name')
    ->limit(10)
    ->get();

$db->find()
    ->from('users')
    ->where('active', 1)
    ->andWhere('age', 18, '>')
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->get();

// ✅ Works
$db->find()->from('users')->limit(20)->offset(10)->get();

foreach ($db->find()->from('users')->batch(100) as $batch) {
    processBatch($batch);
}

bash
cd examples

# SQLite (ready to use, no setup config.mysql.php with your credentials)
PDODB_DRIVER=mysql php 01-basic/02-simple-crud.php

# Test all examples on all available databases
./scripts/test-examples.sh