Documentation

MySql
in package

MilkCore

MySQL Database Management Class

This class provides a comprehensive interface for interacting with MySQL 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

Properties

$dbname : string: Name of the current database
$error : bool: Error flag indicating if the last query resulted in an error
$fields_list : 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
$mysqli : mysqli|null: MySQLi connection object
$prefix : string: Table prefix used for all database operations
$tablesList : array<string|int, mixed>: List of tables in the database
$type : string: Type of the database
$viewsList : array<string|int, mixed>: List of views in the database

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 MySQL 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() : bool: Drops a view if it exists
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 (MySQL)
query() : mixed: 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 (removes all data and resets auto-increment)
update() : bool: Updates records in a table based on conditions
yield() : Generator|null: Executes a query and returns a generator for iterating through results

$dbname

Name of the current database


    public
        string
    $dbname
     = ''

$error

Error flag indicating if the last query resulted in an error


    public
        bool
    $error
     = false

$fields_list

List of fields for each table


    public
        array<string|int, mixed>
    $fields_list
     = 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
     = ""

$mysqli

MySQLi connection object


    public
        mysqli|null
    $mysqli
     = null

$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()

$type

Type of the database


    public
        string
    $type
     = 'mysql'

$viewsList

List of views in the database


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

__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

connect()

Connects to a MySQL database


    public
                    connect(string $ip, string $login, string $pass, string $dbname) : bool

Parameters

$ip : string: Database server hostname or IP
$login : string: Database username
$pass : string: Database password
$dbname : string: Database name

Tags

example

// Connect to local database
$connected = $db->connect('localhost', 'root', 'password', 'my_database');

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 $table_name[, bool $cache = true ]) : array<string|int, mixed>

Parameters

$table_name : string
$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., "int(11)"

// Get primary keys
$primaryKeys = $structure['keys']; // e.g., ["id"]

Return values

array<string|int, mixed> —

Associative array with 'fields' and 'keys' elements

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()

Drops a view if it exists


    public
                    drop_view(string $view) : bool

Parameters

$view : string: The view name

Return values

bool —

True on success, false on failure

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 (MySQL)


    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 ]) : mixed

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

mixed —

Query MySQLResult 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 current 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

Tags

example

// Set fields list for users table
$db->set_fields_list('myprefix_table', ['id' => 'int(11)', 'username' => 'varchar(250)'], 'id');

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

truncate_table()

Truncates a table (removes all data and resets auto-increment)


    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

MySql in package MilkCore

Tags

Table of Contents

Properties

Methods

Properties

$dbname

$error

$fields_list

$last_error

$last_query

$mysqli

$prefix

$tablesList

$type

$viewsList

Methods

__construct()

Parameters

affected_rows()

Return values

begin()

Tags

check_connection()

Return values

close()

commit()

Tags

connect()

Parameters

Tags

Return values

delete()

Parameters

Tags

Return values

describes()

Parameters

Tags

Return values

drop_table()

Parameters

Return values

drop_view()

Parameters

Return values

get_columns()

Parameters

Tags

Return values

get_last_error()

Return values

get_results()

Parameters

Tags

Return values

get_row()

Parameters

Tags

Return values

get_tables()

Parameters

Return values

get_var()

Parameters

Tags

Return values

get_view_definition()

Parameters

Return values

get_views()

Parameters

Return values

insert()

Parameters

Tags

Return values

insert_id()

Return values

last_query()

MySql
in package

MilkCore