Overview

Namespaces

  • None
  • PHP

Classes

  • AbstractTest
  • AMysql
  • AMysql_Abstract
  • AMysql_Expr
  • AMysql_Iterator
  • AMysql_Profiler
  • AMysql_Select
  • AMysql_Statement
  • AMysql_TestCase
  • ExceptionTest
  • ExprTest
  • IteratorTest
  • SelectTest
  • StatementTest

Exceptions

  • AMysql_Exception
  • Overview
  • Namespace
  • Class
  • Tree
  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: 
API documentation generated by ApiGen 2.8.0