DB Class

The DB class is a utility class that provides short, convenient methods for common database operations. It's a wrapper around the QueryBuilder system that makes database usage simple and fast.

Table of Contents

• Overview
• Basic Usage
• CRUD Operations
• Data Search
• Where Conditions
• Transaction Management
• Additional Utilities
• All Methods
• Usage Examples
• Important Notes
• Related Classes

Overview

The DB class is designed to make database operations simple, providing:

• Short methods for basic CRUD operations
• Simple transaction management
• Utilities for table checking and management
• Appropriate error handling

Basic Usage

Creating DB Instance

use Kotchasan\DB;
use Kotchasan\Database;

// Create new instance
$db = DB::create();

// Create instance with specific Database connection
$database = Database::create('custom_connection');
$db = DB::create($database);

// Create instance with constructor
$db = new DB();

CRUD Operations

INSERT - Adding Data

// Insert single row
$userId = $db->insert('users', [
    'username' => 'john_doe',
    'email' => 'john@example.com',
    'password' => password_hash('secret', PASSWORD_DEFAULT),
    'created_at' => date('Y-m-d H:i:s')
]);

echo "User ID: {$userId}";

// Insert without needing ID
$result = $db->insert('logs', [
    'level' => 'info',
    'message' => 'User logged in',
    'timestamp' => time()
]);

if ($result) {
    echo "Log entry created successfully";
}

SELECT - Retrieving Data

// Get all records from table
$users = $db->select('users');

// Get records with conditions
$activeUsers = $db->select('users', ['status' => 'active']);

// Get records with complex conditions
$recentUsers = $db->select('users',
    [
        ['status', 'active'],
        ['created_at', '>=', '2024-01-01']
    ],
    ['orderBy' => ['created_at' => 'DESC'], 'limit' => 10]
);

// Get specific columns
$userEmails = $db->select('users',
    ['status' => 'active'],
    [],
    ['email', 'username']
);

// Get records with pagination
$page = 2;
$perPage = 2;
$users = $db->select('users',
    ['status' => 'active'],
    [
        'orderBy' => 'created_at',
        'limit' => $perPage,
        'offset' => ($page - 1) * $perPage
    ]
);

UPDATE - Modifying Data

use Kotchasan\Database\Sql;

// Update by ID
$affectedRows = $db->update('users',
    ['id' => $userId],
    [
        'last_login' => date('Y-m-d H:i:s'),
        'login_count' => Sql::raw('login_count + 1') // Raw SQL expression
    ]
);

echo "Updated {$affectedRows} rows";

// Update multiple rows
$affectedRows = $db->update('posts',
    ['status' => 'draft', 'author_id' => $authorId],
    ['status' => 'published']
);

// Update with complex conditions
$affectedRows = $db->update('users',
    [['status', 'inactive'], ['last_login', '<', '2023-01-01']],
    ['status' => 'archived']
);

DELETE - Removing Data

// Delete by ID
$deletedRows = $db->delete('users', ['id' => $userId]);

// Delete with conditions
$deletedRows = $db->delete('logs', ['level' => 'debug']);

// Delete with LIMIT
$deletedRows = $db->delete('temp_files',
    [['created_at', '<', date('Y-m-d', strtotime('-7 days'))]],
    100 // Delete maximum 100 rows
);

echo "Deleted {$deletedRows} rows";

Data Search

// Find first matching row
$user = $db->first('users', ['email' => 'john@example.com']);

if ($user) {
    echo "Found user: {$user->username}";
} else {
    echo "User not found";
}

// Check if data exists
$exists = $db->exists('users', ['email' => 'john@example.com']);

if ($exists) {
    echo "Email already exists";
}

// Count rows
$totalUsers = $db->count('users');
$activeUsers = $db->count('users', ['status' => 'active']);

echo "Total users: {$totalUsers}, Active: {$activeUsers}";

// Get a single scalar value
$total = $db->value('users', 'COUNT(*) as total', ['status' => 'active'], 0);

// Get an array of values from a column
$emails = $db->pluck('users', 'email', ['status' => 'active'], [
    'orderBy' => ['email' => 'ASC']
]);

Where Conditions

// Simple: [['field', 'value']]
$db->first('users', [['id', 1]]);

// Multiple conditions (AND)
$db->select('users', [
    ['status', 'active'],
    ['role', 'admin']
]);

// With operators
$db->select('orders', [
    ['total', '>', 1000],
    ['created_at', '>=', '2024-01-01']
]);

// IN clause
$db->select('users', [['id', [1, 2, 3]]]);

// For OR conditions or nested groups, use Database/QueryBuilder directly.

Transaction Management

Basic Transaction

