Created
May 29, 2020 14:41
-
-
Save lianglee/5916afcf3e4bd51023bd2d613bbef7ad to your computer and use it in GitHub Desktop.
pdo
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /** | |
| * Open Source Social Network | |
| * | |
| * @package (softlab24.com).ossn | |
| * @author OSSN Core Team <info@softlab24.com> | |
| * @copyright (C) SOFTLAB24 LIMITED | |
| * @license Open Source Social Network License (OSSN LICENSE) http://www.opensource-socialnetwork.org/licence | |
| * @link https://www.opensource-socialnetwork.org/ | |
| * | |
| * | |
| * Database v5.3 #1525 | |
| * Improvements in v5.3, | |
| * You can use wheres based on array parameters | |
| * Example | |
| * $db->select(array( | |
| * 'from' => 'mysqli', | |
| * 'wheres' => array( | |
| * array( | |
| * 'name' => 'b', | |
| * 'comparator' => '=', | |
| * 'value' => '10', | |
| * 'separator' => 'AND', | |
| * ), | |
| * array( | |
| * 'name' => 'c', | |
| * 'comparator' => '=', | |
| * 'value' => '20', | |
| * ) | |
| * ), | |
| * )); | |
| */ | |
| class OssnDatabase extends OssnBase { | |
| /** | |
| * Initialize the database | |
| * | |
| * return void | |
| */ | |
| public function __construct() { | |
| global $Ossn; | |
| //Avoid the multiple db connections #1001 | |
| if(!isset($Ossn->dbLINK) || isset($Ossn->dbLINK) && $Ossn->dbLINK == false) { | |
| $Ossn->dbLINK = $this->Connect(); | |
| } | |
| //set the sql mode and avoid setting again and again for each request | |
| if(!isset($Ossn->setSQLMode)) { | |
| $this->statement("SET SESSION sql_mode=(SELECT REPLACE(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY', ''));"); | |
| $this->execute(); | |
| $Ossn->setSQLMode = true; | |
| } | |
| } | |
| /** | |
| * Connect to database | |
| * | |
| * @return boolean | |
| */ | |
| public function Connect() { | |
| $settings = ossn_database_settings(); | |
| $options = array( | |
| PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, | |
| PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, | |
| PDO::ATTR_EMULATE_PREPARES => false | |
| ); | |
| $conector = "mysql:host={$settings->host};dbname={$settings->database};port={$settings->port};charset=utf8mb4"; | |
| try { | |
| $connect = new PDO($conector, $settings->user, $settings->password, $options); | |
| return $connect; | |
| } | |
| catch(PDOException $ex) { | |
| throw new OssnDatabaseException($ex->getMessage()); | |
| } | |
| } | |
| /** | |
| * Prepare a query to insert data in database | |
| * | |
| * @param array @param['names'] Names of columns | |
| * @param array @param['values'] Values that need to be inserted | |
| * @param string @param['into'] Table name | |
| * | |
| * @return boolean | |
| */ | |
| public function insert($params) { | |
| global $Ossn; | |
| if(is_array($params)) { | |
| if(count($params['names']) == count($params['values'])) { | |
| for($i = 1; $i <= count($params['values']); $i++) { | |
| $values[] = '?'; | |
| } | |
| $colums = "`" . implode("`, `", $params['names']) . '`'; | |
| $values = implode(", ", $values); | |
| $actual_values = array(); | |
| foreach($params['values'] as $val){ | |
| if(!isset($val)){ | |
| $val = ''; | |
| } | |
| $actual_values[] = $val; | |
| } | |
| $this->statement("INSERT INTO {$params['into']} ($colums) VALUES ($values);"); | |
| if($this->execute($actual_values)) { | |
| $this->last_id = intval($this->database->lastInsertId()); | |
| return $this->last_id; | |
| } | |
| } | |
| } | |
| return false; | |
| } | |
| /** | |
| * Prepare a database query | |
| * | |
| * @return boolean | |
| */ | |
| public function statement($query) { | |
| if(!empty($query)) { | |
| $this->query = $query; | |
| return true; | |
| } | |
| return false; | |
| } | |
| /** | |
| * Execute a mysqli query and store result in memory | |
| * | |
| * @param array $values Values | |
| * | |
| * @return boolean | |
| */ | |
| public function execute($values = array()) { | |
| global $Ossn; | |
| $this->database = $Ossn->dbLINK; | |
| if(isset($this->query) && !empty($this->query)) { | |
| try { | |
| if(empty($values)) { | |
| $this->exe = $this->database->query($this->query); | |
| } else { | |
| $this->exe = $this->database->prepare($this->query); | |
| $this->exe->execute($values); | |
| } | |
| } | |
| catch(PDOException $ex) { | |
| throw new OssnDatabaseException("{$ex->getMessage()} \n {$this->query} "); | |
| } | |
| unset($this->query); | |
| //Using mysqli_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution. | |
| //$this->database->close(); | |
| return true; | |
| } | |
| return false; | |
| } | |
| /** | |
| * Prepare a query to update data in database | |
| * | |
| * @param string @param['names'] Names of columns | |
| * @param array @param['values'] Values that need to be updated | |
| * @param string @param['table'] Table name | |
| * @param array @param['wheres'] Specify a selection criteria to update required records | |
| * | |
| * @return boolean | |
| */ | |
| public function update($params = array()) { | |
| if(is_array($params)) { | |
| if(count($params['names']) == count($params['values']) && !empty($params['table'])) { | |
| $valuec = count($params['names']); | |
| $i = 1; | |
| foreach($params['names'] as $key => $val) { | |
| $data[$val] = $params['values'][$key]; | |
| } | |
| foreach($data as $keys => $vals) { | |
| if($i == $valuec) { | |
| $valyes[] = "`{$keys}` = ?"; | |
| } else { | |
| $valyes[] = "`{$keys}` = ?,"; | |
| } | |
| $i++; | |
| } | |
| $q = implode('', $valyes); | |
| //wheres rebuild | |
| if(!isset($params['wheres'][0]['name'])) { | |
| $params['wheres'] = implode(' ', $params['wheres']); | |
| $this->statement("UPDATE {$params['table']} SET {$q} WHERE {$params['wheres']}"); | |
| } else { | |
| $where_merge = ''; | |
| $wheres_values = array(); | |
| foreach($params['wheres'] as $where_item) { | |
| if(!isset($where_item['name']) || !isset($where_item['value'])) { | |
| continue; | |
| } | |
| if(!isset($where_item['separator'])) { | |
| $where_item['separator'] = ''; | |
| } | |
| if(!isset($where_item['comparator'])) { | |
| $where_item['comparator'] = '='; | |
| } | |
| $where_merge .= " `{$where_item['name']}` {$where_item['comparator']} ? {$where_item['separator']}"; | |
| $params['values'][] = $where_item['value']; | |
| } | |
| $this->statement("UPDATE {$params['table']} SET {$q} WHERE {$where_merge}"); | |
| } | |
| if($this->execute($params['values'])) { | |
| return true; | |
| } | |
| } | |
| } | |
| return false; | |
| } | |
| /** | |
| * Prepare a query to select data from database | |
| * | |
| * @param string @param['from'] Names of table | |
| * @param array @param['params'] Names of columns which you want to select | |
| * @param array @param['wheres'] Specify a selection criteria to get required records | |
| * | |
| * @return boolean | |
| */ | |
| public function select($params, $multi = '') { | |
| if(is_array($params)) { | |
| if(!isset($params['params'])) { | |
| $parameters = '*'; | |
| } else { | |
| $parameters = implode(', ', $params['params']); | |
| } | |
| $order_by = ''; | |
| if(!empty($params['order_by'])) { | |
| $order_by = "ORDER by {$params['order_by']}"; | |
| } | |
| $group_by = ''; | |
| if(!empty($params['group_by'])) { | |
| $group_by = "GROUP by {$params['group_by']}"; | |
| } | |
| $where = ''; | |
| $wheres_values = false; | |
| //wheres rebuild | |
| if(isset($params['wheres']) && !isset($params['wheres'][0]['name']) && is_array($params['wheres'])) { | |
| $where = implode(' ', $params['wheres']); | |
| } elseif(isset($params['wheres'])) { | |
| $where_merge = ''; | |
| $wheres_values = array(); | |
| foreach($params['wheres'] as $where_item) { | |
| if(!isset($where_item['name']) || !isset($where_item['value'])) { | |
| continue; | |
| } | |
| if(!isset($where_item['separator'])) { | |
| $where_item['separator'] = ''; | |
| } | |
| if(!isset($where_item['comparator'])) { | |
| $where_item['comparator'] = '='; | |
| } | |
| $where_merge .= " `{$where_item['name']}` {$where_item['comparator']} ? {$where_item['separator']}"; | |
| $wheres_values[] = $where_item['value']; | |
| } | |
| $where = $where_merge; | |
| } | |
| $wheres = ''; | |
| if(!empty($params['wheres'])) { | |
| $wheres = "WHERE({$where})"; | |
| } | |
| $limit = ''; | |
| if(!empty($params['limit'])) { | |
| $limit = "LIMIT {$params['limit']}"; | |
| } | |
| $joins = ''; | |
| if(!empty($params['joins']) && !is_array($params['joins'])) { | |
| $joins = $params['joins']; | |
| } elseif(!empty($params['joins']) && is_array($params['joins'])) { | |
| $joins = implode(' ', $params['joins']); | |
| } | |
| $this->statement("SELECT {$parameters} FROM {$params['from']} {$joins} {$wheres} {$group_by} {$order_by} {$limit};"); | |
| if($this->execute($wheres_values)) { | |
| return $this->fetch($multi); | |
| } | |
| } | |
| return false; | |
| } | |
| /** | |
| * Fetch the data from memory that is stored during execution; | |
| * | |
| * @param boolean $data Ture if you want to fetch all data , or false if only one row | |
| * | |
| * @return boolean | |
| */ | |
| public function fetch($data = false) { | |
| if(isset($this->exe)) { | |
| if($data !== true) { | |
| if($fetch = $this->exe) { | |
| return arrayObject($fetch->fetch(PDO::FETCH_ASSOC)); | |
| } | |
| } | |
| if($data === true) { | |
| if($fetch = $this->exe) { | |
| $all = $fetch->fetchAll(); | |
| if($all) { | |
| return arrayObject($all); | |
| } | |
| } | |
| } | |
| } | |
| return false; | |
| } | |
| /** | |
| * Prepare a query to delete data from database | |
| * | |
| * @param string @param['from'] Names of table | |
| * @param array @param['wheres'] Specify a selection criteria to get required records | |
| * | |
| * @return boolean | |
| */ | |
| public function delete($params) { | |
| if(is_array($params)) { | |
| $wheres_values = false; | |
| //wheres rebuild | |
| if(isset($params['wheres']) && !isset($params['wheres'][0]['name']) && is_array($params['wheres'])) { | |
| $where = implode(' ', $params['wheres']); | |
| } elseif(isset($params['wheres'])) { | |
| $where_merge = ''; | |
| $wheres_values = array(); | |
| foreach($params['wheres'] as $where_item) { | |
| if(!isset($where_item['name']) || !isset($where_item['value'])) { | |
| continue; | |
| } | |
| if(!isset($where_item['separator'])) { | |
| $where_item['separator'] = ''; | |
| } | |
| if(!isset($where_item['comparator'])) { | |
| $where_item['comparator'] = '='; | |
| } | |
| $where_merge .= " `{$where_item['name']}` {$where_item['comparator']} ? {$where_item['separator']}"; | |
| $wheres_values[] = $where_item['value']; | |
| } | |
| $where = $where_merge; | |
| } | |
| if(!empty($params['wheres'])) { | |
| $wheres = "WHERE({$where})"; | |
| } | |
| //don't let any component or query to empty entire table | |
| if(empty($params['wheres'])) { | |
| return false; | |
| } | |
| $this->statement("DELETE FROM `{$params['from']}` {$wheres};"); | |
| if($this->execute($wheres_values)) { | |
| return true; | |
| } | |
| } | |
| return false; | |
| } | |
| /** | |
| * Get a guid of newly create entry | |
| * | |
| * @return integer | |
| */ | |
| public function getLastEntry() { | |
| if(!empty($this->last_id)) { | |
| return $this->last_id; | |
| } | |
| } | |
| /** | |
| * Create a wheres clause for database | |
| * | |
| * @param array $array A valid array containg wheres clauses; | |
| * @param string $operator AND, OR, LIKE | |
| * | |
| * @return string | |
| */ | |
| public function constructWheres(array $array, $operator = "AND") { | |
| if(!empty($array) && !empty($operator)) { | |
| $result = implode(" {$operator} ", $array); | |
| return $result; | |
| } | |
| return false; | |
| } | |
| /** | |
| * Generate limit from options | |
| * | |
| * @param integer $data_limit How much data should be fetched? | |
| * @param integer $page_limit Limit of data on one page | |
| * @param integer $offset Offset value | |
| * | |
| * @return string|false | |
| */ | |
| public function generateLimit($data_limit = false, $page_limit = false, $offset = false) { | |
| $limit = $data_limit; | |
| //get only required result, don't bust your server memory | |
| if(isset($offset) && $offset !== false && $page_limit !== false) { | |
| $limitfrom = ($offset - 1) * ($page_limit); | |
| $limitto = $page_limit; | |
| $data_limit = "{$limitfrom}, {$limitto}"; | |
| if($offset > 1) { | |
| if($limit > $limitfrom) { | |
| $limitto = $limit - $limitfrom; | |
| if($limitto <= $page_limit) { | |
| $data_limit = "{$limitfrom}, {$limitto}"; | |
| } | |
| } | |
| } | |
| if(!empty($limit) && $limit < $page_limit) { | |
| $data_limit = $limit; | |
| } | |
| return $data_limit; | |
| } | |
| return false; | |
| } | |
| /** | |
| * Unset the stuff that is not need once op is finished | |
| * | |
| * @return void | |
| */ | |
| public function __destruct(){ | |
| unset($this->exe); | |
| unset($this->database); | |
| } | |
| } //class |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment