daily pastebin goal
21%
SHARE
TWEET

Untitled

a guest Mar 17th, 2018 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3.  
  4. // declare a config variable that can be accessed by including this file
  5. $config = require("config.php");
  6.  
  7. class Connection {
  8.  
  9.     // establish connection details from config file
  10.     function __construct() {
  11.         $config = require("config.php");
  12.         $this->db_host = $config["masterDB"]["host"];
  13.         $this->db_user = $config["masterDB"]["user"];
  14.         $this->db_pass = $config["masterDB"]["password"];
  15.         $this->db_name = $config["masterDB"]["name"];
  16.     }
  17.  
  18.     // make the connection
  19.     public function connect() {
  20.         $this->connect_db = new mysqli($this->db_host, $this->db_user, $this->db_pass, $this->db_name);
  21.         if (mysqli_connect_errno()) {
  22.             die("Connection failed: %s\n".mysqli_connect_error());
  23.             exit();
  24.         }
  25.         if (!$this->connect_db->set_charset("utf8")) {
  26.             die("Could not set character set to UTF8: %s\n".$this->connect_db->error);
  27.             exit();
  28.         }
  29.         return true;
  30.     }
  31.  
  32.     // return the current connection
  33.     public function get_connection() {
  34.         return $this->connect_db;
  35.     }
  36. }
  37.  
  38.  
  39.  
  40. /*
  41.     class container for art request actions
  42. */
  43. class RequestTemplate {
  44.  
  45.     /**
  46.      * @var object Mysqli instance used for database operations
  47.      */
  48.     var $mysqli = null;
  49.  
  50.     /**
  51.      * @var integer ID of user performing operations
  52.      */
  53.     var $currentUSER = null;
  54.  
  55.     /**
  56.      * @var integer ID of current request being worked with
  57.      */
  58.     var $currentID = null;
  59.  
  60.     /**
  61.      * @var boolean If false will not automatically commit changes to database (requires external mysqli be declared)
  62.      */
  63.     var $autoCommit = true;
  64.  
  65.     /**
  66.      * @var boolean If true will throw an exception upon error rather than the default behavior
  67.      */
  68.     var $useException = false;
  69.  
  70.     /**
  71.      * @var array An array of field names and their datatypes fpr constructing insert & update statements
  72.      */
  73.     protected $fieldTypes = array();
  74.  
  75.     /**
  76.      * @var string The current table to perform insert & update operations on
  77.      */
  78.     private $currentTable = null;
  79.  
  80.     /**
  81.      * @var array A white-list of tables eligible for insert & update operations
  82.      */
  83.     private $tablesWhitelist = array();
  84.  
  85.     /**
  86.      * @var array A white-list of functions that can be used in insert & update operations
  87.      */
  88.     var $myslqFuncWhiteList = array(null, "null", "NULL", "NOW()");
  89.  
  90.     /**
  91.      * Class constructor
  92.      *
  93.      * @param mixed[false|object] $mysqli An existing mysqli instance or false to initialize one
  94.      * @param mixed[boolean|integer] $exists The ID of the request currently being operated on, or false if unknown (or a new request)
  95.      * @param boolean $autocommit If false will not automatically commit mysql transactions (which requires an external instance of mysqli)
  96.      */
  97.     function __construct($mysqli = false, $exists = false, $autocommit = true) {
  98.         if ($mysqli == false) {
  99.             if ($autocommit == false) {
  100.                 $this->handleErr("autocommit can not be used without an existing mysqli connection");
  101.             }
  102.             $this->checkDBConn();
  103.         } else {
  104.             $this->mysqli = $mysqli;
  105.         }
  106.         if ($exists != false && is_int( intval($exists) )) {
  107.             $this->currentID = $exists;
  108.         }
  109.  
  110.         $this->autoCommit = $autocommit;
  111.         $this->useException = false;
  112.         if ($this->autoCommit == false) {
  113.             $this->useException = true;
  114.         }
  115.     }
  116.  
  117.     /**
  118.      * Handels errors
  119.      *
  120.      * Will throw an exception if this->useException is true (and return false)
  121.      * Or else dies, printing the argument
  122.      *
  123.      * @param  string   $arg    Description of the encountered error
  124.      * @return bool false
  125.      */
  126.     public function handleErr($arg) {
  127.         if ($this->useException == true) {
  128.             throw new Exception($arg);
  129.             return false;
  130.         } else {
  131.             die($arg);
  132.         }
  133.     }
  134.  
  135.  
  136.     /**
  137.      * Sets the current operating table - checks against whitelist of tables (throws error if no match)
  138.      *
  139.      * @param  string   $table  Name of the white-listed table
  140.      * @return bool true
  141.      */
  142.     public function setCurrentTable($table = null) {
  143.         if (!in_array($table, $this->tablesWhitelist)) {
  144.             $this->handleErr("RequestTemplate->setCurrentTable invalid table used: $table");
  145.         }
  146.         $this->currentTable = $table;
  147.         return true;
  148.     }
  149.  
  150.  
  151.  
  152.     /* create request entries
  153.         arguments:
  154.             $dataArray (string) - user notes to use on event
  155.                 keyName -> ['s', 'value'],
  156.             $initialInsert (boolean) - will use the ID from the insert statement for subsequent requests
  157.             $insertIgnore (boolean) - will ignore duplicate unique entries instead of throwing an error
  158.     */
  159.     public function insertFields($dataArray, $initialInsert = false, $insertIgnore = false) {
  160.         if (!isset($this->currentUSER)) {
  161.             $this->handleErr("No currentUSER defined");
  162.         }
  163.  
  164.         if (is_null($this->currentTable)) {
  165.             $this->handleErr("RequestTemplate->insertFields no valid table defined");
  166.         } else if ($this->checkTableField() == false) {
  167.             $this->handleErr("RequestTemplate->insertFields invalid table used: {$this->currentTable}");
  168.         }
  169.  
  170.         $setStringArry0 = array();
  171.         $setStringArry1 = array();
  172.         $arParams = array();
  173.         $typeString = "";
  174.         $return = false;
  175.  
  176.         // might want to enforce the existance of some fields here
  177.         foreach ($dataArray as $key => $value) {
  178.             if ($this->checkTableField($key) == false) {
  179.                 $this->handleErr("RequestTemplate->insertFields Invalid field passed: $key");
  180.             }
  181.  
  182.             if (strtolower($dataArray[$key][1]) == 'null') {
  183.                 $dataArray[$key][1] = null;
  184.             }
  185.  
  186.             $setStringArry0[] = "`$key`";
  187.             if ($dataArray[$key][0] == "f" && in_array($dataArray[$key][1], $this->myslqFuncWhiteList)) {
  188.                 if (is_null($dataArray[$key][1])) {
  189.                     $setStringArry1[] = 'NULL';
  190.                 } else {
  191.                     $setStringArry1[] = $dataArray[$key][1];
  192.                 }
  193.             } else {
  194.                 $typeString .= $dataArray[$key][0];
  195.                 $setStringArry1[] = "?";
  196.                 $arParams[] = &$dataArray[$key][1];
  197.             }
  198.         }
  199.  
  200.         array_unshift($arParams, $typeString);
  201.         $qryPart0 = implode(", ", $setStringArry0);
  202.         $qryPart1 = implode(", ", $setStringArry1);
  203.  
  204.         $insert = "INSERT";
  205.         if ($insertIgnore === true) {
  206.             $insert = "INSERT IGNORE";
  207.         }
  208.  
  209.         $sql = "INSERT INTO `{$this->currentTable}` ($qryPart0) VALUES ($qryPart1)";
  210.  
  211.         if ($insertIgnore === true) {
  212.             $sql .= " ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(id)";
  213.         }
  214.  
  215.         $stmt = $this->mysqli->prepare($sql);
  216.  
  217.         if ($stmt) {
  218.             call_user_func_array(array($stmt, "bind_param"), $arParams);
  219.             $rc = $stmt->execute();
  220.             if ($rc === false) {
  221.                 $this->handleErr("RequestTemplate->insertFields->stmt->execute() failed: " . htmlspecialchars($stmt->error));
  222.             }
  223.             $return = $stmt->insert_id;
  224.             if ($initialInsert == true) {
  225.                 $this->currentID = $return;
  226.             }
  227.             if ($this->autoCommit == true) {
  228.                 $stmt->close();
  229.             }
  230.         } else {
  231.             $this->handleErr("RequestTemplate->insertFields->stmt->prepare()  failed: " . htmlspecialchars($this->mysqli->error));
  232.         }
  233.        
  234.         return $return;
  235.     }
  236.  
  237.     /* update fields on the request
  238.         arguments:
  239.             $dataArray (string) - user notes to use on event
  240.                 keyName -> ['s', 'value'],
  241.     */
  242.     public function updateFields($dataArray) {
  243.         $this->checkRequired();
  244.  
  245.         if (is_null($this->currentTable)) {
  246.             $this->handleErr("RequestTemplate->updateFields no valid table defined");
  247.         } else if ($this->checkTableField() == false) {
  248.             $this->handleErr("RequestTemplate->updateFields invalid table used: {$this->currentTable}");
  249.         }
  250.  
  251.         $setStringArry = array();
  252.         $arParams = array();
  253.         $typeString = "";
  254.  
  255.         foreach($dataArray as $key => $value) {
  256.             if ($this->checkTableField($key) == false) {
  257.                 $this->handleErr("RequestTemplate->updateFields Invalid field passed: $key");
  258.             }
  259.  
  260.             if (strtolower($dataArray[$key][1]) == 'null') {
  261.                 $dataArray[$key][1] = null;
  262.             }
  263.  
  264.             if ($dataArray[$key][0] == "f" && in_array($dataArray[$key][1], $this->myslqFuncWhiteList)) {
  265.                 if (is_null($dataArray[$key][1])) {
  266.                     $setStringArry[] = "`$key` = NULL";
  267.                 } else {
  268.                     $setStringArry[] = "`$key` = {$dataArray[$key][1]}";
  269.                 }
  270.             } else {
  271.                 $setStringArry[] = "`$key` = ?";
  272.                 $arParams[] = &$dataArray[$key][1];
  273.                 $typeString .= $dataArray[$key][0];
  274.             }
  275.         }
  276.  
  277.         // add extra integer for id
  278.         $typeString .= "i";
  279.         $arParams[] = &$this->currentID;
  280.  
  281.         array_unshift($arParams, $typeString);
  282.         $qryPart = implode(", ", $setStringArry);
  283.  
  284.         $sql = "UPDATE `{$this->currentTable}` SET $qryPart WHERE `id` = ?";
  285.         $stmt = $this->mysqli->prepare($sql);
  286.  
  287.         if ($stmt) {
  288.             call_user_func_array( array($stmt, "bind_param"), $arParams);
  289.             $rc = $stmt->execute();
  290.             if( $rc === false ) $this->handleErr("RequestTemplate->updateFields->stmt->execute() failed: " . htmlspecialchars($stmt->error));
  291.             if($this->autoCommit == true) {
  292.                 $stmt->close();
  293.             }
  294.         } else {
  295.             $this->handleErr("RequestTemplate->updateFields->stmt->prepare()  failed: " . htmlspecialchars($this->mysqli->error));
  296.         }
  297.        
  298.         return true;
  299.     }
  300.  
  301.     /**
  302.      * Checks if a field exists within the current table
  303.      *
  304.      * @param  string   $field  name of the field to check for
  305.      * @return bool     true | false (whether the field was found or not)
  306.      */
  307.     private function checkTableField($field = false) {
  308.         $rows = 0;
  309.         $qry = "SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = ?";
  310.         if ($field != false) {
  311.             $qry = "SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?";
  312.         }
  313.         if ($stmt = $this->mysqli->prepare($qry)) {
  314.             if($field != false) {
  315.                 $rc = $stmt->bind_param("ss", $this->currentTable, $field);
  316.             } else {
  317.                 $rc = $stmt->bind_param("s", $this->currentTable);
  318.             }
  319.             if ( $rc === false ) $this->handleErr("RequestTemplate->checkTableField->stmt->bind_param() failed: " . htmlspecialchars($stmt->error));
  320.             $rc = $stmt->execute();
  321.             if ( $rc === false ) $this->handleErr("RequestTemplate->checkTableField->stmt->execute() failed: " . htmlspecialchars($stmt->error));
  322.  
  323.             $stmt->store_result();
  324.             $rows = $stmt->num_rows;
  325.             $stmt->free_result();
  326.  
  327.             if($this->autoCommit == true) {
  328.                 $stmt->close();
  329.             }
  330.         } else {
  331.             $this->handleErr("RequestTemplate->checkTableField->stmt->prepare() failed: " . htmlspecialchars($this->mysqli->error));
  332.         }
  333.  
  334.         if ($rows == 1) return true;
  335.  
  336.         return false;
  337.     }
  338.  
  339.     /**
  340.      * Build input object for inserting and updating database fields
  341.      *
  342.      * @param  array    $dataArray  array of key names and values to parse
  343.      * @return array    $output     array of database column names and their respective inputs
  344.      */
  345.     public function buildTableInputs($dataArray) {
  346.         $output = array();
  347.  
  348.         foreach ($dataArray as $key => $value) {
  349.             if (isset($this->fieldTypes[$key])) {
  350.                 if (isset($this->fieldTypes[$key]["formatting"])) {
  351.                     $formatted = call_user_func_array(array($this, $this->fieldTypes[$key]["formatting"]), array($dataArray[$key]));
  352.  
  353.                     if (is_null($formatted)) {
  354.                         if (isset($this->fieldTypes[$key]["allow_null_format"]) && $this->fieldTypes[$key]["allow_null_format"] === true) {
  355.                             $dataArray[$key] = $formatted;
  356.                         }
  357.                     } else {
  358.                         $dataArray[$key] = $formatted;
  359.                     }
  360.                 }
  361.  
  362.                 $output[$this->fieldTypes[$key]["db_field"]] = array($this->fieldTypes[$key]["db_type"], $dataArray[$key]);
  363.             }
  364.         }
  365.  
  366.         return $output;
  367.     }
  368.  
  369.     /**
  370.      * Checks for a database connection, creates one if it does not exist
  371.      *
  372.      * @return object A mysqli connection instance
  373.      */
  374.     public function checkDBConn() {
  375.         if (is_null($this->mysqli)) {
  376.             $db = new Connection();
  377.             $db->connect();
  378.             $this->mysqli = $db->get_connection();
  379.         }
  380.  
  381.         return $this->mysqli;
  382.     }
  383. }
  384. ?>
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