PHP Luminova: MySQLi and PDO Database Connection Driver

Luminova built-in PDO and MySQli database drivers are the core interface for managing and executing database operations.

These drivers implement a shared interface and behavior for:

• running queries
• preparing statements
• fetching results

Each driver hides low-level database operations behind a consistent API.

For practical usage, see: Database Examples

Opening a Database Connection

To establish a database connection, use Luminova\Database\Connection.This approach allows you to switch database drivers without changing application code.

The active driver is controlled through the database.connection environment value (for example: PDO or MYSQLI).

If you need direct control over a specific driver, you can manually initialize the connection using the driver class instead.

Connection Class

Use the Database Connection Class to manage connections. It provides:

• connection pooling
• sharding support
• retry handling
• fallback connections
• driver switching via .env
• zero code changes when switching drivers

use Luminova\Database\Connection;

$enableConnectionPool = true;
$maxConnectionPools = 5;
$autoConnect = true;

$conn = new Connection(
    $enableConnectionPool,
    $maxConnectionPools,
    $autoConnect
);

MySQLi Driver Specific

use App\Config\Database;
use Luminova\Database\Driver\MysqliDatabase;

$mysqli = new MysqliDatabase(Database::fromArray(
    'port' => 3306,
    'host' => 'localhost',
    'username' => 'admin',
    'password' => 'passwd',
    'database' => 'example-db'
    //...
));

$mysqli->connect();

PDO Driver Specific

use App\Config\Database;
use Luminova\Database\Driver\PdoDatabase;

$pdo = new PdoDatabase(Database::fromArray([
    'port' => 3306,
    'host' => 'localhost',
    'username' => 'admin',
    'password' => 'passwd',
    'database' => 'example-db'
    //...
]));

$pdo->connect();

Important limitations of manual usage:

• no connection pooling
• no fallback connections
• no sharding
• no retry handling
• connection must be opened manually

See configuration details here:Database Configuration

Emulate Prepares

Emulate prepares allows reuse of the same named placeholder multiple times in a query.

Native driver behavior:

• PDO → supports reuse through internal emulation
• MySQLi → Luminova provides consistent behavior across both drivers.

Enable or Disable

You can control this feature using:

• .env → database.emulate.prepares = true
• configuration → emulate_prepares

When Disabled, queries that reuse placeholders will fail with an error similar to:

The number of variables must match the number of parameters in the prepared statement.

When emulate prepares is enabled in MySQli driver, Luminova internally expands placeholders and parameters to maintain driver compatibility.

This is useful when:

• comparing one value across multiple columns
• building dynamic conditions
• generating reusable query fragments

Reused Placeholder

