diff --git a/class-DB.php b/class-DB.php index 7eaf186..5d8bea7 100644 --- a/class-DB.php +++ b/class-DB.php @@ -1,493 +1,496 @@ . /* * Use: esc_html(), error_die(), DEBUG, force_array() */ /** - * The database class from the Open Student project. - * Some functions to run MySQL queries and analyze results. + * Database class + * + * This class was forked from my defunct project called Open Student. */ class DB { /** - * MySQL connection link. - * @var MySQLi->link MySQLi connection link + * MySQL connection link */ private $mysqli; /** - * Table prefix. + * Table prefix + * * @var string */ private $prefix; /** - * Number of executed queries. + * Number of executed queries + * * @var int */ - private $numQueries = 0; + private $queries = 0; /** - * Last query result. + * Last query result + * * @var query-result */ - private $lastResult = false; + private $last = false; /** * Singleton instance * * @var self */ private static $_instance; /** - * Get an instance of this class + * Get the singleton instance of this class * * @return self */ public function instance() { if( ! self::$_instance ) { self::$_instance = new self(); } return self::$_instance; } /** * Check if ::instance() was called * * @return boolean */ public function instanced() { return isset( self::$_instance ); } /** * Prepare the DB object. * * @param type $username Database username * @param type $password Database password * @param type $location Database location * @param type $database Database name * @param type $prefix Table Prefix */ function __construct( $username = null, $password = null, $location = null, $database = null, $prefix = '', $charset = 'utf8' ) { + + // default credentials usually defined from your load.php if( func_num_args() === 0 ) { $username = $GLOBALS['username']; $password = $GLOBALS['password']; $location = $GLOBALS['location']; $database = $GLOBALS['database']; $prefix = $GLOBALS['prefix']; } $this->prefix = $prefix; - @$this->mysqli = new mysqli($location, $username, $password, $database); - if( $this->errorConnection() ) { + @$this->mysqli = new mysqli( $location, $username, $password, $database ); + if( $this->mysqli->connect_errno ) { if(DEBUG) { $password_shown = ($password === '') ? __("nessuna") : sprintf( __("di %d caratteri"), strlen( $password ) ); error_die( sprintf( __("Impossibile connettersi al database '%s' tramite l'utente '%s' e password (%s) sul server MySQL/MariaDB '%s'. Specifica correttamente queste informazioni nel file di configurazione del tuo progetto (usualmente '%s'). %s."), $database, $username, $password_shown, $location, 'load.php', HTML::a( 'https://github.com/valerio-bozzolan/boz-php-another-php-framework/blob/master/README.md#use-it', __("Documentazione") ) ) ); } else { error_die( __("Errore nello stabilire una connessione al database.") ); } + } else { + $this->mysqli->set_charset( $charset ); } - - @$this->mysqli->set_charset($charset); - } - - function __destruct() { - $this->closeConnection(); } /** - * Lock the door when you leave a room. - */ - public function closeConnection() { - $this->mysqli && @$this->mysqli->close(); - } - - /** - * To execute a query. + * Execute an SQL query * - * @param string $SQL The SQL query to execute - * @param boolean $tagReplace Use tag sobstitution, or no + * @param string $query The SQL query to execute */ - public function query($query) { - $this->numQueries++; - if( $this->lastResult !== false && $this->lastResult !== true) { - $this->lastResult->free(); + public function query( $query ) { + $this->queries++; + if( $this->last !== false && $this->last !== true) { + $this->last->free(); } - $this->lastResult = $this->mysqli->query($query); - if( ! $this->lastResult ) { - error_die( $this->getQueryErrorMessage($query) ); - } elseif(DEBUG && SHOW_EVERY_SQL) { - $this->showSQL($query); + $this->last = $this->mysqli->query( $query ); + if( !$this->last ) { + error_die( $this->getQueryErrorMessage( $query ) ); + } elseif( SHOW_EVERY_SQL ) { + $this->debugQuery( $query ); } - return $this->lastResult; + return $this->last; } - private function showSQL($SQL) { - echo "
" . sprintf( - __("Query numero %d:
%s"), - $this->numQueries, + /** + * Log the following query + * + * @param $query string + */ + private function debugQuery( $query ) { + error( sprintf( + 'query n. %d: %s', + $this->queries, $SQL - ) . "\n"; + ) ); } /** * Select only a row from the database */ public function getRow($query, $class_name = null, $params = [] ) { $results = $this->getResults($query, $class_name, $params); return @$results[0]; } /** * Select only a column from a single row */ public function getValue($query, $column_name, $class_name = null, $params = [] ) { $row = $this->getRow($query, $class_name, $params); return @$row->{$column_name}; } /** * Execute a query and return an array of $class_name objects. * * @param string $query Database SQL query. * @param string $class_name The name of the class to instantiate. * @param array $params Optional data for the $class_name constructor. * @See http://php.net/manual/en/mysqli-result.fetch-object.php */ public function getResults($query, $class_name = null, $params = [] ) { // IS_ARRAY() IS SHIT FOR HISTORICAL REASONS if( $class_name === null || is_array( $class_name ) ) { $class_name = 'Queried'; } // IS_ARRAY() IS SHIT FOR HISTORICAL REASONS - $this->query($query); + $this->query( $query ); $res = []; - while( $row = $this->lastResult->fetch_object($class_name, $params) ) { + while( $row = $this->last->fetch_object( $class_name, $params ) ) { $res[] = $row; } return $res; } /** * Execute a query and return a generator of $class_name objects. * * @param string $query Database SQL query. * @param string $class_name The name of the class to instantiate. * @param array $params Optional data for the $class_name constructor. * @See http://php.net/manual/en/mysqli-result.fetch-object.php */ public function getGenerator( $query, $class_name = null, $params = [] ) { - if( null === $class_name ) { + if( !$class_name ) { $class_name = 'Queried'; } $result = $this->query( $query ); - $this->lastResult = true; // to don't be killed from another query() call + $this->last = true; // to don't be killed from another query() call while( $row = $result->fetch_object( $class_name, $params ) ) { yield $row; } $result->free(); } /** * To insert a single row. * I have not time to check if $dbCols are DBCol objects. * * @param string $table_name * @param DBCol[] $cols * @param $args arguments like 'replace-into */ public function insertRow( $table_name, $cols, $args = [] ) { $SQL_columns = ''; $n = count($cols); for($i=0; $i<$n; $i++) { if($i !== 0) { $SQL_columns .= ', '; } $SQL_columns .= "`{$cols[$i]->column}`"; } $values = ''; for($i=0; $i<$n; $i++) { if($i !== 0) { $values .= ', '; } $values .= $this->forceType($cols[$i]->value, $cols[$i]->forceType); } $args = array_replace( [ 'replace-into' => false, ], $args ); $what = $args[ 'replace-into' ] ? 'REPLACE' : 'INSERT'; return $this->query("$what INTO {$this->getTable($table_name)} ($SQL_columns) VALUES ($values)"); } /** * Executes one or multiple queries which are concatenated by a semicolon * * @param $queries string */ public function multiQuery( $queries ) { return $this->mysqli->multi_query( $queries ); } /** * To execute clean insert SQL queries. * * @param string $table_name Table Name without prefix * @param array $columns Assoc array of types ('ID' => 'null', 'name' => 's', ..) * @param array $rows Array of rows * @param array $args Extra arguments */ public function insert($table_name, $columns, $rows, $args = []) { $args = array_replace( [ 'replace-into' => false ], $args ); force_array($rows); if( ! @is_array($rows[0]) ) { $rows = [ $rows ]; // array_columns('value col 1', '..') => array_rows( array_columns( 'value col 1', ..) ) } $n_columns = count($columns); $n_rows = count($rows); $SQL_columns = ''; $first = key($columns); foreach($columns as $column => $type) { if($column !== $first) { $SQL_columns .= ', '; } $SQL_columns .= "`$column`"; } $insert = ($args['replace-into']) ? 'REPLACE' : 'INSERT'; $SQL = "$insert INTO {$this->getTable($table_name)} ($SQL_columns) VALUES"; for($i=0; $i<$n_rows; $i++) { $SQL .= ($i === 0) ? ' (' : ', ('; $SQL_values = []; if($n_columns != count($rows[$i])) { - DEBUG && error( sprintf( + error( sprintf( __("Errore inserendo nella tabella %s. Colonne: %d. Colonne values[%d]: %d"), esc_html($table_name), $n_columns, $i, count($rows[$i]) ) ); return false; } $j = 0; foreach($columns as $column => $type) { $SQL_values[] = $this->forceType($rows[$i][$j], $type); $j++; } $SQL .= implode(', ', $SQL_values) . ')'; } return $this->query($SQL); } /** * To execute update queries. */ public function update($table_name, $dbCols, $conditions, $after = '') { force_array($dbCols); $SQL = "UPDATE {$this->getTable($table_name)} SET "; $n_cols = count($dbCols); for($i=0; $i<$n_cols; $i++) { if($i !== 0) { $SQL .= ', '; } $val = $this->forceType($dbCols[$i]->value, $dbCols[$i]->forceType); $SQL .= "`{$dbCols[$i]->column}` = $val"; } if($after !== '') { $after = " $after"; } $SQL .= " WHERE {$conditions}{$after}"; return $this->query($SQL); } + /** + * Get the last inserted ID + * + * @return string + */ public function getLastInsertedID() { return $this->mysqli->insert_id; } /** * Escape a string * * @param string $s String to be escaped * @return string String escaped. */ public function escapeString($s) { return $this->mysqli->real_escape_string($s); } - public function getNumQueries() { - return $this->numQueries; + public function getqueries() { + return $this->queries; } public function getPrefix() { return $this->prefix; } /** * Return a table name with it's school prefix * * @param string $name Table name * @param boolean $as True if you want to access to the table $name in the SQL * @return string Table $name with the prefix */ public function getTable($name, $as = false) { if($this->prefix === '') { $as = false; } $r = "`{$this->prefix}$name`"; if($as) { $r .= " AS `$name`"; } return $r; } /** * Return the list of every table name inserted as arguments or as an [] */ public function getTables($args = []) { $tables = []; if( ! is_array( $args ) ) { $args = func_get_args(); } foreach( $args as $arg ) { $tables[] = $this->getTable( $arg, true ); } return implode( ' JOIN ', $tables ); } /** * Force a string to a defined type. * * @param string $s String to be forced. * @param string $type Type ('d' for integer, 's' for string, 'f' for float, 'null' for autoincrement values or for "don't care" values). * @see http://news.php.net/php.bugs/195815 * @return string Forced string */ private function forceType($s, $type) { if( $type === 'd' ) return (int) $s; // Integer if( $type === 'f' ) return (float) $s; // Float if( $type === 'dnull' ) return ($s === null) ? 'NULL' : (int) $s; // Integer - or NULL if( $type === 'fnull' ) return ($s === null) ? 'NULL' : (float) $s; // Float - or NULL if( $type === 's' ) return "'{$this->escapeString($s)}'"; // String escaped if( $type === 'snull' ) return ($s === null) ? 'NULL' : "'{$this->escapeString($s)}'"; // String escaped - or NULL if( $type === 'f' ) return (float) $s; // Float value if( $type === '-' ) return $s; // Float value if( $type === null || $type === 'null' ) return 'NULL'; // 'NULL' literally for indexes - DEBUG && error( sprintf( + error( sprintf( "Tipo '%s' non concesso in DB::forceType(). Vedi la documentazione (esiste?). SarĂ usato l'escape 's'.", esc_html($type) ) ); return $this->forceType($s, 's'); } /** - * Check if there is an error in the connection + * Get the number of affected rows * - * @return boolean + * @return int */ - private function errorConnection() { - return @mysqli_connect_errno(); + public function affectedRows() { + $this->mysqli->affected_rows(); } /** - * You have to check the result before use this! - * - * @deprecated + * Please close the door when you leave a room */ - private function fetch_row($fetch) { - if($fetch === 'object' || $fetch === 'assoc') { - return $this->lastResult->{'fetch_' . $fetch}(); + public function closeConnection() { + if( $this->mysqli ) { + @$this->mysqli->close(); } - error_die('Fetch row cannot be ' . $fetch); } - public function affectedRows() { - return @$this->mysqli->affected_rows(); + /** + * Automatically close the door when you leave the room + */ + public function __destruct() { + $this->closeConnection(); } /** * Used to show "friendly" error. * * @param string $SQL SQL query executed during the error. * @return string Kind message. */ private function getQueryErrorMessage($SQL) { return sprintf( __("Errore eseguendo una query SQL: Query n. %d:
%s
%s"), - $this->numQueries, + $this->queries, $SQL, esc_html($this->mysqli->error) ); } }