GRIS_DATABASE
extends MySQLi
in package
Class for database connections and data manipulation
With this class the main database is connected.
PHP version 7
Copyright (c) 2012, Holger Heuser
Tags
Table of Contents
- $classname : object
- The name of the class
- $db_name : string
- The name of the current database
- $debug : bool
- The variable for enabling or disabling debug
- $instance : object
- The instance counter
- $number_of_hits : int
- The number of hits of the last query
- $state : object
- State object for errors and successful actions
- __construct() : mixed
- The class constructor
- __destruct() : mixed
- The class destructor
- check_references() : int
- Check if entry is referenced in other tables
- checkbox_to_db() : int
- Converts checkbox values to a tinyint value (MySQL)
- clean_data() : array<string|int, mixed>
- Cleans null values and negative IDs from the insert/update array
- cleanup_reference() : int
- Checks if an attribute with the given ID is referenced in the table
- date_mysql_to_php() : string
- Changes the date format from MySQL to php (yyyy-mm-dd or dd.mm.yyyy)
- date_php_to_mysql() : string
- Changes the date format from php (dd.mm.yyyy) to MySQL
- db_count() : int
- Counts the appropriate rows in the database
- debug() : boolen
- Switches the debug mode
- delete_row() : bool
- Deletes a row in the database
- escape_statement() : array<string|int, mixed>
- Escapes the SQL statement to avoid injections
- execute_multi_query() : bool
- Executes a multiline mysql query (just for updates, does not return the result set)
- execute_single_query() : object
- Executes a mysql query
- get_all() : array<string|int, mixed>
- Returns the whole table
- get_db_name() : mixed
- Get the database name
- get_dbms_version() : int
- Returns the current dbms version
- get_number_of_hits() : int
- Returns the number of hits of the last query
- get_rows() : array<string|int, mixed>
- Returns rows from the database
- get_version() : int
- Returns the current database version
- getInstance() : mixed
- The instance creator
- insert_row() : int
- Inserts a row into the database
- manage_references() : bool
- Changes references from an old to a new ID in different tables
- prepared_statement() : array<string|int, mixed>
- Use prepared statements to avoid injections
- replace_row() : int
- Replaces a row in the database
- transaction() : bool
- Executes a multi query as a transaction and starts a rollback on error
- unset_clean_data() : array<string|int, mixed>
- Unsets an attribute in both arrays key and value
- update_row() : bool
- Updates a row in the database
- __clone() : mixed
- The instance creator
- get_types() : array<string|int, mixed>
- Returns the type of each column
- replace_null() : mixed
- Replaces NULL with "NULL" for use in sql statetments
Properties
$classname
The name of the class
private
object
$classname
= NULL
Tags
$db_name
The name of the current database
private
string
$db_name
= NULL
Tags
$debug
The variable for enabling or disabling debug
private
bool
$debug
= false
Tags
$instance
The instance counter
private
static object
$instance
= NULL
Tags
$number_of_hits
The number of hits of the last query
private
int
$number_of_hits
= 0
Tags
$state
State object for errors and successful actions
private
object
$state
= NULL
Tags
Methods
__construct()
The class constructor
public
__construct([string $read_only = false ]) : mixed
Reads the connection parameters from the initfile and initializes the connection to the database
Parameters
- $read_only : string = false
-
Use a read only user (instead of extended permissions)
Return values
mixed —__destruct()
The class destructor
public
__destruct() : mixed
Closes the connection to the datatbase
Return values
mixed —check_references()
Check if entry is referenced in other tables
public
check_references(int $id, string $table, array<string|int, mixed> $ref_tables) : int
Parameters
- $id : int
-
The ID of the entry to be checked
- $table : string
-
The table where the refenece is from
- $ref_tables : array<string|int, mixed>
-
The array of tables to be checked for reference entries (works for relation table too)
$ref_tables = array( "gris_table1" => null, // normal table "gris_table2" => "gris_relations_subject" // relation table (value is the column to search in) };
Return values
int —Returns the number of existing refs
checkbox_to_db()
Converts checkbox values to a tinyint value (MySQL)
public
checkbox_to_db(string $input) : int
Parameters
- $input : string
-
The input value of the checkbox
Return values
int —The converted value ("on" -> 1, otherwise 0)
clean_data()
Cleans null values and negative IDs from the insert/update array
public
clean_data(array<string|int, mixed> $data, string $dbtable[, array<string|int, mixed> $fields = array() ]) : array<string|int, mixed>
Parameters
- $data : array<string|int, mixed>
-
The data array with (key => value)
- $dbtable : string
-
The table
- $fields : array<string|int, mixed> = array()
-
The fields to be used (works as a filter)
Return values
array<string|int, mixed> —The cleaned array [key][value]
cleanup_reference()
Checks if an attribute with the given ID is referenced in the table
public
cleanup_reference(array<string|int, mixed> $reference_tables, string $table, string $id_attribute) : int
Parameters
- $reference_tables : array<string|int, mixed>
-
The database table
- $table : string
-
The table to clean up
- $id_attribute : string
-
The attribute with the ID of the entity
Return values
int —The count of references (or 0 if there are no references)
date_mysql_to_php()
Changes the date format from MySQL to php (yyyy-mm-dd or dd.mm.yyyy)
public
static date_mysql_to_php(string $date[, bool $dmy = false ]) : string
Parameters
- $date : string
-
The MySQL date string
- $dmy : bool = false
-
Return the date in dd.mm.yyyy format (default is yyyy-mm-dd)
Return values
string —The php date
date_php_to_mysql()
Changes the date format from php (dd.mm.yyyy) to MySQL
public
static date_php_to_mysql(string $date) : string
Parameters
- $date : string
-
The MySQL date string
Return values
string —The php date
db_count()
Counts the appropriate rows in the database
public
db_count(string $dbtable[, string $testarea = NULL ][, string $testvalue = NULL ][, string $filters = NULL ]) : int
Parameters
- $dbtable : string
-
The database table
- $testarea : string = NULL
-
The testarea for the WHERE clause
- $testvalue : string = NULL
-
The testvalue for the WHERE clause
- $filters : string = NULL
-
Some optional filters for the WHERE clause (e. g. 'AND a > b')
Return values
int —The count or -1 on error
debug()
Switches the debug mode
public
debug([bool $debug = NULL ]) : boolen
Parameters
- $debug : bool = NULL
Return values
boolen —Returns current debug mode
delete_row()
Deletes a row in the database
public
delete_row(string $dbtable, string $constraints) : bool
Parameters
- $dbtable : string
-
The database table
- $constraints : string
-
The constraints for the row
Return values
bool —Returns true on success or false on error
escape_statement()
Escapes the SQL statement to avoid injections
public
escape_statement(string $statement) : array<string|int, mixed>
Parameters
- $statement : string
-
The statement to be escaped
Return values
array<string|int, mixed> —The escaped statement
execute_multi_query()
Executes a multiline mysql query (just for updates, does not return the result set)
public
execute_multi_query(string $query[, bool $return_result = false ]) : bool
Parameters
- $query : string
-
The query in SQL syntax
- $return_result : bool = false
-
Return the result sets as an array
Return values
bool —Returns true for success and false for error or exceptions
execute_single_query()
Executes a mysql query
public
execute_single_query(string $query) : object
Parameters
- $query : string
-
The query in SQL syntax
Return values
object —The result
get_all()
Returns the whole table
public
get_all(string $dbtable) : array<string|int, mixed>
Parameters
- $dbtable : string
-
The database table
Return values
array<string|int, mixed> —The table as an 2 dimensional array
get_db_name()
Get the database name
public
get_db_name() : mixed
Returns the name as a string
Return values
mixed —get_dbms_version()
Returns the current dbms version
public
get_dbms_version() : int
Return values
int —Returns the current version of the database management system
get_number_of_hits()
Returns the number of hits of the last query
public
get_number_of_hits() : int
Return values
int —The number of hits
get_rows()
Returns rows from the database
public
get_rows(string $dbtable, string $testarea, mixed $testvalue[, string $filters = NULL ]) : array<string|int, mixed>
Parameters
- $dbtable : string
-
The database table
- $testarea : string
-
The testarea for the WHERE clause
- $testvalue : mixed
- $filters : string = NULL
-
Some optional filters for the WHERE clause (e. g. 'AND a > b')
Return values
array<string|int, mixed> —The 2 dimensional array with the results
get_version()
Returns the current database version
public
get_version() : int
Return values
int —Returns the current database version or NULL
getInstance()
The instance creator
public
static getInstance() : mixed
Returns an instance of the object
Return values
mixed —insert_row()
Inserts a row into the database
public
insert_row(string $dbtable, array<string|int, mixed> $attributes, array<string|int, mixed> $values) : int
Parameters
- $dbtable : string
-
The database table
- $attributes : array<string|int, mixed>
-
The attributes (columns) where to insert
- $values : array<string|int, mixed>
-
The values to be inserted (Strings have to be preformatted with quotes)
Return values
int —Returns the insert ID or NULL on error
manage_references()
Changes references from an old to a new ID in different tables
public
manage_references(string $old_id, string $new_id, string $id_name, array<string|int, mixed> $tables) : bool
Parameters
- $old_id : string
-
The old ID in the table
- $new_id : string
-
The replacement ID
- $id_name : string
-
The name of the ID attribute
- $tables : array<string|int, mixed>
-
The tables for the replacement
Return values
bool —Returns true
prepared_statement()
Use prepared statements to avoid injections
public
prepared_statement(string $query, array<string|int, mixed> $data[, string $key = NULL ][, string $value = NULL ]) : array<string|int, mixed>
Parameters
- $query : string
-
The query
- $data : array<string|int, mixed>
-
The data for the query (First entry defines the types. Example: array ("si", "hello", 1)
- $key : string = NULL
-
Use an attribute as an array key
- $value : string = NULL
-
Use an attribute as an array value
Return values
array<string|int, mixed> —The result as an array
replace_row()
Replaces a row in the database
public
replace_row(string $dbtable, array<string|int, mixed> $attributes, array<string|int, mixed> $values) : int
Parameters
- $dbtable : string
-
The database table
- $attributes : array<string|int, mixed>
-
The attributes (columns) where to insert
- $values : array<string|int, mixed>
-
The values to be inserted (Strings have to be preformatted with quotes)
Return values
int —Returns the insert ID or NULL on error
transaction()
Executes a multi query as a transaction and starts a rollback on error
public
transaction(string $query) : bool
Parameters
- $query : string
Return values
bool —Returns true for a successful commit or false for a rollback
unset_clean_data()
Unsets an attribute in both arrays key and value
public
unset_clean_data(array<string|int, mixed> $clean_data, string $attribute) : array<string|int, mixed>
Parameters
- $clean_data : array<string|int, mixed>
-
The cleaned data (two dimensional array with key and value)
- $attribute : string
-
The attribute to be unset
Return values
array<string|int, mixed> —The cleaned array
update_row()
Updates a row in the database
public
update_row(string $dbtable, array<string|int, mixed> $attributes, array<string|int, mixed> $values, string $testarea, string $testvalue[, string $filters = NULL ]) : bool
Parameters
- $dbtable : string
-
The database table
- $attributes : array<string|int, mixed>
-
The attributes (columns) where to update
- $values : array<string|int, mixed>
-
The values to be updated (Strings have to be preformatted with quotes)
- $testarea : string
-
The testarea for the WHERE clause
- $testvalue : string
-
The testvalue for the WHERE clause
- $filters : string = NULL
-
Some optional filters for the WHERE clause (e. g. 'AND a > b')
Return values
bool —true
__clone()
The instance creator
private
__clone() : mixed
Prohibits external cloning of the object
Return values
mixed —get_types()
Returns the type of each column
private
get_types(string $dbtable) : array<string|int, mixed>
Parameters
- $dbtable : string
-
The database table
Return values
array<string|int, mixed> —The type and column name as an assoziative array
replace_null()
Replaces NULL with "NULL" for use in sql statetments
private
replace_null(mixed $item) : mixed
Parameters
- $item : mixed
-
The item to be "NULL"
Return values
mixed —Returns the old value or "NULL" if input was NULL