$stmt = $driver->prepare('
    SELECT * FROM users
    WHERE (id = :identifier OR email = :identifier)
');

// Or using bind
// $stmt->bind(':identifier', 100);

$result = $stmt->execute(['identifier' => 100]);

Using the Query Builder

use Luminova\Database\Builder;

$result = Builder::query('
    SELECT * FROM users
    WHERE (id = :identifier OR email = :identifier)
')->execute(['identifier' => 100]);

Class Definition

• Namespaces:

  • Luminova\Database\Driver\PdoDatabase - For PDO driver class.
  • Luminova\Database\Driver\MysqliDatabase - For MySQli driver class.

• Implements:\Luminova\Interface\DatabaseInterface

Methods

constructor

Initialize database driver constructor for configurations.

This constructor allows you to initialize driver with database configuration object to later establish a connection with.

public __constructor(Luminova\Foundation\Core\Database $config)

Parameters:

connect

Establish a database connection.

This method allows you to open a database connection for selected driver with database configurations.

public connect(): bool

Return Value:

bool - Return true if database connection was established, otherwise false.

Throws:

• \Luminova\Exceptions\DatabaseException - If the database connection fails.

isConnected

Check if the database is connection is already established.

public isConnected(): bool

Return Value:

bool - Returns true if connected, false otherwise.

isStatement

Determine Whether the executed query prepared statement is ready.

public isStatement(): bool

Return Value:

bool - Return true if is a prepared statement, false otherwise.

isResult

Determine Whether the executed query result is ready.

public isResult(): bool

Return Value:

bool - Return true if query result, false otherwise.

getDriver

Get the driver version name of the database connection driver.

This method will retrieve the database driver name in use (e.g sqlite, mysql, cubrid etc...).

public getDriver(): ?string

Return Value:

string|null - Returns the driver version name if the connection is open, otherwise null.

getConfig

Returns a database configuration value by name.

If the property exists, its value is returned.If the property is undefined or restricted, the method returns null.

Sensitive connection details are intentionally not accessible.

public getConfig(string $property): mixed

Parameters:

Return Value:

mixed - Returns the configuration value, or null if the property does not exist or is restricted.

Available Properties:

• connection (string) — Connection driver (default: 'pdo').
• pdo_version (string) — PDO driver type (e.g., 'mysql', 'sqlite') (default: 'mysql').
• charset (string) — Connection character set (default: 'utf8mb4').
• database (string) — Selected database name (default: '').
• persistent (bool) — Enables persistent connections (default: false).
• emulate_prepares (bool) — Enables query emulation (default: false).
• buffered_query (bool) — Enables MySQL buffered queries (default: false).
• sqlite_path (string|null) — SQLite database file path (default: null).
• socket (bool) — Use Unix socket instead of TCP/IP (default: false).
• socket_path (string) — Unix socket path (default: '').
• timeout (int) — Connection timeout in seconds (default: 0).
• production (bool) — Production environment flag (default: false).
• commands (array|null) — Commands executed after connection (default: null).

Restricted Properties:

To prevents exposure of connection credentials, the following properties cannot be retrieved and always return null:

• username
• password
• host
• port

Example:

$charset = $connection->getConfig('charset');

if ($charset === null) {
    // property does not exist or is restricted
}

raw

Returns a wrapper around the underlying native connection.

This provides direct access to the low-level driver instance (e.g., PDO or MySQLi), through a safe interface.

public raw(): Luminova\Interface\ConnInterface

Return Value:

ConnInterface - Returns the connection wrapper that exposes the native driver instance.

Available Methods

If no connection exists, the wrapper still returns but contains null.

Examples:

Access Native PDO Instance.

Useful when you need features not exposed by the database layer.

use Luminova\Database\Driver\PdoDatabase;

$conn = new PdoDatabase(...);
$conn->connect();

$raw = $conn->raw();

$pdo = $raw->getConn();

if ($pdo instanceof PDO) {
    $stmt = $pdo->query("SELECT NOW()");
    $time = $stmt->fetchColumn();
}

$raw->close();

Note: closing the wrapper clears the native instance reference.Connection lifecycle is still managed by the connection manager when pooling is enabled.

getStatement

Retrieve the last executed query prepared statement.

public getStatement(): PDOStatement|mysqli_stmt|null

Return Value:

Returns null if no statement or:

• PDOStatement - PDO statement object if using PdoDatabase driver.
• mysqli_stmt - MySOLI statement object if using MysqliDatabase driver.

setDebug

Sets the debug mode.

public setDebug(bool $debug): Luminova\Interface\DatabaseInterface

Return Value:

DatabaseInterface - Returns the instance of database driver interface.

Parameters:

error

Get the error information for the last executed statement.

public error(): string

Return Value:

string - Returns the error information as a string.

errors

Get all error information.

public errors(): array

Return Value:

array - Returns an array of error information.

dumpDebug

Dump debug information for the last executed statement.

public dumpDebug(): bool

Return Value:

bool - Returns true if debug information is dumped, otherwise false.

info

Get information about the last executed statement.

public info(): array

Return Value:

array - Returns an array of statement execution information.

prepare

Prepares an SQL statement for execution.

This method initializes a prepared statement for both PDO and MySQLi drivers. The query should contain placeholders (:columnName) to be bound later using bind() or param().

public prepare(string $query): Luminova\Interface\DatabaseInterface

Parameters:

Return Value:

DatabaseInterface - Returns the instance of database driver interface.

Throws:

• \Luminova\Exceptions\DatabaseException - If no database connection is established.

Example:

Preparing SQL Statement:

$stmt = $driver->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bind(':foo', 'bar');
$stmt->execute();

$result = $stmt->fetch();

query

Executes an SQL query without binding or placeholders.

This method is used for executing raw SQL statements that do not require parameter binding, such as DDL operations (CREATE, ALTER, DROP) or direct SELECT queries.

public query(string $query): Luminova\Interface\DatabaseInterface

Parameters:

Return Value:

DatabaseInterface - Returns the instance of database driver interface.

Throws:

• \Luminova\Exceptions\DatabaseException - If no database connection is established.

Example:

Query Examples:

// select query
$stmt = $driver->query("SELECT * FROM users");
$result = $stmt->fetchAll();

// Create a new table
$result = $driver->query("CREATE TABLE logs (id INT AUTO_INCREMENT PRIMARY KEY, message TEXT)")
    ->rowCount();

exec

Executes an SQL statement without placeholders and returns the number of affected rows.

This method is useful for operations like ALTER, CREATE, DELETE, where you need to know how many rows were modified.

public exec(string $query): int

Parameters:

Return Value:

int - Returns the number of rows affected by the query.

Throws:

• \Luminova\Exceptions\DatabaseException - If no database connection is established.

Example:

Using the exec method.

$affected = $driver->exec(
    "UPDATE users SET status = 'active' WHERE last_login > NOW() - INTERVAL 30 DAY"
);

echo "Updated {$affected} rows.";

beginTransaction

Begin a transaction with an optional read-only isolation level and savepoint.

Bitmask Flags:

• 1 - Starts transaction with a consistent snapshot (InnoDB behavior)
• 2 - Starts transaction in read/write mode
• 4 - Starts transaction in read-only mode

public beginTransaction(int $flags = 0, ?string $name = null): bool

Parameters:

Return Value:

bool - Returns true if the transaction and optional savepoint were successfully started.

Throws:

• \Luminova\Exceptions\DatabaseException - If an invalid savepoint name or database error during isolation.

beginNestedTransaction

Start a nested transaction using a savepoint if already in a transaction.

If a transaction is already active, creates a unique savepoint and returns its name.If no transaction is active, begins a new transaction.

Optionally frees the current statement cursor before starting.

public beginNestedTransaction(bool $closeCursor = false): string|false|null

Parameters:

Return Value:

Returns:

• string - Name of the savepoint if a nested transaction is created.
• null - A new transaction was started successfully.
• false - Failed to start a transaction or create a savepoint.

Example:

Nested transaction example:

$name = $driver->beginNestedTransaction();

if($name === false){
    echo 'Failed';
}

if($isSuccess){
    if($name !== null){
        $driver->release($name);
    }

    $driver->commit();
}else{
    $driver->rollback();
}

setTransactionIsolation

Set the transaction isolation level for the current connection.

This determines how transactions interact with other concurrent transactionsin terms of visibility of changes, locking behavior, and consistency.

Supported levels (by integer code):

• 0 => 'NONE' : Skips setting isolation level.
• 1 => 'READ UNCOMMITTED' : Lowest isolation, allows dirty reads.
• 2 => 'READ COMMITTED' : Default in most databases, prevents dirty reads.
• 3 => 'REPEATABLE READ' : Ensures consistent reads within a transaction.
• 4 => 'SERIALIZABLE' : Highest isolation, full serial execution.
• 5 => 'READ WRITE' : Transaction can perform reads and writes.
• 6 => 'READ ONLY' : Transaction can only read data.

public setTransactionIsolation(int $level = 2): bool

Parameters:

Return Value:

bool - Return true on success otherwise false.

Throws:

• \Luminova\Exceptions\DatabaseException - If the level is invalid, or setting the isolation fails, or called within an active transaction.

Note:

• Cannot be changed inside an active transaction.
• Throws DatabaseException if the level is invalid or the query fails.

savepoint

Set a named transaction savepoint.

public savepoint(string $name): bool

Parameters:

Return Value:

bool - Returns true on success or false on failure.

Throws:

• \Luminova\Exceptions\DatabaseException - If an invalid savepoint name or database error.

commit

Commits a transaction if any.

public commit(int $flags = 0, ?string $name = null): bool

Parameters:

Return Value:

bool - Returns true if the transaction was successfully committed, otherwise false.

Throws:

• \Luminova\Exceptions\DatabaseException - If an invalid savepoint name or database error during commit.

rollback

Roll back the current transaction or to a specific savepoint.

public rollback(int $flags = 0, ?string $name = null): bool

Parameters:

Return Value:

bool - Returns true if the rollback was successful, otherwise false.

Throws:

• \Luminova\Exceptions\DatabaseException - If an invalid savepoint name or database error during rollback.

release

Removes the named savepoint from the set of savepoints of the current transaction.

public release(string $name): bool

Parameters:

Return Value:

bool - Returns true on success or false on failure.

Throws:

• \Luminova\Exceptions\DatabaseException - If an invalid savepoint name or database error.

inTransaction

Check if there is an active transaction.

public inTransaction(): bool

Return Value:

bool - Returns true if there is an active transaction, otherwise false.

Throws:

• \Luminova\Exceptions\DatabaseException - If an called when no connection is established.

getType

Determines the appropriate database parameter type for a given value.

This is used to bind parameters correctly when preparing SQL statements.

Supported types (by integer code):

• 0 => PARAM_NULL (null values)
• 1 => PARAM_INT (integers)
• 2 => PARAM_STR (strings)
• 3 => PARAM_LOB (binary data or strings with non-printable characters)
• 5 => PARAM_BOOL (boolean values, treated as integers in MySQLi)
• 6 192 => PARAM_FLOAT (floating-point numbers, treated as doubles in PDO)

public static getType(mixed $value): int

Parameters:

Return Value:

int - Return an integer value representing the parameter type.

bind

Binds a value to a named parameter for use in a prepared statement.

This method works for both PDO and MySQLi drivers. For PDO, the binding is done via bindValue(), while for MySQLi, the value is stored in an internal array for later binding.

public bind(string $param, mixed $value, ?int $type = null): Luminova\Interface\DatabaseInterface

Parameters:

Return Value:

DatabaseInterface - Returns the instance of database driver interface.

Throws:

• \Luminova\Exceptions\DatabaseException - If called without a prepared statement.

Example:

Supported in both PDO and MySQLi:

$stmt = $driver->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bind(':id', 123)->execute();

value

Binds a value to a named parameter for use in a prepared statement.

This method is an alias of bind().

public value(string $param, mixed $value, ?int $type = null): Luminova\Interface\DatabaseInterface

Parameters:

Return Value:

DatabaseInterface - Returns the instance of database driver interface.

Throws:

• \Luminova\Exceptions\DatabaseException - If called without a prepared statement.

param

Binds a variable to a named parameter by reference use in a prepared statement.

Unlike bind() and value(), this method binds the parameter by reference, which means changes to the variable will be reflected in the query execution. This is useful for scenarios where values might change before execution.

For PDO, the method binds using bindParam(), while for MySQLi, it stores a reference in an internal array.

public param(string $param, mixed &$value, ?int $type = null): Luminova\Interface\DatabaseInterface

Parameters:

Return Value:

DatabaseInterface - Returns the instance of database driver interface.

Throws:

• \Luminova\Exceptions\DatabaseException - If called without a prepared statement.

Example:

For both PDO and MySQLi:

$stmt = $driver->prepare("UPDATE users SET status = :status WHERE id = :id");

$status = 'active';
$stmt->param(':status', $status)
    ->param(':id', $userId);

// Changing the variable before execution affects the query
$status = 'inactive';
$stmt->execute();

execute

Executes the prepared statement.

If parameters are provided, they will be bound to the statement during execution. This method works for both PDO and MySQLi drivers.

public execute(?array<string,mixed> $params = null): bool

Parameters:

Return Value:

bool - Returns true on success or false on failure.

Throws:

• \Luminova\Exceptions\DatabaseException - If execution fails due to a database error.

Example:

Executing Using Prepared Statement:

$stmt = $driver->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute([
    ':name' => 'John Doe', 
    ':email' => '[email protected]'
]);

var_dump($stmt->rowCount());

ok

Check if the last query execution was successful.

public function ok(): bool;

Return Value:

bool - Returns true on success, false on failure.

Example:

Checking Execution status:

$stmt = $driver->prepare("SELECT * users WHERE id = :id");
$stmt->bind('id', 100);
$stmt->execute();

$result = null;

if($stmt->ok()){
    $result = $stmt->fetchAll();
}

rowCount

Get the number of rows affected by the last executed statement.

public rowCount(): int

Return Value:

int - Returns the number of affected rows.

fetchNext

Fetch the next row from the result set as an object or array.

Alias getNext().

public fetchNext(int $mode = FETCH_OBJ): array|object|false

Parameters:

Return Value:

array|object|false - Returns the next row as an object or array, or false if no more rows are available.

fetchAll

Fetch all rows from the result set as an array of objects or arrays.

Alias getAll().

public fetchAll(int $mode = FETCH_OBJ): array|object|bool

Parameters:

Return Value:

array|object|false - Returns an array of result objects or arrays, an empty array if no rows are found or false.

getInt

Fetch the result set as a 2D array of integers.

public getInt(): array

Return Value:

array - Returns a 2D array of integers, or an empty array if no results are found.

getCount

Get the total count of selected rows as an integer.

public getCount(): int

Return Value:

int - Returns the total row count, or 0 if no results are found.

getColumns

Retrieve a specific column or multiple columns from the result set.

public getColumns(int $mode = FETCH_COLUMN): array

Parameters:

Return Value:

array - Return and associative array or indexed array of columns depending on mode.

fetch

Fetch data from the result set using a specified fetch mode.

Return Modes

• RETURN_ALL to retrieve all rows at once,
• RETURN_NEXT to fetch a single row,
• RETURN_STREAM to fetch rows one at a time (use in while loops).

public fetch(int $returnMode = RETURN_ALL, int $fetchMode = FETCH_OBJ): mixed

Parameters:

Return Value:

mixed - Return the fetched result(s) based on the specified type and mode.

Throws:

• \Luminova\Exceptions\DatabaseException - Throws an exception if an error occurs.

fetchObject

Fetch the result set as an object of the specified class or stdClass.

public fetchObject(?\T<string> $class = null, mixed ...$arguments): ?object

Parameters:

Return Value:

\T<object>|null - Returns the fetched object, or null if an error occurs.

Throws:

• \Luminova\Exceptions\DatabaseException - If an error occurs.

Example:

Define your user model class.

Note: This fetchObject doesn't strictly require class to extend Luminova\Base\Model. It can be any class.

// /app/Models/User.php

namespace App\Models;

use Luminova\Base\Model;

class User extends Model
{
    public string $name = '';
    public string $email = '';
    private ?string $password = null;

    public function __construct(string $password)
    {
        $this->password = $password;
    }
}

Execute query and return instance of User class object that resolve to result.

$stmt = $driver->prepare("SELECT name, email users WHERE id = :id LIMIT 1");
$stmt->bind('id', 100);
$stmt->execute();

$user = null;

if($stmt->ok()){
    $user = $stmt->fetchObject(User::class, 'user-password');
    echo $user->name;
}

getLastInsertId

Get the ID of the last inserted row or sequence value.

public getLastInsertId(?string $name = null): mixed

Parameters:

Return Value:

mixed - Returns the last inserted ID, or null/false on failure.

getResult

Retrieves a result item based on the specified mode and return type.

This method allows flexible result retrieval using a single interface, depending on the mode and return type provided.

Available Modes:

• RETURN_NEXT: Fetch the next row (same as $stmt->fetchNext(...)).
• RETURN_2D_NUM: Fetch a 2D numeric array (same as $stmt->getInt()).
• RETURN_INT: Fetch a single integer value (same as $stmt->getCount()).
• RETURN_ID: Fetch the last insert ID (same as $stmt->getLastInsertId(...)).
• RETURN_COUNT: Fetch the count of affected rows (same as $stmt->rowCount()).
• RETURN_COLUMN: Fetch specific columns (same as $stmt->getColumns()).
• RETURN_ALL: Fetch all rows (same as $stmt->fetchAll($return)).
• RETURN_STMT: Return the statement object itself (same as $stmt->getStatement()).
• RETURN_RESULT: Return the query result in MySQLi or statement in PDO.

See documentation for database return modes.

public getResult(int $returnMode = RETURN_ALL, int $fetchMode = FETCH_OBJ): mixed

Parameters:

Return Value:

mixed - Return the result based on the specified mode and return type.

Throws:

• \Luminova\Exceptions\DatabaseException - If an error occurs.

free

Frees up the statement cursor and sets the statement object to null.

public free(): void

close

Frees up the statement cursor and closes the database connection.

public close(): void

profiling

Start recording the database query execution time for queries.

This method records the duration of a query in shared memory under the key __DB_QUERY_EXECUTION_TIME__. The stored value can later be retrieved from anywhere in your application.

Note:The profiling mechanism is built into Luminova’s database drivers.Avoid calling profiling() directly — doing so resets the internal timer.Instead, use getQueryTime() or getLastQueryTime() after a query runs.This method is intended for use only when building or extending a custom driver.

public profiling(bool $start = true, bool $finishedTransaction = false): void

Parameters:

Example:

To retrieve the last recorded query execution time from anywhere in your app:

$time = shared(
    '__DB_QUERY_EXECUTION_TIME__', /* Default key used for profiling */
    null, /* Do not override the existing value */
    0  /* Fallback if not set */
);

getQueryTime

Returns the total accumulated execution time for all queries executed so far.

This method returns the accumulated time spent on executing queries in either float or integer format, depending on the internal state of the query time.

public getQueryTime(): float|int

Return Value:

float|int - Return the total query execution time in seconds.

getLastQueryTime

Returns the execution time of the most recently executed query.

This method returns the time spent on the last query execution in either float or integer format, depending on the internal state of the query time.

public getLastQueryTime(): float|int

Return Value:

float|int - Return the last query execution time in seconds.