DB (Database)

The DB class provides a simple abstraction layer for performing common database operations like SELECT, INSERT, UPDATE, and DELETE. It uses PDO for database access.

Constructor

__construct

__construct(PDO $pdo)

Creates a new DB instance with the provided PDO connection.

Parameters

Name	Type	Required	Description
`$pdo`	`PDO`	Yes	A configured PDO database connection instance

Example

$config = [
    'db' => [
        'dsn' => 'mysql:host=localhost;dbname=schoolkit;charset=utf8mb4',
        'user' => 'your_username',
        'pass' => 'your_password'
    ],
    'storage_path' => __DIR__ . '/storage'
];

$schoolKit = SchoolKit::start($config);
$db = $schoolKit->db();

Methods

get

get(string $table, array $where): ?array

Fetches a single record from a table.

Parameters

Name	Type	Required	Description
`$table`	`string`	Yes	The name of the table
`$where`	`array`	Yes	An associative array of WHERE conditions

Returns

An associative array representing the record, or null if not found.

Example

$config = [
    'db' => [
        'dsn' => 'mysql:host=localhost;dbname=schoolkit;charset=utf8mb4',
        'user' => 'your_username',
        'pass' => 'your_password'
    ],
    'storage_path' => __DIR__ . '/storage'
];

$schoolKit = SchoolKit::start($config);
$db = $schoolKit->db();
$user = $db->get('users', ['id' => 1]);

select

select(string $table, array $where = [], array $opts = []): array

Fetches multiple records from a table.

Parameters

Name	Type	Required	Description
`$table`	`string`	Yes	The name of the table
`$where`	`array`	No	An associative array of WHERE conditions
`$opts`	`array`	No	An array of options (e.g., order, limit, offset, columns)

Returns

An array of associative arrays.

Example

$config = [
    'db' => [
        'dsn' => 'mysql:host=localhost;dbname=schoolkit;charset=utf8mb4',
        'user' => 'your_username',
        'pass' => 'your_password'
    ],
    'storage_path' => __DIR__ . '/storage'
];

$schoolKit = SchoolKit::start($config);
$db = $schoolKit->db();
$active_users = $db->select('users', ['status' => 'active'], ['order' => ['created_at' => 'DESC']]);

insert

insert(string $table, array $data): int

Inserts a new record into a table.

Parameters

Name	Type	Required	Description
`$table`	`string`	Yes	The name of the table
`$data`	`array`	Yes	An associative array of data to insert

Returns

The ID of the newly inserted record.

Example

$config = [
    'db' => [
        'dsn' => 'mysql:host=localhost;dbname=schoolkit;charset=utf8mb4',
        'user' => 'your_username',
        'pass' => 'your_password'
    ],
    'storage_path' => __DIR__ . '/storage'
];

$schoolKit = SchoolKit::start($config);
$db = $schoolKit->db();
$userId = $db->insert('users', ['email' => 'test@example.com', 'name' => 'Test User']);

update

update(string $table, array $data, array $where): bool

Updates existing records in a table.

Parameters

Name	Type	Required	Description
`$table`	`string`	Yes	The name of the table
`$data`	`array`	Yes	An associative array of data to update
`$where`	`array`	Yes	An associative array of WHERE conditions

Returns

true if any rows were affected, false otherwise.

Example

$config = [
    'db' => [
        'dsn' => 'mysql:host=localhost;dbname=schoolkit;charset=utf8mb4',
        'user' => 'your_username',
        'pass' => 'your_password'
    ],
    'storage_path' => __DIR__ . '/storage'
];

$schoolKit = SchoolKit::start($config);
$db = $schoolKit->db();
$success = $db->update('users', ['status' => 'inactive'], ['id' => 1]);

delete

delete(string $table, array $where): bool

Deletes records from a table.

Parameters

Name	Type	Required	Description
`$table`	`string`	Yes	The name of the table
`$where`	`array`	Yes	An associative array of WHERE conditions

