· 8 years ago · Jan 23, 2018, 08:10 AM
1<?php
2/**
3 * MySQLi Database Class
4 * @category Database Access
5 * @package Database
6 * @author AashikP
7 * @copyright Copyright (c) 2018 AashikP
8 * @license https://opensource.org/licenses/MIT The MIT License
9 * @version 0.1
10 */
11
12namespace database;
13
14class MySQLiDB
15{
16 // Mysqli instance.
17 private $mySqli;
18
19 // Save Prefix if defined.
20 private $prefix = '';
21
22 // Generate an array from given $data values for bindParam
23 private $bind_arr = array(''); // Create the empty 0 index
24
25 // Set type to use in bindPar function
26 private $type;
27
28 // Set table with prefix if exists
29 private $table;
30
31 // array to generate bind_results
32 private $result_arr = array('');
33
34 // array to catch multiple rows of results
35 private $multi_result_arr = array();
36
37 // array to fetch values
38 private $fetch = array();
39
40 public function __construct()
41 {
42 // Create a connection
43 $this->connect();
44 // Check if a database prefix is defined. If defined, set prefix value
45 defined('DB_PREFIX') ? $this->setPrefix(DB_PREFIX) : null;
46 }
47
48 // Connect using a mysqli instance
49 private function connect()
50 {
51 $this->mySqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
52 // Is there an issue connecting to the database?
53 if ($this->mySqli->connect_errno) {
54 echo '<br/>', 'Error: Unable to connect to Database.' , '<br>';
55 echo "Debugging errno: " . $this->mySqli->connect_errno , '<br>';
56 echo "Debugging error: " . $this->mySqli->connect_error , '<br>';
57 unset($this->mySqli);
58 exit;
59 }
60 }
61
62 // Set prefix for the table name if there's a prefix setup in the config file
63 private function setPrefix($value = '')
64 {
65 $this->prefix = $value;
66 }
67
68 // Function to insert data into table
69 public function insert($args)
70 {
71 // set type
72 $this->type = 'insert';
73 // set table and configure prefix, if available
74 $this->setTable($args['table']);
75 // generate insert query
76 $query = $this->genQuery($args);
77 // prepare query statement
78 $stmt = $this->mySqli->prepare($query);
79 if ($this->mySqli->errno) {
80 die('Unable to insert data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
81 }
82 // generate the bind_arr to be used to bind_param
83 $this->bindPar($args);
84 // bind parameters for statement execution
85 call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
86 // execute the statement (return error if execution failed)
87 if (!$stmt->execute()) {
88 die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
89 }
90 // close statement
91 $stmt->close();
92 $this->reset();
93 }
94
95 // Function to update data
96 public function update($args)
97 {
98 // set type for use in query generator
99 $this->type = 'update';
100 // set table and configure prefix, if available
101 $this->setTable($args['table']);
102 // generate update query
103 $query = $this->genQuery($args);
104 // prepare query statement
105 $stmt = $this->mySqli->prepare($query);
106 if ($this->mySqli->errno) {
107 die('Unable to insert data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
108 }
109 // generate the bind_arr to be used to bind_param
110 $this->bindPar($args);
111 // bind parameters for statement execution
112 call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
113 // execute the statement (return error if execution failed)
114 if (!$stmt->execute()) {
115 die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
116 }
117 // close statement
118 $stmt->close();
119 $this->reset();
120 }
121
122 // Function to select data from the table
123 public function select($args)
124 {
125 // set type for use in query generator
126 $this->type = 'select';
127 // set table and configure prefix, if available
128 $this->setTable($args['table']);
129 // generate select query
130 $query = $this->genQuery($args);
131 // prepare query statement
132 $stmt = $this->mySqli->prepare($query);
133 if ($this->mySqli->errno) {
134 die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
135 }
136 // generate the bind_arr to be used to bind_param
137 $this->bindPar($args);
138 // bind parameters for statement execution if bind_arr is not empty
139 // bind_arr will be empty if you're trying to retrieve all the values in a row
140 if (!empty($this->bind_arr)) {
141 call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
142 }
143 // execute the statement (return error if execution failed)
144 if (!$stmt->execute()) {
145 die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
146 }
147 // if you've manually defined the data that you need to retrieve, generate result set
148 if (is_array($args['data'])) {
149 // generate the result set as an array to be
150 $this->genResultArr($args);
151 call_user_func_array(array($stmt, 'bind_result'), $this->returnRef($this->result_arr));
152 if ($this->mySqli->errno) {
153 die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
154 }
155 $this->fetch = array(); // making sure the array is empty
156 $i=0;
157 while ($stmt->fetch()) {
158 $this->multi_result_arr = array_combine($args['data'], $this->result_arr);
159 // Get the values and append it to fetch array $i denotes the row number
160 foreach ($this->multi_result_arr as $arr => $val) {
161 $this->fetch[$i][$arr] = $val;
162 }
163 $i++;
164 }
165 // if there's just one row of results retrieved, just reset the array
166 // so that you can directly call the value by $fetch['column_name']
167 if (count($this->fetch) == 1) {
168 $this->fetch = $this->fetch[0];
169 }
170 } elseif ($args['data'] == '*') {
171 // Generate a result metadata variable to be used to fetch column names in the array
172 $res = $stmt->result_metadata();
173 // Copy the column tables as an array into the fields variable to generate bind_result later
174 $fields = $res->fetch_fields();
175 // Field count for iteration
176 $count = $res->field_count;
177 // row count to chose type of array (multidimensional if more than one row found)
178 $row = $res->num_rows;
179 for ($i = 0; $i < $count; $i++) {
180 $this->multi_result_arr[$i] = $this->result_arr[$i] = $fields[$i]->name;
181 }
182 call_user_func_array(array($stmt, 'bind_result'), $this->returnRef($this->result_arr));
183 if ($this->mySqli->errno) {
184 die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
185 }
186 $this->fetch = array(); // making sure the array is empty
187 $i=0;
188 // create a fetch array that combines the required db column names with the retrieved results
189 while ($stmt->fetch()) {
190 $this->fetch[$i] = array_combine($this->multi_result_arr, $this->result_arr);
191 $i++;
192 }
193 // if there's just one row of results retrieved, just reset the array
194 // so that you can directly call the value by $fetch['column_name']
195 if (count($this->fetch) == 1) {
196 $this->fetch = $this->fetch[0];
197 }
198 }
199 $stmt->close();
200 // reset values for next query
201 $this->reset();
202 return $this->fetch;
203 }
204
205 // Function to delete values from a Database
206 public function delete($args)
207 {
208 // delete function must not be used to truncate tables
209 if (!isset($args['where'])) {
210 echo 'If you really want to delete all the contents, use truncate() method.';
211 return;
212 } elseif (isset($args['data'])) { // if you're just deleting fields, use update statement instead
213 echo 'If you want to delete certain column in a row, use the update statement instead';
214 }
215 // set type for use in query generator
216 $this->type = 'delete';
217 // set table and configure prefix, if available
218 $this->setTable($args['table']);
219 // generate delete query
220 $query = $this->genQuery($args);
221 // prepare query statement
222 $stmt = $this->mySqli->prepare($query);
223 if ($this->mySqli->errno) {
224 die('Unable to delete data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
225 }
226 // generate the bind_arr to be used to bind_param
227 $this->bindPar($args);
228 // bind parameters for statement execution
229 call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
230 // execute the statement (return error if execution failed)
231 if (!$stmt->execute()) {
232 die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
233 }
234 // close statement
235 $stmt->close();
236 $this->reset();
237 }
238
239 // Deletes all the data and resets the table. Please use with caution
240 public function truncate($table)
241 {
242 // set table and configure prefix, if available
243 $this->setTable($table);
244 // query to truncate the entire table
245 // NOTE: This is irreversible
246 $query = 'TRUNCATE ' . $this->table;
247 // prepare query statement
248 $stmt = $this->mySqli->prepare($query);
249 // execute the statement (return error if execution failed)
250 if (!$stmt->execute()) {
251 die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
252 }
253 // close statement
254 $stmt->close();
255 $this->reset();
256 }
257
258 // prefix table name if db prefix is setup
259 private function setTable($table)
260 {
261 $this->table = $this->prefix . $table;
262 }
263
264 // Generates the mysqli query statement
265 private function genQuery($args)
266 {
267 switch ($this->type) {
268 case 'insert':
269 $query = "INSERT INTO `" . $this->table .'` ';
270 $query .= $this->genInsert($args['data']);
271 $query .= " VALUES " . $this->genInsval($args['data']);
272 break;
273 case 'select':
274 $query = "SELECT " . $this->genSelect($args) . " FROM " . $this->table;
275 if (isset($args['where'])) {
276 $query .= $this->genWhere($args);
277 }
278 if (isset($args['order'])) {
279 $query .= $this->genOrder($args);
280 }
281 if (isset($args['group'])) {
282 $query .= $this->genGroup($args);
283 }
284 if (isset($args['limit'])) {
285 $query .= " LIMIT " . $args['limit'];
286 }
287 break;
288 case 'update':
289 $query = "UPDATE `" . $this->table . "` SET";
290 $query .= $this->genUpdate($args['data']);
291 if (isset($args['where'])) {
292 $query .= $this->genWhere($args);
293 }
294 break;
295 case 'delete':
296 $query ="DELETE FROM `" . $this->table . '` ';
297 if (isset($args['where'])) {
298 $query .= $this->genWhere($args);
299 }
300 break;
301 default:
302 $query ='';
303 break;
304 }
305 return $query;
306 }
307
308 // Generate insert query
309 private function genInsert($data)
310 {
311 $ins_query = '( ';
312 foreach ($data as $key => $value) {
313 if ($data[$key] == end($data)) {
314 $ins_query .= ' ' . $key . ' ';
315 continue;
316 }
317 $ins_query .= ' ' . $key . ', ';
318 }
319 $ins_query .= ')';
320 return $ins_query;
321 }
322
323 // generate the value part of the insert query to be used as a prepared statement
324 // Eg (? , ?, ?)
325 private function genInsVal($data)
326 {
327 $ins_value = '(';
328 foreach ($data as $k => $v) {
329 if ($data[$k] == end($data)) {
330 $ins_value .= '?';
331 continue;
332 }
333 $ins_value .= '?, ';
334 }
335 $ins_value .=')';
336 return $ins_value;
337 }
338
339 // generate update query
340 private function genUpdate($data)
341 {
342 $update_query = '';
343 foreach ($data as $key => $value) {
344 $update_query .= ' ' .$key .' =?,' ;
345 }
346 $update_query = rtrim($update_query, ',');
347 return $update_query;
348 }
349
350 // Generate select query
351 private function genSelect($sel_array)
352 {
353 $sel_string = '';
354 if (is_array($sel_array['data'])) {
355 foreach ($sel_array['data'] as $value) {
356 $sel_string .= $value . ', ';
357 }
358 $sel_string = rtrim($sel_string, ', ');
359 } elseif ($sel_array['data'] == '*') {
360 $sel_string = '*';
361 }
362 return $sel_string;
363 }
364
365 // Generate where condition for query generator (genQuery)
366 private function genWhere($where_arr)
367 {
368 $where_query = ' WHERE';
369 if (isset($where_arr['whereOp'])) {
370 $opr = $where_arr['whereOp'];
371 } else {
372 $opr = '=';
373 }
374 // Check if the given array is associative
375 if ($this->isAssoc($where_arr)) {
376 foreach ($where_arr['where'] as $key => $value) {
377 $where_query .= ' ' . $key . $opr . '? ';
378 }
379 } else {
380 foreach ($where_arr['where'] as $value) {
381 $where_query .= ' ' . $value . $opr . '? ';
382 }
383 }
384 if (isset($where_arr['and']) && !empty($where_arr['and'])) {
385 $where_query .= $this->andWhere($where_arr);
386 }
387 if (isset($where_arr['or']) && !empty($where_arr['or'])) {
388 $where_query .= $this->orWhere($where_arr);
389 }
390 return $where_query;
391 }
392
393 // Generate and condition for query generator (genQuery)
394 private function andWhere($and_arr)
395 {
396 $and_query = ' AND';
397 if (isset($where_arr['andOP'])) {
398 $opr = $where_arr['andOP'];
399 } else {
400 $opr = '=';
401 }
402 foreach ($and_arr['and'] as $key => $value) {
403 $and_query .= ' ' . $key . $opr . '? ';
404 }
405 return $and_query;
406 }
407
408 // Generate OR condition for query generator (genQuery)
409 private function orWhere($or_arr)
410 {
411 $or_query = ' OR';
412 if (isset($or_arr['orOP'])) {
413 $opr = $or_arr['orOp'];
414 } else {
415 $opr = '=';
416 }
417 foreach ($or_arr['and'] as $key => $value) {
418 $or_query .= ' ' . $key . $opr . '? ';
419 }
420 return $or_query;
421 }
422
423 // Generate order by condition
424 private function genOrder($args)
425 {
426 $order_query = ' ORDER BY ' . $args['order'] .' ';
427 if (isset($args['oType']) && (($args['oType'] == 'ASC') || ($args['oType'] == 'DESC'))) {
428 $order_query .= $args['oType'];
429 }
430 return $order_query;
431 }
432
433 // Generate group by conditions
434 private function genGroup()
435 {
436 $grp_query = ' GROUP BY ' . $args['group'] .' ';
437 if (isset($args['gType']) && (($args['gType'] == 'ASC') || ($args['gType'] == 'DESC'))) {
438 $grp_query .= $args['gType'];
439 }
440 return $grp_query;
441 }
442
443 // Check the input array and forward it to bindParam for further processing
444 private function bindPar($args)
445 {
446 if (isset($args['data']) && $this->type != 'select') {
447 $this->bindParam($args['data']);
448 }
449 if (isset($args['where'])) {
450 $this->bindParam($args['where']);
451 }
452 if (isset($args['and'])) {
453 $this->bindParam($args['and']);
454 }
455 if (isset($args['or'])) {
456 $this->bindParam($args['or']);
457 }
458 if ($this->type == 'select' && !isset($args['where']) && !isset($args['and']) && !isset($args['or'])) {
459 unset($this->bind_arr);
460 }
461 }
462
463 // Organize generation of bind_arr in the below method based on $data
464 private function bindParam($data)
465 {
466 if (is_array($data)) {
467 if ($this->isAssoc($data)) {
468 foreach ($data as $key => $value) {
469 $this->bindValues($value);
470 }
471 } else {
472 foreach ($data as $value) {
473 $this->bindValues($value);
474 }
475 }
476 } else {
477 $this->bindValues($data);
478 }
479 }
480
481 // Detect type and push values inside the bind_arr to be submitted as bind parameters
482 private function bindValues($value)
483 {
484 $this->bind_arr[0] .= $this->detectType($value);
485 array_push($this->bind_arr, $value);
486 }
487
488 // Detect value type to generate bind parameter
489 protected function detectType($value)
490 {
491 switch (gettype($value)) {
492 case 'string':
493 return 's';
494 break;
495 case 'integer':
496 return 'i';
497 break;
498 case 'blob':
499 return 'b';
500 break;
501 case 'double':
502 return 'd';
503 break;
504 }
505 return '';
506 }
507
508 protected function returnRef(array &$arr)
509 {
510 //Referenced data array is required by mysqli since PHP 5.3+
511 if (strnatcmp(phpversion(), '5.3') >= 0) {
512 $refs = array();
513 foreach ($arr as $key => $value) {
514 $refs[$key] = & $arr[$key];
515 }
516 return $refs;
517 }
518 return $arr;
519 }
520
521 // Generate a result array with selected values from database for given data
522 private function genResultArr($args)
523 {
524 $this->result_arr = array();
525 foreach ($args['data'] as $value) {
526 array_push($this->result_arr, $value);
527 }
528 }
529
530 // Check if an array is associative
531 private function isAssoc(array $array)
532 {
533 $keys = array_keys($array);
534 return array_keys($keys) !== $keys;
535 }
536
537 // Reset to default values after an operation
538 private function reset()
539 {
540 $this->type = null;
541 $this->table = '';
542 $this->bind_arr = array('');
543 $this->result_arr = array();
544 $this->multi_result_arr = array();
545 }
546
547 // Disconnects the active connection
548 private function disconnect()
549 {
550 if (isset($this->mySqli)) {
551 $this->mySqli->close();
552 unset($this->mySqli);
553 }
554 }
555
556 // Making sure we don't have open connections
557 public function __destruct()
558 {
559 if (isset($this->mySqli)) {
560 // if there's an active connection, close it
561 if ($this->mySqli->ping()) {
562 $this->disconnect();
563 }
564 }
565 }
566}
567
568<?php
569/**
570 * This is an example configuration file. Even though the file is optional,
571 * the constants defined below are required for the wrapper class to work.
572 */
573
574/** MySQL database name */
575define('DB_NAME', 'DATABASE NAME HERE');
576
577/** MySQL database username */
578define('DB_USER', 'DATABASE USER NAME HERE');
579
580/** MySQL database password */
581define('DB_PASSWORD', 'DATABASE PASSWORD HERE');
582
583/** MySQL hostname */
584define('DB_HOST', 'localhost');
585
586/** [Optional] MySQL database prefix */
587define('DB_PREFIX', '');
588
589<?php
590$db = new MySQLiDB;
591?>
592
593<?php
594$args = [
595 'table' => 't1',
596 'data' => [
597 'f11' => '123',
598 'f12' => 'hello'
599 ]
600 ];
601// Calling the below function will submit f11 = 123, f12 ='hello' etc into the table 't1'
602$db->insert($args);
603?>
604
605<?php
606$args = [
607 'table' => 't1',
608 'data' => [
609 'f11' => '123',
610 'f14' => '456',
611 ],
612 // [Optional] However, if you do not define a where condition, fields in every row will
613 // be overwritten with the arg contents.
614 'where' => [
615 'id' => 10
616 ],
617 // [Optional] where operator is '=' by default, you only need to specify
618 // this if you would like to use a different operator.
619 'whereOp' => '=',
620 // [Optional] 'and' condition, works the same way as where condition
621 // andOp is '=' by default
622 'and' => [
623
624 ],
625 'andOp' => '=',
626 // [Optional] 'or' condition, works the same way as where condition
627 // orOp is '=' by default
628 'or' => [
629
630 ],
631 'orOp' => '',
632 ];
633?>
634
635<?php
636$args = [
637 'table' => 't1',
638 'data' => [
639 'f1' => 'test',
640 ],
641 'where' => [
642 'id' => 10,
643 ],
644 'and' => [
645 'f2' => 'foo',
646 ]
647 ];
648
649$db->update($args);
650?>
651
652<?php
653$args = [
654 'table' => 'test',
655 'data' => [
656 'f1' => '',
657 'f2' => 'foo'
658 ],
659 'where' => [
660 'id' => 1
661 ]
662 ];
663
664$db->update($args);
665?>
666
667<?php
668 // available options
669$args = [
670 'table' => 'table_name',
671 // data can either be an array with values defining field names that need to be retrieved, or just 'data' => '*'
672 'data' => [
673 'field1', 'field2'
674 ],
675 'where' => [
676 'field3' => 'foo'
677 ],
678 'whereOp' => '!=', // (only need to be defined if its anything other than =)
679 'and' => [
680 ],
681 'andOp' => '', // (only need to be defined if its anything other than =)
682 'or' => [
683 ],
684 'orOp' => '', // (only need to be defined if its anything other than =)
685 'limit' => 2, // this will limit the rows returned
686 'order' => '', // order by
687 'oType' => '', // ASC or DESC
688 'group' => '', // group by
689 'gType' => '', // ASC or DESC
690 ];
691
692// Example
693$args = [
694 'table' => 't1',
695 'data' => [
696 'f1'
697 ],
698 'where' => [
699 'f1' => 'hi',
700 ]
701 ];
702
703$fetch = $db->select($args);
704foreach ($fetch as $res) {
705// below code will dump all the rows. If you want a specific output,
706// check the echo statement below
707 var_dump($res);
708}
709// Or you can chose to display them row wise
710echo $fetch[0]['f2'];
711?>
712
713<?php
714$args = [
715 'table' => 't1',
716 'where' => [
717
718 ],
719 'or' => [
720
721 ],
722 ];
723
724$db->delete($args);
725?>
726
727Example
728<?php
729// Below statements should delete the row where id = 10, in table 'test'
730$args = [
731 'table' => 'test',
732 'where' => [
733 'id' => 10
734 ]
735 ];
736
737$db->delete($args);
738
739?>
740
741<?php
742$db->truncate($table_name);
743?>
744
745<?php
746// Example
747private function connect()
748{
749 if (!$this->mySqli->ping()) {
750 // make the connection
751 }
752}
753?>