Database - ForkBB

The DB class is ForkBB’s database abstraction layer, extending PDO with typed placeholders, automatic query tracking, and simplified query building.

Overview

The Database class provides:

Typed Placeholders - Enhanced parameter binding (?i, ?s, ?ai, etc.)
Query Tracking - Automatic performance monitoring
Table Prefixes - Automatic table name substitution (::table)
Transaction Support - Built-in transaction management
Driver Support - MySQL, PostgreSQL, SQLite

Access via Container: $this->c->DB (shared singleton instance)

Constructor

public function __construct(
    string $dsn,
    ?string $username = null,
    ?string $password = null,
    array $options = [],
    string $prefix = ''
)

dsn

string

required

PDO Data Source Name (e.g., mysql:host=localhost;dbname=forkbb)

username

string

Database username

password

string

Database password (marked sensitive)

options

array

default:"[]"

PDO options plus custom options:

initSQLCommands - Commands to run on connect
initFunction - Callback to run after connect

prefix

string

default:"''"

Table prefix (e.g., fork_)

Example:

$db = new DB(
    'mysql:host=localhost;dbname=forkbb;charset=utf8mb4',
    'username',
    'password',
    [
        'initSQLCommands' => [
            'SET NAMES utf8mb4',
            'SET time_zone = \'+00:00\''
        ],
        PDO::ATTR_TIMEOUT => 5
    ],
    'fork_'
);

Query Methods

query

public function query(string $query, mixed ...$args): DBStatement|false

Executes a query and returns a statement object.

query

string

required

SQL query with typed placeholders

args

mixed

Parameters array as first argument, or PDO fetch mode arguments

return

DBStatement|false

Statement object or false on failure

Simple Query:

$stmt = $this->c->DB->query('SELECT * FROM ::users WHERE id = ?i', [42]);
$user = $stmt->fetch();

Multiple Parameters:

$stmt = $this->c->DB->query(
    'SELECT * FROM ::posts WHERE forum_id = ?i AND topic_id = ?i LIMIT ?i',
    [5, 123, 10]
);

Named Parameters:

$stmt = $this->c->DB->query(
    'SELECT * FROM ::users WHERE username = :username AND email = :email',
    [':username' => 'john', ':email' => 'john@example.com']
);

With Fetch Mode:

$stmt = $this->c->DB->query(
    'SELECT * FROM ::users',
    [],
    PDO::FETCH_CLASS,
    User::class
);

exec

public function exec(string $query, array $params = []): int|false

Executes a query and returns affected row count.

query

string

required

SQL query (INSERT, UPDATE, DELETE)

params

array

default:"[]"

Query parameters

return

int|false

Number of affected rows or false on failure

Insert:

$affected = $this->c->DB->exec(
    'INSERT INTO ::users (username, email, registered) VALUES (?s, ?s, ?i)',
    ['john', 'john@example.com', time()]
);

$userId = $this->c->DB->lastInsertId();

Update:

$affected = $this->c->DB->exec(
    'UPDATE ::users SET last_visit = ?i WHERE id = ?i',
    [time(), 42]
);

Delete:

$affected = $this->c->DB->exec(
    'DELETE FROM ::posts WHERE topic_id = ?i',
    [123]
);

prepare

public function prepare(string $query, array $params = [], array $options = []): DBStatement|false

Prepares a statement for execution.

query

string

required

SQL query with placeholders

params

array

default:"[]"

Parameters for placeholder parsing

options

array

default:"[]"

PDO driver options

return

DBStatement|false

Prepared statement or false on failure

Example:

$stmt = $this->c->DB->prepare(
    'SELECT * FROM ::users WHERE group_id = ?i AND registered > ?i'
);

// Execute multiple times with different parameters
foreach ($groups as $groupId) {
    $stmt->execute([$groupId, $timestamp]);
    while ($user = $stmt->fetch()) {
        // Process user
    }
}

Placeholder Types

The DB class extends PDO with typed placeholders:

Scalar Placeholders

placeholder

String value

'WHERE username = ?s'

placeholder

Integer value

'WHERE id = ?i'

placeholder

Float value

'WHERE price = ?f'

placeholder

Boolean value

'WHERE is_active = ?b'

placeholder

Plain value (not escaped, use with caution)

'ORDER BY ?p'  // For dynamic column names

Only use ?p with trusted, validated input!

Array Placeholders

?ai

placeholder

Array of integers

'WHERE id IN (?ai)'
$db->query($sql, [[1, 2, 3]])
// Expands to: WHERE id IN (1, 2, 3)

?as

placeholder

Array of strings

'WHERE username IN (?as)'
$db->query($sql, [['john', 'jane', 'bob']])
// Expands to: WHERE username IN ('john', 'jane', 'bob')

placeholder

Generic array (auto-detect type)

'WHERE id IN (?a)'

Named Placeholders

// Named parameters use same type suffixes
'WHERE id = :id|i AND username = :name|s'

$db->query($sql, [
    ':id' => 42,
    ':name' => 'john'
]);

Table Prefix Placeholder

::table

placeholder

Automatic table prefix substitution

'SELECT * FROM ::users'  // Becomes: fork_users
'JOIN ::posts ON ::users.id = ::posts.user_id'

Transaction Methods

beginTransaction

public function beginTransaction(): bool

Starts a new transaction. Example:

$this->c->DB->beginTransaction();

try {
    $this->c->DB->exec('INSERT INTO ::topics ...', [...]);
    $topicId = $this->c->DB->lastInsertId();
    
    $this->c->DB->exec('INSERT INTO ::posts ...', [...]);
    
    $this->c->DB->commit();
} catch (Exception $e) {
    $this->c->DB->rollback();
    throw $e;
}

commit

public function commit(): bool

Commits the active transaction.

rollback

public function rollback(): bool

Rolls back the active transaction.

Query Tracking

getCount

public function getCount(): int

Returns the number of executed queries. Example:

$count = $this->c->DB->getCount();
echo "Executed {$count} queries";

getQueries

public function getQueries(): array

Returns array of executed queries with timing.

return

array

Array of [query, execution_time] pairs

Example:

$queries = $this->c->DB->getQueries();
foreach ($queries as [$sql, $time]) {
    echo "Query: {$sql} ({$time}s)\n";
}

getLifeTime

public function getLifeTime(): float

Returns approximate lifetime of the DB connection in seconds. Example:

$lifetime = $this->c->DB->getLifeTime();
echo "DB connection active for {$lifetime}s";

Utility Methods

getType

public function getType(): string

Returns database type (‘mysql’, ‘pgsql’, ‘sqlite’). Example:

$type = $this->c->DB->getType();
if ($type === 'mysql') {
    // MySQL-specific code
}

getValue

public function getValue(int|string $key, array $params): mixed

Retrieves a parameter value by key or throws exception.

lastInsertId

public function lastInsertId(string $name = null): string

Returns the last inserted ID. Example:

$this->c->DB->exec('INSERT INTO ::users ...', [...]);
$userId = (int) $this->c->DB->lastInsertId();

disconnect

public function disconnect(): void

Closes the database connection.

Real-World Examples

User CRUD Operations

// Create
$this->c->DB->exec(
    'INSERT INTO ::users (username, email, registered, group_id) VALUES (?s, ?s, ?i, ?i)',
    [$username, $email, time(), 4]
);
$userId = (int) $this->c->DB->lastInsertId();

// Read
$user = $this->c->DB->query(
    'SELECT * FROM ::users WHERE id = ?i',
    [$userId]
)->fetch();

// Update
$this->c->DB->exec(
    'UPDATE ::users SET last_visit = ?i, num_posts = num_posts + 1 WHERE id = ?i',
    [time(), $userId]
);

// Delete
$this->c->DB->exec(
    'DELETE FROM ::users WHERE id = ?i',
    [$userId]
);

Complex Queries

// JOIN with multiple conditions
$posts = $this->c->DB->query(
    'SELECT p.*, t.subject, u.username
     FROM ::posts AS p
     INNER JOIN ::topics AS t ON p.topic_id = t.id
     INNER JOIN ::users AS u ON p.poster_id = u.id
     WHERE t.forum_id = ?i AND p.posted > ?i
     ORDER BY p.posted DESC
     LIMIT ?i',
    [$forumId, $timestamp, 50]
)->fetchAll();

// Aggregate with GROUP BY
$stats = $this->c->DB->query(
    'SELECT forum_id, COUNT(*) as topic_count, SUM(num_posts) as post_count
     FROM ::topics
     WHERE closed = ?i
     GROUP BY forum_id
     HAVING topic_count > ?i',
    [0, 10]
)->fetchAll();

Array Placeholders

// IN clause with array
$users = $this->c->DB->query(
    'SELECT * FROM ::users WHERE id IN (?ai)',
    [[1, 5, 10, 42]]
)->fetchAll();

// Multiple arrays
$results = $this->c->DB->query(
    'SELECT * FROM ::posts WHERE topic_id IN (?ai) AND poster_id NOT IN (?ai)',
    [[10, 20, 30], [5, 7]]
)->fetchAll();

Batch Operations

// Insert multiple records
$stmt = $this->c->DB->prepare(
    'INSERT INTO ::search_words (word, topic_ids) VALUES (?s, ?s)'
);

foreach ($words as $word => $topicIds) {
    $stmt->execute([$word, implode(',', $topicIds)]);
}

Transaction Example

$this->c->DB->beginTransaction();

try {
    // Create topic
    $this->c->DB->exec(
        'INSERT INTO ::topics (subject, forum_id, posted, poster_id) VALUES (?s, ?i, ?i, ?i)',
        [$subject, $forumId, $now, $userId]
    );
    $topicId = (int) $this->c->DB->lastInsertId();
    
    // Create first post
    $this->c->DB->exec(
        'INSERT INTO ::posts (topic_id, poster_id, posted, message) VALUES (?i, ?i, ?i, ?s)',
        [$topicId, $userId, $now, $message]
    );
    $postId = (int) $this->c->DB->lastInsertId();
    
    // Update topic with first post ID
    $this->c->DB->exec(
        'UPDATE ::topics SET first_post_id = ?i, last_post_id = ?i WHERE id = ?i',
        [$postId, $postId, $topicId]
    );
    
    // Update forum stats
    $this->c->DB->exec(
        'UPDATE ::forums SET num_topics = num_topics + 1, num_posts = num_posts + 1 WHERE id = ?i',
        [$forumId]
    );
    
    $this->c->DB->commit();
} catch (Exception $e) {
    $this->c->DB->rollback();
    throw new RuntimeException('Failed to create topic', 0, $e);
}

Search Implementation

$keywords = ['php', 'database', 'pdo'];

// Dynamic query building
$conditions = [];
$params = [];

foreach ($keywords as $keyword) {
    $conditions[] = 'message LIKE ?s';
    $params[] = "%{$keyword}%";
}

$sql = 'SELECT * FROM ::posts WHERE ' . implode(' OR ', $conditions) . ' LIMIT ?i';
$params[] = 50;

$results = $this->c->DB->query($sql, $params)->fetchAll();

Performance Tips

Use Prepared Statements

$stmt = $this->c->DB->prepare('SELECT * FROM ::users WHERE group_id = ?i');
foreach ($groups as $groupId) {
    $stmt->execute([$groupId]);
}

Reuse prepared statements for repeated queries.

Monitor Query Count

$count = $this->c->DB->getCount();
if ($count > 50) {
    // Log warning: too many queries
}

Track queries to identify N+1 problems.

Use Array Placeholders

// Good: Single query
WHERE id IN (?ai)

// Bad: Multiple queries
foreach ($ids as $id) { WHERE id = ?i }

Batch operations when possible.

Wrap Multi-Step Operations

$this->c->DB->beginTransaction();
// Multiple related queries
$this->c->DB->commit();

Use transactions for data consistency.

Error Handling

The DB class throws PDOException on errors:

try {
    $this->c->DB->exec('INVALID SQL', []);
} catch (PDOException $e) {
    error_log("Database error: " . $e->getMessage());
    throw new RuntimeException('Database operation failed', 0, $e);
}

Security Notes

Never concatenate user input into SQL queries!

// DANGEROUS - SQL Injection vulnerability
$sql = "SELECT * FROM users WHERE username = '{$_POST['username']}'";

// SAFE - Use placeholders
$sql = 'SELECT * FROM ::users WHERE username = ?s';
$this->c->DB->query($sql, [$_POST['username']]);

Use ?p placeholder only with validated input:

// Safe: Whitelist validation
$allowedColumns = ['username', 'email', 'registered'];
$orderBy = in_array($_GET['sort'], $allowedColumns) ? $_GET['sort'] : 'registered';
$this->c->DB->query('SELECT * FROM ::users ORDER BY ?p', [$orderBy]);

Best Practices

Use typed placeholders - Specify types (?i, ?s) for clarity and safety
Leverage table prefixes - Always use ::table notation
Monitor performance - Check query count and timing in development
Use transactions - Wrap related operations for consistency
Prepare repeated queries - Prepare once, execute multiple times
Handle errors - Catch PDOException and provide context

​Overview

​Constructor

​Query Methods

​query

​exec

​prepare

​Placeholder Types

​Scalar Placeholders

​Array Placeholders

​Named Placeholders

​Table Prefix Placeholder

​Transaction Methods

​beginTransaction

​commit

​rollback

​Query Tracking

​getCount

​getQueries

​getLifeTime

​Utility Methods

​getType

​getValue

​lastInsertId

​disconnect

​Real-World Examples

​User CRUD Operations

​Complex Queries

​Array Placeholders

​Batch Operations

​Transaction Example

​Search Implementation

​Performance Tips

Use Prepared Statements

Monitor Query Count

Use Array Placeholders

Wrap Multi-Step Operations

​Error Handling

​Security Notes

​Best Practices

​See Also

Overview

Constructor

Query Methods

query

exec

prepare

Placeholder Types

Scalar Placeholders

Array Placeholders

Named Placeholders

Table Prefix Placeholder

Transaction Methods

beginTransaction

commit

rollback

Query Tracking

getCount

getQueries

getLifeTime

Utility Methods

getType

getValue

lastInsertId

disconnect

Real-World Examples

User CRUD Operations

Complex Queries

Array Placeholders

Batch Operations

Transaction Example

Search Implementation

Performance Tips

Error Handling

Security Notes

Best Practices

See Also