Connecting 2 databases: one for admins and one for customers

Question

I am working with 2 databases.

One for admins ('backoffice') and one for users.

Account managers: Use the 'backoffice' database to manage customer accounts.

Customers: Whenever a users connects and inserts his username password and company name, the code must connect to the backoffice database, validate his data, get his database host name (there are 5 customer databases), disconnect from 'backoffice' and connect to the customer personal account.

Is it "good practice" to create a general 'Database' class, a 'Backoffice_db' class (extending if with 'Database') and a 'Customer_db' class (extending it with Backoffice_db class)? or should i manage both in one 'Database' class?

Database.class.php:

<?php
/** 
 *  Database class 
 *  https://github.com/wickyaswal/indieteq-php-my-sql-pdo-database-class
 *  
 */

class Database
{
    # @object, The PDO object
    protected $pdo;

    # @object, PDO statement object
    protected $sQuery;

    # @array,  The database settings
    protected $ini;

    # @bool ,  Connected to the database
    protected $connected = false;

    # @object, Object for logging exceptions    
    protected $log;

    # @array, The parameters of the SQL query
    protected $parameters;

    # Create an instance 
    private

    /**
     *   Default Constructor 
     *
     *  1. Instantiate Log class.
     *  2. Creates the parameter array.
     */
    public function __construct()
    {

    }

    /**
     *   Get the PDO connection 
     *
     */
     public function getPDO() 
     {
           if ($this->pdo instanceof PDO) 
                return $this->pdo;
     }

    /**
     *  This method makes connection to the database.
     *  
     *  1. Reads the database settings from a ini file. 
     *  2. Puts  the ini content into the settings array.
     *  3. Tries to connect to the database.
     *  4. If connection failed, exception is displayed and a log file gets created.
     */
    protected function Connect($host, $user, $password, $dbname)
    {

        $dsn            = 'mysql:dbname=' . $dbname . ';host=' . $host . '';

        try {
            # Read settings from INI file, set UTF8
            $this->pdo = new PDO($dsn, $user, $password, array(
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
            ));

            # We can now log any exceptions on Fatal error. 
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            # Disable emulation of prepared statements, use REAL prepared statements instead.
            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

            # Connection succeeded, set the boolean to true.
            $this->connected = true;
        }
        catch (PDOException $e) {
            # Write into log
            // echo $this->ExceptionLog($e->getMessage());
            dbg( $this->ExceptionLog($e->getMessage()) );
            die();
        }
    }


    /*
     *   You can use this little method if you want to close the PDO connection
     *
     */
    public function CloseConnection()
    {
        # Set the PDO object to null to close the connection
        # http://www.php.net/manual/en/pdo.connections.php
        $this->pdo = null;
    }


    /*
     *   Gets db.ini data
     *
     */
    protected function get_db_ini()
    {
        if ( empty($this->ini) ) $this->load_db_ini();
    }


    /*
     *   Load db.ini file contents
     *
     */
    protected function load_db_ini()
    {
        $this->ini = parse_ini_file('/etc/optimizeit/db.ini', true);
    }


    /*
     *   Gets section from db.ini resultarray 
     *
     */
    protected function get_db_ini_section($section)
    {
        return isset( $this->ini[$section] ) ? $this->ini[$section] : Array();
    }


    /**
     *  Every method which needs to execute a SQL query uses this method.
     *  
     *  1. If not connected, connect to the database.
     *  2. Prepare Query.
     *  3. Parameterize Query.
     *  4. Execute Query.   
     *  5. On exception : Write Exception into the log + SQL query.
     *  6. Reset the Parameters.
     */
    protected function Init($query, $parameters = "")
    {
        # Connect to database
        if (!$this->connected) {
            $this->Connect();
        }
        try {
            # Prepare query
            $this->sQuery = $this->pdo->prepare($query);

            # Add parameters to the parameter array 
            $this->bindMore($parameters);

            # Bind parameters
            if (!empty($this->parameters)) {
                foreach ($this->parameters as $param => $value) {
                    if(is_int($value[1])) {
                        $type = PDO::PARAM_INT;
                    } else if(is_bool($value[1])) {
                        $type = PDO::PARAM_BOOL;
                    } else if(is_null($value[1])) {
                        $type = PDO::PARAM_NULL;
                    } else {
                        $type = PDO::PARAM_STR;
                    }
                    // Add type when binding the values to the column
                    $this->sQuery->bindValue($value[0], $value[1], $type);
                }
            }

            # Execute SQL 
            $this->sQuery->execute();
        }
        catch (PDOException $e) {
            # Write into log and display Exception
            // echo $this->ExceptionLog($e->getMessage(), $query);
            dbg( $this->ExceptionLog($e->getMessage(), $query) );
            die();
        }

        # Reset the parameters
        $this->parameters = array();
    }


    /**
     *  @void 
     *
     *  Add the parameter to the parameter array
     *  @param string $para  
     *  @param string $value 
     *  Updated 11.07.2018: https://github.com/wickyaswal/indieteq-php-my-sql-pdo-database-class/issues/83
     */
    public function bind($para, $value)
    {
        if (is_int($para)) {
          $this->parameters[sizeof($this->parameters)] = [++$para , $value];
        } else {
          $this->parameters[sizeof($this->parameters)] = [":" . $para , $value];
        }
    }

    /**
     *  @void
     *  
     *  Add more parameters to the parameter array
     *  @param array $parray
     */
    public function bindMore($parray)
    {
        if (empty($this->parameters) && is_array($parray)) {
            $columns = array_keys($parray);
            foreach ($columns as $i => &$column) {
                $this->bind($column, $parray[$column]);
            }
        }
    }


    /**
     *  If the SQL query  contains a SELECT or SHOW statement it returns an array containing all of the result set row
     *  If the SQL statement is a DELETE, INSERT, or UPDATE statement it returns the number of affected rows
     *
     *      @param  string $query
     *  @param  array  $params
     *  @param  int    $fetchmode
     *  @return mixed
     */
    public function query($query, $params = null, $fetchmode = PDO::FETCH_ASSOC)
    {
        $query = trim(str_replace("\r", " ", $query));

        $this->Init($query, $params);

        $rawStatement = explode(" ", preg_replace("/\s+|\t+|\n+/", " ", $query));

        # Which SQL statement is used 
        $statement = strtolower($rawStatement[0]);

        if ($statement === 'select' || $statement === 'show') {
            return $this->sQuery->fetchAll($fetchmode);
        } elseif ($statement === 'insert' || $statement === 'update' || $statement === 'delete') {
            return $this->sQuery->rowCount();
        } else {
            return NULL;
        }
    }


    /**
     *  Returns the last inserted id.
     *  @return string
     */
    public function lastInsertId()
    {
        return $this->pdo->lastInsertId();
    }


    /**
     * Starts the transaction
     * @return boolean, true on success or false on failure
     */
    public function beginTransaction()
    {
        return $this->pdo->beginTransaction();
    }


    /**
     *  Execute Transaction
     *  @return boolean, true on success or false on failure
     */
    public function executeTransaction()
    {
        return $this->pdo->commit();
    }


    /**
     *  Rollback of Transaction
     *  @return boolean, true on success or false on failure
     */
    public function rollBack()
    {
        return $this->pdo->rollBack();
    }


    /**
     *  Returns an array which represents a column from the result set 
     *
     *  @param  string $query
     *  @param  array  $params
     *  @return array
     */
    public function column($query, $params = null)
    {
        $this->Init($query, $params);
        $Columns = $this->sQuery->fetchAll(PDO::FETCH_NUM);

        $column = null;

        foreach ($Columns as $cells) {
            $column[] = $cells[0];
        }

        return $column;
    }


    /**
     *  Returns an array which represents a row from the result set 
     *
     *  @param  string $query
     *  @param  array  $params
     *  @param  int    $fetchmode
     *  @return array
     */
    public function row($query, $params = null, $fetchmode = PDO::FETCH_ASSOC)
    {
        $this->Init($query, $params);
        $result = $this->sQuery->fetch($fetchmode);
        $this->sQuery->closeCursor(); // Frees up the connection to the server so that other SQL statements may be issued,
        return $result;
    }


    /**
     *  Returns the value of one single field/column
     *
     *  @param  string $query
     *  @param  array  $params
     *  @return string
     */
    public function single($query, $params = null)
    {
        $this->Init($query, $params);
        $result = $this->sQuery->fetchColumn();
        $this->sQuery->closeCursor(); // Frees up the connection to the server so that other SQL statements may be issued
        return $result;
    }


    /** 
     * Writes the log and returns the exception
     *
     * @param  string $message
     * @param  string $sql
     * @return string
     */
    protected function ExceptionLog($message, $sql = "")
    {
        $exception = "Unhandled Exception. <br /> \n";
        $exception .= $message;
        $exception .= "<br /> \n You can find the error back in the log.";

        if (!empty($sql)) {
            # Add the Raw SQL to the Log
            $message .= "\r\nRaw SQL : " . $sql;
        }
        # Write into log
        // $this->log->write($message);

        return $exception;
    }


    /**
     *  Generate a string for columns selection
     *  @param      Array   $app_name   An array of app name/s or preferable - comma separated sting 
     *  @throws     Array               An array of app_id, app_name, lhm_threshold, enable_evm, evm_type, evm_rate for each app.
     */
    private function select_columns(Array $colums_array)
    {
        return implode(",", $columns_array);
    }

}
?>

Backoffice_db.php:

<?php
/**
 *  Backoffice Database Connection class 
 *
 */
require("Database.class.php");

class Backoffice_db extends Database
{
    # @string, Database Host
    private $bo_host;

    # @string, Database Username
    protected $user     = 'root';

    # @string, Database Password
    protected $password = 'thisismyrealpassword';

    # @string, Database Name 
    private $bo_dbname  = 'backoffice';


    /**
     *   Default Constructor 
     *
     *  1. Instantiate Log class.
     *  2. Creates the parameter array.     
     *  3. Get backoffice variables from ini
     *  4. Connect to database.
     *
     */
    public function __construct()
    {
        $this->parameters = array();                                                    // Init params for queries. 
        if ( empty($this->ini) ) $this->get_db_ini();                                   // Make sure INI is fetched. 

        $this->get_bo_host();                                                           // Set global variables for the backoffice.
        $this->Connect($this->bo_host, $this->user, $this->password, $this->bo_dbname); // Connect with global vars. 
    }


    /**
     *   Get backoffice HOST for connection 
     *
     *  1. Get backoffice credentials.
     *  2. Set backoffice credentials.
     *
     */
    protected function get_bo_host()
    {
        $backoffice_credentials = $this->get_db_config($this->dbname);
        $this->bo_host          = $backoffice_credentials['host'];
    }


    /**
     *   Get db configurations via given $dbname 
     *
     *  1. Return 'main' array section (backoffice credentials)
     *
     */
    protected function get_db_config($dbname)
    {

        try 
        {
            $settings = $this->get_db_ini_section(md5($this->bo_dbname));

            if ( empty($settings) ) 
                throw new Exception('Cannot Find the customers name `' . $this->bo_dbname . '` in .ini file.');
        }
        catch (Exception $e)
        {
            // echo $this->ExceptionLog($e->getMessage());
            // dbg( $this->ExceptionLog($e->getMessage()) );
            err( $this->ExceptionLog($e->getMessage()) );
            die($ex->getMessage());
        }

        return $settings;
    }



}

Customer_db.php:

<?php
/**
 *  Customer Database Connection class 
 *
 */
require("Backoffice_db.class.php");

class Customer_db extends Backoffice_db
{

    # @string, Database Host
    private $host;

    # @string, Database Name 
    private $dbname;

    # The Single Instance
    private static $instance; 


    /*
     *  Get an instance of the Database
     *  @return Instance
    */
    public static function getInstance() {

        if ( !self::$instance ) {           // If no instance then make one
            self::$instance = new self();
        }

        return self::$instance;             // Return the Database obj
        // return self::$instance->getPDO();// Return the PDO connectoion instance from dabase obj 
    }


    /**
     *   Default Constructor 
     *  1. Sets given dbnmae
     *  2. Connects to the backoffice
     *  3. Get customer data from backoffice db
     *  4. Disconnects from backoffice db
     *  5. Connect to customers database if active account
     */
    public function __construct($dbname)
    {
        self::$instance = $this;

        $this->dbname                               = $dbname;

        $bo_conn                                    = new Backoffice_db();

        list($this->host, $this->dbname, $status)   = $this->get_customer_conn_info($bo_conn);

        $bo_conn->CloseConnection();                                                               // Disconnect from backoffice and connect to customer

        try
        {
            if ( ($status === 'Deleted') || ($status === 'Inactive') ) 
                throw new Exception('Customer ('. $dbname .') account is "Deleted" or "Inactive".'); 

            $this->Connect($this->host, $this->user, $this->password, $this->dbname);
        }
        catch(Exception $e)
        {
            err($this->ExceptionLog($e->getMessage()));
            die($e->getMessage());
        }

    }


    /**
     *   Get user HOST, DBNAME & STATUS from backoffice=>customers table
     *
     *  1. Run a query on backoffice to fetch users host and db name
     *  2. return 2 separate vairbles of host and db name 
     *
     */
    protected function get_customer_conn_info($bo_conn){

        $customer_data = $bo_conn->row("SELECT db_host, db_name, assessment_status FROM customers WHERE customer_name = :customer_name", array("customer_name" => $this->dbname));
        return array($customer_data['db_host'], $customer_data['db_name'], $customer_data['assessment_status']);
    }

}

Answer 1

There are several areas of improvement

Architecture

First of all, I don't see why these classes should inherit to each other. To me, there is no difference between these classes and I don't see why can't you just instantiate two objects of the same class. Of course if you fix another issue:

There is at least one principal principle violated in your classes - a single responsibility principle. Why a database class should look somewhere in the filesystem for its credentials? Why a filename with credentials is hardcoded?

You must create a distinct class for the configuration and provide its instance as a parameter when instantiating/initializing your database class.

PDO wrapper

Unfortunately, you choose a very bad example to model your class after. It even could be used as a showcase for bad practices, featuring every single one of them. Consider taking a look at my article on database wrappers' common mistakes. In brief, your class

• implements an inflexible error reporting (this is also a code duplication and another violation of the single responsibility principle. It makes no sense for a database class to have an error logger of its own. whatever logging should be done elsewhere (notably core PHP is already excel in it))
• stateful
• duplicates PDO's functionality
• at the same time limits it, offering you less features than exists in vanilla PDO
• overall untidy with a lot of duplicated or unnecessary code.

What I would do is just a single class that extends PDO with addition of 3 new functions:

• init($config) - called once, initializing a connection
• run($query, $params) to run a query with parameters
• getInstance() get the instance

You can take a look at such a class in my other article, make sure to check the usage examples below. You'd be surprised to find most of your code unnecessary, such as for telling a select query from insert

And one class to supplement it, a class to get configuration options, with two methods, one to get from a file and one to get from a database.