1: <?php
2: /**
3: * This file contains the implementation of the abstract base class DBWorker
4: * for the MySQL database using the extension mysqli.
5: *
6: * @package Database
7: *
8: * @author Oleg Schildt
9: */
10:
11: namespace SmartFactory\DatabaseWorkers;
12:
13: use function \SmartFactory\debugger;
14:
15: /**
16: * This is the class for the MySQL database using the extension mysqli.
17: *
18: * This is a wrapper around the database connectivity. It offers an universal
19: * common way for working with databases of different types. Currently, MySQL, PostgreSQL and
20: * MS SQL are supported. If in the future, there will be a better solution, or
21: * the current solution turns out to be inefficient in a new version of PHP,
22: * we can easily reimplement the DB wrapper without touching the business logic code.
23: * Adding support for new database types is also much easier with this wrapping approach.
24: *
25: * @see PostgreSQL_DBWorker
26: * @see MSSQL_DBWorker
27: *
28: * @author Oleg Schildt
29: */
30: class MySQL_DBWorker extends DBWorker
31: {
32: /**
33: * Stores the resource handle of the opened connection.
34: *
35: * @var resource
36: *
37: * @author Oleg Schildt
38: */
39: protected $connection = null;
40:
41: /**
42: * Flag for setting the connection to read only.
43: *
44: * @var bool $read_only
45: *
46: * @author Oleg Schildt
47: */
48: protected bool $read_only = false;
49:
50: /**
51: * Internal MySQLi object.
52: *
53: * @var ?\MySQLi
54: *
55: * @author Oleg Schildt
56: */
57: protected ?\MySQLi $mysqli = null;
58:
59: /**
60: * Internal mysqli_result object.
61: *
62: * @var bool|null|\mysqli_result
63: *
64: * @author Oleg Schildt
65: */
66: protected bool|null|\mysqli_result $mysqli_result = null;
67:
68: /**
69: * Internal mysqli_stmt object.
70: *
71: * @var ?\mysqli_stmt
72: *
73: * @author Oleg Schildt
74: */
75: protected ?\mysqli_stmt $statement = null;
76:
77: /**
78: * Internal variable for storing of the last prepared query.
79: *
80: * @var ?string
81: *
82: * @author Oleg Schildt
83: */
84: protected ?string $prepared_query = null;
85:
86: /**
87: * Internal variable for storing of the current fetched row
88: * from the result of the last retrieving query.
89: *
90: * @var ?array
91: *
92: * @author Oleg Schildt
93: */
94: protected ?array $row = null;
95:
96: /**
97: * Internal variable for storing of the column names
98: * from the result of the last retrieving query.
99: *
100: * @var ?array
101: *
102: * @author Oleg Schildt
103: */
104: protected ?array $field_names = null;
105:
106: /**
107: * Creates a clone of the dbworker that is using the same open
108: * connection.
109: *
110: * This might be useful if you want to execute some additional
111: * queries while iteration through the active results of a select query.
112: *
113: * @return MySQL_DBWorker
114: * It throws an exception in the case of any errors.
115: *
116: * Returns the clone of this dbworker.
117: *
118: * @author Oleg Schildt
119: */
120: public function create_clone(): MySQL_DBWorker
121: {
122: $cln = new MySQL_DBWorker();
123:
124: $cln->is_clone = true;
125:
126: $cln->db_server = $this->db_server;
127: $cln->db_port = $this->db_port;
128: $cln->db_name = $this->db_name;
129: $cln->db_user = $this->db_user;
130: $cln->db_password = $this->db_password;
131: $cln->read_only = $this->read_only;
132:
133: return $cln;
134: } // create_clone
135:
136: /**
137: * Constructor.
138: *
139: * @author Oleg Schildt
140: */
141: public function __construct()
142: {
143: } // __construct
144:
145: /**
146: * Destructor.
147: *
148: * @return void
149: *
150: * @throws DBWorkerException
151: * It throws an exception in the case of any errors.
152: *
153: * @author Oleg Schildt
154: */
155: public function __destruct()
156: {
157: if (!$this->is_clone) {
158: $this->close_connection();
159: }
160: } // __destruct
161:
162: /**
163: * Initializes the dbworker with connection parameters.
164: *
165: * @param array $parameters
166: * Connection settings as an associative array in the form key => value:
167: *
168: * - $parameters["db_server"] - server address.
169: * - $parameters["db_port"] - server port.
170: * - $parameters["db_name"] - database name.
171: * - $parameters["db_user"] - user name.
172: * - $parameters["db_password"] - user password.
173: * - $parameters["read_only"] - this parameter sets the connection to the read only mode.
174: *
175: * @return void
176: *
177: * @author Oleg Schildt
178: */
179: public function init(array $parameters): void
180: {
181: if (!empty($parameters["db_server"])) {
182: $this->db_server = $parameters["db_server"];
183: }
184: if (!empty($parameters["db_port"])) {
185: $this->db_port = $parameters["db_port"];
186: }
187: if (!empty($parameters["db_name"])) {
188: $this->db_name = $parameters["db_name"];
189: }
190: if (!empty($parameters["db_user"])) {
191: $this->db_user = $parameters["db_user"];
192: }
193: if (!empty($parameters["db_password"])) {
194: $this->db_password = $parameters["db_password"];
195: }
196: if (!empty($parameters["read_only"])) {
197: $this->read_only = $parameters["read_only"];
198: }
199: } // init
200:
201: /**
202: * Checks whether the extension (mysqli) is installed which is required for work with
203: * the MySQL database.
204: *
205: * @return bool
206: * The method should return true if the extension is installed, otherwise false.
207: *
208: * @see MySQL_DBWorker::get_extension_name()
209: *
210: * @author Oleg Schildt
211: */
212: public function is_extension_installed(): bool
213: {
214: if (!class_exists("MySQLi")) {
215: return false;
216: }
217:
218: return true;
219: } // is_extension_installed
220:
221: /**
222: * Returns the name of the required PHP extension - "mysqli".
223: *
224: * @return string
225: * Returns the name of the required PHP extension - "mysqli".
226: *
227: * @see MySQL_DBWorker::is_extension_installed()
228: *
229: * @author Oleg Schildt
230: */
231: public function get_extension_name(): string
232: {
233: return "mysqli";
234: } // get_extension_name
235:
236: /**
237: * Returns the name of the supported database - "MySQL Server".
238: *
239: * @return string
240: * Returns the name of the supported database - "MySQL Server".
241: *
242: * @author Oleg Schildt
243: */
244: public function get_rdbms_name(): string
245: {
246: return "MySQL Server";
247: } // get_rdbms_name
248:
249: /**
250: * Returns the connection state.
251: *
252: * @return bool
253: * Returns true if the connection is open, otherwise false.
254: *
255: * @see DBWorker::check_connection()
256: * @see MySQL_DBWorker::connect()
257: * @see MySQL_DBWorker::close_connection()
258: *
259: * @author Oleg Schildt
260: */
261: public function is_connected(): bool
262: {
263: return (!empty($this->mysqli) && empty($this->mysqli->connect_error));
264: } // is_connected
265:
266: /**
267: * Establishes the connection to the database using the connection
268: * settings parameters specified by the initialization.
269: *
270: * @return void
271: *
272: * @throws \Throwable|DBWorkerException
273: * It throws an exception in the case of any errors.
274: *
275: * @see DBWorker::check_connection()
276: * @see MySQL_DBWorker::is_connected()
277: * @see MySQL_DBWorker::close_connection()
278: *
279: * @author Oleg Schildt
280: */
281: public function connect(): void
282: {
283: if ($this->is_connected()) {
284: return;
285: }
286:
287: mysqli_report(MYSQLI_REPORT_STRICT);
288:
289: if (!$this->mysqli) {
290: if (empty($this->db_server) || empty($this->db_user) || empty($this->db_password)) {
291: throw new DBWorkerException("Connection data is incomplete", DBWorker::ERR_CONNECTION_DATA_INCOMPLETE);
292: }
293:
294: try {
295: $this->mysqli = @new \MySQLi;
296:
297: $this->mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 20);
298: $this->mysqli->options(MYSQLI_OPT_READ_TIMEOUT, 20);
299:
300: $port = null;
301: if (!empty($this->db_port)) {
302: $port = $this->db_port;
303: }
304:
305: $this->mysqli->real_connect($this->db_server, $this->db_user, $this->db_password, null, $port);
306: } catch (\mysqli_sql_exception $ex) {
307: $err = $ex->getMessage();
308:
309: throw match ($ex->getCode()) {
310: 2002 => new DBWorkerException($err, DBWorker::ERR_HOST_UNREACHABLE),
311: 1045 => new DBWorkerException($err, DBWorker::ERR_WRONG_USER_CREDENTIALS),
312: default => new DBWorkerException($err, DBWorker::ERR_CONNECTION_FAILED)
313: };
314: }
315: }
316:
317: if (!empty($this->db_name)) {
318: try {
319: $this->use_database($this->db_name);
320: } catch (\Throwable $ex) {
321: $this->connection = null;
322: throw $ex;
323: }
324: }
325:
326: try {
327: $this->execute_query("set charset utf8");
328:
329: if (!empty($this->read_only)) {
330: $this->execute_query("set transaction read only");
331: $this->execute_query("start transaction");
332: }
333: } catch (\mysqli_sql_exception $ex) {
334: $err = $ex->getMessage();
335: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED);
336: }
337: } // connect
338:
339: /**
340: * Sets the database as working database.
341: *
342: * @param string $db_name
343: * The name of the database to be set as working database.
344: *
345: * @return void
346: *
347: * @throws DBWorkerException
348: * It throws an exception in the case of any errors.
349: *
350: * @author Oleg Schildt
351: */
352: public function use_database(string $db_name): void
353: {
354: $this->check_connection();
355:
356: $this->db_name = $db_name;
357:
358: try {
359: if (!$this->mysqli->select_db($this->db_name)) {
360: throw new \mysqli_sql_exception($this->mysqli->error);
361: }
362: } catch (\mysqli_sql_exception $ex) {
363: $err = $ex->getMessage();
364: throw new DBWorkerException($err, DBWorker::ERR_DATABASE_NOT_FOUND);
365: }
366: } // use_database
367:
368: /**
369: * Returns the name of the database schema if applicable.
370: *
371: * @return string
372: * Returns the name of the database schema if applicable, or an empty string.
373: *
374: * @see MySQL_DBWorker::qualify_name_with_schema()
375: *
376: * @author Oleg Schildt
377: */
378: public function get_schema(): string
379: {
380: return "";
381: } // get_schema
382:
383: /**
384: * Completes the name of a database object with the schema name if applicable.
385: *
386: * @param string $name
387: * The name of the database object to be completed with the schema name.
388: *
389: * @return string
390: * Returns the name of the database object with the schema name if applicable,
391: * otherwise the name of the database object remains unchanged.
392: *
393: * @see MySQL_DBWorker::get_schema()
394: *
395: * @author Oleg Schildt
396: */
397: public function qualify_name_with_schema(string $name): string
398: {
399: return $name;
400: } // qualify_name_with_schema
401:
402: /**
403: * Executes the SQL query.
404: *
405: * @param string $query_string
406: * The SQL query to be executed.
407: *
408: * @return void
409: *
410: * @throws \Exception|DBWorkerException
411: * It throws an exception in the case of any errors.
412: *
413: * @author Oleg Schildt
414: */
415: public function execute_query(string $query_string): void
416: {
417: $this->check_connection();
418:
419: $this->last_query = $query_string;
420:
421: if ($this->logging) {
422: debugger()->debugMessage($this->last_query, "sql.log");
423: }
424:
425: $this->mysqli_result = @$this->mysqli->query($query_string);
426: if (!$this->mysqli_result) {
427: throw new DBWorkerException($this->mysqli->error, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
428: }
429: } // execute_query
430:
431: /**
432: * Prepares the SQL query with bindable variables.
433: *
434: * @param string $query_string
435: * The SQL query to be prepared.
436: *
437: * @return void
438: *
439: * @throws DBWorkerException
440: * It throws an exception in the case of any errors.
441: *
442: * @see MySQL_DBWorker::execute_prepared_query()
443: * @see MySQL_DBWorker::free_prepared_query()
444: *
445: * @author Oleg Schildt
446: */
447: public function prepare_query(string $query_string): void
448: {
449: $this->check_connection();
450:
451: if ($this->statement) {
452: $this->statement->close();
453: $this->statement = null;
454: }
455:
456: $this->last_query = $query_string;
457: $this->prepared_query = $query_string;
458:
459: try {
460: $this->statement = $this->mysqli->prepare($query_string);
461: } catch (\mysqli_sql_exception $ex) {
462: $err = $ex->getMessage();
463: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
464: }
465: } // prepare_query
466:
467: /**
468: * Stores long data from a stream.
469: *
470: * @param string $query_string
471: * The SQL query to be used for storing the long data.
472: *
473: * @param resource $stream
474: * The opened valid stream for reading the long data.
475: *
476: * Example:
477: * ```php
478: * $stream = fopen(".../large_binary.jpg", "rb");
479: *
480: * if(!$dbw->stream_long_data("UPDATE LARGE_DATA SET BLOB_DATA = ? WHERE ID = 1", $stream))
481: * {
482: * error reporting ...;
483: * }
484: * ```
485: *
486: * @return void
487: *
488: * @throws DBWorkerException
489: * It throws an exception in the case of any errors.
490: *
491: * @author Oleg Schildt
492: */
493: public function stream_long_data(string $query_string, $stream): void
494: {
495: $this->check_connection();
496:
497: if (!is_resource($stream)) {
498: throw new DBWorkerException("Stream is invalid!", DBWorker::ERR_STREAM_ERROR);
499: }
500:
501: if ($this->statement) {
502: $this->statement->close();
503: $this->statement = null;
504: }
505:
506: $this->last_query = $query_string;
507: $this->prepared_query = $query_string;
508:
509: try {
510: $this->statement = $this->mysqli->prepare($query_string);
511:
512: $null = null;
513: $this->statement->bind_param("b", $null);
514:
515: while (!feof($stream)) {
516: $this->statement->send_long_data(0, fread($stream, 8192));
517: }
518:
519: $this->statement->execute();
520: } catch (\mysqli_sql_exception $ex) {
521: $err = $ex->getMessage();
522: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
523: } finally {
524: fclose($stream);
525:
526: if ($this->statement) {
527: $this->statement->close();
528: }
529: $this->statement = null;
530: }
531: } // stream_long_data
532:
533: /**
534: * Executes the prepared SQL query.
535: *
536: * @param mixed ...$args
537: * The number of parameters may vary and be zero. An array can also be passed.
538: * These are parameters of the prepared query.
539: *
540: * @return void
541: *
542: * @throws \Exception|DBWorkerException
543: * It throws an exception in the case of any errors.
544: *
545: * @see MySQL_DBWorker::prepare_query()
546: * @see MySQL_DBWorker::free_prepared_query()
547: *
548: * @author Oleg Schildt
549: */
550: public function execute_prepared_query(...$args): void
551: {
552: $this->check_connection();
553:
554: if (empty($this->prepared_query) || empty($this->statement)) {
555: throw new DBWorkerException("No prepared query defined", DBWorker::ERR_QUERY_FAILED);
556: }
557:
558: if (count($args) == 1 && is_array($args[0])) {
559: $args = $args[0];
560: }
561:
562: $this->last_query = $this->prepared_query;
563:
564: $parameters = [];
565: $parameters[0] = "";
566:
567: $counter = 1;
568: foreach ($args as &$argval) {
569: if ($argval === null) {
570: $parameters[0] .= "i";
571: $parameters[$counter] = &$argval;
572:
573: $this->last_query = preg_replace("/\\?/", "null", $this->last_query, 1);
574: } elseif (is_int($argval)) {
575: $parameters[0] .= "i";
576: $parameters[$counter] = &$argval;
577:
578: $this->last_query = preg_replace("/\\?/", $argval, $this->last_query, 1);
579: } elseif (is_float($argval)) {
580: $parameters[0] .= "d";
581: $parameters[$counter] = &$argval;
582:
583: $this->last_query = preg_replace("/\\?/", $argval, $this->last_query, 1);
584: } else {
585: $parameters[0] .= "s";
586: $parameters[$counter] = &$argval;
587:
588: $this->last_query = preg_replace("/\\?/", \SmartFactory\preg_r_escape("'" . $this->escape($argval) . "'"), $this->last_query, 1);
589: }
590:
591: $counter++;
592: }
593:
594: if ($this->logging) {
595: debugger()->debugMessage($this->last_query, "sql.log");
596: }
597:
598: if (count($args) > 0 && !call_user_func_array([$this->statement, 'bind_param'], $parameters)) {
599: $err = "Number of elements in type definition string doesn't match number of bind variables.";
600: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
601: }
602:
603: if (!$this->statement->execute()) {
604: throw new DBWorkerException($this->statement->error, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
605: }
606:
607: if (!$this->statement->store_result()) {
608: throw new DBWorkerException($this->statement->error, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
609: }
610:
611: $this->mysqli_result = $this->statement->result_metadata();
612: } // execute_prepared_query
613:
614: /**
615: * Executes the SQL stored procedure.
616: *
617: * @param string $procedure
618: * The name of the SQL stored procedure.
619: *
620: * All subsequent parameters are the paramteres of the SQL stored procedure.
621: *
622: * @return void
623: *
624: * @throws DBWorkerException
625: * It throws an exception in the case of any errors.
626: *
627: * @author Oleg Schildt
628: */
629: public function execute_procedure(string $procedure /* arg list */): void
630: {
631: $this->check_connection();
632:
633: $args = func_get_args();
634:
635: // prepare the arguments for placing in eval()
636: // escape single quotes
637:
638: $this->last_query = "";
639:
640: if (count($args) > 0) {
641: $proc_name = "";
642: $arg_list = "";
643:
644: $first = true;
645: foreach ($args as $argval) {
646: if ($first) {
647: $proc_name = $argval;
648: $first = false;
649: continue;
650: }
651:
652: if ($argval === null) {
653: $arg_list .= "null, ";
654: } elseif (is_int($argval)) {
655: $arg_list .= "$argval, ";
656: } elseif (is_float($argval)) {
657: $arg_list .= "$argval, ";
658: } else {
659: $arg_list .= "'" . $this->escape($argval) . "', ";
660: }
661: }
662:
663: $arg_list = trim($arg_list, ", ");
664:
665: $this->last_query = "CALL $proc_name($arg_list);";
666: }
667:
668: $this->execute_query($this->last_query);
669:
670: if (is_object($this->mysqli_result) && get_class($this->mysqli_result) == "mysqli_result") {
671: $this->mysqli_result->free_result();
672:
673: $this->mysqli_result = null;
674: }
675: } // execute_procedure
676:
677: /**
678: * Frees the prepared query.
679: *
680: * It should be called after all executions of the prepared query.
681: *
682: * @return void
683: *
684: * @throws DBWorkerException
685: * It throws an exception in the case of any errors.
686: *
687: * @see MySQL_DBWorker::prepare_query()
688: * @see MySQL_DBWorker::execute_prepared_query()
689: *
690: * @author Oleg Schildt
691: */
692: public function free_prepared_query(): void
693: {
694: $this->check_connection();
695:
696: try {
697: $this->statement?->close();
698: } catch (\mysqli_sql_exception $ex) {
699: $err = $ex->getMessage();
700: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
701: } finally {
702: $this->statement = null;
703: $this->last_query = null;
704: $this->prepared_query = null;
705: }
706: } // free_prepared_query
707:
708: /**
709: * Closes the currently opened connection.
710: *
711: * @return void
712: *
713: * @throws DBWorkerException
714: * It throws an exception in the case of any errors.
715: *
716: * @see MySQL_DBWorker::is_connected()
717: * @see MySQL_DBWorker::connect()
718: *
719: * @author Oleg Schildt
720: */
721: public function close_connection(): void
722: {
723: $this->last_query = null;
724: $this->prepared_query = null;
725: $this->row = null;
726: $this->field_names = null;
727:
728: try {
729: if ($this->statement) {
730: @$this->statement->close();
731: }
732:
733: if ($this->mysqli) {
734: @$this->mysqli->close();
735: }
736: } catch (\mysqli_sql_exception $ex) {
737: $err = $ex->getMessage();
738: throw new DBWorkerException($err, DBWorker::ERR_CONNECTION_FAILED);
739: } finally {
740: $this->statement = null;
741: $this->mysqli = null;
742: $this->mysqli_result = null;
743: }
744: } // close_connection
745:
746: /**
747: * Starts the translation.
748: *
749: * @return void
750: *
751: * @throws DBWorkerException
752: * It throws an exception in the case of any errors.
753: *
754: * @see MySQL_DBWorker::commit_transaction()
755: * @see MySQL_DBWorker::rollback_transaction()
756: *
757: * @author Oleg Schildt
758: */
759: public function start_transaction(): void
760: {
761: $this->check_connection();
762:
763: $this->execute_query("BEGIN");
764: } // start_transaction
765:
766: /**
767: * Commits the translation.
768: *
769: * @return void
770: *
771: * @throws DBWorkerException
772: * It throws an exception in the case of any errors.
773: *
774: * @see MySQL_DBWorker::start_transaction()
775: * @see MySQL_DBWorker::rollback_transaction()
776: *
777: * @author Oleg Schildt
778: */
779: public function commit_transaction(): void
780: {
781: $this->check_connection();
782:
783: $this->execute_query("COMMIT");
784: } // commit_transaction
785:
786: /**
787: * Rolls back the translation.
788: *
789: * @return void
790: *
791: * @throws DBWorkerException
792: * It throws an exception in the case of any errors.
793: *
794: * @see MySQL_DBWorker::start_transaction()
795: * @see MySQL_DBWorker::commit_transaction()
796: *
797: * @author Oleg Schildt
798: */
799: public function rollback_transaction(): void
800: {
801: $this->check_connection();
802:
803: $this->execute_query("ROLLBACK");
804: } // rollback_transaction
805:
806: /**
807: * Frees the result of the previously executed retrieving query.
808: *
809: * It should be called only for the retrieving queries.
810: *
811: * @return void
812: *
813: * @throws DBWorkerException
814: * It throws an exception in the case of any errors.
815: *
816: * @author Oleg Schildt
817: */
818: public function free_result(): void
819: {
820: $this->check_connection();
821:
822: try {
823: if (is_object($this->mysqli_result) && get_class($this->mysqli_result) == "mysqli_result") {
824: $this->mysqli_result->free_result();
825:
826: $this->mysqli_result = null;
827: }
828: } catch (\mysqli_sql_exception $ex) {
829: $err = $ex->getMessage();
830: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
831: } finally {
832: $this->row = null;
833: $this->field_names = null;
834: $this->last_query = null;
835: }
836: } // free_result
837:
838: /**
839: * Returns the value of the auto increment field by the last insertion.
840: *
841: * @return ?int
842: * Returns the value of the auto increment field by the last insertion.
843: *
844: * @throws DBWorkerException
845: * It throws an exception in the case of any errors.
846: *
847: * @author Oleg Schildt
848: */
849: public function insert_id(): ?int
850: {
851: $this->check_connection();
852:
853: return $this->mysqli->insert_id;
854: } // insert_id
855:
856: /**
857: * Fetches the next row of data from the result of the execution of the retrieving query.
858: *
859: * @return bool
860: * Returns true if the next row exists and has been successfully fetched, otherwise false.
861: *
862: * Example:
863: * ```php
864: * if(!$dbw->execute_query("SELECT FIRST_NAME, LAST_NAME FROM USERS"))
865: * {
866: * error reporting ...;
867: * }
868: *
869: * while($dbw->fetch_row())
870: * {
871: * echo $dbw->field_by_name("FIRST_NAME") . " " . $dbw->field_by_name("LAST_NAME") . "<br>";
872: * }
873: *
874: * $dbw->free_result();
875: * ```
876: *
877: * @throws DBWorkerException
878: * It throws an exception in the case of any errors.
879: *
880: * @author Oleg Schildt
881: */
882: public function fetch_row(): bool
883: {
884: $this->check_connection();
885:
886: try {
887: if ($this->statement) {
888: if (!$this->field_names) {
889: $this->row = [];
890:
891: $fcnt = $this->statement->field_count;
892: if ($fcnt == 0) {
893: return false;
894: }
895:
896: $params = [];
897: for ($i = 0; $i < $fcnt; $i++) {
898: $finfo = $this->field_info_by_num($i);
899: if (empty($finfo)) {
900: return false;
901: }
902:
903: $this->field_names[] = $finfo["name"];
904:
905: $this->row[$finfo["name"]] = "";
906: $params[] = &$this->row[$finfo["name"]];
907: }
908:
909: call_user_func_array([$this->statement, 'bind_result'], $params);
910:
911: unset($params);
912: }
913:
914: $result = $this->statement->fetch();
915: if (!$result) {
916: $this->row = null;
917: }
918:
919: return !empty($result);
920: } // prepared query
921:
922: if (!$this->mysqli_result) {
923: $err = "Result is empty!";
924: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
925: }
926:
927: $this->row = @$this->mysqli_result->fetch_assoc();
928:
929: if (!$this->row) {
930: return false;
931: }
932:
933: if (!$this->field_names) {
934: $this->field_names = array_keys($this->row);
935: }
936: } catch (\mysqli_sql_exception $ex) {
937: $err = $ex->getMessage();
938: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
939: }
940:
941: return true;
942: } // fetch_row
943:
944: /**
945: * Fetches all rows from the result into an array.
946: *
947: * @param array &$rows
948: * Target array for loading the results.
949: *
950: * @param ?array $dimension_keys
951: * Array of the column names that should be used as dimensions.
952: *
953: * Per default, the rows are fetched as two-dimensional array.
954: *
955: * Example:
956: * ```php
957: * $rows = [];
958: * $dbw->fetch_array($rows);
959: *
960: * rows[n] = ["col1" => "val1", "col2" => "val2", "col3" => "val3", ...]
961: * ```
962: * If the dimension columns are specified, their values are used for the dimensions.
963: *
964: * Example:
965: * ```php
966: * $rows = [];
967: * $dbw->fetch_array($rows, ["col1", "col2"]);
968: *
969: * rows["val1"]["val2"] = ["col3" => "val3", ...]
970: * ```
971: *
972: * @return int
973: * Returns the number of the fetched rows. It might be also 0.
974: *
975: * @throws DBWorkerException
976: * It throws an exception in the case of any errors.
977: *
978: * @author Oleg Schildt
979: */
980: public function fetch_array(array &$rows, ?array $dimension_keys = null): int
981: {
982: $this->check_connection();
983:
984: $rows = [];
985:
986: if (!empty($dimension_keys)) {
987: $dimension_keys = array_flip($dimension_keys);
988: }
989:
990: $counter = 0;
991: $row = [];
992:
993: try {
994: if ($this->statement) {
995: $fcnt = $this->statement->field_count;
996: if ($fcnt == 0) {
997: return false;
998: }
999:
1000: for ($i = 0; $i < $fcnt; $i++) {
1001: $finfo = $this->field_info_by_num($i);
1002: if (empty($finfo)) {
1003: return false;
1004: }
1005:
1006: $this->field_names[] = $finfo["name"];
1007:
1008: $row[$finfo["name"]] = "";
1009: }
1010:
1011: $params = [];
1012: foreach ($this->field_names as $fname) {
1013: $params[] = &$row[$fname];
1014: }
1015:
1016: call_user_func_array([$this->statement, 'bind_result'], $params);
1017:
1018: unset($params);
1019:
1020: while ($this->statement->fetch()) {
1021: $counter++;
1022:
1023: // we must create a copy from $row, because its
1024: // items are bound per reference
1025: $rowcpy = array_map(function ($val) {
1026: return $val;
1027: }, $row);
1028:
1029: if (empty($dimension_keys)) {
1030: $rows[] = $rowcpy;
1031: } else {
1032: $dimensions = array_intersect_key($rowcpy, $dimension_keys);
1033: $rowcpy = array_diff_key($rowcpy, $dimension_keys);
1034:
1035: $reference = &$rows;
1036:
1037: foreach ($dimensions as $dval) {
1038: if (empty($reference[$dval])) {
1039: $reference[$dval] = [];
1040: }
1041: $reference = &$reference[$dval];
1042: }
1043:
1044: $reference = $rowcpy;
1045:
1046: unset($reference);
1047: }
1048: }
1049:
1050: return $counter;
1051: } // prepared query
1052:
1053: if (!$this->mysqli_result) {
1054: $err = "Result is empty!";
1055: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1056: }
1057:
1058: while ($row = @$this->mysqli_result->fetch_assoc()) {
1059: $counter++;
1060:
1061: if (!$this->field_names) {
1062: $this->field_names = array_keys($row);
1063: }
1064:
1065: if (empty($dimension_keys)) {
1066: $rows[] = $row;
1067: } else {
1068: $dimensions = array_intersect_key($row, $dimension_keys);
1069: $row = array_diff_key($row, $dimension_keys);
1070:
1071: $reference = &$rows;
1072:
1073: foreach ($dimensions as $dval) {
1074: if (empty($reference[$dval])) {
1075: $reference[$dval] = [];
1076: }
1077: $reference = &$reference[$dval];
1078: }
1079:
1080: $reference = $row;
1081:
1082: unset($reference);
1083: }
1084: }
1085:
1086: return $counter;
1087:
1088: } catch (\mysqli_sql_exception $ex) {
1089: $err = $ex->getMessage();
1090: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1091: }
1092: } // fetch_array
1093:
1094: /**
1095: * Returns the number of the rows fetched by the last retrieving query.
1096: *
1097: * @return int
1098: * Returns the number of the rows fetched by the last retrieving query.
1099: *
1100: * @throws DBWorkerException
1101: * It throws an exception in the case of any errors.
1102: *
1103: * @author Oleg Schildt
1104: */
1105: public function fetched_count(): int
1106: {
1107: $this->check_connection();
1108:
1109: if ($this->statement) {
1110: return $this->statement->num_rows;
1111: }
1112:
1113: if (!$this->mysqli_result || !is_object($this->mysqli_result)) {
1114: $err = "Result fetch error";
1115: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1116: }
1117:
1118: return $this->mysqli_result->num_rows;
1119: } // fetched_count
1120:
1121: /**
1122: * Returns the number of the rows affected by the last modification query.
1123: *
1124: * @return int
1125: * Returns the number of the rows affected by the last modification query.
1126: *
1127: * @throws DBWorkerException
1128: * It throws an exception in the case of any errors.
1129: *
1130: * @author Oleg Schildt
1131: */
1132: public function affected_count(): int
1133: {
1134: $this->check_connection();
1135:
1136: if (!$this->mysqli_result) {
1137: $err = "Result fetch error";
1138: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1139: }
1140:
1141: return $this->mysqli->affected_rows;
1142: } // affected_count
1143:
1144: /**
1145: * Returns the number of the fields in the result of the last retrieving query.
1146: *
1147: * @return int
1148: * Returns the number of the fields in the result of the last retrieving query.
1149: *
1150: * @throws DBWorkerException
1151: * It throws an exception in the case of any errors.
1152: *
1153: * @author Oleg Schildt
1154: */
1155: public function field_count(): int
1156: {
1157: $this->check_connection();
1158:
1159: if ($this->statement) {
1160: return $this->statement->field_count;
1161: }
1162:
1163: if (!$this->mysqli_result || !is_object($this->mysqli_result)) {
1164: $err = "Result fetch error";
1165: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1166: }
1167:
1168: return $this->mysqli_result->field_count;
1169: } // field_count
1170:
1171: /**
1172: * Returns the value of a field specified by name.
1173: *
1174: * @param string $name
1175: * The name of the field.
1176: *
1177: * @param int $type
1178: * The type of the field.
1179: *
1180: * @return mixed
1181: * Returns the value of a field specified by name. In the case
1182: * of any error returns null.
1183: *
1184: * @throws DBWorkerException
1185: * It throws an exception in the case of any errors.
1186: *
1187: * @see MySQL_DBWorker::field_by_num()
1188: * @see MySQL_DBWorker::field_name()
1189: *
1190: * @author Oleg Schildt
1191: */
1192: public function field_by_name(string $name, int $type = self::DB_AS_IS): mixed
1193: {
1194: $this->check_connection();
1195:
1196: if (!$this->row) {
1197: return null;
1198: }
1199:
1200: if (!array_key_exists($name, $this->row)) {
1201: trigger_error("Field with the name '$name' does not exist in the result set!", E_USER_WARNING);
1202: return null;
1203: }
1204:
1205: if (($type == DBWorker::DB_DATE || $type == DBWorker::DB_DATETIME) && !empty($this->row[$name])) {
1206: return strtotime($this->row[$name]);
1207: }
1208:
1209: return $this->row[$name];
1210: } // field_by_name
1211:
1212: /**
1213: * Returns the value of a field specified by number.
1214: *
1215: * @param int $num
1216: * The number of the field.
1217: *
1218: * @param int $type
1219: * The type of the field.
1220: *
1221: * @return mixed
1222: * Returns the value of a field specified by number. In the case
1223: * of any error returns null.
1224: *
1225: * @throws DBWorkerException
1226: * It throws an exception in the case of any errors.
1227: *
1228: * @see MySQL_DBWorker::field_by_name()
1229: * @see MySQL_DBWorker::field_info_by_num()
1230: * @see MySQL_DBWorker::field_name()
1231: *
1232: * @author Oleg Schildt
1233: */
1234: public function field_by_num(int $num, int $type = self::DB_AS_IS): mixed
1235: {
1236: $this->check_connection();
1237:
1238: if (!$this->row) {
1239: return null;
1240: }
1241:
1242: if (!array_key_exists($num, $this->field_names)) {
1243: trigger_error("Field with the index $num does not exist in the result set!", E_USER_WARNING);
1244: return null;
1245: }
1246:
1247: if (($type == DBWorker::DB_DATE || $type == DBWorker::DB_DATETIME) && !empty($this->row[$this->field_names[$num]])) {
1248: return strtotime($this->row[$this->field_names[$num]]);
1249: }
1250:
1251: return $this->row[$this->field_names[$num]];
1252: } // field_by_num
1253:
1254: /**
1255: * Returns the name of the field by number.
1256: *
1257: * @param int $num
1258: * The number of the field.
1259: *
1260: * @return ?string
1261: * Returns the value of a field specified by number as an object with properties. In the case
1262: * of any error returns null.
1263: *
1264: * @throws DBWorkerException
1265: * It throws an exception in the case of any errors.
1266: *
1267: * @see MySQL_DBWorker::field_by_num()
1268: * @see MySQL_DBWorker::field_info_by_num()
1269: *
1270: * @author Oleg Schildt
1271: */
1272: public function field_name(int $num): ?string
1273: {
1274: $this->check_connection();
1275:
1276: $info = $this->field_info_by_num($num);
1277: if (!$info) {
1278: return null;
1279: }
1280:
1281: return $info["name"] ?? "";
1282: } // field_name
1283:
1284: /**
1285: * Returns the meta information about the field as an object with properties.
1286: *
1287: * @param int $num
1288: * The number of the field.
1289: *
1290: * @return ?array
1291: * Returns the associative array with properties. In the case
1292: * of any error returns null.
1293: *
1294: * - $info["name"] - name of the field.
1295: * - $info["type"] - type of the field.
1296: * - $info["size"] - size of the field.
1297: * - $info["binary"] - whether the filed is binary.
1298: * - $info["numeric"] - whether the filed is numeric.
1299: * - $info["datetime"] - whether the filed is datetime.
1300: *
1301: * @throws DBWorkerException
1302: * It throws an exception in the case of any errors.
1303: *
1304: * @see MySQL_DBWorker::field_by_num()
1305: * @see MySQL_DBWorker::field_name()
1306: *
1307: * @author Oleg Schildt
1308: */
1309: public function field_info_by_num(int $num): ?array
1310: {
1311: $this->check_connection();
1312:
1313: if (!$this->mysqli_result) {
1314: $err = "Result fetch error";
1315: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1316: }
1317:
1318: try {
1319: $res = @$this->mysqli_result->fetch_field_direct($num);
1320: } catch (\mysqli_sql_exception $ex) {
1321: $err = $ex->getMessage();
1322: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1323: }
1324:
1325: // some corrections for compatible format
1326:
1327: $mysqli_type = [];
1328:
1329: $mysqli_type[0] = "decimal";
1330: $mysqli_type[1] = "tinyint";
1331: $mysqli_type[2] = "smallint";
1332: $mysqli_type[3] = "integer";
1333: $mysqli_type[4] = "float";
1334: $mysqli_type[5] = "double";
1335:
1336: $mysqli_type[7] = "timestamp";
1337: $mysqli_type[8] = "bigint";
1338: $mysqli_type[9] = "mediumint";
1339: $mysqli_type[10] = "date";
1340: $mysqli_type[11] = "time";
1341: $mysqli_type[12] = "datetime";
1342: $mysqli_type[13] = "year";
1343: $mysqli_type[14] = "date";
1344:
1345: $mysqli_type[16] = "bit";
1346:
1347: $mysqli_type[246] = "decimal";
1348: $mysqli_type[247] = "enum";
1349: $mysqli_type[248] = "set";
1350: $mysqli_type[249] = "tinyblob";
1351: $mysqli_type[250] = "mediumblob";
1352: $mysqli_type[251] = "longblob";
1353: $mysqli_type[252] = "blob";
1354: $mysqli_type[253] = "varchar";
1355: $mysqli_type[254] = "char";
1356: $mysqli_type[255] = "geometry";
1357:
1358: $field_info = [];
1359:
1360: $field_info["name"] = $res->name;
1361: $field_info["type"] = $res->type;
1362: $field_info["size"] = $res->length;
1363:
1364: $field_info["binary"] = 0;
1365: $field_info["numeric"] = 0;
1366:
1367: if (!empty($field_info["type"])) {
1368: if (in_array($field_info["type"], [0, 1, 2, 3, 4, 5, 7, 8, 9, 13, 16, 246])) {
1369: $field_info["numeric"] = 1;
1370: }
1371: elseif (in_array($field_info["type"], [252]) && !($res->flags & MYSQLI_BINARY_FLAG)) {
1372: $field_info["string"] = 1;
1373: }
1374: elseif (in_array($field_info["type"], [249, 250, 251, 252])) {
1375: $field_info["binary"] = 1;
1376: }
1377: elseif (in_array($field_info["type"], [12, 14])) {
1378: $field_info["datetime"] = 1;
1379: }
1380: elseif (in_array($field_info["type"], [253, 254])) {
1381: $field_info["string"] = 1;
1382: }
1383:
1384: if (isset($mysqli_type[$field_info["type"]])) {
1385: $field_info["type"] = $mysqli_type[$field_info["type"]];
1386: } else {
1387: $field_info["type"] = "undefined";
1388: }
1389: }
1390:
1391: return $field_info;
1392: } // field_info_by_num
1393:
1394: /**
1395: * Escapes the string so that it can be used in the query without causing an error.
1396: *
1397: * @param string $str
1398: * The string to be escaped.
1399: *
1400: * @return string
1401: * Returns the escaped string.
1402: *
1403: * @see MySQL_DBWorker::format_date()
1404: * @see MySQL_DBWorker::format_datetime()
1405: *
1406: * @author Oleg Schildt
1407: */
1408: public function escape(string $str): string
1409: {
1410: return preg_replace("/(['\\\\])/", "\\\\$1", $str);
1411: } // escape
1412:
1413: /**
1414: * Formats the date to a string compatible for the corresponding database.
1415: *
1416: * @param int $date
1417: * The date value as timestamp.
1418: *
1419: * @return string
1420: * Returns the string representation of the date compatible for the corresponding database.
1421: *
1422: * @see MySQL_DBWorker::escape()
1423: * @see MySQL_DBWorker::format_datetime()
1424: *
1425: * @author Oleg Schildt
1426: */
1427: public function format_date(int $date): string
1428: {
1429: if (empty($date)) {
1430: return $this->quotes_or_null("");
1431: }
1432:
1433: return $this->quotes_or_null(date("Y-m-d", $date));
1434: } // format_date
1435:
1436: /**
1437: * Formats the date/time to a string compatible for the corresponding database.
1438: *
1439: * @param int $datetime
1440: * The date/time value as timestamp.
1441: *
1442: * @return string
1443: * Returns the string representation of the date/time compatible for the corresponding database.
1444: *
1445: * @see MySQL_DBWorker::escape()
1446: * @see MySQL_DBWorker::format_date()
1447: *
1448: * @author Oleg Schildt
1449: */
1450: public function format_datetime(int $datetime): string
1451: {
1452: if (empty($datetime)) {
1453: return $this->quotes_or_null("");
1454: }
1455:
1456: return $this->quotes_or_null(date("Y-m-d H:i:s", $datetime));
1457: } // format_datetime
1458:
1459: /**
1460: * Prepares the value for putting to a query depending on its type. It does escaping, formatting
1461: * and quotation if necessary.
1462: *
1463: * @param mixed $value
1464: * The value to be formatted.
1465: *
1466: * @param int $type
1467: * The type of the value.
1468: *
1469: * @return string
1470: * Returns the prepared value.
1471: *
1472: * @throws DBWorkerException
1473: * It might throw an exception in the case of any errors.
1474: *
1475: * @author Oleg Schildt
1476: */
1477: function prepare_for_query(mixed $value, int $type): string
1478: {
1479: if (empty($value) && (string)$value != "0") {
1480: return "null";
1481: } else {
1482: return match ($type) {
1483: DBWorker::DB_NUMBER => $this->number_or_null($value),
1484: DBWorker::DB_DATETIME => $this->format_datetime($value),
1485: DBWorker::DB_DATE => $this->format_date($value),
1486: DBWorker::DB_GEOMETRY => "ST_GeomFromText('" . $this->escape($value) . "')",
1487: DBWorker::DB_GEOMETRY_4326 => "ST_GeomFromText('" . $this->escape($value) . "', 4326, 'axis-order=lat-long')",
1488: default => $this->quotes_or_null($value)
1489: };
1490: }
1491: } // prepare_for_query
1492:
1493: /**
1494: * Builds simple select query based on parameters.
1495: *
1496: * It is used for building queries with limits.
1497: *
1498: * @param string $table
1499: * The name of the table.
1500: *
1501: * @param array $fields
1502: * The list of request fields.
1503: *
1504: * @param string $where_clause
1505: * The where clause that should restrict the result.
1506: *
1507: * @param string $order_clause
1508: * The order clause to sort the results.
1509: *
1510: * @param int $limit
1511: * The limit how many records shoud be loaded. 0 for unlimited.
1512: *
1513: * @return string
1514: * Returns the built query.
1515: *
1516: * @author Oleg Schildt
1517: */
1518: function build_select_query(string $table, array $fields, string $where_clause, string $order_clause, int $limit): string
1519: {
1520: $query = "select\n";
1521:
1522: $query .= implode(", ", $fields) . "\n";
1523:
1524: $query .= "from " . $table . "\n";
1525:
1526: if (!empty($where_clause)) {
1527: $query .= $where_clause . "\n";
1528: }
1529:
1530: if (!empty($order_clause)) {
1531: $query .= $order_clause . "\n";
1532: }
1533:
1534: if (!empty($limit)) {
1535: $query .= "limit " . $limit . "\n";
1536: }
1537:
1538: return $query;
1539: } // build_select_query
1540: } // MySQL_DBWorker