1: <?php
2: /**
3: * The class is for helping with the assembly of a mysqli SELECT string.
4: * Rather than having to manually the string - especially of not only the values
5: * of certain parameters would be dynamic, but the tables, columns, joins, wheres
6: * and everything else is - you can get a new instance of this class by calling
7: * $amyql->select(), and you can freely add fragments to the SELECT query with
8: * the different methods of this class.
9: * When you call ->execute(), the final, but unbound SQL string is automatically
10: * prepared for binding with the passed (or pre-set) binds and execution.
11: *
12: * Since the bindings are applied after the SELECT sql string is assembled,
13: * you are encouraged to use :named placeholders for WHEREs and anything similar,
14: * binding the values with ->bindValue() each time, or collecting all the binds
15: * to use in the end and passing it to ->execute().
16: *
17: * Please check @link tests/AMysql/SelectTest.php for examples.
18: *
19: * @todo not choosing any columns should default to all columns rather than
20: * failing
21: *
22: * Anatomy of a select:
23: * SELECT <SELECT OPTIONS> <COLUMNS> FROM <FROMS> <JOINS> <WHERES> <GROUP BYS> <HAVINGS>
24: * <ORDER BYS> <LIMIT> <OFFSET>
25: *
26: * Visit https://github.com/amcsi/amysql
27: * @author Szerémi Attila
28: * @license MIT License; http://www.opensource.org/licenses/mit-license.php
29: */
30: class AMysql_Select extends AMysql_Statement
31: {
32:
33: protected $selectOptions = array ();
34: protected $columnLiteral;
35: protected $columns = array ();
36: protected $froms = array ();
37: protected $joins = array ();
38: protected $wheres = array ();
39: protected $groupBys = array ();
40: protected $havings = array ();
41: protected $orderBys = array ();
42: protected $limit = null;
43: protected $offset = null;
44:
45: /**
46: * Adds a select option.
47: *
48: * e.g.
49: * SQL_CALC_FOUND_ROWS
50: * DISTINCT
51: *
52: * @param string $selectOption The select option.
53: * @access public
54: * @return AMysql_Select (chainable)
55: */
56: public function option($selectOption)
57: {
58: $this->selectOptions[$selectOption] = $selectOption;
59: return $this;
60: }
61:
62: /**
63: * Formats a column name and an optional alias to form `columnName` AS alias.
64: * The alias is automatically not taken into account if it's numeric.
65: * No need to worry about escaping the select all star character '*'.
66: *
67: * @param string $columnName The column name.
68: * @param string $alias (Optional) the alias to select AS
69: * @access public
70: * @return string
71: */
72: public function formatSelectColumn($columnName, $alias = null)
73: {
74: if ('*' == $columnName[strlen($columnName) - 1]) {
75: return '*' === $columnName ? '*'
76: : $this->amysql->escapeColumn(substr($columnName, 0, -2)) .
77: '.*'
78: ;
79: }
80: $ret = $this->amysql->escapeColumn($columnName);
81: if ($alias && !is_numeric($alias)) {
82: $ret .= ' AS ' . $this->amysql->escapeColumn($alias);
83: }
84: return $ret;
85: }
86:
87: /**
88: * Adds one or more COLUMN to the list of columns to select.
89: *
90: * @param string|AMysql_Expr|array $columns The column name. Can be an array of column
91: * names in which case the key can mark the
92: * optional alias of the column.
93: * @param array $options (Options) an array of config options
94: * columnPrefix - prefix each column with this
95: * table prefix
96: * @access public
97: * @return AMysql_Select (chainable)
98: */
99: public function column($columns, $options = array ())
100: {
101: $columns = (array) $columns;
102: $columnPrefix = !empty($options['columnPrefix']) ? $options['columnPrefix'] . '.' : '';
103: foreach ($columns as $alias => $columnName) {
104: if ('*' == $columnName[strlen($columnName)- 1]) {
105: $key = '*';
106: } else {
107: $key = $alias && !is_numeric($alias) ? $alias : $columnName;
108: }
109: $this->columns[$key] = $this->formatSelectColumn("$columnPrefix$columnName", $alias);
110: }
111: return $this;
112: }
113:
114: /**
115: * Add this literal string between select options and columns.
116: *
117: * @param string $columnLiteral Literal string
118: * @access public
119: * @return AMysql_Select (chainable)
120: */
121: public function columnLiteral($columnLiteral)
122: {
123: if ($this->columnLiteral) {
124: $this->columnLiteral .= ", $columnLiteral";
125: } else {
126: $this->columnLiteral = $columnLiteral;
127: }
128: return $this;
129: }
130:
131: /**
132: * Formats a table name and an optional alias to form `tableName` AS alias.
133: * The alias is automatically not taken into account if it's numeric.
134: *
135: * @param string $tableName The table name.
136: * @param string $alias (Optional) the alias to select AS
137: * @access public
138: * @return string
139: */
140: public function formatFrom($tableName, $alias = null)
141: {
142: $ret = $this->amysql->escapeTable($tableName);
143: if ($alias && !is_numeric($alias)) {
144: $ret .= ' AS ' . $this->amysql->escapeTable($alias);
145: }
146: return $ret;
147: }
148:
149: /**
150: * 1) Adds one or more table name to the list of tables to select FROM.
151: *
152: * 2) Alternatively, if you only select from 1 table here, you can supply an array
153: * of columns to select, having them automatically prefixed to the needed prefix
154: * of the table selected from. Similar to Zend Framework 1.
155: *
156: * e.g.
157: * ->from(array('p' => 'products'),
158: * array('product_id', 'product_name'));// Build this query:
159: * // results in: SELECT p."product_id", p."product_name" FROM "products" AS p
160: *
161: * You can use literals as table names with AMysql_Expr.
162: *
163: * @param string|AMysql_Expr|array $tables The table name. Can be an array or table
164: * names in which case the key can mark the
165: * optional alias of the table.
166: * @param array $columns (Optional)
167: * The columns from this table to select.
168: * Do not use if you are selecting from more than 1 tables!
169: * @access public
170: * @return AMysql_Select (chainable)
171: */
172: public function from($tables, $columns = array ())
173: {
174: $tables = (array) $tables;
175: foreach ($tables as $alias => $tableName) {
176: $key = !is_numeric($alias) ? $alias : $tableName;
177: $this->froms[$key] = $this->formatFrom($tableName, $alias);
178: }
179: if ($columns) {
180: $key = !is_numeric($alias) ? $alias : $tableName;
181: $columnOptions = array ();
182: $columnOptions['columnPrefix'] = $key;
183: $this->column($columns, $columnOptions);
184: }
185: return $this;
186: }
187:
188: /**
189: * Adds a JOIN
190: *
191: * @param string $type Type of join. 'left' would be LEFT JOIN, 'inner'
192: * would be INNER JOIN. Leaving this falsy will result
193: * in a normal JOIN.
194: * @param string $table The table name to join. Can be a 1 element array of
195: * ['alias' => 'tableName']
196: * @param string $on The ON clause unbound.
197: * @param array $columns (Optional) The columns from this table to select. TODO!
198: * @param boolean $prepend (Optional) whether to prepend this JOIN to the other
199: * joins. Default: false (append).
200: * @access public
201: * @return AMysql_Select (chainable)
202: */
203: public function join($type, $table, $on, $columns = array (), $prepend = false)
204: {
205: $table = (array) $table;
206: $tableName = reset($table);
207: $alias = key($table);
208: $joinText = $type ? strtoupper($type) . ' JOIN' : 'JOIN';
209: $tableText = $this->formatFrom($tableName, $alias);
210: $text = "$joinText $tableText ON ($on)";
211: if ($prepend) {
212: array_unshift($this->joins, $text);
213: } else {
214: $this->joins[] = $text;
215: }
216: if ($columns) {
217: $key = !is_numeric($alias) ? $alias : $tableName;
218: $columnOptions = array ();
219: $columnOptions['columnPrefix'] = $key;
220: $this->column($columns, $columnOptions);
221: }
222: return $this;
223: }
224:
225: /**
226: * Adds a WHERE fragment. All fragments are joined by an AND
227: * at the end.
228: * WARNING: When binding a WHERE part that is an AMysql_Expr, you shouldn't
229: * pass it to this method. You should instead pass a new bind string
230: * (e.g. :wherePart) and then make a bind to the expression using
231: * "wherePart" as the key. If you ignore this and the expression contains
232: * any bind-related substrings, unexpected results will happen when
233: * placeholders are bound.
234: * Note that this object cannot automatically place
235: * it in for you as a bind due to not knowing whether named or unnamed
236: * binds are being used.
237: *
238: * @param string $where Unbound WHERE fragment
239: * @access public
240: * @return AMysql_Select (chainable)
241: */
242: public function where($where)
243: {
244: $this->wheres[] = $where;
245: return $this;
246: }
247:
248: /**
249: * Syntactic sugar for $this->where($where)->bindValue($key, $val);
250: *
251: * Usage e.g.
252: * $select->whereBind('id = :id', 'id', 3)
253: *
254: * @param string $where Unbound WHERE fragment
255: * @param mixed $key @see AMysql_Statement::bindValue()
256: * @param mixed $val @see AMysql_Statement::bindValue()
257: * @access public
258: * @return AMysql_Select (chainable)
259: */
260: public function whereBind($where, $key, $val)
261: {
262: return $this->where($where)->bindValue($key, $val);
263: }
264:
265: /**
266: * Adds an GROUP BY parameter
267: *
268: * @param name $col Column name
269: * @param bool $desc (Optional) Whether to sort DESC. Default: false
270: * @param bool $prepend (Optional) Whether to prepend this parameter.
271: * Default: FALSE
272: * @access public
273: * @return AMysql_Select (chainable)
274: */
275: public function groupBy($col, $desc = false, $prepend = false)
276: {
277: $what = $this->amysql->escapeColumn($col);
278: if ($desc) {
279: $what .= ' DESC';
280: }
281: if ($prepend) {
282: array_unshift($this->groupBys, $what);
283: } else {
284: $this->groupBys[] = $what;
285: }
286: return $this;
287: }
288:
289: /**
290: * Adds an GROUP BY parameter with no escaping.
291: *
292: * @param name $col What to group by. Can list multiple literals separated by commas.
293: * @param bool $prepend (Optional) Whether to prepend this parameter.
294: * Default: FALSE
295: * @access public
296: * @return AMysql_Select (chainable)
297: */
298: public function groupByLiteral($what, $prepend = false)
299: {
300: if ($prepend) {
301: array_unshift($this->groupBys, $what);
302: } else {
303: $this->groupBys[] = $what;
304: }
305: return $this;
306: }
307:
308: /**
309: * Adds a HAVING fragment. All fragments are joined by an AND
310: * at the end.
311: *
312: * @param string $having Unbound HAVING fragment
313: * @access public
314: * @return AMysql_Select (chainable)
315: */
316: public function having($having)
317: {
318: $this->havings[] = $having;
319: return $this;
320: }
321:
322: /**
323: * Adds an ORDER BY parameter
324: *
325: * @param name $col Column name
326: * @param bool $desc (Optional) Whether to sort DESC. Default: false
327: * @param bool $prepend (Optional) Whether to prepend this parameter.
328: * Default: FALSE
329: * @access public
330: * @return AMysql_Select (chainable)
331: */
332: public function orderBy($col, $desc = false, $prepend = false)
333: {
334: $what = $this->amysql->escapeColumn($col);
335: if ($desc) {
336: $what .= ' DESC';
337: }
338: if ($prepend) {
339: array_unshift($this->orderBys, $what);
340: } else {
341: $this->orderBys[] = $what;
342: }
343: return $this;
344: }
345:
346: /**
347: * Adds an ORDER BY parameter with no escaping.
348: *
349: * @param name $col What to order by. Can list multiple literals separated by commas.
350: * @param bool $prepend (Optional) Whether to prepend this parameter.
351: * Default: FALSE
352: * @access public
353: * @return AMysql_Select (chainable)
354: */
355: public function orderByLiteral($what, $prepend = false)
356: {
357: if ($prepend) {
358: array_unshift($this->orderBys, $what);
359: } else {
360: $this->orderBys[] = $what;
361: }
362: return $this;
363: }
364:
365: /**
366: * Adds a LIMIT
367: * You can only limit the number of rows returned here (e.g. LIMIT 10).
368: * To set the offset (e.g. LIMIT 20, 10), you must do:
369: * $select->limit(10)->offset(20);
370: *
371: * @param int $limit The LIMIT
372: * @access public
373: * @return AMysql_Select (chainable)
374: */
375: public function limit($limit)
376: {
377: $this->limit = (is_numeric($limit) && 0 < $limit) ? (int) $limit : null;
378: return $this;
379: }
380:
381: /**
382: * Adds an OFFSET
383: *
384: * @param int $offset The OFFSET
385: * @access public
386: * @return AMysql_Select (chainable)
387: */
388: public function offset($offset)
389: {
390: $this->offset = (is_numeric($offset) && 0 <= $offset) ?
391: (int) $offset :
392: null;
393: return $this;
394: }
395:
396: /**
397: * Gets the full, bound SQL string ready for use with MySQL.
398: *
399: * @param string $prepared (Optional) Only for parent compatibility.
400: * @access public
401: * @return string
402: */
403: public function getSql($prepared = null)
404: {
405: if (!$prepared) {
406: $this->prepared = $this->getUnboundSql();
407: }
408: $ret = parent::getSql($prepared);
409: return $ret;
410: }
411:
412: /**
413: * Gets the SQL string without the binds applied yet.
414: *
415: * @access public
416: * @return string
417: */
418: public function getUnboundSql()
419: {
420: $parts = array ('SELECT');
421: if ($this->selectOptions) {
422: $parts[] = join(', ', $this->selectOptions);
423: }
424:
425: $columns = $this->columns;
426: if ($this->columnLiteral) {
427: $columns[] = $this->columnLiteral;
428: }
429: $parts[] = join(', ', $columns);
430:
431: $parts = array (join(' ', $parts)); // okay so everything so far should be 1 part.
432:
433: if ($this->froms) {
434: $parts[] = 'FROM ' . join(', ', $this->froms);
435: }
436:
437: foreach ($this->joins as $join) {
438: $parts[] = $join;
439: }
440:
441: if ($this->wheres) {
442: $parts[] = 'WHERE ' . join(' AND ', $this->wheres);
443: }
444:
445: if ($this->groupBys) {
446: $ob = array ();
447: foreach ($this->groupBys as $groupBy) {
448: $ob[] = $groupBy;
449: }
450: $ob = join(', ', $ob);
451: $parts[] = 'GROUP BY ' . $ob;
452: }
453:
454: if ($this->havings) {
455: $parts[] = 'HAVING ' . join(' AND ', $this->havings);
456: }
457:
458: if ($this->orderBys) {
459: $ob = array ();
460: foreach ($this->orderBys as $orderBy) {
461: $ob[] = $orderBy;
462: }
463: $ob = join(', ', $ob);
464: $parts[] = 'ORDER BY ' . $ob;
465: }
466:
467: if ($this->limit) {
468: $parts[] = "LIMIT $this->limit";
469: }
470:
471: if ($this->offset) {
472: $parts[] = "OFFSET $this->offset";
473: }
474: $sql = join("\n", $parts);
475: return $sql;
476: }
477: }
478: