SQL Class for easier SQL Queries

requestException.php

<?php

class RequestException extends \Exception {}

rollbackException.php

<?php

class RollbackException extends RequestException {}

SQL.php

<?php

require_once __DIR__ . '/requestException.php';
require_once __DIR__ . '/rollbackException.php';

class SQL {
    private static $cons = [];

    private $con, $database;
    private $transaction = false;

    private $lastRawQuery = null;
    private $lastQuery = null;
    private $lastResult = null;
    private $escapedQuery = false;

    /**
     * Method: __construct
     * Set $this->con for the current object
     *
     * @param int $database
     * @throws RequestException
     */
    public function __construct($database = 1) {
        $this->updateCon($database);

        if($this->getCon()->inTransaction())
            $this->startTransaction();
    }

    /**
     * Method: execute
     * Helper method for making SQL queries.
     *
     * Pass only $query to execute basic SQL Query.
     * Pass both $query and $params to create new prepared query and execute with first set of values;
     * To make subsequent queries on same prepared statement, pass null $query and parameters to repeat with new values.
     *
     * @param string|null $query
     * @param array $params
     * @param bool $single
     * @return null
     * @throws RequestException
     * @throws RollbackException
     */
    public function execute(string $query = null, array $params = [], bool $single = false) {
        try {
            if(is_string($query)) {
                if(empty($params)) {
                    $this->escapedQuery = false;
                    $this->_executeBasic($query, $single);
                } else {
                    $this->escapedQuery = true;
                    $this->_executePrepared($query, $params, $single);
                }
                $this->lastRawQuery = $query;
            } else if(is_null($query) && !empty($params)) {
                $this->_executePrepared(null, $params, $single); // Repeat the query with new values
            }

            return $this->lastResult;
        } catch(RequestException $e) {
            if($this->transaction) {
                $this->rollback($e);
            } else throw $e;
        }
    }

    /**
     * Method: _executePrepared
     * Execute a prepared SQL query; if $query not provided, use the existing prepared statement with new values
     *
     * @param string|null $query
     * @param array $params
     * @param bool $single
     * @throws RequestException
     */
    private function _executePrepared(string $query = null, array $params = [], bool $single) {
        $method = substr(is_string($query) ? $query : $this->lastRawQuery, 0, 6);

        try {
            if(is_string($query)) {
                $this->lastQuery = $this->getCon()->prepare($query);
            } else {
                if(!$this->escapedQuery)
                    throw new RequestException('Could not use previous query because it was not escaped.');
            }

            $this->lastQuery->execute($params);

            switch($method) {
                case 'SELECT':
                    if(!$single) $this->lastResult = $this->lastQuery->fetchAll(\PDO::FETCH_ASSOC);
                    else $this->lastResult = $this->lastQuery->fetch(\PDO::FETCH_ASSOC);
                    break;
                case 'INSERT':
                case 'UPDATE':
                case 'DELETE':
                    $this->lastResult = ['insert_id' => $this->getCon()->lastInsertId(), 'rows_affected' => $this->lastQuery->rowCount()];
                    break;
            }
        } catch(\PDOException $e) {
            $this->lastResult = false;
            throw new RequestException('Could not ' . $method . ':' . $e->getMessage(), $e->getCode());
        }
    }

    /**
     * Method: _executeBasic
     * Execute a regular SQL query. Update $this->lastResult.
     *
     * @param string $query
     * @param bool $single
     * @throws RequestException
     */
    private function _executeBasic(string $query, bool $single) {
        $method = substr($query, 0, 6);

        try {
            $this->lastQuery = $this->getCon()->query($query);

            switch($method) {
                case 'SELECT':
                    if(!$single) $this->lastResult = $this->lastQuery->fetchAll(\PDO::FETCH_ASSOC);
                    else $this->lastResult = $this->lastQuery->fetch(\PDO::FETCH_ASSOC);
                    break;
                case 'INSERT':
                case 'UPDATE':
                case 'DELETE':
                    $this->lastResult = ['insert_id' => $this->getCon()->lastInsertId(), 'rows_affected' => $this->lastQuery->rowCount()];
                    break;
            }
        } catch(\PDOException $e) {
            $this->lastResult = false;
            throw new RequestException('Could not ' . $method . ': ' . $e->getMessage(), $e->getCode());
        }
    }

    /**
     * Method: startTransaction
     * Start a transaction for current connection (if the connection is not already engaged in a transaction).
     * If this instance of SQL is inside transaction, roll it back prior to starting new transaction.
     *
     * @return bool
     * @throws RollbackException
     */
    public function startTransaction(): bool {
        if($this->transaction) {
            $this->rollback();
        }

        if(!$this->getCon()->inTransaction())
            $this->getCon()->beginTransaction();

        $this->transaction = $this->getCon()->inTransaction();
        return $this->transaction;
    }

