db.php 6.0 KB

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