Advertisement
Guest User

Untitled

a guest
Apr 27th, 2017
142
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 7.93 KB | None | 0 0
  1. <?php
  2. /**
  3.  * Description of Db Class
  4.  *
  5.  * Database connection and query handles with defined credentials
  6.  * Credentials must be valued in the class constructor
  7.  * Use method _query for insert, update and delete queries
  8.  * Use method _fetch_array for row selecting
  9.  *
  10.  * Example of class initialize
  11.  * $db = new db;
  12.  *
  13.  * Example for use of _query:
  14.  * $params = array($title,$id);
  15.  * $sql = "UPDATE table SET title = ? WHERE id = ?";
  16.  *
  17.  * Example for use of _fetch_array:
  18.  * $params = array($value1,$value2);
  19.  * $sql = "SELECT [*, fieldnames] FROM table WHERE fieldname = ? AND fieldname2 = ?";
  20.  * $array = $db->_fetch_array($sql,$params);
  21.  *
  22.  * @author Heinz K, Nov 2016
  23.  */
  24. class db {
  25.    
  26.     /* Setting properties */
  27.     protected $db;
  28.     protected $dbhost;
  29.     protected $dbuser;
  30.     protected $dbpassword;
  31.     protected $dbname;
  32.     private $sql;
  33.     private $stmt;
  34.     private $result;
  35.     private $row;
  36.    
  37.     /**
  38.      * Class constructor - sets db credentials
  39.      */
  40.     public function __construct() {
  41.         $this->db = "";
  42.         $this->dbhost = "";
  43.         $this->dbuser = "";
  44.         $this->dbpassword = "";
  45.         $this->dbname = "";
  46.         $this->sql = "";
  47.         $this->stmt = "";
  48.         $this->result = "";
  49.         $this->row = array();        
  50.     }
  51.    
  52.     /**
  53.      * DB Connection Error
  54.      * Writes error on connection fail
  55.      */
  56.     public function _connect_error() {
  57.         echo "DB ERROR: " . mysqli_connect_error();
  58.         exit();
  59.     }    
  60.    
  61.     /**
  62.      * DB Method Statement Error
  63.      * Writes error on query fail
  64.      */
  65.     public function _error() {
  66.         echo "STMT ERROR: " . $this->db->error;
  67.         exit();
  68.     }    
  69.  
  70.     /**
  71.      * DB Connect Method
  72.      * Establish a connection to a database
  73.      */
  74.     public function _connect() {
  75.         @$this->db = new mysqli($this->dbhost, $this->dbuser, $this->dbpassword,$this->dbname);
  76.         if (mysqli_connect_errno())
  77.         {
  78.             $this->_connect_error();
  79.         }
  80.         /* BUG FIX: Sets the connection charset to fix danish letter bug */
  81.         mysqli_set_charset($this->db,"utf8");
  82.        
  83.     }
  84.    
  85.     /**
  86.      * DB Query
  87.      * Send a SQL query with or without parametres
  88.      * @param string $sql
  89.      * @param array $params
  90.      * @param int $sanitize
  91.      */
  92.     public function _query($sql, $params = NULL, $sanitize = TRUE) {
  93.  
  94.         $this->sql = $this->_sanitize($sql,$sanitize);
  95.        
  96.         /* Exit on error if statement fails and */
  97.         if(!$this->db->prepare($this->sql)) {
  98.             $this->_error();
  99.         } else {    
  100.             $this->stmt = $this->db->prepare($this->sql);
  101.         }
  102.        
  103.         if(is_array($params)) {
  104.             $this->_bindparam($params);
  105.         }
  106.        
  107.         if(!$this->stmt->execute()) {
  108.             $this->_error();
  109.         }
  110.         $this->stmt->reset();
  111.         $this->stmt->close();
  112.     }      
  113.    
  114.     /**
  115.      * DB Fetch Array
  116.      * Send a SQL select query with or without parametres
  117.      * and returns an array with given values
  118.      * @param string $sql
  119.      * @param array $params
  120.      * @param int $sanitize
  121.      * @return array Returns a dimensioned array with selected rows and fields
  122.      */
  123.     public function _fetch_array($sql, $bindparams = NULL, $sanitize = TRUE, $useKeyType = MYSQLI_ASSOC) {
  124.        
  125.         $this->sql = $this->_sanitize($sql,$sanitize);
  126.        
  127.         /* Exit on error if statement fails and */
  128.         if(!$this->db->prepare($this->sql)) {
  129.             $this->_error();
  130.         } else {    
  131.             $this->stmt = $this->db->prepare($this->sql);
  132.         }        
  133.  
  134.         if(!empty($bindparams)) {
  135.             $this->_bindparam($bindparams);
  136.         }
  137.        
  138.         if(!$this->stmt->execute()) {
  139.             $this->_error();
  140.         } else {
  141.             $this->result = $this->stmt->get_result();
  142.             return $this->row = $this->result->fetch_all($useKeyType);
  143.         }
  144.         $this->stmt->free_result();
  145.         $this->stmt->close();
  146.     }
  147.    
  148.     /**
  149.      * DB Fetch Value
  150.      * Fetches a single value
  151.      * @param type $sql
  152.      * @param type $bindparams
  153.      * @param type $sanitize
  154.      * @return type
  155.      */
  156.     public function _fetch_value($sql, $bindparams = NULL, $sanitize = TRUE) {
  157.                
  158.         $this->sql = $this->_sanitize($sql,$sanitize);
  159.        
  160.         /* Exit on error if statement fails and */
  161.         if(!$this->db->prepare($this->sql)) {
  162.             $this->_error();
  163.         } else {    
  164.             $this->stmt = $this->db->prepare($this->sql);
  165.         }
  166.        
  167.         if(is_array($bindparams)) {
  168.             $this->_bindparam($bindparams);
  169.         }
  170.        
  171.         if(!$this->stmt->execute()) {
  172.             $this->_error();
  173.         } else {
  174.             $this->result = $this->stmt->get_result();
  175.             $this->stmt->store_result();
  176.             $this->row = $this->result->fetch_all();
  177.             if(count($this->row) > 0) {
  178.                 $this->row = call_user_func_array("array_merge", $this->row);
  179.                 return reset($this->row);
  180.             }
  181.         }
  182.         $this->stmt->free_result();
  183.         $this->stmt->close();        
  184.     }    
  185.    
  186.     /**
  187.      * DB Bind params
  188.      * Binds paramteters to a prepared sql statement
  189.      * @param type $arrParams
  190.      */
  191.     protected function _bindparam($arrParams) {
  192.         $params = array();
  193.         $params[0] = "";
  194.        
  195.         foreach ($arrParams as $key => $value) {
  196.             $params[0] .= $this->_gettype($value);
  197.             array_push($params, $arrParams[$key]);
  198.         }        
  199.         call_user_func_array(array($this->stmt,'bind_param'), $this->_refval($params));
  200.     }
  201.    
  202.     /**
  203.      * DB Get Inserted ID
  204.      * Returns last inserted id
  205.      * @return int
  206.      */
  207.     public function _getinsertid() {
  208.         return $this->db->insert_id;
  209.     }
  210.    
  211.     /**
  212.      * Determines the datatype o a given value
  213.      * @param type $var
  214.      * @return string
  215.      */
  216.     protected function _gettype($var) {
  217.         switch(gettype($var)) {
  218.             case 'NULL':
  219.             case 'string':
  220.                 return 's';
  221.                 break;
  222.             case 'boolean':
  223.             case 'integer':
  224.                 return 'i';
  225.                 break;
  226.             case 'blob':
  227.                 return 'b';
  228.                 break;
  229.             case 'double':
  230.                 return 'd';
  231.                 break;
  232.         }
  233.         return '';
  234.     }    
  235.    
  236.     /**
  237.      * Changes array values to referring values
  238.      * @param array $arr
  239.      * @return array $refs
  240.      */
  241.     protected function _refval($arr)
  242.     {
  243.         if (strnatcmp(phpversion(), '5.3') >= 0) {
  244.             $refs = array();
  245.             foreach ($arr as $key => $value) {
  246.                 $refs[$key] =& $arr[$key];
  247.             }
  248.             return $refs;
  249.         }
  250.         return $arr;
  251.     }    
  252.    
  253.     /**
  254.      * Method Sanitize
  255.      * Sanitizes a query string
  256.      * @param string $sql
  257.      * @param int $sanitize
  258.      * @return string
  259.      */
  260.     protected function _sanitize($sql,$sanitize) {
  261.         $str_sanitized = ($sanitize) ? filter_var($sql,FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES) : $sql;
  262.         return $str_sanitized;
  263.     }
  264.  
  265.     /**
  266.      * Method for SQL debugging
  267.      * Combines sql string and params in a string
  268.      * @param string $sql
  269.      * @param array $params
  270.      * @return string Returns a SQL
  271.      */
  272.     public function _toString($sql, $params) {
  273.         foreach($params as $key => $value) {
  274.             if(is_string($value)) {
  275.                 $params[$key] = "'" . $value . "'";
  276.             }
  277.         }
  278.         $sql = preg_replace("/[?]+(\W.)*/", implode(",",$params), $sql);
  279.         return $sql;
  280.     }
  281.  
  282.  
  283.     /**
  284.      * Method Close
  285.      * Close a db connection
  286.      */
  287.     public function _close() {
  288.        $this->db->close();  
  289.     }    
  290. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement