daily pastebin goal
31%
SHARE
TWEET

db.class.php

ricardogodoi Dec 28th, 2015 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2. /***************************************************************************************************************
  3.  *                                          DATABASE CLASS
  4.  *                                          --------------
  5.  *  @author: Wade Dunbar
  6.  *  @version: 1.0
  7.  *
  8.  * Create Instance: (Copy the below code.)
  9.  * -------------------------------------------------------------------------------------------------------------
  10.    
  11.     define("DB_HOSTNAME", "localhost");
  12.     define("DB_USERNAME", "root");
  13.     define("DB_PASSWORD", "");
  14.     define("DB_DATABASE", "database");
  15.     if(DB_HOSTNAME != ""){
  16.         include("db.class.php");
  17.         if(class_exists("db")){
  18.             $db = new db(array("hostname" => DB_HOSTNAME, "username" => DB_USERNAME, "password" => DB_PASSWORD, "database" => DB_DATABASE));
  19.         }
  20.     }
  21.  * -------------------------------------------------------------------------------------------------------------
  22.  *
  23.  * Methods Calls: (Copy the below method code.)
  24.  * -------------------------------------------------------------------------------------------------------------
  25.    
  26.     Query Functions - Add Show for the query to be outputted.
  27.     ------------------------------------------------------------------------------------------------------------
  28.     # Run Basic Query
  29.     $result = $db->query("SELECT * FROM Table");
  30.     # Run Prepare Query
  31.     $result = $db->query_prepare("SELECT * FROM someTable WHERE something = :comparison", array(':comparison' => $comparison));
  32.     # Insert Query
  33.     $result = $db->insert("table_name", array("column_name"=>"column_value"));
  34.     # Update Query
  35.     $result = $db->update('tablename',array('thisfield' => value, 'field2'=>value2),array('conditionfield'=>conValue));
  36.     # Delete Query
  37.     $result = $db->delete("table_name", array("column_name"=>"column_value"));
  38.     # Num Rows Affected
  39.     $result = $db->num_rows_affected($result);
  40.     Fetch Data - Gets the data from the Result. This is not needed for insert, update and delete.
  41.     ------------------------------------------------------------------------------------------------------------
  42.    
  43.     # Fetch Data into an array
  44.     # - Default uses FETCH ASSOC
  45.     $data = $db->fetch_array($result);
  46.     # Fetch Data into an array
  47.     # - PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set
  48.     $data = $db->fetch_array_assoc($result);
  49.     # Fetch Data into an array
  50.     # - PDO::FETCH_BOTH (default)b: returns an array indexed by both column name and 0-indexed
  51.     #   column number as returned in your result set
  52.     $data = $db->fetch_both($result);
  53.     # Fetch Data into an array
  54.     # - PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in your result set
  55.     #   to the PHP variables to which they were bound with the PDOStatement::bindColumn() method
  56.     $data = $db->fetch_bound($result);
  57.     # Fetch Data into an array
  58.     # - PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of
  59.     #   the result set to named properties in the class. If fetch_style includes PDO::FETCH_CLASSTYPE
  60.     #   (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined
  61.     #   from a value of the first column.
  62.     $data = $db->fetch_class($result);
  63.     # Fetch Data into an array
  64.     # - PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set,
  65.     #   starting at column 0
  66.     $data = $db->fetch_num($result);
  67.     # Fetch Data into an array
  68.     # - PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the
  69.     #   column names returned in your result set
  70.     $data = $db->fetch_object($result);
  71.    
  72.     Other
  73.     ------------------------------------------------------------------------------------------------------------
  74.    
  75.     # Last Insert ID
  76.     $id = $db->last_insert_id($var = FALSE);
  77.  * -------------------------------------------------------------------------------------------------------------
  78.  *
  79.  * Errors (Not being used yet.)
  80.  * -------------------------------------------------------------------------------------------------------------
  81.  **************************************************************************************************************/
  82.     class db{
  83.         # Database Setting Variables
  84.         private $hostname, $username, $password, $database, $dbDriver, $charSet;
  85.         # Connection Variables
  86.         private $db, $query_num, $insert_id;
  87.     /**************************************************************************
  88.      * Construct Function
  89.      *************************************************************************/
  90.         function __construct($settings){
  91.             isset($settings['hostname']) ? $this->hostname = $settings['hostname']: die("Hostname is not set!");
  92.             isset($settings['username']) ? $this->username = $settings['username']: die("Username is not set!");
  93.             isset($settings['password']) ? $this->password = $settings['password']: die("Password is not set!");
  94.             isset($settings['database']) ? $this->database = $settings['database']: die("Database is not set!");
  95.             $this->dbDriver = isset($settings['dbDriver']) ? $settings['dbDriver']: "mysql";
  96.             $this->charSet  = isset($settings['charSet']) ? $settings['charSet']: "";
  97.             # Start a connection to DB.
  98.             $this->connect();
  99.         }
  100.     /**************************************************************************
  101.      * /Construct Function
  102.      *************************************************************************/
  103.     /**************************************************************************
  104.      * Connect Function
  105.      *************************************************************************/
  106.         private function connect(){
  107.             try{
  108.                 $this->db   = new PDO("$this->dbDriver:host=$this->hostname;dbname=$this->database;$this->charSet", $this->username, $this->password);
  109.                 $this->error_handling();
  110.             }catch(PDOException $e){
  111.                 die($e);
  112.             }
  113.         }
  114.     /**************************************************************************
  115.      * /Connect Function
  116.      *************************************************************************/
  117.     /**************************************************************************
  118.      * Error Handling Function
  119.      *************************************************************************/
  120.         private function error_handling(){
  121.             if($this->db){
  122.                 $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  123.                 $this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  124.             }
  125.         }
  126.     /**************************************************************************
  127.      * /Error Handling Function
  128.      *************************************************************************/
  129.     /**************************************************************************
  130.      * Print Function
  131.      * @param [any] $var []
  132.      *************************************************************************/
  133.    
  134.         private function _print($var){
  135.             echo is_array($var) ? "<pre>".print_r($var,true)."</pre>": $var."<br>";
  136.         }
  137.     /**************************************************************************
  138.      * /Print Function
  139.      *************************************************************************/
  140.     /**************************************************************************
  141.      * Query Function
  142.      * @param   [query]     $query  []
  143.      * @param   [boolean]   $show   []
  144.      * @return  [result]    $result [] 
  145.      *************************************************************************/
  146.         function query($query, $show = false){
  147.             ($show) ? $this->_print($query): "";
  148.             if($result = $this->db->query($query)):
  149.                 return $result;
  150.             else:
  151.                 $this->_print($this->db->errorInfo());
  152.             endif;
  153.         }
  154.     /**************************************************************************
  155.      * /Query Function
  156.      *************************************************************************/
  157.     /**************************************************************************
  158.      * Query Prepare Function
  159.      * @param   [query]     $query      []
  160.      * @param   [array]     $prepare    []
  161.      * @param   [boolean]   $show       []
  162.      * @return  [result]    $result     [] 
  163.      *************************************************************************/
  164.         function query_prepare($query, $prepare, $show = false){
  165.             if($show){
  166.                 $this->_print($query);
  167.                 $this->_print($prepare);
  168.             }
  169.                            
  170.             $result = $this->db->prepare($query);
  171.             if($result->execute($prepare)){
  172.                 return $result;
  173.             }else{
  174.                 $this->_print($this->db->errorInfo());
  175.             }
  176.         }
  177.     /**************************************************************************
  178.      * /Query Prepare Function
  179.      *************************************************************************/
  180.     /**************************************************************************
  181.      * Num Rows Affected Function
  182.      * @param  [result]  $result [description]
  183.      * @return [integer]         [description]
  184.      *************************************************************************/
  185.         function num_rows_affected(&$result){
  186.             return (isset($result))? $result->rowCount(): "";
  187.         }
  188.     /**************************************************************************
  189.      * /Num Rows Affected Function
  190.      *************************************************************************/
  191.     /**************************************************************************
  192.      * Fetch Array Function
  193.      * - Default uses FETCH ASSOC
  194.      * @param  [result] $result [description]
  195.      * @return [array]          [description]
  196.      *************************************************************************/
  197.         function fetch_array(&$result){
  198.             $fetchType = PDO::FETCH_ASSOC;
  199.             return (isset($result)) ? $result->fetchAll($fetchType): "";
  200.         }
  201.     /**************************************************************************
  202.      * /Fetch Array Function
  203.      *************************************************************************/
  204.     /**************************************************************************
  205.      * Fetch Array Assoc Function
  206.      * - PDO::FETCH ASSOC
  207.      * @param  [result] $result [description]
  208.      * @return [array]          [description]
  209.      *************************************************************************/
  210.         function fetch_array_assoc(&$result){
  211.             $fetchType = PDO::FETCH_ASSOC;
  212.             return (isset($result)) ? $result->fetchAll($fetchType): "";
  213.         }
  214.     /**************************************************************************
  215.      * Fetch Array Assoc Function
  216.      *************************************************************************/
  217.        
  218.     /**************************************************************************
  219.      * Fetch Both Function
  220.      * -------------------
  221.      * PDO::FETCH_BOTH (default): returns an array indexed by both column name
  222.      * and 0-indexed column number as returned in your result set.
  223.      *
  224.      * @param  [type] $result [description]
  225.      * @return [type]         [description]
  226.      *************************************************************************/
  227.         function fetch_both(&$result){
  228.             $fetchType = PDO::FETCH_BOTH;
  229.             return (isset($result)) ? $result->fetchAll($fetchType): "";
  230.         }
  231.     /**************************************************************************
  232.      * /Fetch Both Function
  233.      *************************************************************************/
  234.        
  235.        
  236.     /**************************************************************************
  237.      * Fetch Bound Function
  238.      * --------------------
  239.      * PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in
  240.      * your result set to the PHP variables to which they were bound with the
  241.      * PDOStatement::bindColumn() method
  242.      *
  243.      * @param  [type] $result [description]
  244.      * @return [type]         [description]
  245.      *************************************************************************/
  246.         function fetch_bound(&$result){
  247.             $fetchType = PDO::FETCH_BOUND;
  248.             return (isset($result)) ? $result->fetchAll($fetchType): "";
  249.         }
  250.    
  251.     /**************************************************************************
  252.      * /Fetch Bound Function
  253.      *************************************************************************/
  254.        
  255.     /**************************************************************************
  256.      * Fetch Class Function
  257.      * --------------------
  258.      * PDO::FETCH_CLASS: returns a new instance of the requested class, mapping
  259.      * the columns of the result set to named properties in the class. If fetch_style
  260.      * includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE)
  261.      * then the name of the class is determined from a value of the first column.
  262.      *
  263.      * @param  [type] $result [description]
  264.      * @return [type]         [description]
  265.      *************************************************************************/
  266.         function fetch_class(&$result){
  267.             $fetchType = PDO::FETCH_CLASS;
  268.             return (isset($result)) ? $result->fetchAll($fetchType): "";
  269.         }
  270.    
  271.     /**************************************************************************
  272.      * /Fetch Class Function
  273.      *************************************************************************/
  274.        
  275.     /**************************************************************************
  276.      * Fetch Num Function
  277.      * ------------------
  278.      * PDO::FETCH_NUM: returns an array indexed by column number as returned in
  279.      * your result set, starting at column 0
  280.      *
  281.      * @param  [type] $result [description]
  282.      * @return [type]         [description]
  283.      *************************************************************************/
  284.         function fetch_num(&$result){
  285.             $fetchType = PDO::FETCH_NUM;
  286.             return (isset($result)) ? $result->fetchAll($fetchType): "";
  287.         }
  288.        
  289.     /**************************************************************************
  290.      * /Fetch Num Function
  291.      *************************************************************************/
  292.        
  293.     /**************************************************************************
  294.      * Fetch Object Function
  295.      * ---------------------
  296.      * PDO::FETCH_OBJ: returns an anonymous object with property names that
  297.      * correspond to the column names returned in your result set
  298.      *
  299.      * @param  [type] $result [description]
  300.      * @return [type]         [description]
  301.      *************************************************************************/
  302.         function fetch_object(&$result){
  303.             $fetchType = PDO::FETCH_OBJ;
  304.             return (isset($result)) ? $result->fetchAll($fetchType): "";
  305.         }
  306.     /**************************************************************************
  307.      * /Fetch Object Function
  308.      *************************************************************************/
  309.        
  310.     /**************************************************************************
  311.      * Num Rows Function
  312.      * -----------------
  313.      * Returns the number of rows in a result.
  314.      *
  315.      * @param  [type] $result [description]
  316.      * @return [type]         [description]
  317.      *************************************************************************/
  318.         function num_rows($result){
  319.             return (isset($result)) ? $result->rowCount(): "";
  320.         }
  321.     /**************************************************************************
  322.      * /Num Rows Function
  323.      *************************************************************************/
  324.     /**************************************************************************
  325.      * Insert Function
  326.      * - Insert into database table.
  327.      * @param  [type]  $table         [description]
  328.      * @param  [type]  $arFieldValues [description]
  329.      * @param  boolean $show          [description]
  330.      * @return [type]                 [description]
  331.      *************************************************************************/
  332.         function insert($table, $arFieldValues, $show = FALSE){
  333.             # Create Variable instances.
  334.             $escVals = array();$excVals = array();$cnt = 0;
  335.             # Save field and Values to variables.
  336.             $fields     = array_keys($arFieldValues);
  337.             $values     = array_values($arFieldValues);
  338.            
  339.             foreach($values as $val){
  340.                 $key            = ":$cnt";
  341.                 $escVals[]      = $key;
  342.                 $excVals[$key]  = $val;
  343.                 $cnt++;
  344.             }
  345.        
  346.             $sql = " INSERT INTO $table (" . join(', ',$fields) . ") VALUES(" . join(', ',$escVals) . ")";
  347.        
  348.             ($show) ? $this->_print($sql): "";
  349.        
  350.             $result             = $this->db->prepare($sql);
  351.             $res                = $result->execute($excVals);
  352.             $this->insert_id    = $this->db->lastInsertId();
  353.             return $res;
  354.         }
  355.     /**************************************************************************
  356.      * /Insert Function
  357.      *************************************************************************/
  358.     /**************************************************************************
  359.      * Update Function
  360.      * - Function to update a row or multiple rows in a table  
  361.      * @param  [type]  $table         [description]
  362.      * @param  [type]  $arFieldValues [description]
  363.      * @param  [type]  $arConditions  [description]
  364.      * @param  boolean $show          [description]
  365.      * @return [type]                 [description]
  366.      *************************************************************************/
  367.         function update($table, $arFieldValues, $arConditions, $show = FALSE){
  368.             # Create Variable instances.
  369.             $arUpdates = array();$excVals = array();$arWhere = array();$cnt = 0;
  370.             foreach($arFieldValues as $field => $val){             
  371.                 $key            = ":$cnt";
  372.                 $arUpdates[]    = "$field = $key";
  373.                 $excVals[$key]  = $val;
  374.                 $cnt++;
  375.             }
  376.        
  377.             foreach($arConditions as $field => $val){
  378.                 $key            = ":$cnt";
  379.                 $arWhere[]      = "$field = $key";
  380.                 $excVals[$key]  = $val;
  381.                 $cnt++;
  382.             }
  383.            
  384.             $sql = "UPDATE $table SET ". join(', ',$arUpdates) . " WHERE " . join(' AND ',$arWhere);
  385.        
  386.             if($show){
  387.                 $this->_print($sql);
  388.                 $this->_print($excVals);
  389.             }
  390.            
  391.             $result = $this->db->prepare($sql);
  392.             $res    = $result->execute($excVals);
  393.             return $res;
  394.         }
  395.     /**************************************************************************
  396.      * /Update Function
  397.      *************************************************************************/
  398.        
  399.     /**************************************************************************
  400.      * Delete Function
  401.      * - Function to delete a row or multiple rows from a table
  402.      * @param  [type]  $table        [description]
  403.      * @param  [type]  $arConditions [description]
  404.      * @param  boolean $show         [description]
  405.      * @return [type]                [description]
  406.      *************************************************************************/
  407.         function delete($table, $arConditions, $show = FALSE){
  408.             # Create Variable instances.
  409.             $arWhere = array();$excVals = array();$cnt = 0;
  410.        
  411.             foreach($arConditions as $field => $val){
  412.                 $key            = ":$cnt";
  413.                 $arWhere[]      = "$field = $key";
  414.                 $excVals[$key]  = $val;
  415.                 $cnt++;
  416.                
  417.             }
  418.            
  419.             $sql = "DELETE FROM $table WHERE " . join(' AND ',$arWhere);
  420.        
  421.             ($show) ? $this->_print($sql): "";
  422.        
  423.             $result = $this->db->prepare($sql);
  424.             foreach($excVals as $k => $v){
  425.                 $result->bindParam("$k", $v);
  426.             }
  427.             $res    = $result->execute();
  428.             return $res;
  429.         }
  430.     /**************************************************************************
  431.      * /Delete Function
  432.      *************************************************************************/
  433.        
  434.     /**************************************************************************
  435.      * Last Insert Id Function
  436.      * - Get last insert id
  437.      * @param  boolean $var [description]
  438.      * @return [type]       [description]
  439.      *************************************************************************/
  440.         function last_insert_id($var = FALSE){
  441.             $id = ($var) ? $this->db->lastInsertId(): ($this->db) ? $this->insert_id: 0;
  442.             return $id;
  443.         }
  444.     /**************************************************************************
  445.      * /Last Insert Id Function
  446.      *************************************************************************/
  447.     }
  448. /***************************************************************************************************************
  449.  *                                          /DATABASE CLASS
  450.  **************************************************************************************************************/
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top