1 <?php 2 $dbConfig = require_once(dirname(__FILE__).'/config.php'); 3 4 class Db{ 5 public $conn; 6 private $host = null; 7 private $user = null; 8 private $password = null; 9 private $database = null; 10 private $tablename = null; 11 private $dbConfig = null; 12 private $sql = [ 13 'where' => null, 14 'orderBy' => null, 15 'limit' => null, 16 ]; 17 18 19 public function __construct($tablename = '') { 20 global $dbConfig; 21 $this->dbConfig = $dbConfig; 22 $this->tablename = $dbConfig['DB_PREFIX'].$tablename; 23 $this->user = $dbConfig['DB_USER']; 24 $this->host = $dbConfig['DB_HOST']; 25 $this->password = $dbConfig['DB_PWD']; 26 $this->database = $dbConfig['DB_NAME']; 27 $dsn = 'mysql:dbname='.$this->database.';host='.$this->host.';port=3306'; 28 try { 29 $this->conn = new PDO($dsn, $this->user, $this->password); // also allows an extra parameter of configuration 30 } catch(PDOException $e) { 31 die('Could not connect to the database:<br/>' . $e); 32 } 33 } 34 35 public function table($tablename) { 36 $this->tablename = $this->dbConfig['DB_PREFIX'].$tablename; 37 return $this; 38 } 39 40 public function getAll($fields = '*') { 41 $querySql = sprintf("SELECT %s FROM %s", $fields, $this->tablename); 42 if(!empty($this->sql['where'])) { 43 $querySql .= ' WHERE ' . $this->sql['where']; 44 } 45 if(!empty($this->sql['orderBy'])) { 46 $querySql .= ' ORDER BY ' . $this->sql['orderBy']; 47 } 48 if(!empty($this->sql['limit'])) { 49 $querySql .= ' LIMIT ' . $this->sql['limit']; 50 } 51 return $this->query($querySql); 52 } 53 54 public function getOne($fields = '*') { 55 $result = $this->getAll($fields); 56 return isset($result[0]) ? $result[0] : null; 57 } 58 59 public function insert($data) { 60 foreach ($data as $key => &$value) { 61 $value = addslashes($value); 62 } 63 $keys = "`".implode('`,`', array_keys($data))."`"; 64 $values = "'".implode("','", array_values($data))."'"; 65 $querySql = sprintf("INSERT INTO %s ( %s ) VALUES ( %s )", $this->tablename, $keys, $values); 66 return $this->query($querySql); 67 } 68 69 public function delete() { 70 $querySql = sprintf("DELETE FROM %s WHERE ( %s )", $this->tablename, $this->sql['where']); 71 return $this->query($querySql); 72 } 73 74 public function update($data) { 75 $updateFields = []; 76 foreach ($data as $key => $value) { 77 $up_value = addslashes($value); 78 $updateFields[] = "`$key`='$up_value'"; 79 } 80 $updateFields = implode(',', $updateFields); 81 $querySql = sprintf("UPDATE %s SET %s", $this->tablename, $updateFields); 82 83 if(!empty($this->sql['where'])) { 84 $querySql .= ' WHERE ' . $this->sql['where']; 85 } 86 87 return $this->query($querySql); 88 } 89 90 public function query($querySql) { 91 $querystr = strtolower(trim(substr($querySql,0,6))); 92 $stmt = $this->conn->prepare($querySql); 93 $ret = $stmt->execute(); 94 95 if(!$ret) print_r($stmt->errorInfo()); 96 97 if($querystr == 'select') { 98 $retData = $stmt->fetchAll(PDO::FETCH_ASSOC); 99 return $retData; 100 }elseif($ret && $querystr == 'insert') { 101 return $this->conn->lastInsertId(); 102 }else{ 103 return $ret; 104 } 105 } 106 107 108 public function limit($limit, $limitCount = null) { 109 if(!$limitCount) { 110 $this->sql['limit'] = $limit; 111 }else{ 112 $this->sql['limit'] = $limit .','. $limitCount; 113 } 114 return $this; 115 } 116 117 public function orderBy($orderBy) { 118 $this->sql['orderBy'] = $orderBy; 119 return $this; 120 } 121 122 public function close() { 123 return $this->conn = null; 124 } 125 126 public function where($where) { 127 if(!is_array($where)) { 128 return null; 129 } 130 $crondsArr = []; 131 foreach ($where as $key => $value) { 132 $fieldValue = $value; 133 if(is_array($fieldValue)) { 134 $crondsArr[] = "$key ".$fieldValue[0]. ' ' . addslashes($fieldValue[1]); 135 }else{ 136 $fieldValue = addslashes($fieldValue); 137 $crondsArr[] = "$key='$fieldValue'"; 138 } 139 } 140 $this->sql['where'] = implode(' AND ', $crondsArr); 141 142 return $this; 143 } 144 145 }