db.php 6.3 KB

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