Database is usually the slowest part of PHP application or web site. Most developers solve that problem using Memcached or similar caching software.

But if you have a small web site or a application that doesn’t have much traffic you fall back to a default database functions like mysql_query and mysqli_query and so on. But at that point we usually hit a problem how to support different databases.

Some people prefer MySQL database while others prefer MSSQL database. So most developers create a wrapper class that will make transfer to a different database as easy as possible.

Mine looks like this:

<?php

    /*************************************************************************************************************
     * Database Abstraction/Wrap class
     *
     * @author     Jaka Prasnikar - https://prahec.com/
     * @version    2.0 (Updated 17.10.2012)
     ************************************************************************************************************ */

    /**
     * Class Database
     */
    class Database {

        /**
         * Array of options for this class
         *
         * @var array
         */
        private $options;


        /**
         * MySQLi connection resource, required for every other action
         *
         * @var resource
         */
        private $link;


        /**
         * Cache object (passed from Caching class)
         *
         * @var bool|object
         */
        private $cache = false;


        /**
         * Database connection status
         *
         * @var bool
         */
        public $connected = false;


        /**
         * @var string
         */
        public $error;


        /**
         * @var string
         */
        public $lastQuery;


        /**
         * Database constructor
         * We do not connect to database unless required
         *
         * @param               $mysql_info_array
         * @param bool|object   $cache
         * @param bool          $persistent
         */
        function __construct( $mysql_info_array, $cache = false, $persistent = false ) {

            $this->options = $mysql_info_array + array(

                    // Basic configuration
                    'host'        => '127.0.0.1',
                    'socket'      => false,
                    'port'        => 3306,
                    'user'        => 'root',
                    'pass'        => '',
                    'db'          => 'database',

                    // Additional settings
                    'compression' => false,
                    'charset'     => 'UTF8',
                    'timeout'     => '5',       ## How long does MySQL have to respond (timeout)
                    'logging'     => array(
                        'enabled' => false,
                        'file'    => 'mysqli.log',
                        'level'   => 1
                    )
                );

            // Caching object passed to the class
            if ( $cache !== false && is_object( $cache ) && method_exists( $cache, 'set' ) && method_exists( $cache, 'get' ) ) $this->cache = $cache;
            if ( $persistent === true ) $this->options[ 'host' ] = "p:{$this->options['host']}"; ## Enable persistent connection

        }


        /**
         * This function requires no parameters, its pretty simple. It checks for existing database connection
         * If one does not exist, we create it. On error throws exception to make sure nothing else goes wrong
         *
         * @return bool
         * @throws \Exception
         */
        private function connect() {

            if ( $this->connected == false ) {

                // Create MySQLi Object
                $this->link = mysqli_init();

                // Set timeout to X seconds, we don't want endless connection attempts from PHP to MySQL
                $this->link->options( MYSQLI_OPT_CONNECT_TIMEOUT, $this->options[ 'timeout' ] );

                // Now finally attempt to connect with pre-defined settings
                $this->link->real_connect(
                    $this->options[ 'host' ], $this->options[ 'user' ], $this->options[ 'pass' ], $this->options[ 'db' ],
                    $this->options[ 'port' ], $this->options[ 'socket' ], ( ( $this->options[ 'compression' ] == true ) ? MYSQLI_CLIENT_COMPRESS : 0 )
                );


                // Check if connection was successful
                if ( $this->link->connect_error ) {

                    if ( $this->options[ 'logging' ][ 'enabled' ] != false ) // If logfile is set, log to file
                        file_put_contents( $this->options[ 'logging' ][ 'file' ], "[" . date( "j.n.Y-G:i" ) . "] Could not connect to database! ERROR: {$this->link->error}\n", FILE_APPEND );

                    // Throw exception
                    throw new Exception( 'Connection to the database failed! ' . $this->link->connect_error, $this->link->connect_errno );


                } else { // Only set MySQLi Option if connection was successful

                    // We use UTF-8 encoding for connection between class/database
                    $this->link->set_charset( $this->options[ 'charset' ] );

                }

                return $this->connected = true;

            }

            return $this->connected;

        }


        /**
         * Database query function, this is direct no cache or anything
         *
         * @param $command
         *
         * @return object|bool $result
         */
        public function query( $command ) {

            // Check if we can use cache or not
            if ( !empty( $command ) ) {

                // Benchmark queries
                $bench = microtime( true );

                // Only connect to database if required & execute query
                $this->connect();
                $result = $this->link->query( $command );

                // Catch database errors (don't throw exceptions here)
                if ( $this->link->error ) {

                    if ( $this->options[ 'logging' ][ 'enabled' ] != false AND $this->options[ 'logging' ][ 'level' ] == 1 | 2 )
                        file_put_contents( $this->options[ 'logging' ][ 'file' ], "[" . date( "j.n.Y-G:i" ) . "] Query: {$command}; ERROR: {$this->link->error}\n", FILE_APPEND );

                    $this->error = $this->link->error;

                } else {

                    // If Log enabled Log to File (including benchmark of the query)
                    if ( $this->options[ 'logging' ][ 'enabled' ] != false AND $this->options[ 'logging' ][ 'level' ] == 2 )
                        file_put_contents( $this->options[ 'logging' ][ 'file' ], "[" . date( "j.n.Y-G:i" ) . "] Query: {$command} (" . number_format( ( ( microtime( true ) - $bench ) * 1000 ), 2 ) . "ms)\n", FILE_APPEND );

                    // Its important we set error to false if all went through
                    $this->error = false;

                    /* Store last query command to object */
                    $this->lastQuery = $command;

                }

                // Return whole MySQLi result object
                return $result;

            }

            return false;

        }


        /**
         * This only returns array, take care not to use with while!
         * Note: This function may slow down greatly or run out of memory at large results
         *
         * @param      $command
         * @param bool $cache
         *
         * @return array|bool
         */
        public function fetch( $command, $cache = false ) {

            // Initial definition
            $output = false;

            // If caching enabled, attempt to get cached result
            if ( $cache !== false && $this->cache !== false )
                $output = $this->cache->get( ( ( $cache === true ) ? md5( $command ) : $cache ) );


            // There is no cached data yet, create it
            if ( $output === false ) {

                // Query
                $result = $this->query( $command );

                // If result has more than one row else sql failed
                if ( $result->num_rows >= 1 ) {

                    // Loop through assoc fetch
                    while ( $data = $result->fetch_assoc() ) : $output[] = $data; endwhile;

                }

                // Attempt to cache if enabled
                if ( $cache !== false && $this->cache !== false )
                    $this->cache->set( ( ( $cache === true ) ? md5( $command ) : $cache ), $output );

            }

            return $output;

        }


        /**
         * This is just a simple helper function to get single result, very simple
         *
         * @param      $command
         * @param bool $cache
         *
         * @return bool|mixed
         */
        public function fetchRow( $command, $cache = false ) {

            $data = $this->fetch( $command, $cache );
            return ( ( $data == false ) ? false : $data[ 0 ] );

        }


        /**
         * Simple function to count amount of rows in specified table
         *
         * @param        $table
         * @param string $row
         *
         * @return int
         */
        public function num( $table, $row = '*' ) {

            // Execute Query
            $do = $this->fetchRow( "SELECT COUNT({$row}) as num FROM `{$table}`", true );
            list( $num ) = ( ( $do ) ? $do[ 'num' ] : 0 );

            // Return
            return $num;

        }


        /**
         * MySQLi manual escape function shortage
         *
         * @param $input
         *
         * @return string
         */
        public function s( $input ) {

            $this->connect(); // Connect if no connection is established so far
            return mysqli_real_escape_string( $this->link, $input );

        }


        /**
         * Get the identification of the latest inserted ID
         *
         * @return string
         */
        public function lastID() {

            // Only connect to database if required
            $this->connect();

            // Get last insert_id
            $lastID = $this->link->insert_id;
            if ( empty( $lastID ) OR $lastID == "0" ) {

                return "1";

            } else {

                return $lastID;

            }

        }


        /**
         * Function to insert data into database, this function manually escapes strings.
         * its recommended to use prepared statements instead, better security
         *
         * @param $table
         * @param $array
         *
         * @return bool
         */
        public function insert( $table, $array ) {

            // Exit function if table is empty or array isn't passed properly
            if ( empty( $table ) OR !is_array( $array ) ) {
                return false;
            }

            // First escape whole array
            $array = array_map( array( $this, 's' ), $array );

            // Build query :)
            $this->query( "INSERT INTO `{$table}` (`" . implode( '`,`', array_keys( $array ) ) . "`) VALUES ('" . implode( "' , '", $array ) . "')" );

            // Try insert, return false if it fails
            if ( !$this->error ) {

                return true;

            } else {

                return false;

            }

        }


        /**
         * Update a row in database table, simple function that also manually escapes data.
         * its recommended to use prepared statements instead, better security
         *
         * @param $table
         * @param $where
         * @param $array
         *
         * @return bool
         */
        public function update( $table, $where, $array ) {

            // Exit function if table is empty or array isn't passed properly
            if ( empty( $table ) OR !is_array( $array ) ) return false;

            // First escape whole array using array map
            $array = array_map( array( $this, 's' ), $array );

            // Count amount of data to update
            if ( count( $array ) > 0 ) {

                foreach ( $array as $key => $value ) {

                    $value           = "'$value'";
                    $implodedArray[] = "`$key` = $value";

                }

            }

            // Build query :)
            $this->query( "UPDATE `{$table}` SET " . implode( ', ', $implodedArray ) . " WHERE {$where}" );

            // Try insert, return false if it fails
            if ( !$this->error ) {

                return true;

            } else {

                return false;

            }


        }


        /**
         * Simple helper function to use with "while" and on "query" method
         *
         * @param $resource
         *
         * @return mixed
         */
        public function assoc( $resource ) {

            return $resource->fetch_assoc();

        }


        /**
         * Database class destructor (called just before PHP execution stops)
         */
        function __destruct() {

            if ( is_resource( $this->link ) ) {

                $this->link->close();
                $this->connected = false;

            }

        }


    }

The wrapper also has following functions:

Escape String:

<?php

    $db->s(“my string”);

?>

Display Last inserted ID:

<?php

    $db->lastID();

?>

Count all rows:

<?php

    $db->num(‘tablename’); // Much faster then mysql_num_rows();

?>

Associative result array:

<?php

    $db->assoc();

?>

Numeric and Assoc array:

<?php

    $db->arr();

?>

Its also extremely easy to add more functions to work with.

I’m going to add support for prepared queries one day as well ?