PageRenderTime 56ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 0ms

/classes/mysql.query.select.class.php

https://github.com/magedeveloper/frosted-mysql-library
PHP | 1160 lines | 710 code | 160 blank | 290 comment | 145 complexity | c2a718388cd94c759564fd81144c9e67 MD5 | raw file
  1. <?php
  2. /**
  3. * Frosted MySQL Library Select Query Class
  4. * - - - - - - - - - -
  5. * Add "SELECT" functionality to Frosted MySQL Library and will not work without them.
  6. * - - - - - - - - - -
  7. * Licensed under MIT license
  8. * - - - - - - - - - -
  9. * @Creator Daniel 'Eisbehr' Kern
  10. * @Require PHP5
  11. * @Version 3.0
  12. * @Date 01.08.2013
  13. * @Update 01.08.2013
  14. * - - - - - - - - - -
  15. */
  16. class mysqlClass_Select extends mysqlClass_Abstract implements mysqlClass_Queries
  17. {
  18. /**
  19. * reset select class
  20. * @param boolean $format
  21. * @return mysqlClass_Select
  22. */
  23. public function resetQuery($format)
  24. {
  25. parent::resetQuery($format);
  26. $this->query["all"] = false;
  27. $this->query["distinct"] = false;
  28. $this->query["row"] = false;
  29. $this->query["high"] = false;
  30. $this->query["straight"] = false;
  31. $this->query["columns"] = array();
  32. $this->query["from"] = array();
  33. $this->query["join"] = array();
  34. $this->query["where"] = array();
  35. $this->query["group"] = array();
  36. $this->query["rollup"] = false;
  37. $this->query["having"] = array();
  38. $this->query["order"] = array();
  39. $this->query["limit"] = NULL;
  40. $this->query["procedure"] = NULL;
  41. $this->query["update"] = false;
  42. $this->query["lock"] = false;
  43. $this->query["union"] = NULL;
  44. return $this;
  45. }
  46. /*
  47. ** query related
  48. */
  49. /**
  50. * add 'all' to query
  51. * @param boolean $all
  52. * @return mysqlClass_Select
  53. */
  54. public function all($all = true)
  55. {
  56. if( (bool)$all )
  57. {
  58. $this->query["distinct"] = false;
  59. $this->query["row"] = false;
  60. }
  61. $this->query["all"] = (bool)$all;
  62. return $this;
  63. }
  64. /**
  65. * add 'distinct' to query
  66. * @param boolean $distinct
  67. * @return mysqlClass_Select
  68. */
  69. public function distinct($distinct = true)
  70. {
  71. if( (bool)$distinct )
  72. {
  73. $this->query["all"] = false;
  74. $this->query["row"] = false;
  75. }
  76. $this->query["distinct"] = (bool)$distinct;
  77. return $this;
  78. }
  79. /**
  80. * add 'distinct row' to query
  81. * @param boolean $distinctRow
  82. * @return mysqlClass_Select
  83. */
  84. public function distinctRow($distinctRow = true)
  85. {
  86. if( (bool)$distinctRow )
  87. {
  88. $this->query["all"] = false;
  89. $this->query["distinct"] = false;
  90. }
  91. $this->query["row"] = (bool)$distinctRow;
  92. return $this;
  93. }
  94. /**
  95. * add 'high priority' to query
  96. * @param boolean $high
  97. * @return mysqlClass_Select
  98. */
  99. public function highPriority($high = true)
  100. {
  101. $this->query["high"] = (bool)$high;
  102. return $this;
  103. }
  104. /**
  105. * add 'straight join' to query
  106. * @param boolean $straightJoin
  107. * @return mysqlClass_Select
  108. */
  109. public function straight($straightJoin = true)
  110. {
  111. $this->query["straight"] = (bool)$straightJoin;
  112. return $this;
  113. }
  114. /**
  115. * add columns to select
  116. * @param string|array $column
  117. * @return mysqlClass_Select
  118. */
  119. public function columns($column = "*")
  120. {
  121. // only one string is given
  122. if( func_num_args() == 1 )
  123. {
  124. // string
  125. if( is_string($column) )
  126. {
  127. $this->query["columns"][] = $column;
  128. }
  129. // array
  130. else if( is_array($column) )
  131. {
  132. foreach( $column as $field => $name )
  133. {
  134. if( !is_numeric($field) )
  135. {
  136. if( $name instanceof mysqlClass_Select )
  137. {
  138. $this->query["columns"][] = array($name, $field);
  139. }
  140. else
  141. $this->query["columns"][] = $field . " AS " . $name;
  142. }
  143. else
  144. {
  145. if( $name instanceof mysqlClass_Select )
  146. $this->query["columns"][] = $name;
  147. else
  148. $this->columns($name);
  149. }
  150. }
  151. }
  152. // sub select
  153. else if( $column instanceof mysqlClass_Select )
  154. {
  155. $this->query["columns"][] = $column;
  156. }
  157. return $this;
  158. }
  159. foreach( func_get_args() as $param )
  160. {
  161. if( is_string($param) )
  162. $this->query["columns"][] = $param;
  163. else
  164. $this->columns($param);
  165. }
  166. return $this;
  167. }
  168. /**
  169. * add 'from' to query
  170. * @param string|array $table,...
  171. * @return mysqlClass_Select
  172. */
  173. public function from($table)
  174. {
  175. // only one string is set
  176. if( func_num_args() == 1 && is_string($table) )
  177. {
  178. $this->query["from"][] = $table;
  179. return $this;
  180. }
  181. // add all tables to query
  182. foreach( func_get_args() as $param )
  183. {
  184. if( !is_array($param) )
  185. $this->query["from"][] = $param;
  186. else
  187. foreach( $param as $database => $name )
  188. {
  189. if( !is_numeric($database) )
  190. $this->query["from"][] = $database . " AS " . $name;
  191. else
  192. $this->query["from"][] = $name;
  193. }
  194. }
  195. return $this;
  196. }
  197. /**
  198. * add join to query
  199. * @param string $type
  200. * @param array $tables
  201. * @return mysqlClass_Select
  202. */
  203. private function addJoin($type, $tables)
  204. {
  205. $join = array("type" => $type, "tables" => array(), "on" => array(), "using" => array());
  206. // format tables
  207. foreach( $tables as $_tables )
  208. if( is_array($_tables) )
  209. foreach( $_tables as $table => $name )
  210. if( !is_numeric($table) )
  211. $join["tables"][] = $table . " AS " . $name;
  212. else
  213. $join["tables"][] = $name;
  214. else
  215. $join["tables"][] = $_tables;
  216. // add join
  217. $this->query["join"][] = $join;
  218. return $this;
  219. }
  220. /**
  221. * add 'join' to query
  222. * @param string $table
  223. * @return mysqlClass_Select
  224. */
  225. public function join($table)
  226. {
  227. // prevent php debug notification
  228. if( $table );
  229. return $this->addJoin("JOIN", func_get_args());
  230. }
  231. /**
  232. * add 'join' to query
  233. * @param string $table
  234. * @return mysqlClass_Select
  235. */
  236. public function straightJoin($table)
  237. {
  238. // prevent php debug notification
  239. if( $table );
  240. return $this->addJoin("STRAIGHT_JOIN", func_get_args());
  241. }
  242. /**
  243. * add 'left join' to query
  244. * @param string $table
  245. * @return mysqlClass_Select
  246. */
  247. public function leftJoin($table)
  248. {
  249. // prevent php debug notification
  250. if( $table );
  251. return $this->addJoin("LEFT JOIN", func_get_args());
  252. }
  253. /**
  254. * add 'right join' to query
  255. * @param string $table
  256. * @return mysqlClass_Select
  257. */
  258. public function rightJoin($table)
  259. {
  260. // prevent php debug notification
  261. if( $table );
  262. return $this->addJoin("RIGHT JOIN", func_get_args());
  263. }
  264. /**
  265. * add 'inner join' to query
  266. * @param string $table
  267. * @return mysqlClass_Select
  268. */
  269. public function innerJoin($table)
  270. {
  271. // prevent php debug notification
  272. if( $table );
  273. return $this->addJoin("INNER JOIN", func_get_args());
  274. }
  275. /**
  276. * add 'cross join' to query
  277. * @param string $table
  278. * @return mysqlClass_Select
  279. */
  280. public function crossJoin($table)
  281. {
  282. // prevent php debug notification
  283. if( $table );
  284. return $this->addJoin("CROSS JOIN", func_get_args());
  285. }
  286. /**
  287. * add 'left outer join' to query
  288. * @param string $table
  289. * @return mysqlClass_Select
  290. */
  291. public function leftOuterJoin($table)
  292. {
  293. // prevent php debug notification
  294. if( $table );
  295. return $this->addJoin("LEFT OUTER JOIN", func_get_args());
  296. }
  297. /**
  298. * add 'right outer join' to query
  299. * @param string $table
  300. * @return mysqlClass_Select
  301. */
  302. public function rightOuterJoin($table)
  303. {
  304. // prevent php debug notification
  305. if( $table );
  306. return $this->addJoin("RIGHT OUTER JOIN", func_get_args());
  307. }
  308. /**
  309. * add 'natural join' to query
  310. * @param string $table
  311. * @return mysqlClass_Select
  312. */
  313. public function naturalJoin($table)
  314. {
  315. // prevent php debug notification
  316. if( $table );
  317. return $this->addJoin("NATURAL JOIN", func_get_args());
  318. }
  319. /**
  320. * add 'natural left join' to query
  321. * @param string $table
  322. * @return mysqlClass_Select
  323. */
  324. public function naturalLeftJoin($table)
  325. {
  326. // prevent php debug notification
  327. if( $table );
  328. return $this->addJoin("NATURAL LEFT JOIN", func_get_args());
  329. }
  330. /**
  331. * add 'natural left outer join' to query
  332. * @param string $table
  333. * @return mysqlClass_Select
  334. */
  335. public function naturalLeftOuterJoin($table)
  336. {
  337. // prevent php debug notification
  338. if( $table );
  339. return $this->addJoin("NATURAL LEFT OUTER JOIN", func_get_args());
  340. }
  341. /**
  342. * add 'natural right join' to query
  343. * @param string $table
  344. * @return mysqlClass_Select
  345. */
  346. public function naturalRightJoin($table)
  347. {
  348. // prevent php debug notification
  349. if( $table );
  350. return $this->addJoin("NATURAL RIGHT JOIN", func_get_args());
  351. }
  352. /**
  353. * add 'natural right outer join' to query
  354. * @param string $table
  355. * @return mysqlClass_Select
  356. */
  357. public function naturalRightOuterJoin($table)
  358. {
  359. // prevent php debug notification
  360. if( $table );
  361. return $this->addJoin("NATURAL RIGHT OUTER JOIN", func_get_args());
  362. }
  363. /**
  364. * add 'on' to last join in query
  365. * @param string $on
  366. * @param string $replace
  367. * @param boolean $nextRelation
  368. * @return mysqlClass_Select
  369. */
  370. public function on($on, $replace = NULL, $nextRelation = mysqlClass::JOIN_AND)
  371. {
  372. $last = count($this->query["join"]) - 1;
  373. if( $last >= 0 )
  374. {
  375. if( $replace != NULL )
  376. {
  377. $this->query["join"][$last]["on"][] = str_replace("?", $this->parent->escape($replace), $on);
  378. $this->query["join"][$last]["on"][] = $nextRelation == mysqlClass::JOIN_OR ? mysqlClass::JOIN_OR : mysqlClass::JOIN_AND;
  379. }
  380. else
  381. {
  382. $this->query["join"][$last]["on"][] = $on;
  383. $this->query["join"][$last]["on"][] = $nextRelation == mysqlClass::JOIN_OR ? mysqlClass::JOIN_OR : mysqlClass::JOIN_AND;
  384. }
  385. }
  386. return $this;
  387. }
  388. /**
  389. * add or related 'on' to last join in query
  390. * @param string $on
  391. * @param string $replace
  392. * @param boolean $nextRelation
  393. * @return mysqlClass_Select
  394. */
  395. public function orOn($on, $replace = NULL, $nextRelation = mysqlClass::JOIN_AND)
  396. {
  397. $last = count($this->query["join"]) - 1;
  398. if( $last >= 0 )
  399. {
  400. $lastOn = count($this->query["join"][$last]["on"]) - 1;
  401. if( $lastOn >= 0 )
  402. $this->query["join"][$last]["on"][$lastOn] = mysqlClass::JOIN_OR;
  403. }
  404. return $this->on($on, $replace, $nextRelation);
  405. }
  406. /**
  407. * add 'using' to last join in query
  408. * @param string $column
  409. * @return mysqlClass_Select
  410. */
  411. public function using($column)
  412. {
  413. // prevent php debug notification
  414. if( $column );
  415. // get last join id
  416. $last = count($this->query["join"]) - 1;
  417. if( $last >= 0 )
  418. foreach( func_get_args() as $columns )
  419. if( is_array($columns) )
  420. {
  421. foreach( $columns as $column )
  422. if( !in_array($column, $this->query["join"][$last]["using"]) )
  423. $this->query["join"][$last]["using"][] = $column;
  424. }
  425. else
  426. {
  427. if( !in_array($columns, $this->query["join"][$last]["using"]) )
  428. $this->query["join"][$last]["using"][] = $columns;
  429. }
  430. return $this;
  431. }
  432. /**
  433. * add 'where' to query
  434. * @param string $condition
  435. * @param string $replace
  436. * @param string $nextRelation
  437. * @return mysqlClass_Select
  438. */
  439. public function where($condition, $replace = NULL, $nextRelation = mysqlClass::WHERE_AND)
  440. {
  441. // add condition
  442. if( !is_null($replace) )
  443. {
  444. if( is_array($replace) )
  445. {
  446. // escape all values
  447. foreach( $replace as &$value ) $value = $this->parent->escape($value);
  448. // format sub-query
  449. if( $this->format )
  450. {
  451. $condition = str_replace("ANY(?)", "\nANY\n(\n ?\n)", $condition);
  452. $condition = str_replace("IN(?)", "\nIN\n(\n ?\n)", $condition);
  453. $condition = str_replace("SOME(?)", "\nSOME\n(\n ?\n)", $condition);
  454. }
  455. $glue = $this->format ? ",\n " : ",";
  456. $this->query["where"][] = str_replace("?", join($glue, $replace), $condition);
  457. }
  458. else if( $replace instanceof mysqlClass_Select )
  459. $this->query["where"][] = array($condition, $replace);
  460. else
  461. $this->query["where"][] = str_replace("?", $this->parent->escape($replace), $condition);
  462. }
  463. else
  464. $this->query["where"][] = $condition;
  465. // add relation
  466. if( strtoupper($nextRelation) == mysqlClass::WHERE_OR )
  467. $this->query["where"][] = mysqlClass::WHERE_OR;
  468. else
  469. $this->query["where"][] = mysqlClass::WHERE_AND;
  470. return $this;
  471. }
  472. /**
  473. * add 'or' related 'where' to query
  474. * @param string $condition
  475. * @param string $replace
  476. * @param string $nextRelation
  477. * @return mysqlClass_Select
  478. */
  479. public function orWhere($condition, $replace = NULL, $nextRelation = mysqlClass::WHERE_AND)
  480. {
  481. if( !empty($this->query["where"]) )
  482. $this->query["where"][(count($this->query["where"]) - 1)] = mysqlClass::WHERE_OR;
  483. return $this->where($condition, $replace, $nextRelation);
  484. }
  485. /**
  486. * add 'group by' to query
  487. * @param string $field
  488. * @param string $order
  489. * @return mysqlClass_Select
  490. */
  491. public function groupBy($field, $order = mysqlClass::GROUP_ASC)
  492. {
  493. if( strtoupper($order) == mysqlClass::GROUP_DESC )
  494. $this->query["group"][] = $field . " " . mysqlClass::GROUP_DESC;
  495. else
  496. $this->query["group"][] = $field . " " . mysqlClass::GROUP_ASC;
  497. return $this;
  498. }
  499. /**
  500. * alias of 'groupBy'
  501. * @param string $field
  502. * @param string $order
  503. * @return mysqlClass_Select
  504. */
  505. public function group($field, $order = mysqlClass::GROUP_ASC)
  506. {
  507. return $this->groupBy($field, $order);
  508. }
  509. /**
  510. * add 'with rollup' to query
  511. * @param boolean $rollup
  512. * @return mysqlClass_Select
  513. */
  514. public function withRollup($rollup = true)
  515. {
  516. $this->query["rollup"] = (bool)$rollup;
  517. return $this;
  518. }
  519. /**
  520. * add 'having' to group
  521. * @param string $condition
  522. * @param string $replace
  523. * @param string $nextRelation
  524. * @return mysqlClass_Select
  525. */
  526. public function having($condition, $replace = NULL, $nextRelation = mysqlClass::HAVING_AND)
  527. {
  528. // add condition
  529. if( !is_null($replace) )
  530. {
  531. if( is_array($replace) )
  532. {
  533. foreach( $replace as &$value )
  534. $value = $this->parent->escape($value);
  535. if( $this->format )
  536. $condition = str_replace("IN(?)", "\nIN\n(\n ?\n)", $condition);
  537. $glue = $this->format ? ",\n " : ",";
  538. $this->query["having"][] = str_replace("?", join($glue, $replace), $condition);
  539. }
  540. else if( $replace instanceof mysqlClass_Select )
  541. {
  542. $this->query["having"][] = array($condition, $replace);
  543. }
  544. else
  545. $this->query["having"][] = str_replace("?", $this->parent->escape($replace), $condition);
  546. }
  547. else
  548. $this->query["having"][] = $condition;
  549. // add relation
  550. if( strtoupper($nextRelation) == mysqlClass::HAVING_OR )
  551. $this->query["having"][] = mysqlClass::HAVING_OR;
  552. else
  553. $this->query["having"][] = mysqlClass::HAVING_AND;
  554. return $this;
  555. }
  556. /**
  557. * add to previous 'or' related 'having' to query
  558. * @param string $condition
  559. * @param string $replace
  560. * @return mysqlClass_Select
  561. */
  562. public function orHaving($condition, $replace = NULL)
  563. {
  564. if( !empty($this->query["having"]) )
  565. {
  566. $this->query["having"][(count($this->query["having"]) - 1)] = mysqlClass::HAVING_OR;
  567. }
  568. return $this->having($condition, $replace, mysqlClass::HAVING_AND);
  569. }
  570. /**
  571. * add 'order' to query
  572. * @param string $field
  573. * @param string $order
  574. * @return mysqlClass_Select
  575. */
  576. public function orderBy($field, $order = mysqlClass::ORDER_ASC)
  577. {
  578. if( strtoupper($order) == mysqlClass::ORDER_DESC )
  579. $this->query["order"][] = $field . " " . mysqlClass::ORDER_DESC;
  580. else
  581. $this->query["order"][] = $field . " " . mysqlClass::ORDER_ASC;
  582. return $this;
  583. }
  584. /**
  585. * alias of 'orderBy'
  586. * @param string $field
  587. * @param string $order
  588. * @return mysqlClass_Select
  589. */
  590. public function order($field, $order = mysqlClass::ORDER_ASC)
  591. {
  592. return $this->orderBy($field, $order);
  593. }
  594. /**
  595. * add 'limit' to query
  596. * @param integer $limit
  597. * @param integer $offset
  598. * @return mysqlClass_Select
  599. */
  600. public function limit($limit, $offset = NULL)
  601. {
  602. $this->query["limit"] = $limit;
  603. if( !is_null($offset) && is_numeric($offset) )
  604. {
  605. $this->query["limit"] = $offset . ", " . $limit;
  606. }
  607. return $this;
  608. }
  609. /**
  610. * add 'procedure' to query
  611. * @param string $procedure
  612. * @param string|array $arguments
  613. * @return mysqlClass_Select
  614. */
  615. public function procedure($procedure, $arguments = array())
  616. {
  617. // prevent php debug notification
  618. if( $arguments );
  619. if( func_num_args() == 1 )
  620. {
  621. $this->query["procedure"] = $procedure;
  622. }
  623. else if( func_num_args() == 2 )
  624. {
  625. if( is_array($arguments) )
  626. $this->query["procedure"] = $procedure . "(" . join(",", $arguments) . ")";
  627. else
  628. $this->query["procedure"] = $procedure . "(" . $arguments . ")";
  629. }
  630. else if( func_num_args() >= 2 )
  631. {
  632. $arguments = func_get_args();
  633. $procedure = array_shift($arguments);
  634. $list = array();
  635. foreach( $arguments as $argument )
  636. {
  637. if( is_array($argument) )
  638. $list = array_merge($list, $argument);
  639. else
  640. $list[] = $argument;
  641. }
  642. $this->query["procedure"] = $procedure . "(" . join(",", $list) . ")";
  643. }
  644. return $this;
  645. }
  646. /**
  647. * add 'with rollup' to query
  648. * @param boolean $update
  649. * @return mysqlClass_Select
  650. */
  651. public function forUpdate($update = true)
  652. {
  653. if( (bool)$update )
  654. {
  655. $this->query["lock"] = false;
  656. }
  657. $this->query["update"] = (bool)$update;
  658. return $this;
  659. }
  660. /**
  661. * add 'with rollup' to query
  662. * @param boolean $lock
  663. * @return mysqlClass_Select
  664. */
  665. public function lockInShareMode($lock = true)
  666. {
  667. if( (bool)$lock )
  668. {
  669. $this->query["update"] = false;
  670. }
  671. $this->query["lock"] = (bool)$lock;
  672. return $this;
  673. }
  674. /**
  675. * add a union select to query
  676. * @param mysqlClass_Select|string $select
  677. * @return mysqlClass_Select
  678. */
  679. public function union($select)
  680. {
  681. $this->query["union"] = $select;
  682. return $this;
  683. }
  684. /*
  685. ** build
  686. */
  687. /**
  688. * build mysql select query string
  689. * @param integer $formatOffset
  690. * @return string
  691. */
  692. public function build($formatOffset = 0)
  693. {
  694. $this->formatOffset += $formatOffset;
  695. $offset = str_pad("", $this->formatOffset, " ");
  696. // end if no table is set
  697. if( empty($this->query["from"]) ) return NULL;
  698. $query = $this->format ? $offset . "SELECT " : "SELECT ";
  699. // all
  700. if( $this->query["all"] ) $query .= $this->format ? "\n" . $offset . " ALL " : "ALL ";
  701. // distinct
  702. if( $this->query["distinct"] ) $query .= $this->format ? "\n" . $offset . " DISTINCT " : "DISTINCT ";
  703. // distinct row
  704. if( $this->query["row"] ) $query .= $this->format ? "\n" . $offset . " DISTINCTROW " : "DISTINCTROW ";
  705. // high priority
  706. if( $this->query["high"] ) $query .= $this->format ? "\n" . $offset . " HIGH_PRIORITY " : "HIGH_PRIORITY ";
  707. // straight
  708. if( $this->query["straight"] ) $query .= $this->format ? "\n" . $offset . " STRAIGHT_JOIN " : "STRAIGHT_JOIN ";
  709. $query .= $this->format ? $offset . "\n" : NULL;
  710. // columns
  711. if( !empty($this->query["columns"]) )
  712. {
  713. if( $this->format )
  714. {
  715. for( $i = 0; $i < count($this->query["columns"]); $i++ )
  716. {
  717. $value = $this->query["columns"][$i];
  718. if( is_array($value) )
  719. {
  720. if( $value[0] instanceof mysqlClass_Select )
  721. {
  722. $value[0] = $value[0]->build($this->formatOffset + 4);
  723. $value[0] = trim($value[0]);
  724. }
  725. $this->query["columns"][$i] = "(" . $value[0] . ") AS " . $value[1];
  726. }
  727. else if( $value instanceof mysqlClass_Select )
  728. {
  729. $this->query["columns"][$i] = "(" . $value->build($this->formatOffset + 4) . ")";
  730. }
  731. $query .= $offset . " " . $this->query["columns"][$i];
  732. $query .= $i < count($this->query["columns"]) - 1 ? "," : NULL;
  733. $query .= " \n";
  734. }
  735. }
  736. else
  737. {
  738. for( $i = 0; $i < count($this->query["columns"]); $i++ )
  739. {
  740. if( is_array($this->query["columns"][$i]) )
  741. {
  742. $select = $this->query["columns"][$i][0];
  743. if( $select instanceof mysqlClass_Select )
  744. $select = $select->build();
  745. $this->query["columns"][$i] = "(" . $select . ") AS " . $this->query["columns"][$i][1];
  746. }
  747. else if( $this->query["columns"][$i] instanceof mysqlClass_Select )
  748. {
  749. $select = $this->query["columns"][$i];
  750. if( $select instanceof mysqlClass_Select )
  751. $select = $select->build();
  752. $this->query["columns"][$i] = "(" . $select . ")";
  753. }
  754. }
  755. $query .= join(",", $this->query["columns"]) . " ";
  756. }
  757. }
  758. else
  759. {
  760. $query .= $this->format ? $offset . " *\n" : "* ";
  761. }
  762. // from
  763. if( !empty($this->query["from"]) )
  764. {
  765. if( $this->format )
  766. {
  767. $query .= $offset . "FROM \n";
  768. for( $i = 0; $i < count($this->query["from"]); $i++ )
  769. {
  770. $query .= $offset . " " . $this->query["from"][$i];
  771. $query .= $i < count($this->query["from"]) - 1 ? "," : NULL;
  772. $query .= " \n";
  773. }
  774. }
  775. else
  776. {
  777. $query .= "FROM " . join(",", $this->query["from"]) . " ";
  778. }
  779. }
  780. // join
  781. if( !empty($this->query["join"]) )
  782. {
  783. foreach( $this->query["join"] as $join )
  784. {
  785. $query .= $this->format ? $offset . $join["type"] . "\n" : $join["type"] . " ";
  786. if( $this->format )
  787. {
  788. for( $i = 0; $i < count($join["tables"]); $i++ )
  789. {
  790. $query .= $offset . " " . $join["tables"][$i];
  791. $query .= $i < count($join["tables"]) - 1 ? "," : NULL;
  792. $query .= " \n";
  793. }
  794. if( !empty($join["on"]) )
  795. {
  796. $query .= $offset . "ON\n";
  797. for( $i = 0; $i < count($join["on"]); $i = $i + 2 )
  798. {
  799. $query .= $offset . " " . $join["on"][$i];
  800. $query .= $i < count($join["on"]) - 2 ? " \n" . $offset . $join["on"][$i + 1] . " " : NULL;
  801. $query .= " \n";
  802. }
  803. }
  804. else if( !empty($join["using"]) )
  805. {
  806. $query .= $offset . "USING\n";
  807. $query .= $offset . "(\n";
  808. for( $i = 0; $i < count($join["using"]); $i++ )
  809. {
  810. $query .= $offset . " " . $join["using"][$i];
  811. $query .= $i < count($join["using"]) - 1 ? "," : NULL;
  812. $query .= " \n";
  813. }
  814. $query .= $offset . ")\n";
  815. }
  816. }
  817. else
  818. {
  819. // tables
  820. $query .= join(",", $join["tables"]) . " ";
  821. // on
  822. if( !empty($join["on"]) )
  823. {
  824. $on = array_slice($join["on"], 0, -1);
  825. $query .= "ON " . join(" ", $on) . " ";
  826. }
  827. // using
  828. else
  829. $query .= "USING (" . join(",", $join["using"]) . ") ";
  830. }
  831. }
  832. }
  833. // where
  834. if( !empty($this->query["where"]) )
  835. {
  836. if( $this->format )
  837. {
  838. $query .= $offset . "WHERE \n";
  839. for( $i = 0; $i < count($this->query["where"]); $i = $i + 2 )
  840. {
  841. if( is_array($this->query["where"][$i]) )
  842. {
  843. $select = $this->query["where"][$i][1];
  844. if( $select instanceof mysqlClass_Select )
  845. {
  846. $select = $select->build($this->formatOffset + 4);
  847. $select = trim($select);
  848. }
  849. $query .= $offset . " " . str_replace("?", "\n" . $offset . " (" . $select . ")", $this->query["where"][$i][0]);
  850. $query .= $i < count($this->query["where"]) - 2 ? " \n" . $offset . $this->query["where"][$i + 1] . " " : NULL;
  851. $query .= " \n";
  852. }
  853. else
  854. {
  855. $query .= $offset . " " . $this->query["where"][$i];
  856. $query .= $i < count($this->query["where"]) - 2 ? " \n" . $offset . $this->query["where"][$i + 1] . " " : NULL;
  857. $query .= " \n";
  858. }
  859. }
  860. }
  861. else
  862. {
  863. for( $i = 0; $i < count($this->query["where"]); $i = $i + 2 )
  864. {
  865. if( is_array($this->query["where"][$i]) )
  866. {
  867. $select = $this->query["where"][$i][1];
  868. if( $select instanceof mysqlClass_Select )
  869. $select = $select->build();
  870. $this->query["where"][$i] = str_replace("?", "(" . $select . ")", $this->query["where"][$i][0]);
  871. }
  872. }
  873. $where = array_slice($this->query["where"], 0, -1);
  874. $query .= "WHERE " . join(" ", $where) . " ";
  875. }
  876. }
  877. // group by
  878. if( !empty($this->query["group"]) )
  879. {
  880. if( $this->format )
  881. {
  882. $query .= $offset . "GROUP BY \n";
  883. for( $i = 0; $i < count($this->query["group"]); $i++ )
  884. {
  885. $query .= $offset . " " . $this->query["group"][$i];
  886. $query .= $i < count($this->query["group"]) - 1 ? "," : NULL;
  887. $query .= " \n";
  888. }
  889. }
  890. else
  891. {
  892. $query .= "GROUP BY " . join(",", $this->query["group"]) . " ";
  893. }
  894. // rollup
  895. if( $this->query["rollup"] )
  896. $query .= $this->format ? "WITH ROLLUP \n" : "WITH ROLLUP ";
  897. // having
  898. if( !empty($this->query["having"]) )
  899. {
  900. if( $this->format )
  901. {
  902. $query .= $offset . "HAVING \n";
  903. for( $i = 0; $i < count($this->query["having"]); $i = $i + 2 )
  904. {
  905. if( is_array($this->query["having"][$i]) )
  906. {
  907. $select = $this->query["having"][$i][1];
  908. if( $select instanceof mysqlClass_Select )
  909. {
  910. $select = $select->build($this->formatOffset + 4);
  911. $select = trim($select);
  912. }
  913. $query .= $offset . " " . str_replace("?", "\n" . $offset . " (" . $select . ")", $this->query["having"][$i][0]);
  914. $query .= $i < count($this->query["having"]) - 2 ? " \n" . $this->query["having"][$i + 1] . " " : NULL;
  915. $query .= " \n";
  916. }
  917. else
  918. {
  919. $query .= $offset . " " . $this->query["having"][$i];
  920. $query .= $i < count($this->query["having"]) - 2 ? " \n" . $this->query["having"][$i + 1] . " " : NULL;
  921. $query .= " \n";
  922. }
  923. }
  924. }
  925. else
  926. {
  927. for( $i = 0; $i < count($this->query["having"]); $i = $i + 2 )
  928. {
  929. if( is_array($this->query["having"][$i]) )
  930. {
  931. $select = $this->query["having"][$i][1];
  932. if( $select instanceof mysqlClass_Select )
  933. $select = $select->build();
  934. $this->query["having"][$i] = str_replace("?", "(" . $select . ")", $this->query["having"][$i][0]);
  935. $this->query["having"][$i] .= $i < count($this->query["having"]) - 2 ? " " . $this->query["having"][$i + 1] . " " : NULL;
  936. }
  937. }
  938. $having = array_slice($this->query["having"], 0, -1);
  939. $query .= "HAVING " . join(" ", $having) . " ";
  940. }
  941. }
  942. }
  943. // order
  944. if( !empty($this->query["order"]) )
  945. {
  946. if( $this->format )
  947. {
  948. $query .= $offset . "ORDER BY \n";
  949. for( $i = 0; $i < count($this->query["order"]); $i++ )
  950. {
  951. $query .= $offset . " " . $this->query["order"][$i];
  952. $query .= $i < count($this->query["order"]) - 1 ? "," : NULL;
  953. $query .= " \n";
  954. }
  955. }
  956. else
  957. {
  958. $query .= "ORDER BY " . join(",", $this->query["order"]) . " ";
  959. }
  960. }
  961. // limit
  962. if( !empty($this->query["limit"]) )
  963. {
  964. if( $this->format )
  965. $query .= $offset . "LIMIT \n" . $offset . " " . $this->query["limit"] . "\n";
  966. else
  967. $query .= "LIMIT " . $this->query["limit"] . " ";
  968. }
  969. // procedure
  970. if( !empty($this->query["procedure"]) )
  971. {
  972. if( $this->format )
  973. $query .= $offset . "PROCEDURE \n" . $offset . " " . $this->query["procedure"] . " ";
  974. else
  975. $query .= "PROCEDURE " . $this->query["procedure"] . " ";
  976. }
  977. // for update
  978. if( $this->query["update"] )
  979. $query .= $this->format ? $offset . "FOR UPDATE \n" : "FOR UPDATE ";
  980. // lock in share mode
  981. if( $this->query["lock"] )
  982. $query .= $this->format ? $offset . "LOCK IN SHARE MODE \n" : "LOCK IN SHARE MODE ";
  983. // union
  984. if( !is_null($this->query["union"]) )
  985. {
  986. $select = $this->query["union"];
  987. if( is_string($select) )
  988. if( $this->format )
  989. $query = "(" . $query. ") \nUNION \n(\n" . trim($select) . ")";
  990. else
  991. $query = "(" . $query. ") UNION (" . $select . ")";
  992. if( $select instanceof mysqlClass_Select )
  993. if( $this->format )
  994. $query = "(" . $query. ") \nUNION \n(\n" . trim($select->build()) . ")";
  995. else
  996. $query = "(" . $query. ") UNION (" . $select->build() . ")";
  997. }
  998. return $query;
  999. }
  1000. }