Database

<?php
    namespace MyNameSpace;

    use \PDO;
    use MyNameSpace\Helpers\ArrayHelper;
    /**
     * Database class
     *
     * @package MyPackage
     * @subpackage Database
     * @author JBHUTT09
     */
    class Database {
        const RETURN_QUERY = 0;
        const RETURN_ROW_COUNT = 1;
        const RETURN_LAST_INSERTED = 2;
        const RETURN_ALL_ROWS = 3;


        protected $available_connections = [];
        protected $connections = [];

        public function __construct( array $db_connections ) {
            $this->available_connections = $db_connections;
            return $this;
        }

        /**
         * Checks $connections for the specified index. If the index is not found, attempts to create a PDO instance using the provided settings and store it in $connections.
         *
         * @param string $connection_type The index of the desired connection config array passed to the constructor.
         * @param bool $return (optional) If true, return the PDO object. If false, return bool true. Defaults to false.
         * @return bool|PDO Returns bool false on failure. Success return type determined by $return param.
         */
        private function openLink( string $connection_type, $return=false ) {
            if ( !isset( $this->connections[ $connection_type ] ) ) {
                if ( !isset( $this->available_connections[ $connection_type ] ) || !ArrayHelper::multiIsset( $this->available_connections[ $connection_type ], [ 'host', 'database', 'charset', 'user', 'pass' ] ) ) return false;
                try {
                    $this->connections[ $connection_type ] = new PDO(
                        'mysql:host='.$this->available_connections[ $connection_type ][ 'host' ].';dbname='.$this->available_connections[ $connection_type ][ 'database' ].';charset='.$this->available_connections[ $connection_type ][ 'charset' ],
                        ''.$this->available_connections[ $connection_type ][ 'user' ].'',
                        ''.$this->available_connections[ $connection_type ][ 'pass' ].''
                    );
                }
                catch ( Exception $e ) {
                    return false;
                }
            }
            return $return ? $this->connections[ $connection_type ] : true;
        }

        private function executeQuery( string $connection_type, string $query_text, array $prepared = [], $return=self::RETURN_QUERY ) {
            if ( $this->openLink( $connection_type ) === false ) return false;
            $query = $this->connections[ $connection_type ]->prepare( $query_text );
            if ( $query->execute( $prepared ) !== false ) {
                if ( $return === self::RETURN_QUERY ) return $query;
                elseif ( $return === self::RETURN_ROW_COUNT ) return $query->rowCount();
                elseif ( $return === self::RETURN_ALL_ROWS ) return $query->fetchAll( PDO::FETCH_ASSOC );
                elseif ( $return === self::RETURN_LAST_INSERTED ) return $this->connections[ $connection_type ]->lastInsertId();
            }
            //$this->logQuery( $query, $query_text, $prepared );
            return false;
        }

        /*
         * Perform a SELECT query
         *
         * @param string $connection_type The connection type
         * @param string $table The table to select from
         * @param array $args (optional) An array of options in the following format:
         *      select: (string|array)
         *          A column name |
         *          an array of column names (supports associative arrays for 'column (key) AS alias (value)' selections) |
         *          an array of assoc arrays with the keys 'value' (column name) and 'function' (MySQL function ie DECOMPRESS)
         *      where: (string) a MySQL WHERE clause (do not include 'WHERE'). May contain PDO tokens.
         *      prepared: (assoc array) A valid array of PDO tokens and values. Required if where contains PDO tokens
         *      order: (string|array)
         */
        public function select( string $connection_type, string $table, array $args = [] ) {
            // build select statement
            if ( !isset( $args[ 'select' ] ) ) $selection = '*';
            elseif ( is_array( $args[ 'select' ] ) ) {
                $selection = '';
                $n = 0;
                // iterate through columns to select.
                // if $key is not numeric, we assume $key is the column and $value is the desired alias: SELECT $key AS $value
                // otherwise we assume $value is the column and ignore $key
                foreach ( $args[ "select" ] AS $key => $value ) {
                    if ( $n > 0 )  $selection .= ',';
                    // if $value is an associative array, we assume we are to apply a function to the column.
                    if ( ArrayHelper::isAssoc( $value ) ) $selection .= ( ( !is_numeric( $key ) ) ? $value[ 'function' ]."( $key ) AS ".$value[ 'value' ] : $value[ 'function' ].'( '.$value[ 'value' ].' )' );
                    else $selection .= "`$table`.`".( ( !is_numeric( $key ) ) ? "$key` AS `$value" : $value ).'`';
                    $n++;
                }
            }
            else $selection = '`'.$args[ 'select' ].'`';

            if ( isset( $args[ 'where' ] ) ) {
                $where = 'WHERE '.$args[ 'where' ];
                $prepared = isset( $args[ 'prepared' ] ) ? $args[ 'prepared' ] : [];
            }
            else {
                $where = '';
                $prepared = [];
            }

            if ( isset( $args[ 'order' ] ) ) {
                if ( is_array( $args[ 'order' ] ) ) {
                    $order = 'ORDER BY ';
                    $n = 0;
                    $is_assoc = ArrayHelper::isAssoc( $args[ 'order' ] );
                    foreach ( $args[ 'order' ] AS $key => $value ) {
                        if ( $n > 0 ) $order .= ',';
                        $order .= ( ( $is_assoc ) ? "$key $value" : $value );
                        $n++;
                    }
                }
                else if ( is_string( $args[ 'order' ] ) && strpos( strtolower( ltrim( $args[ 'order' ] ) ), 'order by' ) !== 0 ) $order = 'ORDER BY '.$args[ 'order' ];
                else $order = $args[ 'order' ];
            }
            else $order = '';

            if ( isset( $args[ 'limit' ] ) ) {
                if ( is_array( $args[ 'limit' ] ) && count( $args[ 'limit' ] ) == 2 ) $limit = 'LIMIT '.implode( ',', $args[ 'limit' ] );
                else if ( is_int( $args[ 'limit' ] ) || ( is_string( $args[ 'limit' ] ) && strpos( strtolower( ltrim( $args[ 'limit' ] ) ), 'limit' ) !== 0 ) ) $limit = 'LIMIT '.$args[ 'limit' ];
                else $limit = $args[ 'limit' ];
            }
            else $limit = '';

            return $this->executeQuery( $connection_type, "SELECT $selection FROM $table $where $order $limit;", $prepared, self::RETURN_ALL_ROWS );
        }

        /**
         * Insert new database row
         *
         * @param string $connection_type The database connection to use.
         * @param string $table The database table to insert into.
         * @param array $prepared Associative array of the columns => values to insert.
         * @return int|bool The inserted row id on success. Bool false on failure.
         */
        public function insert( string $connection_type, string $table, array $prepared ) {
            $columns = '';
            $values = '';
            //put column names and values into strings formatted for query
            foreach( $prepared as $name => $value ) {
                $columns .= ",`$name`";
                if ( is_array( $value ) ) {
                    $values .= ','.$value[ 'function' ]."( :$name )";
                    $prepared[ $name ] = $value[ 'value' ];
                }
                else $values .= ",:$name";
            }
            $values = trim( $values, ',' ); //remove the extra commas from the front of the strings
            $columns = trim( $columns, ',' );
            return $this->executeQuery( $connection_type, "INSERT INTO `$table` ($columns) VALUES ($values);", $prepared, self::RETURN_LAST_INSERTED );
        }

        /**
         * Execute UPDATE query.
         *
         * @param string $connection_type The database connection to use.
         * @param string $table The table to update.
         * @param array $to_update Associative array of columns to update mapped to their new values.
         * @param string $where (optional) The WHERE condition. Not supplying a WHERE condition will apply the update to every row! Be careful!
         * @param array $prepared (optional) A valid PDO prepared array. Required when using a WHERE condition containing PDO tokens.
         *
         */
        public function update( string $connection_type, string $table, array $to_update, string $where=NULL, array $prepared=[] ) {
            $where = isset( $where ) ? "WHERE $where" : '';
            $set = '';
            foreach ( $to_update as $key => $value ) {
                if ( isset( $prepared[ $key ] ) ) return false;
                $set .= "`$key`=:$key,";
            }
            $set = rtrim( $set, ',' );
            return $this->executeQuery( $connection_type, "UPDATE `$table` SET $set $where;", array_merge( $to_update, $prepared ), self::RETURN_ROW_COUNT );
        }

        /**
         * Execute DELETE query.
         *
         * @param string $connection_type The database connection to use.
         * @param string $table The table to delete from.
         * @param string $where (optional) The WHERE condition. Not supplying a WHERE condition will apply the delete every row! Be careful!
         * @param array $prepared (optional) A valid PDO prepared array. Required when using a WHERE condition containing PDO tokens.
         *
         */
        public function delete( string $connection_type, string $table, string $where=NULL, array $prepared=[] ) {
            $where = isset( $where ) ? "WHERE $where" : '';
            return $this->executeQuery( $connection_type, "DELETE FROM `$table` $where;", $prepared, self::RETURN_ROW_COUNT );
        }

        /**
         * Run a custom MySQL query
         *
         * @param string $connection_type The database connection to use.
         * @param string $query_text The MySQL query to run.
         * @param array $prepared (optional) A valid PDO prepared array. Required if $query_text contains PDO tokens.
         * @param $return (optional) The format in which to return the query results. Defaults to Database::RETURN_QUERY which returns the PDO query object.
         * @return mixed The query results in the specified format.
         */
        public function custom( string $connection_type, string $query_text, array $prepared=[], $return=self::RETURN_QUERY ) {
            return $this->executeQuery( $connection_type, $query_text, $prepared, $return );
        }
    }
?>