PHP Class for flexible usage of any MySQL database. Just import the class anywhere and use its functions.

Database.class.php

<?php

    /**
     * This class has several static functions for connecting and interacting with a MySQL database using the PDO extension.
     *
     * This class is focused on ease-of-use and flexibility. All functions are static and can be used anywhere without
     * creating an instance. For it to work, just change the credentials in the top of the file to match yours, and you're good to go.
     *
     * The query function automatically detects if only one result is returned and array_shifts onto it (if $_force_array is set to FALSE).
     * This makes querying a single row of data way easier. When you want to query more rows, be sure to set $_force_array to TRUE.
     * That'll make sure the returned data is nested in an array.
     *
     * At the bottom of this file you'll find intuitive example usages.
     *
     * by Thoby Visser
     */

    class Database
    {

        /**
         * Change the credentials below to match your environment:
         */
        const HOST          = "localhost";
        const USERNAME      = "root";
        const PASSWORD      = "";
        const DB_NAME       = "company_db";
        const PORT          = 3306;

        /**
         * The DEBUG_ERRORS variable should be set to TRUE when doing dev work on your application.
         * It will return extensive information on database-errors, including backtracing and query-parsing.
         *
         * When deploying your application, be sure to set it to FALSE.
         */
        const DEBUG_ERRORS  = true;




        /**
         * This function tries to establish a connection to a MySQL database using the PDO extension.
         *
         * Returns the PDO instance that represents the connection to the specified database.
         * If no connection has been established yet, generate a new connection using the database-credentials entered in the top of this class.
         *
         * @return  PDO     The PDO database connection.
         */
        private static function connection()
        {
            # First check if a connection doesn't exist yet:

            if(!isset($GLOBALS['__db_connection'])) {
                $pdo_dsn = 'mysql:host=' . Database::HOST . ';port=' . Database::HOST . ';dbname=' . Database::DB_NAME . ';charset=utf8';
                $pdo_options = [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false,
                ];

                try {

                    $GLOBALS['__db_connection'] = new PDO($pdo_dsn, Database::USERNAME, Database::PASSWORD, $pdo_options);

                } catch (PDOException $exception) {
                    Database::showErrorMessage( $exception->getMessage(), "Wasn't able to connect to the database." );
                    exit(1);
                }
            }

            return $GLOBALS['__db_connection'];
        }


        /**
         * Returns the ID of the last inserted row generated by a query.
         * Uses the database connection to acquire the value.
         *
         * @return  int         The primary key / id as an integer value of the last inserted row.
         */
        public static function lastInsertId()
        {
            return Database::connection()->lastInsertId();
        }


        /**
         * Executes query onto database. Prepares query using PDOs parameter-preparing.
         * Supports all types of queries, automatically returns values when given a SELECT query.
         *
         * @param   string              The query to be executed
         * @param   array               Parameters for the query, used against SQL Injection
         * @param   bool                Define if the query force-returns an array on a SELECT query
         * @return  array|bool|null     Query result, returns an bool when only executed the query, returns an array when SELECTing data and return null on error
         */
        public static function query($_query, $_parameters = [], $_force_array = false)
        {
            if (!empty($_query)) {
                $GLOBALS['__db_last_query']  = $_query;
                $GLOBALS['__db_last_params'] = $_parameters;

                try {

                    $pdo_statement = Database::connection()->prepare($_query);
                    $query_success = $pdo_statement->execute($_parameters);

                } catch( PDOException $exception ) {
                    Database::showErrorMessage( $exception->getMessage() . "\nQuery: <span style='color: gray'>" . Database::debugLastQuery() . "</span>", "Wasn't able to successfully execute database query." );
                    return null;
                }

                $fetched_data = $pdo_statement->fetchAll();

                if($pdo_statement->columnCount() > 0) {

                    return (count($fetched_data) == 1 && $_force_array == false) ? array_shift($fetched_data) : $fetched_data;

                } else {
                    return $query_success;
                }
            }
            return null;
        }

        /**
         * Function simulates PDOs parameter-preparing on the last used query.
         * Will return an HTML-formatted string with the parameters filled in.
         *
         * Useful for debugging and visualizing a query.
         *
         * @return  string|null     The last
         */
        public static function debugLastQuery()
        {

            if(!empty($GLOBALS['__db_last_query'])) {

                $query      = $GLOBALS['__db_last_query'];
                $parameters = @ $GLOBALS['__db_last_params'] ?: [];

                $indexed = $parameters == array_values($parameters);

                foreach($parameters as $key => $value) {
                    if(is_string($value)) $value = "'" . addslashes($value) . "'";

                    $key = ":" . ltrim($key, ':');
                    $value = "<span title='$key' style='cursor:help;text-decoration:underline'>$value</span>";

                    $query = ($indexed) ? preg_replace('/\?/', $value, $query, 1) : str_replace($key, $value, $query);
                }

                return $query;

            } else {

                Database::showErrorMessage("Couldn't debug information: there was no database query executed yet.");

            }

            return null;
        }

        /**
         * Displays formatted text block with error message.
         * Function optionally shows backtrace when Database::DEBUG_ERRORS is set to true.
         * If $_alternative is null, the function won't return any text.
         *
         * @param   string              The message shown when Database::DEBUG_ERRORS is set to TRUE.
         * @param   string|null         The message shown when Database::DEBUG_ERRORS is set to FALSE.
         */
        private static function showErrorMessage($_debug_message, $_alternative = null)
        {
            if(!is_null($_alternative) || Database::DEBUG_ERRORS) {

                $caller = array_shift( array_slice(debug_backtrace(), 1) );
                $function = $caller['class'] . $caller['type'] . $caller['function'] . "()";


                echo "<pre><strong style='color: darkred'>Database Error:</strong>\n";

                echo ((Database::DEBUG_ERRORS) ? ("<small style='color: darkred'>(<em>Error in <strong>" . $function . "</strong> in <strong>" . $caller['file'] . "</strong> on line #" . $caller['line'] . "</em>)</small>\n") : "");
                echo ((Database::DEBUG_ERRORS) ? ($_debug_message) : ($_alternative));

                echo "</pre>";
            }

        }
    }

    ### Example usages:


    # Example of how to query for single row using primary key
    $customer = Database::query("SELECT `first_name`, `last_name` FROM `customers` WHERE `id` = :Identifier", [
        ':Identifier' => $_GET['customerid']
    ]);

    # Example of how to query for multiple rows, notice how $_force_array is set to TRUE.
    $newest_products = Database::query("SELECT `product_name`, `price` FROM `products` ORDER BY `date_added` DESC LIMIT 10", [], true);

    # Example of how to UPDATE data
    Database::query("UPDATE `products` SET `price` = :DiscountPrice WHERE `category` = :CategoryName;", [
        ':DiscountPrice' => 4.95,
        ':CategoryName' => 'books'
    ]);