use Kotchasan\DB;
use Kotchasan\Database\Sql;

// Begin transaction
$db->beginTransaction();

try {
    // Perform multiple operations
    $orderId = $db->insert('orders', [
        'user_id' => $userId,
        'total' => $total,
        'status' => 'pending'
    ]);

    $db->insert('order_items', [
        'order_id' => $orderId,
        'product_id' => $productId,
        'quantity' => $quantity,
        'price' => $price
    ]);

    $db->update('products',
        ['id' => $productId],
        ['stock' => Sql::raw('stock - '.(int) $quantity)]
    );

    // Commit transaction
    $db->commit();

    echo "Order created successfully";

} catch (Exception $e) {
    // Rollback transaction
    $db->rollback();

    echo "Error creating order: " . $e->getMessage();
    throw $e;
}

Advanced Transaction Usage

use Kotchasan\DB;
use Kotchasan\Database\Sql;

class OrderService
{
    private $db;

    public function __construct()
    {
        $this->db = DB::create();
    }

    public function createOrder($userId, $items)
    {
        $this->db->beginTransaction();

        try {
            // Create order
            $orderId = $this->db->insert('orders', [
                'user_id' => $userId,
                'status' => 'pending',
                'created_at' => date('Y-m-d H:i:s')
            ]);

            $total = 0;

            // Add order items
            foreach ($items as $item) {
                // Check stock
                $product = $this->db->first('products', ['id' => $item['product_id']]);

                if (!$product || $product->stock < $item['quantity']) {
                    throw new Exception("Insufficient stock for product {$item['product_id']}");
                }

                // Add item
                $this->db->insert('order_items', [
                    'order_id' => $orderId,
                    'product_id' => $item['product_id'],
                    'quantity' => $item['quantity'],
                    'price' => $product->price
                ]);

                // Reduce stock
                $this->db->update('products',
                    ['id' => $item['product_id']],
                    ['stock' => Sql::raw('stock - '.(int) $item['quantity'])]
                );

                $total += $product->price * $item['quantity'];
            }

            // Update total
            $this->db->update('orders',
                ['id' => $orderId],
                ['total' => $total]
            );

            $this->db->commit();

            return $orderId;

        } catch (Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }
}

Additional Utilities

Table Management

// Empty table data (TRUNCATE)
$success = $db->emptyTable('cache');

if ($success) {
    echo "Cache table cleared";
}

// Empty table with options
$success = $db->emptyTable('logs', [
    'reset_autoincrement' => true
]);

// Optimize table performance
$success = $db->optimizeTable('users');

if ($success) {
    echo "Table optimized";
}

Structure Checking

// Check if field exists
$hasEmailField = $db->fieldExists('users', 'email');

if (!$hasEmailField) {
    echo "Email field does not exist";
}

// Get real table name (with prefix)
$realTableName = $db->getTableName('users');
echo "Real table name: {$realTableName}";

// Check database connection (uses current connection)
$dbExists = $db->databaseExists('myapp_db');

if ($dbExists) {
    echo "Database connection is available";
}

Counters

// Increment a numeric column
$db->increment('users', ['id' => $userId], 'login_count');

// Decrement a numeric column
$db->decrement('products', ['id' => $productId], 'stock', 2);

Diagnostics

// Last executed query
$lastQuery = $db->lastQuery();

// Last error message
$lastError = $db->lastError();

Raw SQL Usage

// Execute raw SQL
$result = $db->raw("SELECT COUNT(*) as total FROM users WHERE created_at > ?", [
    '2024-01-01'
]);

if ($result) {
    $row = $result->fetch();
    echo "Total users: {$row['total']}";
}

// Complex SQL
$result = $db->raw("
    SELECT
        u.username,
        COUNT(p.id) as post_count,
        MAX(p.created_at) as last_post
    FROM users u
    LEFT JOIN posts p ON u.id = p.author_id
    WHERE u.status = ?
    GROUP BY u.id
    HAVING post_count > ?
    ORDER BY post_count DESC
", ['active', 5]);

if ($result) {
    $users = $result->fetchAll();
    foreach ($users as $user) {
        echo "{$user['username']}: {$user['post_count']} posts\n";
    }
}

All Methods

Static Methods

Instance Methods

Usage Examples

Simple User Management System

use Kotchasan\DB;

class SimpleUserManager
{
    private $db;

    public function __construct()
    {
        $this->db = DB::create();
    }

    public function register($username, $email, $password)
    {
        // Check if email already exists
        if ($this->db->exists('users', ['email' => $email])) {
            throw new Exception('Email already exists');
        }

        // Create new user
        $userId = $this->db->insert('users', [
            'username' => $username,
            'email' => $email,
            'password' => password_hash($password, PASSWORD_DEFAULT),
            'status' => 'active',
            'created_at' => date('Y-m-d H:i:s')
        ]);

        return $userId;
    }

    public function login($email, $password)
    {
        $user = $this->db->first('users', [
            'email' => $email,
            'status' => 'active'
        ]);

        if ($user && password_verify($password, $user->password)) {
            // Update last login time
            $this->db->update('users',
                ['id' => $user->id],
                ['last_login' => date('Y-m-d H:i:s')]
            );

            return $user;
        }

        return null;
    }

    public function getUserStats()
    {
        return [
            'total' => $this->db->count('users'),
            'active' => $this->db->count('users', ['status' => 'active']),
            'inactive' => $this->db->count('users', ['status' => 'inactive'])
        ];
    }
}

Simple Cache System

use Kotchasan\DB;

class SimpleCache
{
    private DB $db;

    public function __construct()
    {
        $this->db = DB::create();

        // Create cache table if not exists
        $this->createCacheTable();
    }

    public function set(string $key, $value, int $ttl = 3600): ?int
    {
        $data = [
            'cache_key' => $key,
            'cache_value' => serialize($value),
            'expires_at' => date('Y-m-d H:i:s', time() + $ttl),
            'created_at' => date('Y-m-d H:i:s')
        ];

        // Delete old data if exists
        $this->db->delete('cache', ['cache_key' => $key], 0);

        // Insert new data
        return $this->db->insert('cache', $data);
    }

    public function get(string $key)
    {
        $item = $this->db->first('cache', [
            ['cache_key', $key],
            ['expires_at', '>', date('Y-m-d H:i:s')]
        ]);

        return $item ? unserialize($item->cache_value) : null;
    }

    public function delete(string $key): bool
    {
        return $this->db->delete('cache', ['cache_key' => $key]) > 0;
    }

    public function clear(): bool
    {
        return $this->db->emptyTable('cache');
    }

    public function cleanup(int $days = 3): int
    {
        // Delete expired data
        $cutoff = date('Y-m-d H:i:s', strtotime("-{$days} days"));
        return $this->db->delete('cache', [['expires_at', '<', $cutoff]], 0);
    }

    private function createCacheTable(): void
    {
        // Check if table exists
        if (!$this->db->fieldExists('cache', 'cache_key')) {
            // Create table with raw SQL
            $this->db->raw("
                CREATE TABLE IF NOT EXISTS `{$this->db->getTableName('cache')}` (
                    `id` int(11) NOT NULL AUTO_INCREMENT,
                    `cache_key` varchar(255) NOT NULL,
                    `cache_value` longtext NOT NULL,
                    `expires_at` datetime NOT NULL,
                    `created_at` datetime NOT NULL,
                    PRIMARY KEY (`id`),
                    UNIQUE KEY `cache_key` (`cache_key`),
                    KEY `expires_at` (`expires_at`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            ");
        }
    }
}

Simple Logging System

use Kotchasan\DB;

class SimpleLogger
{
    private DB $db;

    public function __construct()
    {
        $this->db = DB::create();
    }

    public function log($level, $message, $context = [])
    {
        return $this->db->insert('logs', [
            'level' => $level,
            'message' => $message,
            'context' => json_encode($context),
            'created_at' => date('Y-m-d H:i:s'),
            'ip_address' => $_SERVER['REMOTE_ADDR'] ?? null,
            'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? null
        ]);
    }

    public function info($message, $context = [])
    {
        return $this->log('info', $message, $context);
    }

    public function warning($message, $context = [])
    {
        return $this->log('warning', $message, $context);
    }

    public function error($message, $context = [])
    {
        return $this->log('error', $message, $context);
    }

    public function getLogs(?string $level = null, int $limit = 10): array
    {
        $where = [];
        if ($level !== null) {
            $where = ['level' => $level];
        }

        return $this->db->select('logs', $where, [
            'orderBy' => ['created_at' => 'DESC'],
            'limit' => $limit
        ]);
    }

    public function cleanupOld(int $days = 3): int
    {
        $cutoff = date('Y-m-d H:i:s', strtotime("-{$days} days"));

        return $this->db->delete('logs', [['created_at', '<', $cutoff]], 0);
    }
}

Important Notes

1. Simplicity: The DB class is designed for ease of use
2. Performance: For complex queries, use the Database class directly
3. Error Handling: Most methods return null or false on error
4. Transactions: Always use try-catch when working with transactions
5. Security: Use parameter binding and avoid raw SQL when possible

Related Classes

• Database - Main class for database management
• QueryBuilder - Advanced SQL Query builder
• Connection - Database connection management