MilkCore

SQLite
in package

SQLite Database Management Class

This class provides a comprehensive interface for interacting with SQLite databases. It handles connections, queries, and database structure operations with a focus on security through prepared statements and error handling.

Tags
example 
// Connect to database
$db = milkCore\Get::get('db');

// Run a simple query
$results = $db->query("SELECT * FROM users WHERE active = 1");

// Run a prepared statement
$user = $db->query(
    "SELECT * FROM users WHERE id = ?",
    [123]
);

// Insert data
$db->insert('users', [
    'username' => 'john_doe',
    'email' => 'john@example.com',
    'created_at' => date('Y-m-d H:i:s')
]);

// Using table prefix
$results = $db->query("SELECT * FROM #__users"); // Will be converted to prefix_users

Table of Contents

Properties

$dbname  : string
Name of the current database file
$error  : bool
Error flag indicating if the last query resulted in an error
$fieldsList  : array<string|int, mixed>
List of fields for each table
$last_error  : string
Last error message from database operations
$last_query  : string
The last SQL query that was executed
$prefix  : string
Table prefix used for all database operations
$tablesList  : array<string|int, mixed>
List of tables in the database
$type  : mixed

Methods

__construct()  : mixed
Constructor that initializes the connection with an optional table prefix
affected_rows()  : int
Returns the number of affected rows from the last query
begin()  : void
Begins a database transaction
check_connection()  : bool
Checks if the database connection is established
close()  : void
Closes the database connection
commit()  : void
Commits the current transaction, making all changes permanent
connect()  : bool
Connects to a SQLite database
delete()  : bool
Deletes records from a table based on conditions
describes()  : array<string|int, mixed>
Returns an array with table fields and primary keys
drop_table()  : bool
Drops a table if it exists
drop_view()  : mixed
get_columns()  : array<string|int, mixed>
Returns a list of columns for a table (uses cache)
get_last_error()  : string
get_results()  : array<string|int, mixed>|null
Executes a SELECT query and returns all results as an array of objects
get_row()  : object|null
Executes a SELECT query and returns a single row as an object
get_tables()  : array<string|int, mixed>
Returns a list of tables in the database
get_var()  : string|null
Executes a SELECT query and returns a single value from the first row
get_view_definition()  : string|null
Returns the SQL definition of a view
get_views()  : array<string|int, mixed>
Returns a list of views in the database
insert()  : bool|int
Inserts a record into a table
insert_id()  : integer
Returns the ID generated by the last INSERT query
last_query()  : string
Returns the last executed query
multi_query()  : bool
Executes multiple SQL statements separated by semicolons
non_buffered_query()  : Generator|null
Executes a query for non-buffered results from a table
qn()  : string
Escapes and quotes a table or column name for safe use in queries
query()  : SQLiteResult|false
Executes an SQL query with optional parameters
quote()  : string
Quotes a value for safe use in queries
rename_table()  : bool
Renames a table
save()  : bool|int
Updates a record if it exists, or inserts it if it doesn't
set_fields_list()  : void
Sets field definitions for a table to avoid database queries
show_create_table()  : array<string|int, mixed>
tear_down()  : void
Rolls back the current transaction, canceling all changes
truncate_table()  : bool
Truncates a table
update()  : bool
Updates records in a table based on conditions
yield()  : Generator|null
Executes a query and returns a generator for iterating through results

Properties

$dbname

Name of the current database file

public string $dbname = ''

$error

Error flag indicating if the last query resulted in an error

public bool $error = false

$fieldsList

List of fields for each table

public array<string|int, mixed> $fieldsList = array()

$last_error

Last error message from database operations

public string $last_error = ''

$last_query

The last SQL query that was executed

public string $last_query = ""

$prefix

Table prefix used for all database operations

public string $prefix = ''

$tablesList

List of tables in the database

public array<string|int, mixed> $tablesList = array()

Methods

__construct()

Constructor that initializes the connection with an optional table prefix

public __construct([string $prefix = '' ]) : mixed
Parameters
$prefix : string = ''

Optional prefix for database tables

affected_rows()

Returns the number of affected rows from the last query

public affected_rows() : int
Return values
int —

Number of affected rows

begin()

Begins a database transaction

public begin() : void

Use transactions when you need to perform multiple related operations that must all succeed or fail together as a single unit.

Tags
example 
try {
    $db->begin(); // Start transaction

    $db->insert('orders', ['total' => 100.00]);
    $orderId = $db->insert_id();

    $db->insert('order_items', [
        'order_id' => $orderId,
        'product_id' => 123,
        'quantity' => 1
    ]);

    $db->commit(); // Confirm changes
} catch (Exception $e) {
    $db->tear_down(); // Cancel on error
    echo "Transaction failed: " . $e->getMessage();
}

check_connection()

Checks if the database connection is established

public check_connection() : bool
Return values
bool —

True if connected, false otherwise

close()

Closes the database connection

public close() : void

commit()

Commits the current transaction, making all changes permanent

public commit() : void
Tags
see
begin()

For usage example

connect()

Connects to a SQLite database

public connect(string $dbname) : bool
Parameters
$dbname : string

Database file path

Tags
example 
// Connect to SQLite database (creates file if not exists)
$connected = $db->connect('/path/to/database.db');

if (!$connected) {
    echo "Connection failed: " . $db->last_error;
}
Return values
bool —

True on success, false on failure

delete()

Deletes records from a table based on conditions

public delete(string $table, array<string|int, mixed> $where) : bool

The where parameter is an associative array with column names as keys and values to match. Only supports equality conditions.

Parameters
$table : string

The table name

$where : array<string|int, mixed>

Associative array of conditions (column => value)

Tags
example 
// Delete a user by ID
$success = $db->delete('users', ['id' => 123]);

// Delete with multiple conditions
$success = $db->delete('users', [
    'status' => 'inactive',
    'last_login' => '2020-01-01'
]);
Return values
bool —

True on success, false on failure

describes()

Returns an array with table fields and primary keys

public describes(string $tableName[, bool $cache = true ]) : array<string|int, mixed>
Parameters
$tableName : string

The table name

$cache : bool = true

Whether to use cached results (default true)

Tags
example 
// Get table structure
$structure = $db->describes('users');

// Access field types
$idType = $structure['fields']['id']; // e.g., "INTEGER"

// Get primary keys
$primaryKeys = $structure['keys']; // e.g., ["id"]
Return values
array<string|int, mixed> —

Associative array with 'fields' and 'keys' elements ['fields', 'keys', 'struct'];

drop_table()

Drops a table if it exists

public drop_table(string $table) : bool
Parameters
$table : string

The table name

Return values
bool —

True on success, false on failure

drop_view()

public drop_view(mixed $view) : mixed
Parameters
$view : mixed

get_columns()

Returns a list of columns for a table (uses cache)

public get_columns(string $table_name[, bool $force_reload = false ]) : array<string|int, mixed>

Similar to SHOW COLUMNS but with caching capability

Parameters
$table_name : string

The table name

$force_reload : bool = false

Whether to force refresh the cache (default false)

Tags
example 
// Get columns for users table
$columns = $db->get_columns('users');

// Force refresh the cache
$columns = $db->get_columns('users', true);
Return values
array<string|int, mixed> —

Column information for the table

get_last_error()

public get_last_error() : string
Return values
string —

The last error message

get_results()

Executes a SELECT query and returns all results as an array of objects

public get_results(string $sql[, array<string|int, mixed>|null $params = null ]) : array<string|int, mixed>|null
Parameters
$sql : string

The SQL query to execute

$params : array<string|int, mixed>|null = null

Optional parameters for prepared statements

Tags
example 
// Get all active users
$users = $db->get_results("SELECT * FROM #__users WHERE status = ?", ['active']);

// Process results
foreach ($users as $user) {
    echo $user->username;
}
Return values
array<string|int, mixed>|null —

Array of objects containing query results or null on error

get_row()

Executes a SELECT query and returns a single row as an object

public get_row(string $sql[, array<string|int, mixed>|null $params = null ][, int $offset = 0 ]) : object|null
Parameters
$sql : string

The SQL query to execute

$params : array<string|int, mixed>|null = null

Optional parameters for prepared statements

$offset : int = 0

Row offset (default 0)

Tags
example 
// Get a user by ID
$user = $db->get_row("SELECT * FROM #__users WHERE id = ?", [1]);
echo $user->username;

// Get the second row from results
$user = $db->get_row("SELECT * FROM #__users ORDER BY id", null, 1);
Return values
object|null —

Object containing the row data or null if not found or on error

get_tables()

Returns a list of tables in the database

public get_tables([bool $cache = true ]) : array<string|int, mixed>
Parameters
$cache : bool = true

Whether to use cached results (default true)

Return values
array<string|int, mixed> —

List of table names

get_var()

Executes a SELECT query and returns a single value from the first row

public get_var(string $sql[, array<string|int, mixed>|null $params = null ][, int $offset = 0 ]) : string|null
Parameters
$sql : string

The SQL query to execute

$params : array<string|int, mixed>|null = null

Optional parameters for prepared statements

$offset : int = 0

Row offset (default 0)

Tags
example 
// Count users
$count = $db->get_var("SELECT COUNT(*) FROM #__users");

// Get a username by ID
$username = $db->get_var("SELECT username FROM #__users WHERE id = ?", [1]);
Return values
string|null —

The first column value or null if not found or on error

get_view_definition()

Returns the SQL definition of a view

public get_view_definition(string $view_name) : string|null
Parameters
$view_name : string

The name of the view

Return values
string|null —

The SQL definition of the view or null if not found

get_views()

Returns a list of views in the database

public get_views([bool $cache = true ]) : array<string|int, mixed>
Parameters
$cache : bool = true

Whether to use cached results (default true)

Return values
array<string|int, mixed> —

List of view names

insert()

Inserts a record into a table

public insert(string $table, array<string|int, mixed> $data) : bool|int
Parameters
$table : string

The table name

$data : array<string|int, mixed>

Associative array of column names and values

Tags
example 
// Insert a new user
$userId = $db->insert('users', [
    'username' => 'john_doe',
    'email' => 'john@example.com',
    'created_at' => date('Y-m-d H:i:s')
]);

if ($userId) {
    echo "User created with ID: $userId";
} else {
    echo "Error: " . $db->last_error;
}
Return values
bool|int —

Insert ID on success, false on failure

insert_id()

Returns the ID generated by the last INSERT query

public insert_id() : integer
Return values
integer

last_query()

Returns the last executed query

public last_query() : string
Return values
string —

The last executed query

multi_query()

Executes multiple SQL statements separated by semicolons

public multi_query(string $sql) : bool

This is asynchronous and doesn't wait for server response

Parameters
$sql : string

Multiple SQL statements separated by semicolons

Tags
example 
// Execute multiple statements
$db->multi_query("
    CREATE TABLE temp (id INT);
    INSERT INTO temp VALUES (1), (2), (3);
    SELECT * FROM temp;
");
Return values
bool —

True on success, false on failure

non_buffered_query()

Executes a query for non-buffered results from a table

public non_buffered_query(string $table[, bool $assoc = true ]) : Generator|null

Cannot use prepared statements with this method. Useful for processing very large tables with minimal memory usage.

Parameters
$table : string

The table name

$assoc : bool = true

Whether to return associative arrays (true) or indexed arrays (false)

Tags
example 
// Process all records from a large table as associative arrays
foreach ($db->non_buffered_query("large_table") as $key => $row) {
    echo $row['column_name'];
}
Return values
Generator|null —

Generator for iterating through results or null on error

qn()

Escapes and quotes a table or column name for safe use in queries

public qn(string $val) : string

Handles table.column notation and AS aliases Evita di quotare nuovamente campi già quotati

Parameters
$val : string

The name to quote

Return values
string —

The quoted name

query()

Executes an SQL query with optional parameters

public query(string $sql[, array<string|int, mixed>|null $params = null ]) : SQLiteResult|false

This method supports prepared statements when parameters are provided. Errors can be checked with $this->error after execution.

Parameters
$sql : string

The SQL query to execute

$params : array<string|int, mixed>|null = null

Optional parameters for prepared statements

Tags
example 
// Simple query
$result = $db->query("SELECT * FROM #__users");

// Prepared statement
$result = $db->query("SELECT * FROM #__users WHERE id = ?", [1]);

// Multiple parameters
$result = $db->query(
    "SELECT * FROM #__users WHERE username = ? AND status = ?",
    ['john', 'active']
);

// JOIN example
$result = $db->query(
    "SELECT u.*, p.name FROM #__users u JOIN #__profiles p ON u.id = p.user_id WHERE u.id = ?",
    [1]
);
Return values
SQLiteResult|false —

SQLiteResult object or false on error

quote()

Quotes a value for safe use in queries

public quote(string $val) : string
Parameters
$val : string

The value to quote

Return values
string —

The quoted value

rename_table()

Renames a table

public rename_table(string $table_name, string $new_name) : bool
Parameters
$table_name : string

The table name

$new_name : string

The new table name

Return values
bool —

True on success, false on failure

save()

Updates a record if it exists, or inserts it if it doesn't

public save(string $table, array<string|int, mixed> $data, array<string|int, mixed> $where) : bool|int
Parameters
$table : string

The table name

$data : array<string|int, mixed>

Associative array of column names and values

$where : array<string|int, mixed>

Associative array of conditions (column => value)

Tags
example 
// Update user if exists, insert if not
$result = $db->save(
    'users',
    ['username' => 'john_doe', 'email' => 'john@example.com'],
    ['id' => 123]
);

// Create or update based on username
$result = $db->save(
    'settings',
    ['value' => 'new_value', 'updated_at' => date('Y-m-d H:i:s')],
    ['key' => 'site_title']
);
Return values
bool|int —

Insert ID on insert, true on update success, false on failure

set_fields_list()

Sets field definitions for a table to avoid database queries

public set_fields_list(string $tableName, array<string|int, mixed> $fields, array<string|int, mixed> $primaryKey) : void

Used as a cache mechanism for table structure

Parameters
$tableName : string

The table name

$fields : array<string|int, mixed>

Associative array of field names and types

$primaryKey : array<string|int, mixed>

Array of primary key field names

show_create_table()

public show_create_table(string $table_name) : array<string|int, mixed>
Parameters
$table_name : string
Return values
array<string|int, mixed> —

[type, sql]

tear_down()

Rolls back the current transaction, canceling all changes

public tear_down() : void
Tags
see
begin()

For usage example

truncate_table()

Truncates a table

public truncate_table(string $table_name) : bool
Parameters
$table_name : string

The table name

Return values
bool —

True on success, false on failure

update()

Updates records in a table based on conditions

public update(string $table, array<string|int, mixed> $data, array<string|int, mixed> $where[, int $limit = 0 ]) : bool
Parameters
$table : string

The table name

$data : array<string|int, mixed>

Associative array of column names and new values

$where : array<string|int, mixed>

Associative array of conditions (column => value)

$limit : int = 0

Maximum number of records to update (0 for no limit)

Tags
example 
// Update a user by ID
$success = $db->update(
    'users',
    ['username' => 'new_username', 'email' => 'new@example.com'],
    ['id' => 123]
);

// Update with limit
$success = $db->update(
    'users',
    ['status' => 'inactive'],
    ['last_login' => '2020-01-01'],
    10 // Only update 10 records
);
Return values
bool —

True on success, false on failure

yield()

Executes a query and returns a generator for iterating through results

public yield(string $sql[, array<string|int, mixed>|null $params = null ]) : Generator|null

Useful for processing large result sets without loading all data into memory.

Parameters
$sql : string

The SQL query to execute

$params : array<string|int, mixed>|null = null

Optional parameters for prepared statements

Tags
example 
// Process large result sets one row at a time
foreach ($db->yield("SELECT * FROM #__large_table") as $row) {
    echo $row->column_name;
}
Return values
Generator|null —

Generator for iterating through results or null on error


        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results