Returns

true if any rows were affected, false otherwise.

Example

$config = [
    'db' => [
        'dsn' => 'mysql:host=localhost;dbname=schoolkit;charset=utf8mb4',
        'user' => 'your_username',
        'pass' => 'your_password'
    ],
    'storage_path' => __DIR__ . '/storage'
];

$schoolKit = SchoolKit::start($config);
$db = $schoolKit->db();
$success = $db->delete('users', ['id' => 1]);

query

query(string $sql, array $params = []): PDOStatement

Executes a raw SQL query. Warning: Only use for DDL or complex queries. Do not pass user input directly to this method.

Parameters

Name	Type	Required	Description
`$sql`	`string`	Yes	The SQL query to execute
`$params`	`array`	No	An array of parameters to bind to the query

Returns

A PDOStatement object.

Example

$config = [
    'db' => [
        'dsn' => 'mysql:host=localhost;dbname=schoolkit;charset=utf8mb4',
        'user' => 'your_username',
        'pass' => 'your_password'
    ],
    'storage_path' => __DIR__ . '/storage'
];

$schoolKit = SchoolKit::start($config);
$db = $schoolKit->db();
$stmt = $db->query('SELECT name FROM users WHERE id = ?', [1]);
$name = $stmt->fetchColumn();

transaction

transaction(callable $fn): mixed

Executes a series of operations within a database transaction.

Parameters

Name	Type	Required	Description
`$fn`	`callable`	Yes	A function to execute within the transaction. The DB instance is passed as an argument

Returns

The return value of the provided function.

Example

$config = [
    'db' => [
        'dsn' => 'mysql:host=localhost;dbname=schoolkit;charset=utf8mb4',
        'user' => 'your_username',
        'pass' => 'your_password'
    ],
    'storage_path' => __DIR__ . '/storage'
];

$schoolKit = SchoolKit::start($config);
$db = $schoolKit->db();
$db->transaction(function($db) {
    $db->insert('users', ['name' => 'New User']);
    $db->update('settings', ['last_user_added' => time()], ['id' => 1]);
});

createTable

createTable(string $name, array $columns, array $indexes = [], array $options = []): void

Creates a new table in the database.

Parameters

Name	Type	Required	Description
`$name`	`string`	Yes	The name of the table
`$columns`	`array`	Yes	An associative array defining the table columns
`$indexes`	`array`	No	An array of indexes to create
`$options`	`array`	No	An array of table options (e.g., engine, charset)

Example

$config = [
    'db' => [
        'dsn' => 'mysql:host=localhost;dbname=schoolkit;charset=utf8mb4',
        'user' => 'your_username',
        'pass' => 'your_password'
    ],
    'storage_path' => __DIR__ . '/storage'
];

$schoolKit = SchoolKit::start($config);
$db = $schoolKit->db();
$db->createTable('posts', [
    'id' => ['type' => 'pk'],
    'title' => ['type' => 'varchar', 'length' => 255],
    'content' => ['type' => 'text', 'null' => true],
    'created_at' => ['type' => 'datetime', 'default' => 'CURRENT_TIMESTAMP']
]);

validateColumnName

validateColumnName(string $name): void

Validates a column name to prevent SQL injection.

Parameters

Name	Type	Required	Description
`$name`	`string`	Yes	The column name to validate

Throws

InvalidArgumentException on failure.

Example

$config = [
    'db' => [
        'dsn' => 'mysql:host=localhost;dbname=schoolkit;charset=utf8mb4',
        'user' => 'your_username',
        'pass' => 'your_password'
    ],
    'storage_path' => __DIR__ . '/storage'
];

$schoolKit = SchoolKit::start($config);
$db = $schoolKit->db();
try {
    $db->validateColumnName('valid_column'); // OK
    $db->validateColumnName('invalid-column'); // Throws InvalidArgumentException
} catch (InvalidArgumentException $e) {
    // Handle error
}