1: <?php
2:
3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25:
26:
27: namespace Flea;
28:
29: 30: 31: 32: 33: 34:
35: class SqlQuery {
36:
37: 38: 39: 40: 41:
42: public static $TYPE_CREATE = 1;
43:
44: 45: 46: 47: 48:
49: public static $TYPE_SELECT = 2;
50:
51: 52: 53: 54: 55:
56: public static $TYPE_UPDATE = 3;
57:
58: 59: 60: 61: 62:
63: public static $TYPE_INSERT = 4;
64:
65: 66: 67: 68: 69:
70: public static $TYPE_DELETE = 5;
71:
72: 73: 74: 75: 76:
77: public static $TYPE_MULTI_INSERT = 6;
78:
79:
80: private static $_TEMP = null;
81:
82: 83: 84: 85: 86: 87:
88: public static function getTemp($type = 0) {
89: if (self::$_TEMP === null) {
90: self::$_TEMP = new SqlQuery();
91: } else {
92: self::$_TEMP->clean($type);
93: }
94: return self::$_TEMP;
95: }
96:
97:
98:
99:
100:
101:
102:
103: private $_type;
104:
105: 106: 107: 108: 109: 110: 111: 112: 113: 114:
115: public function getType() {
116: return $this->_type;
117: }
118:
119: 120: 121: 122: 123: 124: 125: 126: 127: 128:
129: public function setType($type) {
130: $this->_type = $type;
131: }
132:
133:
134:
135:
136:
137:
138:
139: private $_binds;
140:
141: 142: 143: 144: 145:
146: public function getBinds() {
147: return $this->_binds;
148: }
149:
150: 151: 152: 153: 154:
155: public function setBinds(array $binds) {
156: $this->_binds = $this->_binds + $binds;
157: }
158:
159: 160: 161: 162: 163: 164: 165:
166: public function addBind($key, $value, $pdoParamType) {
167: $this->_binds[] = array($key, $value, $pdoParamType);
168: }
169:
170:
171:
172:
173:
174:
175:
176: private $_create;
177:
178: 179: 180: 181: 182: 183:
184: public function getCreate() {
185: return $this->_create;
186: }
187:
188: 189: 190: 191: 192: 193:
194: public function setCreate($create) {
195: $this->_type = self::$TYPE_CREATE;
196: $this->_create = $create;
197: }
198:
199:
200:
201:
202:
203:
204:
205: private $_select;
206:
207: 208: 209: 210: 211: 212:
213: public function getSelect() {
214: return $this->_select;
215: }
216:
217: 218: 219: 220: 221: 222:
223: public function setSelect($select) {
224: $this->_type = self::$TYPE_SELECT;
225: $this->_select = $select;
226: }
227:
228: private $_where;
229:
230: 231: 232: 233: 234: 235:
236: public function getWhere() {
237: return $this->_where;
238: }
239:
240: 241: 242: 243: 244: 245:
246: public function setWhere($where) {
247: $this->_where = $where;
248: }
249:
250: private $_from;
251:
252: 253: 254: 255: 256: 257:
258: public function getFrom() {
259: return $this->_from;
260: }
261:
262: 263: 264: 265: 266: 267:
268: public function setFrom($from) {
269: $this->_from = $from;
270: }
271:
272: private $_groupBy;
273:
274: 275: 276: 277: 278: 279:
280: public function getGroupBy() {
281: return $this->_groupBy;
282: }
283:
284: 285: 286: 287: 288: 289:
290: public function setGroupBy($groupBy) {
291: $this->_groupBy = $groupBy;
292: }
293:
294: private $_having;
295:
296: 297: 298: 299: 300: 301:
302: public function getHaving() {
303: return $this->_having;
304: }
305:
306: 307: 308: 309: 310: 311:
312: public function setHaving($having) {
313: $this->_having = $having;
314: }
315:
316: private $_orderBy;
317:
318: 319: 320: 321: 322: 323:
324: public function getOrderBy() {
325: return $this->_orderBy;
326: }
327:
328: 329: 330: 331: 332: 333:
334: public function setOrderBy($orderBy) {
335: $this->_orderBy = $orderBy;
336: }
337:
338: private $_limit;
339:
340: 341: 342: 343: 344: 345:
346: public function getLimit() {
347: return $this->_limit;
348: }
349:
350: 351: 352: 353: 354: 355:
356: public function setLimit($limit) {
357: $this->_limit = $limit;
358: }
359:
360:
361:
362:
363:
364:
365:
366: private $_insert;
367:
368: 369: 370: 371: 372: 373:
374: public function getInsert() {
375: return $this->_insert;
376: }
377:
378: 379: 380: 381: 382: 383:
384: public function setInsert($insert) {
385: $this->_type = self::$TYPE_INSERT;
386: $this->_insert = $insert;
387: }
388:
389: private $_values;
390:
391: 392: 393: 394: 395: 396:
397: public function getValues() {
398: return $this->_values;
399: }
400:
401: 402: 403: 404: 405: 406:
407: public function setValues($values) {
408: $this->_values = $values;
409: }
410:
411:
412:
413:
414:
415:
416:
417: private $_update;
418:
419: 420: 421: 422: 423: 424:
425: public function getUpdate() {
426: return $this->_update;
427: }
428:
429: 430: 431: 432: 433: 434:
435: public function setUpdate($update) {
436: $this->_type = self::$TYPE_UPDATE;
437: $this->_update = $update;
438: }
439:
440: private $_set;
441:
442: 443: 444: 445: 446: 447:
448: public function getSet() {
449: return $this->_set;
450: }
451:
452: 453: 454: 455: 456: 457:
458: public function setSet($set) {
459: $this->_set = $set;
460: }
461:
462:
463:
464:
465:
466:
467:
468: private $_delete;
469:
470: 471: 472: 473: 474: 475:
476: public function getDelete() {
477: return $this->_delete;
478: }
479:
480: 481: 482: 483: 484: 485:
486: public function setDelete($delete) {
487: $this->_type = self::$TYPE_DELETE;
488: $this->_delete = $delete;
489: }
490:
491: 492: 493: 494: 495: 496: 497: 498: 499: 500:
501: public function __construct($type = 0) {
502: $this->clean($type);
503: }
504:
505: 506: 507: 508: 509:
510: public function clean($type = 0) {
511: $this->_type = $type;
512: $this->_create = '';
513: $this->_select = '';
514: $this->_from = '';
515: $this->_where = '';
516: $this->_groupBy = '';
517: $this->_having = '';
518: $this->_orderBy = '';
519: $this->_limit = '';
520: $this->_update = '';
521: $this->_set = '';
522: $this->_insert = '';
523: $this->_values = '';
524: $this->_delete = '';
525: $this->_binds = array();
526: }
527:
528: 529: 530: 531: 532: 533: 534: 535: 536: 537:
538: public function initSelect($select, $from, array $whereList = null, array $whereSigns = null, $orderBy = '', $limit = '') {
539: $this->_type = self::$TYPE_SELECT;
540: $this->_select = $select;
541: $this->_from = $from;
542: if ($whereList !== null) {
543: $this->_where = $this->getStrFromBinding($whereList, ' AND ', $whereSigns);
544: }
545: $this->_orderBy = $orderBy;
546: $this->_limit = $limit;
547: }
548:
549: 550: 551: 552: 553: 554: 555:
556: public function initCount($from, array $whereList = null, array $signList = null) {
557: $this->_type = self::$TYPE_SELECT;
558: $this->_select = 'COUNT(*)';
559: $this->_from = $from;
560: if ($whereList !== null) {
561: $this->_where = $this->getStrFromBinding($whereList, ' AND ', $signList);
562: }
563: }
564:
565: 566: 567: 568: 569: 570:
571: public function initInsertValues($tableName, array $values = array()) {
572: $this->_type = self::$TYPE_INSERT;
573: $this->_insert = 'INTO `' . $tableName . '` (';
574:
575: $this->_values = '';
576: $first = true;
577: foreach ($values as $key => $value) {
578: if (gettype($value) == 'boolean') {
579: $this->_insert .= ( ($first) ? '' : ', ' ) . $key;
580: $this->_values .= ( ($first) ? ':' : ', :' ) . $key;
581: $this->_binds[] = array(':' . $key, (($value) ? '1' : '0'), \PDO::PARAM_BOOL);
582: $first = false;
583: } elseif (gettype($value) == 'integer') {
584: $this->_insert .= ( ($first) ? '' : ', ' ) . $key;
585: $this->_values .= ( ($first) ? ':' : ', :' ) . $key;
586: $this->_binds[] = array(':' . $key, $value, \PDO::PARAM_INT);
587: $first = false;
588: } elseif (gettype($value) == 'double') {
589: $this->_insert .= ( ($first) ? '' : ', ' ) . $key;
590: $this->_values .= ( ($first) ? ':' : ', :' ) . $key;
591: $this->_binds[] = array(':' . $key, $value, \PDO::PARAM_STR);
592: $first = false;
593: } elseif (gettype($value) == 'string') {
594: $this->_insert .= ( ($first) ? '' : ', ' ) . $key;
595: $this->_values .= ( ($first) ? ':' : ', :' ) . $key;
596: $this->_binds[] = array(':' . $key, $value, \PDO::PARAM_STR);
597: $first = false;
598: }
599: }
600: $this->_insert .= ')';
601: }
602:
603: 604: 605: 606: 607: 608: 609:
610: public function initMultiInsertValues($tableName, array $keys, array $values) {
611: $this->_type = self::$TYPE_MULTI_INSERT;
612: $this->_insert = 'INTO `' . $tableName . '` (' . implode(', ', $keys) . ')';
613:
614: $this->_values = array();
615: $binds = array();
616: foreach ($values as $line) {
617: $tmp = array();
618: foreach ($line as $row) {
619: $tmp[] = '?';
620: }
621: $this->_values[] = implode(', ', $tmp);
622: $binds = array_merge($binds, $line);
623: }
624: $this->_values = implode('), (', $this->_values);
625: $this->_binds = $binds;
626: }
627:
628: 629: 630: 631: 632:
633: 634: 635: 636: 637: 638: 639: 640: 641: 642: 643: 644: 645: 646: 647: 648: 649: 650: 651: 652: 653: 654: 655: 656: 657: 658: 659: 660: 661: 662: 663: 664: 665: 666: 667: 668: 669: 670:
671:
672: 673: 674: 675: 676: 677: 678:
679: public function initUpdateSet($tableName, array $setList, array $whereList = null) {
680: $this->_type = self::$TYPE_UPDATE;
681: $this->_update = '`' . $tableName . '`';
682: $this->_set = $this->getStrFromBinding($setList, ', ');
683: if ($whereList !== null) {
684: $this->_where = $this->getStrFromBinding($whereList, ' AND ');
685: }
686: }
687:
688: 689: 690: 691: 692: 693:
694: public function initCreate($tableName, array $getObjectVars) {
695: $this->_type = self::$TYPE_CREATE;
696: $this->_create = 'TABLE `' . $tableName . '` (';
697:
698: $first = true;
699: foreach ($getObjectVars as $key => $value) {
700: if (gettype($value) == "boolean") {
701: $this->_create .= ( ($first) ? '' : ', ' ) . $key . ' BOOLEAN';
702: if ($first)
703: $first = false;
704: }
705: elseif (gettype($value) == "integer") {
706: $this->_create .= ( ($first) ? '' : ', ' ) . $key . ' INT';
707: if ($first)
708: $first = false;
709: }
710: elseif (gettype($value) == "double") {
711: $this->_create .= ( ($first) ? '' : ', ' ) . $key . ' DOUBLE';
712: if ($first)
713: $first = false;
714: }
715: elseif (gettype($value) == "string") {
716: $this->_create .= ( ($first) ? '' : ', ' ) . $key . ' TEXT';
717: if ($first)
718: $first = false;
719: }
720: }
721: $this->_create .= ' )';
722: }
723:
724: 725: 726: 727: 728: 729:
730: public function initDelete($from, array $whereList) {
731: $this->_type = self::$TYPE_DELETE;
732:
733: $this->_from = $from;
734:
735: if ($whereList !== null)
736: $this->_where = $this->getStrFromBinding($whereList, ' AND ');
737: }
738:
739: private function getStrFromBinding(array $valueList , $strGlue = ', ', array $signList = null) {
740:
741: $values = array();
742: $signs = array();
743: $keys = array();
744: $i = 0;
745: foreach ($valueList as $key => $value) {
746: $values[$i] = $value;
747: $keys[$i] = $key;
748:
749: if ($signList === null || !isset($signList[$i]))
750: $signs[$i] = '=';
751: else
752: $signs[$i] = $signList[$i];
753:
754: $i++;
755: }
756:
757: $output = '';
758:
759: $first = true;
760: $l = count($values);
761: for ($i = 0; $i < $l; $i++) {
762: $key = $keys[$i];
763: $sign = $signs[$i];
764: $value = $values[$i];
765:
766: if (gettype($value) == 'boolean') {
767: $output .= ( ($first) ? '' : $strGlue ) . $key . ' ' . $sign . ' :' . $key;
768: if (!array_key_exists(':' . $key, $this->_binds)) {
769: $this->_binds[] = array(':' . $key, (($value) ? '1' : '0'), \PDO::PARAM_BOOL);
770: }
771: $first = false;
772: } elseif (gettype($value) == 'integer') {
773: $output .= ( ($first) ? '' : $strGlue ) . $key . ' ' . $sign . ' :' . $key;
774: if (!array_key_exists(':' . $key, $this->_binds)) {
775: $this->_binds[] = array(':' . $key, $value, \PDO::PARAM_INT);
776: }
777: $first = false;
778: } elseif (gettype($value) == 'double') {
779: $output .= ( ($first) ? '' : $strGlue ) . $key . ' ' . $sign . ' :' . $key;
780: if (!array_key_exists(':' . $key, $this->_binds)) {
781: $this->_binds[] = array(':' . $key, $value, \PDO::PARAM_STR);
782: }
783: $first = false;
784: } elseif (gettype($value) == 'string') {
785: $output .= ( ($first) ? '' : $strGlue ) . $key . ' ' . $sign . ' :' . $key;
786: if (!array_key_exists(':' . $key, $this->_binds)) {
787: $this->_binds[] = array(':' . $key, $value, \PDO::PARAM_STR);
788: }
789: $first = false;
790: }
791: }
792:
793: return $output;
794: }
795:
796: 797: 798: 799: 800:
801: public function getRequest() {
802: switch ($this->_type) {
803: case self::$TYPE_CREATE:
804: return $this->getRequestCreate();
805: break;
806:
807: case self::$TYPE_SELECT:
808: return $this->getRequestRead();
809: break;
810:
811: case self::$TYPE_INSERT:
812: return $this->getRequestInsert();
813: break;
814:
815: case self::$TYPE_UPDATE:
816: return $this->getRequestUpdate();
817: break;
818:
819: case self::$TYPE_DELETE:
820: return $this->getRequestDelete();
821: break;
822:
823: case self::$TYPE_MULTI_INSERT:
824: return $this->getRequestMuliInsert();
825: break;
826:
827: default :
828: if (_DEBUG && $this->_type == 0) {
829: Debug::getInstance()->addError('No type declared for this SQL request');
830: }
831: }
832:
833: return '';
834: }
835:
836: 837: 838: 839: 840:
841: private function getRequestCreate() {
842: if (_DEBUG && $this->_create == '') {
843: Debug::getInstance()->addError('For a TYPE_CREATE SQL query You must init the var "create"');
844: }
845: return 'CREATE ' . $this->_create . ';';
846: }
847:
848: 849: 850: 851: 852:
853: private function getRequestRead() {
854: if ($this->_select == '') {
855: $this->_select = '*';
856: }
857: if (_DEBUG && $this->_from == '') {
858: Debug::getInstance()->addError('For a TYPE_SELECT SQL query You must init the var "from"');
859: }
860: $request = 'SELECT ' . $this->_select;
861: $request .= ' FROM ' . $this->_from;
862: if ($this->_where != '') {
863: $request .= ' WHERE ' . $this->_where;
864: }
865: if ($this->_groupBy != '') {
866: $request .= ' GROUP BY ' . $this->_groupBy;
867: }
868: if ($this->_having != '') {
869: $request .= ' HAVING ' . $this->_having;
870: }
871: if ($this->_orderBy != '') {
872: $request .= ' ORDER BY ' . $this->_orderBy;
873: }
874: if ($this->_limit != '') {
875: $request .= ' LIMIT ' . $this->_limit;
876: }
877: return $request . ';';
878: }
879:
880: 881: 882: 883: 884:
885: private function getRequestInsert() {
886: if (_DEBUG) {
887: if ($this->_insert == '') {
888: Debug::getInstance()->addError('For a TYPE_INSERT SQL query You must init the var "insert"');
889: }
890: }
891: $request = 'INSERT ' . $this->_insert;
892: if ($this->_values != '') {
893: $request .= ' VALUES (' . $this->_values . ')';
894: }
895: if ($this->_set != '') {
896: $request .= ' SET ' . $this->_groupBy;
897: }
898: if ($this->_select != '') {
899: $request .= ' SELECT ' . $this->_select;
900: }
901: return $request . ';';
902: }
903:
904: private function getRequestMuliInsert() {
905: if (_DEBUG) {
906: if ($this->_insert == '') {
907: Debug::getInstance()->addError('For a TYPE_INSERT SQL query You must init the var "insert"');
908: }
909: }
910: $request = 'INSERT ' . $this->_insert;
911: if ($this->_values != '') {
912: $request .= ' VALUES (' . $this->_values . ')';
913: }
914: if ($this->_set != '') {
915: $request .= ' SET ' . $this->_groupBy;
916: }
917: if ($this->_select != '') {
918: $request .= ' SELECT ' . $this->_select;
919: }
920: return $request . ';';
921: }
922:
923: 924: 925: 926: 927:
928: private function getRequestUpdate() {
929: if (_DEBUG) {
930: if ($this->_update == '') {
931: Debug::getInstance()->addError('For a TYPE_UPDATE SQL query You must init the var "update"');
932: }
933: if ($this->_set == '') {
934: Debug::getInstance()->addError('For a TYPE_UPDATE SQL query You must init the var "set"');
935: }
936: }
937: $request = 'UPDATE ' . $this->_update;
938: if ($this->_set != '') {
939: $request .= ' SET ' . $this->_set;
940: }
941: if ($this->_where != '') {
942: $request .= ' WHERE ' . $this->_where;
943: }
944: if ($this->_orderBy != '') {
945: $request .= ' ORDER BY ' . $this->_orderBy;
946: }
947: if ($this->_limit != '') {
948: $request .= ' LIMIT ' . $this->_limit;
949: }
950: return $request . ';';
951: }
952:
953: 954: 955: 956: 957:
958: private function getRequestDelete() {
959: if (_DEBUG) {
960: 961: 962: 963:
964: if ($this->_from == '') {
965: Debug::getInstance()->addError('For a TYPE_DELETE SQL query You must init the var "from"');
966: }
967: }
968: $request = 'DELETE ' . $this->_delete;
969: $request .= ' FROM ' . $this->_from;
970: if ($this->_where != '') {
971: $request .= ' WHERE ' . $this->_where;
972: }
973: if ($this->_orderBy != '') {
974: $request .= ' ORDER BY ' . $this->_orderBy;
975: }
976: if ($this->_limit != '') {
977: $request .= ' LIMIT ' . $this->_limit;
978: }
979: return $request . ';';
980: }
981:
982: }
983: