-
-
Save async-costelo/a24f41712ea70a013b1c04aaa4f3b167 to your computer and use it in GitHub Desktop.
PHP OOP Database class using MySQLI and Singleton pattern. Only one instance of the class will be made, this requires less memory.
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 | |
| class DB { | |
| private $connection; | |
| private static $_instance; | |
| private $dbhost = ""; // Ip Address of database if external connection. | |
| private $dbuser = ""; // Username for DB | |
| private $dbpass = ""; // Password for DB | |
| private $dbname = ""; // DB Name | |
| private $query = ""; | |
| /* | |
| Get an instance of the Database | |
| @return Instance | |
| */ | |
| public static function getInstance(){ | |
| if(!self::$_instance) { | |
| self::$_instance = new self(); | |
| } | |
| return self::$_instance; | |
| } | |
| // Constructor | |
| private function __construct() { | |
| try{ | |
| $this->connection = new PDO('mysql:host='.$this->dbhost.';dbname='.$this->dbname, $this->dbuser, $this->dbpass); | |
| $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); | |
| // Error handling | |
| }catch(PDOException $e){ | |
| die("Failed to connect to DB: ". $e->getMessage()); | |
| } | |
| } | |
| // Magic method clone is empty to prevent duplication of connection | |
| private function __clone(){} | |
| // Get the connection | |
| public function getConnection(){ | |
| return $this->connection; | |
| } | |
| //[C] REATE | |
| public function insert($table, $params = []){ //insert $db->insert('table',['key'=>'value']); | |
| $con = self::getConnection(); | |
| if($this->tableExists($table)){ | |
| try | |
| { | |
| $sql = 'INSERT INTO `'.$table.'` (`' . implode('`, `',array_keys($params)) . '`) VALUES (' . $this->placeholders($params) . ')'; | |
| $stmt = $con->prepare($sql); | |
| try{ | |
| $con->beginTransaction(); | |
| $stmt->execute(array_values($params)); | |
| $con->commit(); | |
| } | |
| catch( PDOException $e) | |
| { | |
| $con->rollback(); | |
| return $e->getMessage() . "</br>"; | |
| } | |
| } | |
| catch( PDOException $e){ | |
| return $e->getMessage() . "</br>"; | |
| } | |
| return true; | |
| } | |
| else | |
| { | |
| return false; | |
| } | |
| } | |
| //[R] ETRIEVE | |
| public function select() | |
| { | |
| } | |
| //[U] PDATE | |
| public function update($table, $params = [], $where = null){ //$db->update('table',[],where); | |
| $con = self::getConnection(); | |
| if($this->tableExists($table)){ | |
| try | |
| { | |
| $a = array(); | |
| foreach($params as $f=>$v){ | |
| // Seperate each column out with it's corresponding value | |
| $a[] = $f.'='.$this->placeholders($v); | |
| } | |
| $sql = 'UPDATE '.$table.' SET '.implode(',',$a).' WHERE '.$where; | |
| $stmt = $con->prepare($sql); | |
| try{ | |
| $con->beginTransaction(); | |
| $stmt->execute(array_values($params)); | |
| $con->commit(); | |
| } | |
| catch( PDOException $e) | |
| { | |
| $con->rollback(); | |
| return $e->getMessage() . "</br>"; | |
| } | |
| } | |
| catch(PDOException $e){ | |
| return $e->getMessage() ."</br>"; | |
| } | |
| return true; | |
| } | |
| else{ | |
| return false; | |
| } | |
| } | |
| //[D] ELETE | |
| public function delete($table, $where = null){ | |
| $del=''; | |
| $con = self::getConnection(); | |
| if($this->tableExists($table)){ | |
| if($where == null) | |
| { | |
| //$delete = 'DROP TABLE '.$table; | |
| } | |
| else{ | |
| $del = 'DELETE FROM '.$table.' WHERE '.$where; | |
| } | |
| try | |
| { | |
| $stmt = $con->prepare($del); | |
| try{ | |
| $con->beginTransaction(); | |
| $stmt->execute(); | |
| $con->commit(); | |
| } | |
| catch( PDOException $e) | |
| { | |
| $con->rollback(); | |
| return $e->getMessage() . "</br>"; | |
| } | |
| } | |
| catch(PDOException $e) | |
| { | |
| return $e->getMessage()."</br>"; | |
| } | |
| } | |
| else{ | |
| return false; | |
| } | |
| } | |
| private function tableExists($tbl) //find table if exists | |
| { | |
| try{ | |
| $tablesInDb = self::getConnection()->query('SHOW TABLES FROM '.$this->dbname.' LIKE "'.$tbl.'"'); | |
| if($tablesInDb) | |
| { | |
| if($tablesInDb->fetch() > 0){ | |
| return true; | |
| } | |
| else{ | |
| return false; | |
| } | |
| } | |
| } | |
| catch(PDOException $e){ | |
| return 'error: '.$e->getMessage(); | |
| } | |
| } | |
| /* | |
| * extra functions | |
| */ | |
| //placeholders for binding params | |
| public function placeholders($a = []){ | |
| $bind = []; | |
| for($i = 0; $i < count($a); $i++){ | |
| $bind[$i] = '?'; | |
| } | |
| return implode(',',$bind); | |
| } | |
| //escapes strings and date values | |
| public function escapeString($val) | |
| { | |
| $new_val = self::getConnection()->quote($val); | |
| return $new_val; | |
| } | |
| } | |
| ?> |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Added CRUD operation, $where attribute does not yet support multiple conditions.