HelPHP

DB
in package

Tags
class

DB

Made for MariaDB and MySQL, but should work with any SQL server, this class is used to manage the connection to the SQL server, all kind of queries, and the management of the results. It also manages the connection to a master/slave cluster of SQL servers, allowing to switch between them in case of failure.
It can also manage a centralized database, which is useful for multi-node applications with centralized user accounts, user groups etc. All connections are maintained as global variables, so you can access them from anywhere in the application. Config variables are set in the config/db.php file of the helPHP instance.
default charset is utf8mb4, which is the recommended charset for MySQL and MariaDB. see constructor for the parameters to pass to create an instance of this class. or create_instance() to create a classic instance of the DB class for a helPHP instance.

basic usage :

$DB = new DB(array('host'=>'localhost', 'user'=>'myuser', 'password'=>'mypassword', 'dbname'=>'mydb', 'table_prefix'=>'mytableprefix')); $DB->connect(); // to establish the connection $DB->query('SELECT * FROM mytable'); // to execute a query $DB->prepared_query('SELECT * FROM mytable WHERE id = ?','i', [$id]); // to execute a prepared query (query,typedef,params) $DB->close(); // to close the connection when you are done

Table of Contents

Properties

$_last_count  : int
total number of lines (without the limit) of the last request made in gettable_data this is used to manage the pagination of the results
$_last_limit_count  : int
last limit count for LIMIT queries
$_last_limit_start  : int
last limit start for LIMIT queries
$connected  : bool
indicates if the connection to the SQL server is established or if the connection to the slave server is established in master/slave mode
$db_name  : string
database name to use.
$host  : string
mysql server hostname/ip for the slave server in cluster mode or for the only server in non-cluster mode
$last_error  : string
last error message from the SQL server
$last_insert_id  : int
value of the last ID created via an INSERT request
$link_master_SQL  : int
pointer to the Master SQL connection in master/slave mode,
$link_SQL  : resource
pointer to the SQL connection in default mode, this is the only connection used in master/slave mode, this is the connection to the slave server in case of failure of the master server, this connection will be used as the master server connection
$master_connected  : bool
indicates if the connection to the master server is established
$master_host  : string
mysql master server hostname/ip in cluster mode
$master_slave_mode  : bool
indicates if the connection is in master/slave mode we write on the master server and read on both the slave and the master server in case of faillure of the master we must switch it off and reconfigure to connect to surviving mysql slave server
$password  : string
mysql server password for the master server in cluster mode or for the only server in non-cluster mode (be careful, this is not the password of the slave server in cluster mode !)
$query_count  : int
total number of requests
$query_count_debug  : array<string|int, mixed>
counting of requests by type (insert, select, count, update, etc ...)
$query_error_count  : int
total number of failed requests
$query_time  : int
total duration of requests in seconds
$query_time_debug  : array<string|int, mixed>
total duration of each type of request
$slave_password  : string
password for the slave server in cluster mode
$slave_user  : string
username for the slave server in cluster mode
$table_prefix  : string
table prefix to use for the SQL queries This is used to avoid conflicts with other applications using the same database.
$user  : string
mysql server username for the master server in cluster mode or for the only server in non-cluster mode (be careful, this is not the user of the slave server in cluster mode !)

Methods

__construct()  : void
Constructor of the DB class.
close()  : void
Close the connection to the SQL server.
connect()  : bool
Check if the connection to the master SQL server is established.
create_instance()  : bool
Create an instance of the DB class in $DB global variable.
create_table_from_json()  : mixed
delete_associations()  : mixed
Delete all associations for a given ID in a module.
disconnect()  : void
will disconnect from the SQL server.
duplicate_line()  : int
Duplicate a line in a table.
format_string_list()  : string
Formats a list of strings into a comma-separated string with quotes around each value.
get_association_tables()  : array<string|int, mixed>
Get a list of association tables for a given module.
is_connected()  : bool
Check if the connection to the SQL server is established.
json_from_sql()  : string
Return the descriptive json of a table.
last_insert_id()  : int
Get the last inserted ID from the last INSERT query.
last_pages_data()  : array<string|int, mixed>
Returns an array with the data needed to manage the pagination of the results.
microtime_float()  : float
Returns the current time in seconds as a float.
prepared_query()  : array<string|int, mixed>|bool
This function is used to execute a prepared query with parameters and return the result as an associative array.
prepared_query_line()  : array<string|int, mixed>|null
Execute a prepared SQL query and return the first row of the result set.
prepared_query_list()  : array<string|int, mixed>|null
Execute a prepared SQL query and return the result as a list of values.
prepared_query_list_assoc()  : array<string|int, mixed>|null
Execute a prepared SQL query and return the result as an associative array with multiple values per key.
prepared_query_value()  : mixed
Execute a prepared SQL query and return the first value of the first row.
query()  : mixed
Execute a SQL query.
query_line()  : array<string|int, mixed>|null
Execute a SQL query and return the first row of the result set as an associative array.
query_list()  : array<string|int, mixed>|null
Execute a SQL query and return the result as an array of associative arrays.
query_list_assoc()  : array<string|int, mixed>|null
Execute a SQL query and return the result as an associative array with multiple values per key.
query_list_for_select()  : array<string|int, mixed>|null
Execute a SQL query and return the result as an associative array for use in a select input. (generaly a H::select) This function is used to execute a query that returns multiple rows, such as a SELECT query.
query_value()  : mixed
Execute a SQL query and return the first value of the first row.
set_data_base()  : bool
Set the database to use.
sql_from_json()  : string
Make sql query to create table or update from a json description of the table The json have to be to the following format: - { - tables: [ - { - name: "my_table", - fields: [ - { - name: "field_name", - type: varchar|int|tinyint|datetime... (sql type) - limit: 255 (an integer) - null: (optional) boolean, true to accept null value, false to add NOT NULL - default: (optional), the default value - index: (optional) fulltext|index - primary: (optional) only one by table, autoincrement - }, - ... - }, - ... - ], - entries: [ - { - table: 'block_data', - fields: [ - field_name: [ - name: 'field_name', - type: 's' or 'i' or 'd', - value: 'field_value', - ] - ... - ], - languages: [ - short: [ - field: [ - en: 'trad_en', - fr: 'trad_fr', - ... - ]... - ], - long: [ - field: [ - en: 'trad_en', - fr: 'trad_fr', - ... - ]... - ] - ], - categories: [ - field_identifier: [ - // the first element of this list in the category than contains the element - // next elements are each time the parent of the previous one - [ series: "category's serie", name: "category's name"], - [ series: "category's serie", name: "category's name"], This one is the parent of the one before - ... - ], - ... - ] - } - ... - ] - }
synch_other_DB()  : bool|int
Synchronize a table with another database.
table()  : string
Returns the name of a table with the prefix if it is set.
table_data()  : array<string|int, mixed>|null
Get the structure of a table.
table_exists()  : bool
Check if a table exists in the database.
table_field_list()  : array<string|int, mixed>
Returns a list of fields of a given table.
test_connection()  : mixed
replace mysqli_ping

Properties

$_last_count

total number of lines (without the limit) of the last request made in gettable_data this is used to manage the pagination of the results

public int $_last_count = 0

$_last_limit_count

last limit count for LIMIT queries

public int $_last_limit_count = 0

$_last_limit_start

last limit start for LIMIT queries

public int $_last_limit_start = 0

$connected

indicates if the connection to the SQL server is established or if the connection to the slave server is established in master/slave mode

public bool $connected = false

$db_name

database name to use.

public string $db_name = ""

$host

mysql server hostname/ip for the slave server in cluster mode or for the only server in non-cluster mode

public string $host = ""

$last_error

last error message from the SQL server

public string $last_error = ''

$last_insert_id

value of the last ID created via an INSERT request

public int $last_insert_id = 0

$link_master_SQL

pointer to the Master SQL connection in master/slave mode,

public int $link_master_SQL = 0

$link_SQL

pointer to the SQL connection in default mode, this is the only connection used in master/slave mode, this is the connection to the slave server in case of failure of the master server, this connection will be used as the master server connection

public resource $link_SQL = false

$master_connected

indicates if the connection to the master server is established

public bool $master_connected = false

$master_host

mysql master server hostname/ip in cluster mode

public string $master_host = ""

$master_slave_mode

indicates if the connection is in master/slave mode we write on the master server and read on both the slave and the master server in case of faillure of the master we must switch it off and reconfigure to connect to surviving mysql slave server

public bool $master_slave_mode = false

$password

mysql server password for the master server in cluster mode or for the only server in non-cluster mode (be careful, this is not the password of the slave server in cluster mode !)

public string $password = ""

$query_count

total number of requests

public int $query_count = 0

$query_count_debug

counting of requests by type (insert, select, count, update, etc ...)

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

$query_error_count

total number of failed requests

public int $query_error_count = 0

$query_time

total duration of requests in seconds

public int $query_time = 0

$query_time_debug

total duration of each type of request

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

$slave_password

password for the slave server in cluster mode

public string $slave_password = ""

$slave_user

username for the slave server in cluster mode

public string $slave_user = ""

$table_prefix

table prefix to use for the SQL queries This is used to avoid conflicts with other applications using the same database.

public string $table_prefix = ''

$user

mysql server username for the master server in cluster mode or for the only server in non-cluster mode (be careful, this is not the user of the slave server in cluster mode !)

public string $user = ""

Methods

__construct()

Constructor of the DB class.

public __construct([mixed $params = array() ]) : void

This function initializes the connection parameters to the SQL server. ex : $myDb = new DB(array('host'=>'localhost', 'user'=>'myuser', 'password'=>'mypassword', 'dbname'=>'mydb', 'table_prefix'=>'mytableprefix')); can be followeb by $myDb->connect() to establish the connection. then, after usage : $myDb->close() to close the connection.

Parameters
$params : mixed = array()
Return values
void —

don't return anything

close()

Close the connection to the SQL server.

public close() : void

This function disconnects from the SQL server and sets the connection variables to null. Don't forget to call this function when you are done with the database connection to free resources.

connect()

Check if the connection to the master SQL server is established.

public connect() : bool

if not connected, it will try to connect. and in case of failure, it will try to set a master server with the survivor.

Return values
bool —

true if the connection to the server is established, false otherwise

create_instance()

Create an instance of the DB class in $DB global variable.

public static create_instance() : bool

This function checks if the global $DB variable is set, and if not, it creates a new instance of the DB class with the parameters defined in the CONFIG_DB class. if the CONFIG_DB::DB_CENTRAL is set, it will also create a global $DB_CENTRAL variable that will be used to connect to the centralized database.

Return values
bool —

$can_be_disconnected

create_table_from_json()

public static create_table_from_json(mixed $json) : mixed
Parameters
$json : mixed

delete_associations()

Delete all associations for a given ID in a module.

public delete_associations(int $id, string $module_name[, string $other_module = '' ]) : mixed

This function is used to delete all associations for a given ID in a module. It will delete records from all association tables related to the module.

Parameters
$id : int

The ID of the record to delete associations for

$module_name : string

The name of the module

$other_module : string = ''

Optional, the name of another module to filter association tables

disconnect()

will disconnect from the SQL server.

public disconnect() : void

or from the master and slave SQL server in master/slave mode.

duplicate_line()

Duplicate a line in a table.

public duplicate_line(string $table, mixed $original_line[, array<string|int, mixed>|null $replace_fields = null ]) : int

This function is used to duplicate a line in a table by copying all fields except the ID. If the original line is not found, it will log an error and return 0.

Parameters
$table : string

The name of the table to duplicate the line in

$original_line : mixed

The original line to duplicate, can be an ID or an associative array

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

An associative array of fields to replace in the duplicated line, or null if no fields to replace

Return values
int —

The ID of the newly duplicated line, or 0 if the original line was not found

format_string_list()

Formats a list of strings into a comma-separated string with quotes around each value.

public format_string_list(mixed $a[, string $quote = '"' ]) : string

This function is used to format a list of strings for use in SQL queries.

Parameters
$a : mixed

A string or an array of strings to format

$quote : string = '"'

The quote character to use around each value (default is double quote)

Return values
string —

A comma-separated string with quotes around each value

get_association_tables()

Get a list of association tables for a given module.

public get_association_tables(mixed $module_name[, string $other_module = '' ]) : array<string|int, mixed>
Parameters
$module_name : mixed
$other_module : string = ''
Return values
array<string|int, mixed> —

An array of association tables related to the module.

is_connected()

Check if the connection to the SQL server is established.

public is_connected() : bool
Return values
bool —

true if the connection is established, false otherwise

json_from_sql()

Return the descriptive json of a table.

public json_from_sql(mixed $table) : string
Parameters
$table : mixed

the table name

Return values
string —

json table's definition

last_insert_id()

Get the last inserted ID from the last INSERT query.

public last_insert_id() : int

This function returns the last inserted ID from the last INSERT query executed.

Return values
int —

The last inserted ID, or 0 if no INSERT query has been executed

last_pages_data()

Returns an array with the data needed to manage the pagination of the results.

public last_pages_data() : array<string|int, mixed>
Return values
array<string|int, mixed> —

$result

The array contains the following keys: $result['start'] : index of the first result to display $result['limit'] : number of results to display from the first result returned (= number of results per page) $result['total'] : total number of query results $result['page_count'] : total number of pages $result['page_index'] : index of the current page

microtime_float()

Returns the current time in seconds as a float.

public microtime_float() : float
Return values
float —

The current time in seconds as a float.

prepared_query()

This function is used to execute a prepared query with parameters and return the result as an associative array.

public prepared_query(string $sql[, string|bool $typeDef = false ][, array<string|int, mixed>|bool $params = false ]) : array<string|int, mixed>|bool

If the query is not a SELECT query, it will log an error and return null. the main difference with query() is that this function uses prepared statements to execute the query. So it is more secure against SQL injection attacks, and should be used for queries with user input.

exemple usage: $db->prepared_query("SELECT * FROM mytable WHERE id = ?", "i", array($id)); typedef is a string that defines the types of the parameters, for example "i" for integer, "s" for string, "d" for double, "b" for blob.

Parameters
$sql : string

The SQL query to execute

$typeDef : string|bool = false

The type definition for the prepared statement, or false if not needed

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

The parameters to bind to the prepared statement, or false if not needed

Tags
see
prepared_query_list()
see
prepared_query_list_assoc()
see
prepared_query_value()
see
prepared_query_line()
Return values
array<string|int, mixed>|bool —

An associative array with the result of the query, or false if the query failed or returned no results

prepared_query_line()

Execute a prepared SQL query and return the first row of the result set.

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

This function is used to execute a prepared query with parameters and return the first row of the result set as an associative array. If the query is not a SELECT query, it will log an error and return null.

Parameters
$sql : string

The SQL query to execute

$typeDef : string|bool = false

The type definition for the prepared statement, or false if not needed

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

The parameters to bind to the prepared statement, or false if not needed

Return values
array<string|int, mixed>|null —

The first row of the result set as an associative array, or null if the query failed or returned no results

prepared_query_list()

Execute a prepared SQL query and return the result as a list of values.

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

This function is used to execute a prepared query with parameters and return the result as a list of values. If the query is not a SELECT query, it will log an error and return null.

Parameters
$sql : string

The SQL query to execute

$typeDef : string|bool = false

The type definition for the prepared statement, or false if not needed

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

The parameters to bind to the prepared statement, or false if not needed

Return values
array<string|int, mixed>|null —

An array with the result of the query, or null if the query failed or returned no results

prepared_query_list_assoc()

Execute a prepared SQL query and return the result as an associative array with multiple values per key.

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

.

Parameters
$sql : string

The SQL query to execute

$typeDef : string|bool = false

The type definition for the prepared statement, or false if not needed

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

The parameters to bind to the prepared statement, or false if not needed

Return values
array<string|int, mixed>|null —

An associative array with the result of the query, or null if the query failed or returned no results

prepared_query_value()

Execute a prepared SQL query and return the first value of the first row.

public prepared_query_value(string $sql[, string|bool $typeDef = false ][, array<string|int, mixed>|bool $params = false ]) : mixed

This function is used to execute a prepared query with parameters and return the first value of the first row. If the query is not a SELECT query, it will log an error and return null.

Parameters
$sql : string

The SQL query to execute

$typeDef : string|bool = false

The type definition for the prepared statement, or false if not needed

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

The parameters to bind to the prepared statement, or false if not needed

Return values
mixed —

The first value of the first row of the result set, or null if the query failed or returned no results

query()

Execute a SQL query.

public query([string|array<string|int, mixed> $q = '' ]) : mixed

This function executes a SQL query and returns the result. If the query is a multi-query, it will execute all queries in the string. If the query is an INSERT query, it will return the last inserted ID. If the query is a SELECT query, it will return the result set.

Parameters
$q : string|array<string|int, mixed> = ''

The SQL query to execute, or an array of queries to execute as a multi-query

Tags
see
query_value()
see
query_line()
see
query_list()
Return values
mixed —

The result of the query, or false if the query failed

query_line()

Execute a SQL query and return the first row of the result set as an associative array.

public query_line([string $q = "" ]) : array<string|int, mixed>|null

This function is used to execute a query that returns only one row, such as a SELECT query with a LIMIT 1. If the query is not a SELECT query, it will log an error and return the result of the query.

Parameters
$q : string = ""

The SQL query to execute

Return values
array<string|int, mixed>|null —

The first row of the result set as an associative array, or null if the query failed or returned no results

query_list()

Execute a SQL query and return the result as an array of associative arrays.

public query_list([string $q = "" ]) : array<string|int, mixed>|null

This function is used to execute a query that returns multiple rows, such as a SELECT query. It will return an array with each row as an associative array. If the query is not a SELECT query, it will log an error and return null. The result can be displayed quickly with H::simple_data_grid().

Parameters
$q : string = ""

The SQL query to execute

Return values
array<string|int, mixed>|null —

An array of associative arrays, or null if the query failed or returned no results

query_list_assoc()

Execute a SQL query and return the result as an associative array with multiple values per key.

public query_list_assoc([string $q = '' ]) : array<string|int, mixed>|null

This function is used to execute a query that returns multiple rows, such as a SELECT query. It will return an associative array where each key is a field name and the value is an array of values for that field. If the query is not a SELECT query, it will log an error and return null.

Parameters
$q : string = ''

The SQL query to execute

Return values
array<string|int, mixed>|null —

An associative array with multiple values per key, or null if the query failed or returned no results

query_list_for_select()

Execute a SQL query and return the result as an associative array for use in a select input. (generaly a H::select) This function is used to execute a query that returns multiple rows, such as a SELECT query.

public query_list_for_select([string $q = "" ][, string $keyname = "" ][, string $displayname = "" ][, mixed $firstblank = false ]) : array<string|int, mixed>|null

It will return an array with the key as the value of the specified key field and the value as the display name field. If the query is not a SELECT query, it will log an error and return null.

Parameters
$q : string = ""

The SQL query to execute

$keyname : string = ""

The name of the field to use as the key in the returned array

$displayname : string = ""

The name of the field to use as the value in the returned array

$firstblank : mixed = false

If true or a string, adds a blank option at the beginning of the list

Return values
array<string|int, mixed>|null —

An associative array for use in a select input, or null if the query failed or returned no results

query_value()

Execute a SQL query and return the first value of the first row.

public query_value([string $q = '' ]) : mixed

This function is used to execute a query that returns only one value, such as a COUNT or a single field. If the query is not a SELECT query, it will log an error and return the result of the query.

Parameters
$q : string = ''

The SQL query to execute

Return values
mixed —

The first value of the first row of the result set, or null if the query failed or returned no results

set_data_base()

Set the database to use.

public set_data_base(string $dbname) : bool

This function selects the database to use for the SQL queries. If the database does not exist, it will return false and log an error. in master/slave mode, it will also select the database on the master server.

Parameters
$dbname : string

The name of the database to use

Return values
bool —

true if the database was selected successfully, false otherwise

sql_from_json()

Make sql query to create table or update from a json description of the table The json have to be to the following format: - { - tables: [ - { - name: "my_table", - fields: [ - { - name: "field_name", - type: varchar|int|tinyint|datetime... (sql type) - limit: 255 (an integer) - null: (optional) boolean, true to accept null value, false to add NOT NULL - default: (optional), the default value - index: (optional) fulltext|index - primary: (optional) only one by table, autoincrement - }, - ... - }, - ... - ], - entries: [ - { - table: 'block_data', - fields: [ - field_name: [ - name: 'field_name', - type: 's' or 'i' or 'd', - value: 'field_value', - ] - ... - ], - languages: [ - short: [ - field: [ - en: 'trad_en', - fr: 'trad_fr', - ... - ]... - ], - long: [ - field: [ - en: 'trad_en', - fr: 'trad_fr', - ... - ]... - ] - ], - categories: [ - field_identifier: [ - // the first element of this list in the category than contains the element - // next elements are each time the parent of the previous one - [ series: "category's serie", name: "category's name"], - [ series: "category's serie", name: "category's name"], This one is the parent of the one before - ... - ], - ... - ] - } - ... - ] - }

public sql_from_json(mixed $json[, mixed $execute = true ]) : string
Parameters
$json : mixed
$execute : mixed = true
Return values
string —

The sql query

synch_other_DB()

Synchronize a table with another database.

public static synch_other_DB(array<string|int, mixed> $config, string $module, string $table, mixed $id[, string $action = '' ][, string $id_field = 'id' ][, array<string|int, mixed>|string|null $exclude_fields = null ]) : bool|int

This function is used to synchronize a table in another database with the current database. It can insert, update, or delete records based on the action specified.

Parameters
$config : array<string|int, mixed>

The configuration for the other database connection

$module : string

The module name

$table : string

The table name

$id : mixed

The ID or IDs of the records to synchronize

$action : string = ''

The action to perform: 'insert', 'update', or 'delete'

$id_field : string = 'id'

The field name for the ID (default is 'id')

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

Fields to exclude from synchronization

Return values
bool|int —

Returns false if no ID is provided, or the result of the query execution

table()

Returns the name of a table with the prefix if it is set.

public table(string $table_name) : string

This function is used to get the full name of a table with the prefix.

Parameters
$table_name : string

The name of the table to get

Return values
string —

The full name of the table with the prefix if set, otherwise just the table name

table_data()

Get the structure of a table.

public table_data(string $table_name[, array<string|int, mixed> $fields = array() ]) : array<string|int, mixed>|null

This function executes a SHOW FULL COLUMNS query on the specified table and returns an associative array with the field names as keys. Each field will have its type, size, extra information, comment, and default value.

Parameters
$table_name : string

The name of the table to get the structure from

$fields : array<string|int, mixed> = array()

An array of fields to be marked as "file" in the comment

Return values
array<string|int, mixed>|null —

An associative array with the table structure, or null if the table does not exist or has no fields

table_exists()

Check if a table exists in the database.

public table_exists(string $table_name[, bool $forceRequest = false ]) : bool

This function executes a SHOW TABLES query to check if the specified table exists. If the table exists, it returns true, otherwise it returns false.

Parameters
$table_name : string

The name of the table to check

$forceRequest : bool = false

If true, it will force a new request to the database, otherwise it will use cached results

Return values
bool —

true if the table exists, false otherwise

table_field_list()

Returns a list of fields of a given table.

public table_field_list(string $table_name) : array<string|int, mixed>

This function executes a SHOW COLUMNS query on the specified table and returns an array of field names.

Parameters
$table_name : string

The name of the table to get the fields from

Return values
array<string|int, mixed> —

An array of field names in the specified table

test_connection()

replace mysqli_ping

public test_connection(mixed $db_object) : mixed
Parameters
$db_object : mixed
Tags
@return

[type]


        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results