XoopsDatabase Class¶

The XoopsDatabase class provides a database abstraction layer for XOOPS, handling connection management, query execution, result processing, and error handling. It supports multiple database drivers through a driver architecture.

Class Overview¶

namespace Xoops\Database;

abstract class XoopsDatabase
{
    protected $conn;
    protected $prefix;
    protected $logger;

    abstract public function connect(bool $selectdb = true): bool;
    abstract public function query(string $sql, int $limit = 0, int $start = 0);
    abstract public function fetchArray($result): ?array;
    abstract public function fetchObject($result): ?object;
    abstract public function getRowsNum($result): int;
    abstract public function getAffectedRows(): int;
    abstract public function getInsertId(): int;
    abstract public function escape(string $string): string;
}

Class Hierarchy¶

XoopsDatabase (Abstract Base)
├── XoopsMySQLDatabase (MySQL Extension)
│   └── XoopsMySQLDatabaseProxy (Security Proxy)
└── XoopsMySQLiDatabase (MySQLi Extension)
    └── XoopsMySQLiDatabaseProxy (Security Proxy)

XoopsDatabaseFactory
└── Creates appropriate driver instances

Getting a Database Instance¶

Using the Factory¶

// Recommended: Use the factory
$db = XoopsDatabaseFactory::getDatabaseConnection();

Using getInstance¶

// Alternative: Direct singleton access
$db = XoopsDatabase::getInstance();

Global Variable¶

// Legacy: Use global variable
global $xoopsDB;

Core Methods¶

connect¶

Establishes a database connection.

abstract public function connect(bool $selectdb = true): bool

Parameters:

Parameter	Type	Description
`$selectdb`	bool	Whether to select the database

Returns: bool - True on successful connection

Example:

$db = XoopsDatabaseFactory::getDatabaseConnection();
if ($db->connect()) {
    echo "Connected successfully";
}

query¶

Executes an SQL query.

abstract public function query(
    string $sql,
    int $limit = 0,
    int $start = 0
): mixed

Parameters:

Parameter	Type	Description
`$sql`	string	SQL query string
`$limit`	int	Maximum rows to return (0 = no limit)
`$start`	int	Starting offset

Returns: resource|bool - Result resource or false on failure

Example:

$db = XoopsDatabaseFactory::getDatabaseConnection();

// Simple query
$sql = "SELECT * FROM " . $db->prefix('users') . " WHERE uid > 0";
$result = $db->query($sql);

// Query with limit
$sql = "SELECT * FROM " . $db->prefix('users');
$result = $db->query($sql, 10, 0); // First 10 rows

// Query with offset
$result = $db->query($sql, 10, 20); // 10 rows starting at row 20

queryF¶

Executes a query forcing the operation (bypasses security checks).

public function queryF(string $sql, int $limit = 0, int $start = 0): mixed

Use Cases: - INSERT, UPDATE, DELETE operations - When you need to bypass read-only restrictions

Example:

$sql = sprintf(
    "UPDATE %s SET views = views + 1 WHERE article_id = %d",
    $db->prefix('articles'),
    $articleId
);
$db->queryF($sql);

prefix¶

Prepends the database table prefix.

public function prefix(string $table = ''): string

Parameters:

Parameter	Type	Description
`$table`	string	Table name without prefix

Returns: string - Table name with prefix

Example:

$db = XoopsDatabaseFactory::getDatabaseConnection();

echo $db->prefix('users');       // "xoops_users" (if prefix is "xoops_")
echo $db->prefix('modules');     // "xoops_modules"
echo $db->prefix();              // "xoops_" (just the prefix)

fetchArray¶

Fetches a result row as an associative array.

abstract public function fetchArray($result): ?array

Parameters:

Parameter	Type	Description
`$result`	resource	Query result resource

Returns: array|null - Associative array or null if no more rows

Example:

$sql = "SELECT * FROM " . $db->prefix('users') . " WHERE level > 0";
$result = $db->query($sql);

while ($row = $db->fetchArray($result)) {
    echo "User: " . $row['uname'] . "\n";
    echo "Email: " . $row['email'] . "\n";
}

fetchObject¶

Fetches a result row as an object.

abstract public function fetchObject($result): ?object

Parameters:

Parameter	Type	Description
`$result`	resource	Query result resource

Returns: object|null - Object with properties for each column

Example:

$sql = "SELECT * FROM " . $db->prefix('users') . " WHERE uid = 1";
$result = $db->query($sql);

if ($user = $db->fetchObject($result)) {
    echo "Username: " . $user->uname;
    echo "Email: " . $user->email;
}

fetchRow¶

Fetches a result row as a numeric array.

abstract public function fetchRow($result): ?array

Example:

$sql = "SELECT uname, email FROM " . $db->prefix('users');
$result = $db->query($sql);

while ($row = $db->fetchRow($result)) {
    echo "Username: " . $row[0] . ", Email: " . $row[1];
}

fetchBoth¶

Fetches a result row as both associative and numeric array.

abstract public function fetchBoth($result): ?array

Example:

$result = $db->query($sql);
$row = $db->fetchBoth($result);
echo $row['uname'];  // By name
echo $row[0];        // By index

getRowsNum¶

Gets the number of rows in a result set.

abstract public function getRowsNum($result): int

Parameters:

Parameter	Type	Description
`$result`	resource	Query result resource

Returns: int - Number of rows

Example:

$sql = "SELECT * FROM " . $db->prefix('users') . " WHERE level > 0";
$result = $db->query($sql);
$count = $db->getRowsNum($result);
echo "Found $count active users";

getAffectedRows¶

Gets the number of affected rows from last query.

abstract public function getAffectedRows(): int

Returns: int - Number of affected rows

Example:

$sql = "UPDATE " . $db->prefix('users') . " SET last_login = " . time() . " WHERE uid = 1";
$db->queryF($sql);
$affected = $db->getAffectedRows();
echo "Updated $affected rows";

getInsertId¶

Gets the auto-generated ID from the last INSERT.

abstract public function getInsertId(): int

Returns: int - Last insert ID

Example:

$sql = sprintf(
    "INSERT INTO %s (title, content) VALUES (%s, %s)",
    $db->prefix('articles'),
    $db->quoteString($title),
    $db->quoteString($content)
);
$db->queryF($sql);
$newId = $db->getInsertId();
echo "Created article with ID: $newId";

escape¶

Escapes a string for safe use in SQL queries.

abstract public function escape(string $string): string

Parameters:

Parameter	Type	Description
`$string`	string	String to escape

Returns: string - Escaped string (without quotes)

Example:

$unsafeInput = "O'Reilly";
$safe = $db->escape($unsafeInput);  // "O\'Reilly"

$sql = "SELECT * FROM " . $db->prefix('users') . " WHERE uname = '" . $safe . "'";

quoteString¶

Escapes and quotes a string for SQL.

public function quoteString(string $string): string

Parameters:

Parameter	Type	Description
`$string`	string	String to quote

Returns: string - Escaped and quoted string

Example:

$name = "John O'Connor";
$quoted = $db->quoteString($name);  // "'John O\'Connor'"

$sql = "INSERT INTO users (name) VALUES (" . $quoted . ")";

freeRecordSet¶

Frees memory associated with a result.

abstract public function freeRecordSet($result): void

Example:

$result = $db->query($sql);
// Process results...
$db->freeRecordSet($result);  // Free memory

Error Handling¶

error¶

Gets the last error message.

abstract public function error(): string

Example:

$result = $db->query($sql);
if (!$result) {
    echo "Database error: " . $db->error();
}

errno¶

Gets the last error number.

abstract public function errno(): int

Example:

$result = $db->query($sql);
if (!$result) {
    echo "Error #" . $db->errno() . ": " . $db->error();
}

Prepared Statements (MySQLi)¶

The MySQLi driver supports prepared statements for enhanced security.

prepare¶

Creates a prepared statement.

public function prepare(string $sql): mysqli_stmt|false

Example:

$db = XoopsDatabaseFactory::getDatabaseConnection();

$sql = "SELECT * FROM " . $db->prefix('users') . " WHERE uid = ?";
$stmt = $db->prepare($sql);

$stmt->bind_param('i', $userId);
$userId = 5;
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['uname'];
}
$stmt->close();

Prepared Statement with Multiple Parameters¶

$sql = "INSERT INTO " . $db->prefix('articles') . " (title, content, author_id) VALUES (?, ?, ?)";
$stmt = $db->prepare($sql);

$stmt->bind_param('ssi', $title, $content, $authorId);

$title = "My Article";
$content = "Article content here";
$authorId = 1;

if ($stmt->execute()) {
    echo "Article created with ID: " . $stmt->insert_id;
}

$stmt->close();

Transaction Support¶

beginTransaction¶

Starts a transaction.

public function beginTransaction(): bool

commit¶

Commits the current transaction.

public function commit(): bool

rollback¶

Rolls back the current transaction.

public function rollback(): bool

Example:

$db = XoopsDatabaseFactory::getDatabaseConnection();

try {
    $db->beginTransaction();

    // Multiple operations
    $sql1 = "UPDATE " . $db->prefix('accounts') . " SET balance = balance - 100 WHERE id = 1";
    $db->queryF($sql1);

    $sql2 = "UPDATE " . $db->prefix('accounts') . " SET balance = balance + 100 WHERE id = 2";
    $db->queryF($sql2);

    // Check for errors
    if ($db->errno()) {
        throw new Exception($db->error());
    }

    $db->commit();
    echo "Transaction completed";

} catch (Exception $e) {
    $db->rollback();
    echo "Transaction failed: " . $e->getMessage();
}

Complete Usage Examples¶

Basic CRUD Operations¶

$db = XoopsDatabaseFactory::getDatabaseConnection();

// CREATE
$sql = sprintf(
    "INSERT INTO %s (title, content, created) VALUES (%s, %s, %d)",
    $db->prefix('articles'),
    $db->quoteString('New Article'),
    $db->quoteString('Article content'),
    time()
);
$db->queryF($sql);
$articleId = $db->getInsertId();

// READ
$sql = "SELECT * FROM " . $db->prefix('articles') . " WHERE id = " . (int)$articleId;
$result = $db->query($sql);
$article = $db->fetchArray($result);

// UPDATE
$sql = sprintf(
    "UPDATE %s SET title = %s, updated = %d WHERE id = %d",
    $db->prefix('articles'),
    $db->quoteString('Updated Title'),
    time(),
    $articleId
);
$db->queryF($sql);

// DELETE
$sql = "DELETE FROM " . $db->prefix('articles') . " WHERE id = " . (int)$articleId;
$db->queryF($sql);

Pagination Query¶

function getArticles(int $page = 1, int $perPage = 10): array
{
    $db = XoopsDatabaseFactory::getDatabaseConnection();
    $start = ($page - 1) * $perPage;

    // Get total count
    $sql = "SELECT COUNT(*) as total FROM " . $db->prefix('articles') . " WHERE published = 1";
    $result = $db->query($sql);
    $row = $db->fetchArray($result);
    $total = $row['total'];

    // Get page of results
    $sql = "SELECT * FROM " . $db->prefix('articles') .
           " WHERE published = 1 ORDER BY created DESC";
    $result = $db->query($sql, $perPage, $start);

    $articles = [];
    while ($row = $db->fetchArray($result)) {
        $articles[] = $row;
    }

    return [
        'articles' => $articles,
        'total' => $total,
        'pages' => ceil($total / $perPage),
        'current' => $page
    ];
}

Search Query with LIKE¶

function searchArticles(string $keyword): array
{
    $db = XoopsDatabaseFactory::getDatabaseConnection();

    $keyword = $db->escape($keyword);
    $sql = "SELECT * FROM " . $db->prefix('articles') .
           " WHERE title LIKE '%" . $keyword . "%'" .
           " OR content LIKE '%" . $keyword . "%'" .
           " ORDER BY created DESC";

    $result = $db->query($sql, 50);  // Limit to 50 results

    $articles = [];
    while ($row = $db->fetchArray($result)) {
        $articles[] = $row;
    }

    return $articles;
}

Join Query¶

function getArticlesWithAuthors(): array
{
    $db = XoopsDatabaseFactory::getDatabaseConnection();

    $sql = "SELECT a.*, u.uname as author_name, u.email as author_email
            FROM " . $db->prefix('articles') . " a
            LEFT JOIN " . $db->prefix('users') . " u ON a.author_id = u.uid
            WHERE a.published = 1
            ORDER BY a.created DESC";

    $result = $db->query($sql, 20);

    $articles = [];
    while ($row = $db->fetchArray($result)) {
        $articles[] = $row;
    }

    return $articles;
}

SqlUtility Class¶

Helper class for SQL file operations.

splitMySqlFile¶

Splits a SQL file into individual queries.

public static function splitMySqlFile(string $content): array

Example:

$sqlContent = file_get_contents('install.sql');
$queries = SqlUtility::splitMySqlFile($sqlContent);

foreach ($queries as $query) {
    $db->queryF($query);
    if ($db->errno()) {
        echo "Error executing: " . $query . "\n";
        echo "Error: " . $db->error() . "\n";
    }
}

prefixQuery¶

Replaces table placeholders with prefixed table names.

public static function prefixQuery(string $sql, string $prefix): string

Example:

$sql = "CREATE TABLE {PREFIX}_articles (id INT PRIMARY KEY)";
$prefixedSql = SqlUtility::prefixQuery($sql, $db->prefix());
// "CREATE TABLE xoops_articles (id INT PRIMARY KEY)"

Best Practices¶

Security¶

Always escape user input:
```
$safe = $db->escape($_POST['input']);
```

Use prepared statements when available:

$stmt = $db->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param('i', $id);

Use quoteString for values:

$sql = "INSERT INTO table (name) VALUES (" . $db->quoteString($name) . ")";

Performance¶

Always use LIMIT for large tables:

$result = $db->query($sql, 100);  // Limit results

Free result sets when done:
```
$db->freeRecordSet($result);
```
Use appropriate indexes in your table definitions
Prefer handlers over raw SQL when possible

Error Handling¶

Always check for errors:

$result = $db->query($sql);
if (!$result) {
    trigger_error($db->error(), E_USER_WARNING);
}

Use transactions for multiple related operations:

$db->beginTransaction();
// ... operations ...
$db->commit();  // or $db->rollback();

Criteria - Query criteria system
QueryBuilder - Fluent query building
XoopsObjectHandler - Object persistence

See also: XOOPS Source Code

XoopsDatabase Class¶

Class Overview¶

Class Hierarchy¶

Getting a Database Instance¶

Using the Factory¶

Using getInstance¶

Global Variable¶

Core Methods¶

connect¶

query¶

queryF¶

prefix¶

fetchArray¶

fetchObject¶

fetchRow¶

fetchBoth¶

getRowsNum¶

getAffectedRows¶

getInsertId¶

escape¶

quoteString¶

freeRecordSet¶

Error Handling¶

error¶

errno¶

Prepared Statements (MySQLi)¶

prepare¶

Prepared Statement with Multiple Parameters¶

Transaction Support¶

beginTransaction¶

commit¶

rollback¶

Complete Usage Examples¶

Basic CRUD Operations¶

Pagination Query¶

Search Query with LIKE¶

Join Query¶

SqlUtility Class¶

splitMySqlFile¶

prefixQuery¶

Best Practices¶

Security¶

Performance¶

Error Handling¶

Related Documentation¶