API tools faq
paste
Guest User

Untitled

a guest
Jun 8th, 2018
83
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.08 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. class DB {
  3. private static $instance;
  4. private $dbh;
  5. private $error;
  6. private $stmt;
  7.  
  8. public static function getInstance() {
  9.  
  10. $host = '127.0.0.1';
  11. $user = 'user';
  12. $pass = 'password';
  13. $dbname = 'dbname';
  14. $port = '3306';
  15.  
  16.  
  17. if (!self::$instance) {
  18. // If no instance then make one
  19. self::$instance = new self($host, $user, $pass, $dbname, $port);
  20. }
  21. return self::$instance;
  22. }
  23. private function __construct($host, $user, $pass, $dbname, $port) {
  24. $dsn = 'mysql:host=' . $host . ';port=' . $port . ';dbname=' . $dbname;
  25.  
  26. // Set options
  27. $options = array(
  28. PDO::ATTR_PERSISTENT => false,
  29. PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  30. PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
  31. );
  32. // Create a new PDO instanace
  33. try {
  34. $this->dbh = new PDO($dsn, $user, $pass, $options);
  35. }
  36. // Catch any errors
  37. catch (PDOException $e) {
  38. $log .= "\nSQL TIME: " . date('c');
  39. $log .= "\nQuery: CONNECTING DB";
  40. $log .= "\nERROR: " . $e->getMessage();
  41. $log .= "\nERROR CODE: " . $e->getCode();
  42. $log .= "\n----------------------------------------------------------";
  43. //error_log($log, 3, LOG_PATH.'mysql_error.log');
  44. //throw new Exception("DBError: " . $e->getMessage());
  45. }
  46. }
  47.  
  48. private function bindType($value = null) {
  49. switch (true) {
  50. case is_int($value):
  51. $type = PDO::PARAM_INT;
  52. break;
  53. case is_bool($value):
  54. $type = PDO::PARAM_BOOL;
  55. break;
  56. case is_null($value):
  57. $type = PDO::PARAM_NULL;
  58. break;
  59. default:
  60. $type = PDO::PARAM_STR;
  61. }
  62. return $type;
  63. }
  64.  
  65. public function select($sql, array $bind = []) {
  66.  
  67. $this->stmt = $this->dbh->prepare($sql);
  68. $result = false;
  69. if (count($bind) > 0) {
  70. foreach ($bind as $key => $val) {
  71. $this->stmt->bindValue(':' . $key, $val, $this->bindType($val));
  72. }
  73. }
  74. try {
  75. if ($this->stmt && $this->stmt->execute()) {
  76. $data = array();
  77. while ($row = $this->stmt->fetch(\PDO::FETCH_ASSOC)) {
  78. $data[] = $row;
  79. }
  80.  
  81. $result = new stdClass();
  82. $result->row = (isset($data[0]) ? $data[0] : array());
  83. $result->rows = $data;
  84. $result->num_rows = $this->stmt->rowCount();
  85. }
  86. } catch (PDOException $e) {
  87. $log .= "\nSQL TIME: " . date('c');
  88. $log .= "\nQuery: CONNECTING DB";
  89. $log .= "\nERROR: " . $e->getMessage();
  90. $log .= "\nERROR CODE: " . $e->getCode();
  91. $log .= "\n----------------------------------------------------------";
  92. error_log($log, 3, LOG_PATH . 'mysql_error.log');
  93. //throw new Exception('DBError: ' . $e->getMessage() . ' Error Code : ' . $e->getCode() . ' <br />' . $sql);
  94. }
  95. return $result;
  96. }
  97.  
  98. public function insert($table, array $data = []) {
  99. $columnString = implode(',', array_keys($data));
  100. $valueString = ':' . implode(',:', array_keys($data));
  101. $sql = "INSERT INTO {$table} ({$columnString}) VALUES ({$valueString})";
  102. $this->stmt = $this->dbh->prepare($sql);
  103. if (count($data) > 0) {
  104. foreach ($data as $key => $val) {
  105. $this->stmt->bindValue(':' . $key, $val, $this->bindType($val));
  106. }
  107. }
  108. $return = 0;
  109. try {
  110. if ($this->stmt && $this->stmt->execute()) {
  111. $return = $this->dbh->lastInsertId();
  112. }
  113. } catch (PDOException $e) {
  114. $log .= "\nSQL TIME: " . date('c');
  115. $log .= "\nQuery: CONNECTING DB";
  116. $log .= "\nERROR: " . $e->getMessage();
  117. $log .= "\nERROR CODE: " . $e->getCode();
  118. $log .= "\n----------------------------------------------------------";
  119. error_log($log, 3, LOG_PATH . 'mysql_error.log');
  120. //throw new Exception('DBError: ' . $e->getMessage() . ' Error Code : ' . $e->getCode() . ' <br />' . $sql);
  121. }
  122. return $return;
  123. }
  124.  
  125. public function insertIgnore($table, array $data = []) {
  126. $columnString = implode(',', array_keys($data));
  127. $valueString = ':' . implode(',:', array_keys($data));
  128. $sql = "INSERT IGNORE INTO {$table} ({$columnString}) VALUES ({$valueString})";
  129. $this->stmt = $this->dbh->prepare($sql);
  130. if (count($data) > 0) {
  131. foreach ($data as $key => $val) {
  132. $this->stmt->bindValue(':' . $key, $val, $this->bindType($val));
  133. }
  134. }
  135. $return = 0;
  136. try {
  137. if ($this->stmt && $this->stmt->execute()) {
  138. $return = $this->dbh->lastInsertId();
  139. }
  140. } catch (PDOException $e) {
  141. $log .= "\nSQL TIME: " . date('c');
  142. $log .= "\nQuery: CONNECTING DB";
  143. $log .= "\nERROR: " . $e->getMessage();
  144. $log .= "\nERROR CODE: " . $e->getCode();
  145. $log .= "\n----------------------------------------------------------";
  146. error_log($log, 3, LOG_PATH . 'mysql_error.log');
  147. //throw new Exception('DBError: ' . $e->getMessage() . ' Error Code : ' . $e->getCode() . ' <br />' . $sql);
  148. }
  149. return $return;
  150. }
  151.  
  152. public function update($table, array $column, $where, array $bind) {
  153. $_column = [];
  154. foreach ($column as $key => $val) {
  155. $_column[] = $key . " = :" . $key;
  156. }
  157. $_where = '';
  158. if (!empty($where)) {
  159. $_where = ' WHERE ' . $where;
  160. }
  161. $sql = "UPDATE {$table} SET " . implode(', ', $_column) . " {$_where}";
  162. $this->stmt = $this->dbh->prepare($sql);
  163. foreach ($column as $key => $val) {
  164. $this->stmt->bindValue(':' . $key, $val, $this->bindType($val));
  165. }
  166. foreach ($bind as $key => $val) {
  167. $this->stmt->bindValue(':' . $key, $val, $this->bindType($val));
  168. }
  169. $return = 0;
  170. try {
  171. if ($this->stmt && $this->stmt->execute()) {
  172. $return = $this->stmt->rowCount();
  173. }
  174. } catch (PDOException $e) {
  175. $log .= "\nSQL TIME: " . date('c');
  176. $log .= "\nQuery: CONNECTING DB";
  177. $log .= "\nERROR: " . $e->getMessage();
  178. $log .= "\nERROR CODE: " . $e->getCode();
  179. $log .= "\n----------------------------------------------------------";
  180. error_log($log, 3, LOG_PATH . 'mysql_error.log');
  181. //throw new Exception('DBError: ' . $e->getMessage() . ' Error Code : ' . $e->getCode() . ' <br />' . $sql);
  182. }
  183.  
  184. return $return;
  185. }
  186.  
  187. public function updateRaw($table, $column, $where, array $bind) {
  188.  
  189. $_where = '';
  190. if (!empty($where)) {
  191. $_where = ' WHERE ' . $where;
  192. }
  193. $sql = "UPDATE {$table} SET {$column} {$_where}";
  194. $this->stmt = $this->dbh->prepare($sql);
  195.  
  196. foreach ($bind as $key => $val) {
  197. $this->stmt->bindValue(':' . $key, $val, $this->bindType($val));
  198. }
  199. $return = 0;
  200. try {
  201. if ($this->stmt && $this->stmt->execute()) {
  202. $return = $this->stmt->rowCount();
  203. }
  204. } catch (PDOException $e) {
  205. $log .= "\nSQL TIME: " . date('c');
  206. $log .= "\nQuery: CONNECTING DB";
  207. $log .= "\nERROR: " . $e->getMessage();
  208. $log .= "\nERROR CODE: " . $e->getCode();
  209. $log .= "\n----------------------------------------------------------";
  210. error_log($log, 3, LOG_PATH . 'mysql_error.log');
  211. //throw new Exception('DBError: ' . $e->getMessage() . ' Error Code : ' . $e->getCode() . ' <br />' . $sql);
  212. }
  213.  
  214. return $return;
  215. }
  216.  
  217. public function delete($table, $where, array $bind) {
  218. $sql = "DELETE FROM {$table} WHERE {$where}";
  219. $this->stmt = $this->dbh->prepare($sql);
  220. foreach ($bind as $key => $val) {
  221. $this->stmt->bindValue(':' . $key, $val, $this->bindType($val));
  222. }
  223. $return = 0;
  224. try {
  225. if ($this->stmt && $this->stmt->execute()) {
  226. $return = $this->stmt->rowCount();
  227. }
  228. } catch (PDOException $e) {
  229. $log .= "\nSQL TIME: " . date('c');
  230. $log .= "\nQuery: CONNECTING DB";
  231. $log .= "\nERROR: " . $e->getMessage();
  232. $log .= "\nERROR CODE: " . $e->getCode();
  233. $log .= "\n----------------------------------------------------------";
  234. error_log($log, 3, LOG_PATH . 'mysql_error.log');
  235. //throw new Exception('DBError: ' . $e->getMessage() . ' Error Code : ' . $e->getCode() . ' <br />' . $sql);
  236. }
  237. return $return;
  238. }
  239.  
  240. public function getConnection() {
  241. return $this->dbh;
  242. }
  243.  
  244. public function lastInsertId() {
  245. return $this->dbh->lastInsertId();
  246. }
  247.  
  248. public function beginTransaction() {
  249. return $this->dbh->beginTransaction();
  250. }
  251.  
  252. public function endTransaction() {
  253. return $this->dbh->commit();
  254. }
  255.  
  256. public function cancelTransaction() {
  257. return $this->dbh->rollBack();
  258. }
  259.  
  260. public function debugDumpParams() {
  261. return $this->stmt->debugDumpParams();
  262. }
  263.  
  264. public function __destruct() {
  265. $this->dbh = null;
  266. }
  267.  
  268. private function __clone() {}
  269.  
  270. }
  271. //$result = $db->select("SELECT * FROM `store_media` WHERE sm_id = :sm_id",array("sm_id"=>"7"));
  272. //$result = $db->select("store_media",'*',"",[]);
  273. //$result = $db->insert('store_media',['sm_store_id'=>'22', 'sm_parent_id'=>'33', 'sm_name'=>'44', 'sm_type'=>'55', 'sm_path'=>'66', 'sm_width'=>'77', 'sm_height'=>'88', 'sm_isfile'=>'99', 'sm_creation_date'=>date('Y-m-d H:i:s')]);
  274. //$result = $db->update('store_media',['sm_name'=>'21'],'sm_id = :sm_id',['sm_id'=>1]);
  275. #$result = $db->delete('store_media','sm_id = :sm_id',['sm_id'=>1]);
  276. #print_r($result);
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!