    /**
     * Method: commit
     * Commit the current transaction
     */
    public function commit() {
        if($this->transaction) {
            $this->getCon()->commit();
            $this->transaction = false;
        }
    }

    /**
     * Method: rollback
     * Rollback current transaction; if rollback is result of failed query, throw RollbackException
     *
     * @param RequestException|null $e
     * @throws RollbackException
     */
    public function rollback(RequestException $e = null) {
        if($this->transaction) {
            $this->getCon()->rollBack();
            $this->transaction = false;

            if(!is_null($e))
                throw new RollbackException('Forced rollback: ' . $e->getMessage(), $e->getCode());
        }
    }

    /**
     * method: getCon
     * Returns the current PDO object or null if not set
     *
     * @return null|\PDO
     */
    public function getCon(): ?\PDO {
        return ($this->con instanceof \PDO) ? $this->con : null;
    }

    /**
     * Method: updateCon
     * Update $this->con to new connection, or to existing SQL connection if exists in self::$cons.
     *
     * @param $database
     * @throws RequestException
     */
    private function updateCon($database) {
        $this->database = $database;

        if(!array_key_exists($this->database, self::$cons)) {
            try {
                switch ($this->database) {
                    case 1:
                    default:
                        self::$cons[1] = new \PDO('mysql:dbname=;host:localhost', '', '', [
                            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
                            \PDO::ATTR_EMULATE_PREPARES => false
                        ]);
                        break;
                }
            } catch(\PDOException $e) {
                throw new RequestException('Could not establish a connection to our database.', 500);
            }
        }

        $this->con = self::$cons[$this->database];
    }

    /**
     * Method: closeConnections
     * Destroy the PDO objects in self::$cons, thus closing connection.
     */
    public static function closeConnections() {
        if(is_array(self::$cons) && count(self::$cons) >= 1) {
            for($i = 1; $i <= count(self::$cons); $i++) {
                self::$cons[$i] = null;
            }
        }
    }

    /**
     * Method: getInsertId
     * Returns insert ID for last request if of type INSERT, UPDATE or DELETE; null otherwise.
     *
     * @return int|null
     */
    public function getInsertId(): ?int {
        return is_array($this->lastResult) && array_key_exists('insert_id', $this->lastResult)
            ? $this->lastResult['insert_id']
            : null;
    }

    /**
     * Method: getAffectedRows
     * Returns number of affected rows for last request if of type INSERT, UPDATE or DELETE; null otherwise.
     *
     * @return int|null
     */
    public function getAffectedRows(): ?int {
        return is_array($this->lastResult) && array_key_exists('rows_affected', $this->lastResult)
            ? $this->lastResult['rows_affected']
            : null;
    }

    public function getLastRawQuery(): ?string {
        return is_string($this->lastRawQuery) ? $this->lastRawQuery : null;
    }

    /**
     * Method: getLastResult
     * Return $this->lastResult if valid result set, otherwise null.
     *
     * @return array|null
     */
    public function getLastResult(): ?array {
        return is_array($this->lastResult) ? $this->lastResult : null;
    }
}

EXAMPLE USAGE:

<?php
// Open a connection (or share existing if there is one)
$SQL = new SQL;

try {
    // Initiate a transaction to enable automatic rollbacks - don't forget to $SQL->commit()!
    // If using an existing SQL connection and a transaction was open, subsequent queries will be part of the existing transaction
    $SQL->startTransaction();

    // Get many results
    $term = 'John';
    $name = '%' . $term . '%';
    $users = $SQL->execute('SELECT `id` FROM users WHERE name LIKE ?', [$name]);

    // Get a single result
    $userID = $users[0]['id'];
    $userInfo = $SQL->execute('SELECT * FROM users WHERE userID = ' . $userID, [], true);

    // Take action on behalf of John
    $product = 'Schmoozer 5000';
    $SQL->execute('INSERT INTO products (product_name, owner) VALUES (?, ' . $userID . ')', [$product]); // Escape raw input
    $productID = $SQL->getInsertId();

    $SQL->commit();

    echo 'Thanks ' . $userInfo['name'] . "! Product #" . $productID . ' has been created!';
} catch(RollbackException $e) {
    // One of the queries failed and prior queries have automatically been rolled back
    echo 'Could not take this action! ' . $e->getMessage();
}
?>

This creates a record in the products table and outputs:
Thanks John Goodman! Product #1221 has been created!