1
0

db.php 6.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294
  1. <?php /** @noinspection PhpUnused */
  2. class DB {
  3. public static $lastQuery = "";
  4. public static $queries = [];
  5. public static $queryCount = 0;
  6. public static $DB;
  7. public static function affectedRows(mysqli $link = null): int {
  8. if($link === null) $link = self::$DB;
  9. return mysqli_affected_rows($link);
  10. }
  11. /**
  12. * @param string $host
  13. * @param string $username
  14. * @param string $password
  15. * @param string $database
  16. * @param int $port
  17. * @return mysqli
  18. * @throws Exception
  19. */
  20. public static function connect(string $host, string $username, string $password, string $database = "", int $port = 3306): mysqli {
  21. $link = @new mysqli($host, $username, $password, $database, $port);
  22. $link->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
  23. self::$DB = $link;
  24. if(!is_null($link->connect_error)) {
  25. throw new Exception("Connect Error: " . $link->connect_error);
  26. }
  27. self::query("SET NAMES utf8mb4");
  28. return $link;
  29. }
  30. /**
  31. * @param mysqli_result $result
  32. * @param int $row_number
  33. * @return bool
  34. */
  35. public static function dataSeek(mysqli_result $result, int $row_number): bool {
  36. return mysqli_data_seek($result, $row_number);
  37. }
  38. /**
  39. * @param $value
  40. * @return string
  41. */
  42. public static function escape($value): string {
  43. return mysqli_real_escape_string(self::$DB, $value);
  44. }
  45. public static function escapeNvp(array $nvp): array {
  46. foreach($nvp as $name => $value) {
  47. $nvp[$name] = self::escape($value);
  48. }
  49. return $nvp;
  50. }
  51. /**
  52. * @return string
  53. */
  54. public static function error(): string {
  55. return mysqli_error(self::$DB);
  56. }
  57. /**
  58. * @param mysqli_result $result
  59. * @return array|null
  60. */
  61. public static function fetchArray(mysqli_result $result): ?array {
  62. return mysqli_fetch_array($result);
  63. }
  64. /**
  65. * @param mysqli_result $result
  66. * @return array|null
  67. */
  68. public static function fetchAssoc(mysqli_result $result): ?array {
  69. return mysqli_fetch_assoc($result);
  70. }
  71. /**
  72. * @param mysqli_result $result
  73. * @return object|null
  74. */
  75. public static function fetchObject(mysqli_result $result): ?object {
  76. return mysqli_fetch_object($result);
  77. }
  78. /**
  79. * @param mysqli_result $result
  80. * @return array|null
  81. */
  82. public static function fetchRow(mysqli_result $result): ?array {
  83. return mysqli_fetch_row($result);
  84. }
  85. public static function freeResult(mysqli_result $result): void {
  86. mysqli_free_result($result);
  87. }
  88. /**
  89. * @param array $nvp
  90. * @param bool $escape
  91. * @return string
  92. */
  93. public static function getValueString(array $nvp, bool $escape = false): string {
  94. if($escape) {
  95. $nvp = self::escapeNvp($nvp);
  96. }
  97. $string_array = [];
  98. foreach($nvp as $field => $value) {
  99. if(is_null($value)) {
  100. $string_array[] = "`$field`=NULL";
  101. }
  102. else if(stripos($field, "binary")!==false && stripos($value, "x'")===0) {
  103. $string_array[] = "`$field`=$value";
  104. }
  105. else {
  106. $string_array[] = "`$field`='$value'";
  107. }
  108. }
  109. return implode(", ", $string_array);
  110. }
  111. /**
  112. * @param array $nvp
  113. * @param array|bool $options
  114. * @return string
  115. */
  116. public static function getWhereString(array $nvp, $options = []): string {
  117. $escape = false;
  118. $fuzzy = false;
  119. if(is_bool($options)) { // Legacy escape
  120. $escape = $options;
  121. }
  122. if(is_array($options)) {
  123. $escape = $options["escape"] ?? false;
  124. $fuzzy = $options["fuzzy"] ?? false;
  125. }
  126. if($escape) {
  127. $nvp = self::escapeNvp($nvp);
  128. }
  129. $string_array = [];
  130. foreach($nvp as $field => $value) {
  131. if($value === null) {
  132. $string_array[] = "`$field` IS NULL";
  133. }
  134. else if(is_array($value)) {
  135. if(empty($value)) $string_array[] = "0"; // Empty array
  136. else $string_array[] = "`$field` IN (" . implode(", ", self::quoteValues($value)) . ")";
  137. }
  138. else if($value instanceof Constraint) {
  139. $string_array[] = "`$field` " . $value->getConstraintString();
  140. }
  141. else {
  142. $string_array[] = $fuzzy ? "`$field` LIKE '%$value%'" : "`$field`='$value'";
  143. }
  144. }
  145. return implode(" AND ", $string_array);
  146. }
  147. /**
  148. * @param string $table
  149. * @param string $index_name
  150. * @return bool
  151. * @throws Exception
  152. */
  153. public static function hasIndex(string $table, string $index_name): bool {
  154. $sql = "SHOW INDEX FROM `$table` WHERE `Key_name`='$index_name'";
  155. $result = self::query($sql);
  156. return self::numRows($result) === 1;
  157. }
  158. /**
  159. * @return int
  160. */
  161. public static function insertId(): int {
  162. return mysqli_insert_id(self::$DB);
  163. }
  164. /**
  165. * @param mysqli_result $result
  166. * @return int
  167. */
  168. public static function numRows(mysqli_result $result): int {
  169. return mysqli_num_rows($result);
  170. }
  171. /**
  172. * @param mysqli_result $result
  173. * @return object|null
  174. */
  175. public static function objectOrNull(mysqli_result $result): ?object {
  176. return mysqli_num_rows($result)>0 ? self::fetchObject($result) : null;
  177. }
  178. /**
  179. * @param string $table
  180. * @param string $field
  181. * @param int $id
  182. * @return object|null
  183. * @throws Exception
  184. */
  185. public static function objectWithId(string $table, string $field, int $id): ?object {
  186. $sql = "SELECT * FROM `$table` WHERE `$field`='$id'";
  187. $result = self::query($sql);
  188. return self::objectOrNull($result);
  189. }
  190. /**
  191. * @return bool
  192. */
  193. public static function ping(): bool {
  194. return mysqli_ping(self::$DB);
  195. }
  196. /**
  197. * @param string $sql
  198. * @return bool|mysqli_result
  199. * @throws Exception
  200. * @noinspection ForgottenDebugOutputInspection
  201. */
  202. public static function query(string $sql) {
  203. if(!is_string($sql)) return false;
  204. self::$lastQuery = $sql;
  205. $result = mysqli_query(self::$DB, $sql);
  206. if(!$result) {
  207. $error = mysqli_error(self::$DB);
  208. error_log("Query failed: $sql\n");
  209. error_log("Error: $error\n");
  210. if(!PRODUCTION) {
  211. throw new Exception("Query failed: $sql");
  212. }
  213. else {
  214. throw new Exception("A database error has occurred. Please try again later.");
  215. }
  216. }
  217. self::$queries[] = $sql;
  218. self::$queryCount++;
  219. return $result;
  220. }
  221. /**
  222. * @param string $name
  223. * @param mysqli|null $link_identifier
  224. * @return bool
  225. */
  226. public static function selectDb(string $name, mysqli $link_identifier = null): bool {
  227. if($link_identifier === null) {
  228. return mysqli_select_db(self::$DB, $name);
  229. }
  230. return mysqli_select_db($link_identifier, $name);
  231. }
  232. /**
  233. * @return int
  234. */
  235. public static function threadId(): int {
  236. return mysqli_thread_id(self::$DB);
  237. }
  238. public static function dts(): string {
  239. return gmdate("Y-m-d H:i:s");
  240. }
  241. public static function quoteValue($value): string {
  242. return "'{$value}'";
  243. }
  244. public static function quoteValues(array $values): array {
  245. return array_map("self::quoteValue", $values);
  246. }
  247. }