Smart.Framework Logo

final class \SmartMysqliDb
{ } ::

Class: SmartMysqliDb - provides a Static Client for MariaDB Server / MySQL that can be used just with the DEFAULT connection from configs.
This class can be used just with the DEFAULT connection which must be set in etc/config.php: $configs['mysqli'].
It connects automatically, when needed (the connection is lazy, and is made just when is needed to avoid permanent connections to MySQL which slower down the app and takes busy the slots).
Tested and Stable with MariaDB versions: 5.5.x / 10.x
Tested and Stable on MySQL versions: 5.5.x / 5.6.x / 5.7.x / 6.x / 7.x / 8.x


class Methods

public static function default_connect ( ) {} :: 
Pre-connects manually to the Default Server.
This function is OPTIONAL as the connection on the DEFAULT Server will be done automatically when needed.
Anyway, if there is a need to create an explicit connection to the DEFAULT Server earlier, this function can be used by example in App Bootstrap.
public static function quote_likes ( STRING $y_string ) {} :: 
@param: {STRING} $y_string: A String or a Number to be Quoted for LIKES
Fix a string to be compliant with MySQL LIKE syntax.
It will use special quotes for the LIKE special characters: % _
This function IS NOT INTENDED TO ESCAPE AGAINST SQL INJECTIONS ; USE IT ONLY WITH PREPARED PARAMS OR USE escape_str() with mode 'likes'
public static function escape_str ( STRING $y_string, ENUM $y_mode = "", RESOURCE $y_connection = "DEFAULT" ) {} :: STRING
@return: {STRING} The Escaped String / Number
@param: {STRING} $y_string: A String or a Number to be Escaped
@param: {ENUM} $y_mode: '' = default ; 'likes' = Escape LIKE Syntax (% _)
@param: {RESOURCE} $y_connection: the connection
Escape a string to be compliant and Safe (against SQL Injection) with MySQL standards.
This function WILL NOT ADD the SINGLE QUOTES (') arround the string, but just will just escape it to be safe.
public static function check_if_table_exists ( STRING $y_table, RESOURCE $y_connection = "DEFAULT" ) {} :: 0/1
@return: {0/1} 1 if exists ; 0 if not
@param: {STRING} $y_table: The Table Name
@param: {RESOURCE} $y_connection: The connection to Server
Check if a Table Exists in the current Database.
public static function count_data ( STRING $queryval, STRING $params_or_title = "", RESOURCE $y_connection = "DEFAULT" ) {} :: INTEGER
@return: {INTEGER} the result of COUNT()
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters or query title for easy debugging
@param: {RESOURCE} $y_connection: the connection
MySQL Query :: Count
This function is intended to be used for count type queries: SELECT COUNT().
public static function read_data ( STRING $queryval, STRING $params_or_title = "", RESOURCE $y_connection = "DEFAULT" ) {} :: ARRAY
@return: {ARRAY} (non-asociative) of results :: array('column-0-0', 'column-0-1', null, ..., 'column-0-n', 'column-1-0', 'column-1-1', ... 'column-1-n', ..., 'column-m-0', 'column-m-1', ..., 'column-m-n')
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters or query title for easy debugging
@param: {RESOURCE} $y_connection: the connection
MySQL Query :: Read (Non-Associative) one or multiple rows.
This function is intended to be used for read type queries: SELECT.
public static function read_adata ( STRING $queryval, STRING $params_or_title = "", RESOURCE $y_connection = "DEFAULT" ) {} :: ARRAY
@return: {ARRAY} (asociative) of results :: array(0 => array('column1' => 'val1', 'column2' => null, ... 'column-n' => 't'), 1 => array('column1' => 'val2', 'column2' => 'val2', ... 'column-n' => 'f'), ..., m => array('column1' => 'valM', 'column2' => 'xyz', ... 'column-n' => 't'))
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters or query title for easy debugging
@param: {RESOURCE} $y_connection: the connection
MySQL Query :: Read (Associative) one or multiple rows.
This function is intended to be used for read type queries: SELECT.
public static function read_asdata ( STRING $queryval, STRING $params_or_title = "", RESOURCE $y_connection = "DEFAULT" ) {} :: ARRAY
@hints: ALWAYS use a LIMIT 1 OFFSET 0 with all queries using this function to avoid situations that will return more than 1 rows and will raise ERROR with this function.
@return: {ARRAY} (asociative) of results :: Returns just a SINGLE ROW as: array('column1' => 'val1', 'column2' => null, ... 'column-n' => 't')
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters or query title for easy debugging
@param: {RESOURCE} $y_connection: the connection
MySQL Query :: Read (Associative) - Single Row (just for 1 row, to easy the use of data from queries).
!!! This will raise an error if more than one row(s) are returned !!!
This function does not support multiple rows because the associative data is structured without row iterator.
For queries that return more than one row use: read_adata() or read_data().
This function is intended to be used for read type queries: SELECT.
public static function write_data ( STRING $queryval, STRING $params_or_title = "", RESOURCE $y_connection = "DEFAULT" ) {} :: ARRAY
@return: {ARRAY} [ 0 => 'control-message', 1 => #affected-rows, 2 => #last-inserted-id(autoincrement)|0|null ]
@param: {STRING} $queryval: the query
@param: {STRING} $params_or_title: *optional* array of parameters or query title for easy debugging
@param: {RESOURCE} $y_connection: the connection
MySQL Query :: Write.
This function is intended to be used for write type queries: BEGIN (TRANSACTION) ; COMMIT ; ROLLBACK ; INSERT ; INSERT IGNORE ; REPLACE ; UPDATE ; CREATE SCHEMAS ; CALLING STORED PROCEDURES ...
public static function prepare_statement ( ARRAY $arrdata, ENUM $mode, RESOURCE $y_connection = "DEFAULT" ) {} :: STRING
@return: {STRING} The SQL partial Statement
@param: {ARRAY} $arrdata: associative array: array of form data as $arr=array(); $arr['field1'] = 'a string'; $arr['field2'] = 100; | non-associative array $arr[] = 'some value'; $arr[] = 'other-value', ...
@param: {ENUM} $mode: mode: 'insert' | 'update' | 'in-select'
@param: {RESOURCE} $y_connection: the connection to Server
Create Escaped Write SQL Statements from Data - to be used with MySQL for: INSERT ; UPDATE ; IN-SELECT
Can be used with: write_data() to build INSERT / UPDATE queries from an associative array
or can be used with read_data(), read_adata(), read_asdata(), count_data() to build IN-SELECT queries from a non-associative array
public static function prepare_param_query ( STRING $query, $replacements_arr, RESOURCE $y_connection = "DEFAULT" ) {} :: STRING
@return: {STRING} The SQL processed (partial/full) Statement
@param: {STRING} $query: SQL Statement to process like ' WHERE ("id" = ?)'
@param: {ARRAY} $arrdata: The non-associative array as of: $arr=array('a');
@param: {RESOURCE} $y_connection: the connection to Server
Create Escaped SQL Statements from Parameters and Array of Data by replacing ? (question marks)
This can be used for a full SQL statement or just for a part.
The statement must not contain any Single Quotes to prevent SQL injections which are unpredictable if mixing several statements at once !
public static function new_safe_id ( ENUM $y_mode, $y_id_field, STRING $y_table_name, RESOURCE $y_connection = "DEFAULT" ) {} :: STRING
@return: {STRING} the generated Unique ID
@param: {ENUM} $y_mode: mode: uid10str | uid10num | uid10seq | uid12seq | uid32 | uid34 | uid36 | uid45
@param: {STRING} $y_field_name: the field name
@param: {STRING} $y_table_name: the table name
@param: {RESOURCE} $y_connection: the connection to Server
Get A UNIQUE (SAFE) ID for DB Tables

class Properties


class Constants


Sample code: PHP

<?php

// The connection to the DEFAULT MariaDB Server / MySQL will be done automatically, when needed, using the config parameters
 $count = (int) SmartMysqliDb::count_data('SELECT COUNT(`id`) FROM `table` WHERE (`active` = \''.SmartMysqliDb::escape_str('1').'\')');
 $non_associative_read_multi_records = (array) SmartMysqliDb::read_data('SELECT * FROM `table` WHERE `id` = ?', array(3));
 $associative_read_multi_records = (array) SmartMysqliDb::read_adata('SELECT * FROM `table` WHERE `id` = ?', array('some-id'));
 $associative_read_for_just_one_record = (array) SmartMysqliDb::read_asdata('SELECT * FROM `table` WHERE `id` = ? LIMIT 1 OFFSET 0', array(99)); // NOTICE: this function will return just one record, so always use LIMIT 1 OFFSET 0 (or LIMIT 0,1) ; if the query will return more records will raise an error
 $update = (array) SmartMysqliDb::write_data('UPDATE `table` SET `active` = 1 WHERE `id` = ?', array(55)); // will return an array[ 0 => message, 1 => (integer) affected rows ]
 $arr_insert = array(
         'id' => 100,
         'active' => 1,
         'name' => 'Test Record'
 );
 $insert = (array) SmartMysqliDb::write_data('INSERT INTO `table` '.SmartMysqliDb::prepare_statement($arr_insert, 'insert'));
 $prepared_sql = $db->prepare_param_query('SELECT * FROM `table` WHERE `id` = ?', [99]);

// #end php code

documentation generated on: 2023-10-19 23:15:47 +0000


Smart.Framework © 2009-2023 unix-world.org