Luminova Framework

PHP Luminova: ORM Database Query Builder Module

Last updated: 2024-08-31 15:18:38

Database Builder Class: An Object-Relational Mapping Tool for Simplified CRUD Operations and Object-Oriented Database Management.

The Luminova database query Builder class is a powerful ORM (Object-Relational Mapping) tool designed to simplify database interactions. It enables developers to work with intuitive, object-oriented methods, abstracting the complexities of constructing SQL queries.

With its versatile CRUD (Create, Read, Update, Delete) capabilities, you can effortlessly build both simple and complex SQL queries without writing raw SQL. This allows you to focus more on developing your application and less on the intricacies of database queries. Each method in the Builder class is carefully crafted with names and functionality that align closely with SQL syntax, making it easy to grasp if you're familiar with SQL.

For more detailed examples and usage instructions, refer to the query Builder documentation.



Methods

db

Get database connection driver instance (e.g, MySqlDriver or PdoDriver).

public db(): ?Luminova\Instance\DatabaseInterface

Return Value:

\Luminova\Interface\DatabaseInterface|null - Return database driver instance.

Returning the Original Database Connection Object

This method retrieves the underlying database connection instance, which can be either PDO or MySqli, encapsulated within the Luminova\Interface\ConnInterface.

<?php 
$conn = $builder->db()->raw();

This is useful when you need to access the raw connection for operations not covered by the interface.


getInstance

To get a shared singleton instance of builder class.

public static getInstance(): static

Return Value:

static - Return new static instance of builder class.

Throws:


table

Specifies the database table to build the query for. This method is essential in the Builder class and is typically called first to define the table you want to work with.

public table(string $table, ?string $alias = null): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to query (non-empty string).
$aliasstring|nullOptional table alias (default: NULL).

Return Value:

self - Returns the instance of builder class.

Throws:


join

To join main table table with another using JOIN operators (e.g INNER JOIN, FULL JOIN).

public join(string $table, string $type = 'INNER', ?string $alias = null): self

Parameters:

ParameterTypeDescription
$tablestringThe name of the table to join.
$typestringThe type of join (default: "INNER").
$aliasstring|nullOptional table join alias (default: NULL).

Return Value:

self - Returns the instance of builder class.

Throws:

Join Types

  • INNER - Returns rows with matching values in both tables.
  • LEFT - Returns all rows from the left table and matching rows from the right table, or NULLs for non-matching rows from the right table.
  • RIGHT - Returns all rows from the right table and matching rows from the left table, or NULLs for non-matching rows from the left table.
  • CROSS - Returns the Cartesian product of the two tables.
  • FULL - Returns rows with matching values in either table, with NULLs for non-matching rows from either table.
  • FULL OUTER - Returns all rows when there is a match in either the left or right table, or NULL from the side that does not have a match.

on

Specifies join conditions to query table, the on method can be called multiple times with different arguments, each call to on sets a condition for the query.

public on(string $condition, string $operator, mixed $value): self

Parameters:

ParameterTypeDescription
$conditionstringJoin condition or column name.
$operatorstringJoin operator (default: '=').
$valuemixedValue to bind to the condition or another table column.

Return Value:

self - Returns the instance of builder class.


innerJoin

Sets table join condition as INNER JOIN.

public innerJoin(string $table, string|null $alias = null): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstring|nullOptional table join alias (default: NULL).

Return Value:

self - Returns the instance of the class.

Throws:


leftJoin

Sets table join condition as LEFT JOIN.

public leftJoin(string $table, string|null $alias = null): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstring|nullOptional table join alias (default: NULL).

Return Value:

self - Returns the instance of the class.

Throws:


rightJoin

Sets table join condition as RIGHT JOIN.

public rightJoin(string $table, string|null $alias = null): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstring|nullOptional table join alias (default: NULL).

Return Value:

self - Returns the instance of the class.

Throws:


crossJoin

Sets table join condition as CROSS JOIN.

public crossJoin(string $table, string|null $alias = null): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstring|nullOptional table join alias (default: NULL).

Return Value:

self - Returns the instance of the class.

Throws:


fullJoin

Sets table join condition as FULL JOIN.

public fullJoin(string $table, string|null $alias = null): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstring|nullOptional table join alias (default: NULL).

Return Value:

self - Returns the instance of the class.

Throws:


fullOuterJoin

Sets table join condition as FULL OUTER JOIN.

public fullOuterJoin(string $table, string|null $alias = null): self

Parameters:

ParameterTypeDescription
$tablestringThe table name to join.
$aliasstring|nullOptional table join alias (default: NULL).

Return Value:

self - Returns the instance of the class.

Throws:


limit

Set the limit and offset for SELECT query execution.This method can be called when working while calling methods like total, select fetch, count, sum and average.

public limit(int $limit, int $offset): self

Parameters:

ParameterTypeDescription
$limitintThe maximum number of results to return.
$offsetintThe starting offset for the results (default is 0).

Return Value:

self - Returns the instance of the builder class.


max

Set the maximin record to execute when called update or deletemethod.

public max(int $limit): self

Parameters:

ParameterTypeDescription
$limitintnumber of records to update or delete.

Return Value:

self - Return instance of builder class.


order

Set result return order for query selection (e.g., id ASC, date DESC).The order Method can be called multiple times with different arguments to allows chaining of multiple query orders.

public order(string $column, string $order = 'ASC'): self

Parameters:

ParameterTypeDescription
$columnstringThe column name to set the order for.
$orderstringThe order algorithm to use (either "ASC" or "DESC")

Return Value:

self - Return instance of builder class.


orderByMatch

Set the result return order when match against method is called.

public orderByMatch(array $columns, string|int|float $value, string $mode = 'NATURAL_LANGUAGE', string $order = 'ASC'): self

Parameters:

ParameterTypeDescription
$columnsarrayThe column names to index match order.
$valuestring|int|floatThe value to match against in order.
$modestringThe comparison match mode operator.
Optionally you can choose any of these modes or pass your own mode.
- NATURAL_LANGUAGE
- BOOLEAN
- NATURAL_LANGUAGE_WITH_QUERY_EXPANSION
- WITH_QUERY_EXPANSION
$orderstringThe order algorithm to use (either "ASC" or "DESC").

Return Value:

self - Returns an instance of the class.


group

Set query grouping for the SELECT statement.The group Method can be called multiple times with different arguments to allows chaining of multiple query grouping.

public group(string $group): self

Parameters:

ParameterTypeDescription
$groupstringThe column name to group by.

Return Value:

self - Return instance of builder class.


where

Set query condition for WHERE operator.This can be called while working with methods like: select, find, stmt, update, delete, sum, total or average methods execution.

public where(string $column, string $operator, mixed $key): self

Parameters:

ParameterTypeDescription
$columnstringThe column name.
$operatorstringThe comparison operator (e.g. =, &gt;=, &lt;&gt;).
$valuemixedThe where condition column value.

Return Value:

self - Return instance of builder class.


and

Set query condition for AND operator.The and method can be called multiple times with different arguments to allows chaining of multiple query conditions.

public and(string $column, string $operator, mixed $value): self

Parameters:

ParameterTypeDescription
$columnstringThe column name.
$operatorstringThe comparison operator (e.g. =, &gt;=, &lt;&gt;).
$valuemixedThe and condition column value.

Return Value:

self - Return instance of builder class.


against

The against method allows you to perform database querying with the match operator for efficient matching against specified columns. It sets the query match columns and mode. It also support chaining multiple against conditions.

public against(array $columns, string $mode, mixed $value): self

Parameters:

ParameterTypeDescription
$columnsarrayThe column names to match against.
$modestringThe comparison match mode operator.
$valuemixedThe value to match against.

Modes:

You can use any of the following matching modes or pass your own mode:

  • NATURAL_LANGUAGE: This mode provides a natural language search experience.
  • BOOLEAN: This mode enables Boolean search capabilities.
  • NATURAL_LANGUAGE_WITH_QUERY_EXPANSION: This mode extends the NATURAL_LANGUAGE mode with query expansion.
  • WITH_QUERY_EXPANSION: This mode extends the standard search with query expansion.

Return Value:

self - Return instance of builder class.


set

To stage the table column name and value which will be use when called update method.The set Method can be called multiple times with different arguments to allows chaining of multiple query update key-values.

public set(string $column, mixed $value): self

Parameters:

ParameterTypeDescription
$columnstringThe column name to update.
$valuemixedThe column key value to update.

Return Value:

self - Return instance of builder class.


or

To set query OR condition operator for execution.The or method can be called multiple times with different arguments to allows chaining of multiple query OR conditions.

public or(string $column, string $operator, mixed $value): self

Parameters:

ParameterTypeDescription
$columnstringThe column name.
$operatorstringThe comparison operator to use.
$valuemixedThe column key value.

Return Value:

self - Return instance of builder class.


orGroup

To set an OR group of conditions (e.g (foo = 1 OR bar = 2)).

public orGroup(array $conditions): self

Parameters:

ParameterTypeDescription
$conditionsarrayArray of conditions to be grouped with OR.

Return Value:

self - Return instance of builder class.


andGroup

Adds a group of conditions combined with AND to the query.

public andGroup(array $conditions): self

Parameters:

ParameterTypeDescription
$conditionsarrayArray of conditions to be grouped with AND.

Return Value:

self - Return instance of builder class.


Group Example

Here is an example of how you can implement orGroup and andGroup.

<?php 
$builder->orGroup([
    ['column1' => ['operator' => '=', 'value' => 1]],
    ['column2' => ['operator' => '=', 'value' => 2]]
]);
<?php 
$builder->andGroup([
    ['column1' => ['operator' => '=', 'value' => 1]],
    ['column2' => ['operator' => '=', 'value' => 2]]
]);

Note:

The binding columns must be the array key while the child elements must have only 2 index with keys operator and value.


orBind

To bind multiple OR group conditions and combined to a single group (e.g: ((foo = 1 OR bar = 2) OR (baz = 3 OR bra = 4))).

public orBind(array $group1, array $group2, string $bind = 'OR'): self

Parameters:

ParameterTypeDescription
$group1arrayFirst group of conditions.
$group2arraySecond group of conditions.
$bindstringThe type of logical operator to use in binding groups together (default: 'OR').
- AND or OR.

Return Value:

self - Return instance of builder class.


andBind

Adds two groups of conditions combined with AND condition.The andBind is similar with orBind, except that it uses AND operator to bind group conditions before combining to a single group (e.g: ((foo = 1 AND bar = 2) OR (baz = 3 AND bra = 4))).

public andBind(array $group1, array $group2, string $bind = 'AND'): self

Parameters:

ParameterTypeDescription
$group1arrayFirst group of conditions.
$group2arraySecond group of conditions.
$bindstringThe type of logical operator to use in binding groups together (default: 'AND').
- AND or OR.

Return Value:

self - Return instance of builder class.


Binds Group Example

Here is an example of how you can implement orBind and andBind.

<?php 
$builder->orBind([
    ['column1' => ['operator' => '=', 'value' => 1]],
    ['column2' => ['operator' => '=', 'value' => 2]]
],
[
    ['column3' => ['operator' => '=', 'value' => 3]],
    ['column4' => ['operator' => '=', 'value' => 4]]
]);
<?php 
$builder->andBind([
    ['column1' => ['operator' => '=', 'value' => 1]],
    ['column2' => ['operator' => '=', 'value' => 2]]
],
[
    ['column3' => ['operator' => '=', 'value' => 3]],
    ['column4' => ['operator' => '=', 'value' => 4]]
]);

Note:

The binding columns must be the parent array key while the child elements should only have 2 elements with key operator and value.


in

Set query where IN operator expression, allows you to specify array values to search.

public in(string $column, array<int,mixed> $list): self

Parameters:

ParameterTypeDescription
$columnstringThe column name.
$listarray<int,mixed>The expression values.

Return Value:

self - Return instance of builder class.

Throws:


inset

Add a FIND_IN_SET condition to the query.

public inset(string $search, string $operator, array<int,mixed>|string $list, bool $isSearchColumn = false): self

Parameters:

ParameterTypeDescription
$searchstringThe search value or column name depending on $isSearchColumn.
$operatorstringThe operator for matching (e.g., exists, first, &gt;= foo, &lt;= bar).
$listarray<int,mixed>|stringThe comma-separated values or a column name containing the list.
$isSearchColumnboolWhether the $search argument is a column name (default: false).

Return Value:

self - Return instance of builder class.

Throws:

Default Operators:

  • exists|> - Check if exists or match any in the list.
  • first|= - Check if it's the first in the list.
  • last - Check if it's the first in the list.
  • position - Position in the list (as inset_position).
  • contains - Check if it contains the search term (uses the $search as the search value).
  • none - No match in the list.

Usage Examples:

Using the custom Operator:

$builder->table('fruits')->inset('banana', '= 2', ['apple','banana','orange']);

Using the exists Operator with a column:

$builder->table('employees')->inset('PHP', 'exists', 'column_language_skills');

Using the exists Operator with a search column:

$builder->table('employees')->inset('department', 'exists', 'HR,Finance,Marketing', true);

returns

To change the result return type to either object or array, call this method before finally executing fetch, find or select method. The default result response is object.

public returns(string $type): self

Parameters:

ParameterTypeDescription
$typestringThe result return type object or array.

Return Value:

self - Return instance of builder class.

Throws:


datetime

Get date/time format for storing SQL.

public static datetime(string $format = 'datetime', ?int $timestamp = null): string

Parameters:

ParameterTypeDescription
$formatstringFormat to return (default: datetime).
$timestampint|nullAn optional timestamp.

Return Value:

string - Returns formatted date-time.

Available time formats:

  • time - Return time format from timestamp
  • datetime - Return SQL datetime format
  • date - Return SQL date format.

caching

The caching method provides the flexibility to enable or disable all caches universally. This is particularly useful for debugging or temporarily disabling database caching throughout your application without the need to manually remove cache() method calls from your all code.

To disable all database caching, simply call this method with a false parameter.

public caching(bool $enable): self

Parameters:

ParameterTypeDescription
$enableboolThe caching status action (e.g, true or false).

Return Value:

self - Return instance of builder class.

Note: By default caching is enabled once you call the cache method.


cacheDelete

Deletes the cached data associated with current table or a specific database table.

This method clears cached data for a specified table and subfolder within the cache system.It supports both file-based and memory-based caching (e.g., Memcached).If no table is specified, it defaults to the table name associated with the current instance.

public cacheDelete(?string $table = null, ?string $subfolder = null): bool

Parameters:

ParameterTypeDescription
$tablestring|nullThe name of the table for which to delete the cache (default: null).
$subfolderstring|nullOptional file-based caching feature, the subfolder name used while storing the cache if any (default: null).

Return Value:

bool - Returns true if the cache was successfully cleared; otherwise, false.


cacheDeleteAll

Deletes all cached items for the specified subfolder or the default database cache.

public cacheDeleteAll(?string $subfolder = null): bool

Parameters:

ParameterTypeDescription
$subfolderstring|nullOptional file-based caching feature, the subfolder name used while storing caches if any (default: null).

Return Value:

boo - Returns true if the cache was successfully flushed, false otherwise.


cache

Configures and manages caching for current query operation, to cache query result response, and return cached data when next user request the same content.

This method sets up the caching mechanism based on the provided parameters.It supports both memory-based caching (e.g., Memcached) and file-based caching.The method determines if the cache for the given key exists and whether it has expired.

public cache(
    string $key, 
    ?string $storage = null, 
    \DateTimeInterface|int $expiry = 7 * 24 * 60 * 60, 
    ?string $subfolder = null, 
    ?string $persistent_id = null
): self

Parameters:

ParameterTypeDescription
$keystringThe unique key identifying the cache item.
$storagestring|nullOptional storage name for the cache (it's recommended to void storing large data in one file when using file-based cache).
Defaults to the current table name or 'capture' if not specified.
$expiry\DateTimeInterface|intThe cache expiration time (default: to 7 days).
$subfolderstring|nullOptional file-based caching feature, to set subfolder within the cache root directory (default: database).
$persistent_idstring|nullOptional memory-based caching feature, to set a unique persistent connection ID (default: __database_builder__).

Return Value:

self - Return instance of builder class.

Note: In other for caching to work, you must call cache method before select, find, execute, fetch, sum, average or total method is called.Additionally, using stmt method for returning statement object doesn't support caching, you will need to manually implement it if need.

Throws:


insert

To insert records into a specified database table.

This method allows for inserting multiple records at once by accepting an array of associative arrays or by using the set method to specify. For arrays, each associative array should contain the column names as keys and their corresponding values as values.

Additionally the insert method supports inserting array value to database, the array will be converted to JSON encoded string, if wish to insert a serialized string, you should do that before passing to insert

public insert(array<string,mixed> $values, bool $prepare = true): int

Parameters:

ParameterTypeDescription
$valuesarray<int,array<string,mixed>>An array of associative arrays, where each associative array represents a record to be inserted into the table.
$prepareboolIf set to true, uses prepared statements with bound values for the insert operation.
If false, executes a raw query instead (default: true).

Return Value:

int - Returns the number of affected rows, 0 if no rows were inserted.

Throws:


query

Build a custom SQL query string for execution when calling the execute method. This method allows you to run more custom queries as needed.

The method supports caching, and when you pass an array of values directly to the execute method, it utilizes prepared statements for enhanced security. If no array values are provided, the method defaults to executing the query directly, so ensure that any values included in the query string are properly escaped to prevent SQL injection.

public query(string $query): self

Parameters:

ParameterTypeDescription
$querystringThe SQL query string

Return Value:

self - Return instance of builder class.

Throws:

Example

The below example show how you can use custom query builder to create and execute your SQL queries.

<?php
$id = 1;
$customer_id = 'peter';

$query = $builder->query("
    UPDATE addresses 
    SET address_default = (address_id = :address_id) 
    WHERE address_customer_id = :customer_id
");

$response = $query->execute([
    'address_id' => $id,
    'customer_id' => $customer_id
]);

execute

Executes an SQL query string that was previously prepared using the query method.

To enable caching of the query result, call the cache method before invoking execute.

You can optionally pass an associative array of placeholders (key-value pairs) to bind values to the query. The key serves as the placeholder name, while the value is the corresponding value to bind.

When placeholders are provided, prepared statements are used. If no placeholders are given, the method falls back to executing the query directly, so ensure any values included in the query are properly escaped.

public execute(?array<string,mixed> $placeholder = null, int $mode = RETURN_ALL): mixed

Parameters:

ParameterTypeDescription
$placeholderarray<string,mixed>|nullThe placeholder and value to binds with query string (default: null).
$modeintThe query return result mode (e.g, RETURN_*) (default: RETURN_ALL).

Return Value:

mixed - Return result or prepared statement depending on $mode otherwise false on failed.

Return Modes

Throws:


total

Calculate the total number of records table,

public total(string $column = '*'): int|false

Parameters:

ParameterTypeDescription
$columnstringThe column to index calculation (default: *).

Return Value:

int|false - Return total number of records in table, otherwise false if execution failed.

Throws:


sum

Calculate the total sum of a numeric column in the table.

public sum(string $column): int|float|false

Parameters:

ParameterTypeDescription
$columnstringThe column to calculate the sum.

Return Value:

int|float|false - Return total sum columns, otherwise false if execution failed.

Throws:


average

Calculate the average value of a numeric column in the table.

public average(string $column): int|float|false

Parameters:

ParameterTypeDescription
$columnstringThe column to calculate the average.

Return Value:

int|float|false - Return total average of columns, otherwise false if execution failed.

Throws:


find

Select next or a single record from database table.

public find(array<int,string> $columns = ['*']): mixed

Parameters:

ParameterTypeDescription
$columnsarray<int,string>An array of table columns to return (default: [*]).

Return Value:

mixed - Return selected single row, otherwise false if execution failed.

Throws:


select

To select multiple records from table, call select method after you have prepared other conditional methods.

public select(array<int,string> $columns = ['*']): mixed

Parameters:

ParameterTypeDescription
$columnsarray<int,string>An array of table columns to return (default: [*]).

Return Value:

mixed - Return selected rows, otherwise false if execution failed.

Throws:


fetch

Fetch records from database table.The fetch method allows you to retrieve records from table, by passing desired fetch mode and result type.

public fetch(string $result = 'all', int $mode = FETCH_OBJ, array<int,string> $columns = ['*']): object|array|int|float|false|null

Parameters:

ParameterTypeDescription
$resultstringThe fetch result type to use, default all (e.g, next or all).
$modeintThe fetch result mode (e.g, FETCH_*).
$columnsarray<int,string>An array of table columns to return (default: [*]).

Return Value:

object|array|int|float|false|null - Return selected records, otherwise false if execution failed.

Throws:


stmt

Returns the prepared query statement based on the conditions built using previous method calls.

Note: To obtain a prepared statement object, do not call any of the following methods: total, sum, find, select, average, fetch, or execute. Calling any of these methods will result in stmt returning NULL.

public stmt(array<int,string> $columns = ['*']): \Luminova\Interface\DatabaseInterface;

Parameters:

ParameterTypeDescription
$columnsarray<int,string>An array of table columns to return (default: [*]).

Return Value:

\Luminova\Interface\DatabaseInterface - Returns the prepared statement object if the query is successful; otherwise, it returns NULL.

Throws:

Example

For more information about the methods available in the statement object, see the Database Driver Documentation.

<?php
$tbl = $builder->table('users');
$tbl->where('programming_language', '=', 'PHP');
$stmt = $tbl->stmt();

// Use the statement object as needed 
var_export($stmt->getAll());
$stmt->free();

update

Updates records in the database table with new data. You can pass an associative array of column-value pairs directly to the update method or use the set method to stage your columns and values for updating.

public update(array<string,mixed> $setValues = []): int|bool

Parameters:

ParameterTypeDescription
$setValuesarray<string,mixed>An optional associative array of columns and values to update.

Return Value:

int - Returns the number of affected rows.

Throws:

Note: If you pass an array value to an update field, it will be automatically converted to a JSON string. Ensure that your table field is designed to accept JSON strings before passing an array.


delete

To delete record from database table.

public delete(): int

Return Value:

int - Return number of affected rows.

Throws:


transaction

Begins a transaction with optional read-only isolation level and SAVEPOINT for PDO.

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

Parameters:

ParameterTypeDescription
$flagsintOptional flags to set transaction properties (default: 0).
For MySQLi: use MYSQLI_TRANS_START_READ_ONLY to set transaction as read-only.
For PDO: No predefined flags, specify 4 to create read-only isolation.
$namestring|nullOptional transaction name to use.
If provided in PDO, SAVEPOINT will be created with name instead.

Return Value:

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

Throws:

Note:

  • If $flags is set to 4 in PDO, which is equivalent to MYSQLI_TRANS_START_READ_ONLY, a read-only isolation level will be established. If the transaction starts successfully, it will return true.

  • If $name is specified in PDO, a SAVEPOINT will be created. If the savepoint creation is successful, the transaction will return true.


commit

Commits a transaction.

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

Parameters:

ParameterTypeDescription
$flagsintOptional flags to set transaction properties. (Default: 0).
Only supported in MySQLi.
$namestring|nullOptional name.
Only supported in MySQLi.

Return Value:

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


rollback

Rolls back the current transaction or to a specific name while in PDO uses SAVEPOINT.

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

Parameters:

ParameterTypeDescription
$flagsintOptional flags to set transaction properties. (Default: 0).
Only supported in MySQLi.
$namestring|nullOptional name.
If provided in PDO, rolls back to the SAVEPOINT named

Return Value:

bool - Return true if rolled back was successful, otherwise false.

Throws:


truncate

Delete all records in a table, and reset table auto increment to 1.

public truncate(bool $transaction = true): bool

Parameters:

ParameterTypeDescription
$transactionboolWeather to use transaction (default: true).

Return Value:

bool - Return true truncation was completed, otherwise false.

Throws:


temp

Creates a temporary table, it automatically copies all records from the main table into it.

public temp(bool $transaction = true): bool

Parameters:

ParameterTypeDescription
$transactionboolWhether to use a transaction (default: true).

Return Value:

bool - Returns true if the operation was successful; false otherwise.

Throws:

Example

<?php
if ($builder->table('users')->temp()) {
   $data = $builder->table('temp_users')->select();
}

Note: Temporary tables are automatically deleted when the current session ends. You won't find these tables in phpMyAdmin or any other database manager as they are session-specific. To query a temporary table, use the temp_ prefix before the main table name.


exec

Execute an SQL query string directly without bind nor prepared statement and return the number of affected rows.

public exec(string $query): int

Parameters:

ParameterTypeDescription
$querystringThe SQL query string to execute.

Return Value:

int - Return number affected rows.

Throws:


drop

Drop database table if table exists.

public drop(bool $transaction = false): bool

Parameters:

ParameterTypeDescription
$transactionboolWhether to use a transaction (default: false).

Return Value:

bool - Return true if table was successfully dropped, false otherwise.

Throws:


dropTemp

Drop a temporal database table if table exists.

public dropTemp(bool $transaction = false): bool

Parameters:

ParameterTypeDescription
$transactionboolWhether to use a transaction (default: false).

Return Value:

bool - Return true if table was successfully dropped, false otherwise.

Throws:


manager

Returns the shared instance of the database manager class, initialized with the current database connection. The manager object can be used to create database table backups or export entire tables in CSV or JSON format.

public manager(): \Luminova\Database\Manager

Return Value:

\Luminova\Database\Manager - An instance of the database manager class.

See Also:

Database Manager - Detailed documentation on the database manager.

**

export

Exports the current database table and prompts the browser to download it as a JSON or CSV file. This method utilizes the manager class for the export operation.

public export(string $as = 'csv', ?string $filename = null, array $columns = ['*']): bool

Parameters:

ParameterTypeDescription
$asstringThe format to export the table in, either csv or json.
$filenamestring|nullThe name of the file to download (optional).
$columnsarrayThe table columns to export (default: all columns).

Return Value:

bool - Returns true if the export is successful, false otherwise.

Throws:


backup

Creates a backup of the current database table and saves the backup file in the writeable/backups directory. This method utilizes the manager class for the backup operation.

public backup(?string $filename = null): bool

Parameters:

ParameterTypeDescription
$filenamestring|nullOptional name of the backup file (default: null). If not provided, table name and timestamp will be used.

Return Value:

bool - Return true if backup is successful, false otherwise.

Throws:


errors

Retrieves the last query execution errors.

public errors(): array

Return Value:

array - Return an array containing error information from the last executed query.


debug

Enables query string debugging, allowing you to preview the compiled query string before execution.

public debug(): self

Return Value:

self - Returns the current instance of the builder class.

Note: In production environments, the query string will be logged at the debug level. When debugging is enabled, query execution will intentionally fail, and caching will be disabled.


printDebug

Returns detailed debug information about the query string, including formats for MySQL and PDO placeholders, as well as the exact binding mappings for each column.

public printDebug(): array<string,mixed>

Return Value:

array<string,mixed> - Return an array containing the debug information for the query string.


dump

Outputs the debug information for the last executed query.

public dump(): ?bool

Return Value:

bool|null - Returns true on success, false on failure, or null if no debug information is available.


reset

Resets query conditions, parameters, and frees database resources, preparing the builder for the next query execution.

public reset(): void

free

Releases the statement cursor and sets the statement object to null.

public free(): void

close

Closes the database connection and releases the statement cursor.

public close(): void