Do I need to Instantiate Php Database class for every query?

Question

I have a php database class for managing all my database queries:

    class DatabaseConnection() 
{
    private $link;
    public $filter;

    public function log_db_errors( $error, $query )
    {

        if( MY_DEBUG )
        {
            echo $message;
        }
    }


    public function __construct()
    {
        global $connection;
        mb_internal_encoding( 'UTF-8' );
        mb_regex_encoding( 'UTF-8' );
        $this->link = new mysqli( MY_HOST, MY_USER, MY_PASS, MY_DB );
        $this->link->set_charset( "utf8" );

        if( $this->link->connect_errno )
        {
            $this->log_db_errors( "Connect failed", $this->link->connect_error );
            echo 'Server error. Please try again sometime. DB';
            exit();
        }
    }

    public function __destruct()
    {
        $this->disconnect();
    }

    public function filter( $data )
    {
        if( !is_array( $data ) )
        {
            $data = trim( htmlentities( $data ) );
            $data = $this->link->real_escape_string( $data );
        }
        else
        {
            $data = array_map( array( 'DB', 'filter' ), $data );
        }
        return $data;
    }

    public function query( $query )
    {
        $full_query = $this->link->query( $query );
        if( $this->link->error )
        {
            $this->log_db_errors( $this->link->error, $query );
            $full_query->free();
            return false; 
        }
        else
        {
            $full_query->free();
            return true;
        }
    }

    public function my_table_exists_create( $table,  $variables = array() ) {
        $check = $this->link->query("SELECT * FROM '$table' LIMIT 1");        
        if( $check ) return true;           
        else {
            if( empty( $variables ) ) {
                return false;
                exit;
            }

            $sql = "CREATE TABLE IF NOT EXISTS ". $table;
            $fields = array();
            $values = array();
            foreach( $variables as $field ) {
                $fields[] = $field;     //$values[] = "'".$value."'";
            }
            $fields = ' (' . implode(', ', $fields) . ')';      
            $sql .= $fields;
            $query = $this->link->query( $sql );

            if( $this->link->error ) {
                $this->log_db_errors( $this->link->error, $sql );
                return false;
            }
            else return true;
        }
    }

    public function my_num_rows( $query )
    {
        $num_rows = $this->link->query( $query );
        if( $this->link->error )
        {
            $this->log_db_errors( $this->link->error, $query );
            return $this->link->error;
        }
        else
        {
            return $num_rows->num_rows;
        }
    }

    public function exists( $table = '', $check_val = '', $params = array() )
    {
        if( empty($table) || empty($check_val) || empty($params) )
        {
            return false;
            exit;
        }
        $check = array();
        foreach( $params as $field => $value )
        {

            if( !empty( $field ) && !empty( $value ) )
            {
                if( $this->db_common( $value ) )
                {
                    $check[] = "$field = $value";   
                }
                else
                {
                    $check[] = "$field = '$value'";   
                }
            }

        }
        $check = implode(' AND ', $check);

        $rs_check = "SELECT $check_val FROM ".$table." WHERE $check";
        $number = $this->my_num_rows( $rs_check );
        if( $number === 0 )
        {
            return false;
        }
        else
        {
            return true;
        }
        exit;
    }


    public function disconnect()
    {
        $this->link->close();
    }

}

I use this class to manage all my queries, like insert to the database :

    $database = new DatabaseConnection();   
    $Item_Details = array(
        'item_title' => trim($_POST['title']),
        'item_content' => trim($_POST['content']),
        'item_updated' => date('Y-m-d H:i:s'),
        'item_updatedby' => $my_loginid,
    );
    $where_clause = array('itemid' => $itemid);
    $updated = $database->as_update( 'my_item', $Item_Details , $where_clause, 1 );

Now I need to know I can use this class without having a lot of connections that can slow down connection to the server which leads to timeout and having too many connection. I thought I could use a global variable

function my_constant_initialize()
{
   global $databasecon;
   $databasecon = new DatabaseConnection();
   return $databasecon;
}

So please advice how to avoid too many connections. Also as well as tell whether it is necessary to instanciate the database class for every query or I can just call it once because I have used a lot of Include and require functions in my php code.

Answer 1

Reaching the MySQL database connection limit is hard in OOP PHP because it destructs the connection object if the script is finished. Just to be safe, have a destruct function in your class and unset the object at the end of the script. If you are really worried about reaching the connection cap you can go into MySQL and modify the max_connection_limit from I think 1000 to higher and increase the buffer pool.

Additionally you might look into switching languages to like Java which has a technique called "connection pooling." Works better than PHP's version. No this is not the same as p_connect or whatever it is in PHP.

Answer 2

At least you don't have to initiate the connection every time you are calling your class.

To see how you could use your database class without having to instantiate it every time, we would need to see some more of your code. The already mentioned singleton pattern is the way to go, but it is hard to judge if it would really improve your code without knowing how you are actually programming.

This shows an implementation of your class as singleton:

class DatabaseConnection {

    protected static $instance = null;
    private $link;
    public $filter;

    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self;
        }
        return self::$instance;
    }

    protected function __construct() {
        global $connection;
        mb_internal_encoding( 'UTF-8' );
        mb_regex_encoding( 'UTF-8' );
        $this->link = new mysqli( MY_HOST, MY_USER, MY_PASS, MY_DB );
        $this->link->set_charset( "utf8" );

        if( $this->link->connect_errno )
        {
            $this->log_db_errors( "Connect failed", $this->link->connect_error );
            echo 'Server error. Please try again sometime. DB';
            exit();
        }
    }

    protected function __clone() {

    }

    // add your other methods

}

You can call the connection by using

$databasecon = DatabaseConnection::getInstance();

By implementing it this way, when you call getInstance it checks if the connection has already been initialised so you can reuse it.

You set the __construct() as protected so the class can not be called without using the getInstance() method, same for __clone().