Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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 .= "rnRaw 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);
- }
- }
- ?>
- <?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;
- }
- }
- <?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']);
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement