Creating a simple MySQL abstraction library

28
January
Image

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 🙂

Share:


There are no comments on this blog post yet.

Leave A Comment