Reference - Select options - fields

<p>where: {offices where="country = 'USA'", order='officeCode'}{$officeCode},{/offices}</p>
<p>distinct: {offices distinct, fields='country', order='country'}{$country},{/offices}</p>
<p>distinctrow: {offices distinctrow, fields='country', order='country'}{$country},{/offices}</p>
<p>group: {payments fields='customerNumber, count(*) as cnt', group='customerNumber', having='cnt > 9', order='customerNumber'}{$customerNumber}={$cnt},{/payments}</p>
<p>rollup: {offices key='', fields='count(*) as cnt', group='country', rollup}{last@offices}{$cnt}{/last@offices}{/offices}</p>
<p>db: {sales db='employees', where="jobTitle = 'President'"}{$lastName}{/sales}</p>
<p>key: {offices key='city', where="country = 'USA'"}{$city},{/offices}</p>
<p>union: {offices where="officeCode = 1", union='offices'}{$officeCode},{/offices}</p>
<p>join: {employees join='offices on employees.officeCode = offices.officeCode', where="offices.city = 'Boston'", order='lastName'}{$lastName},{/employees}</p>
<p>htmlAttrJson: {select:offices htmlAttrJson='record', fields='officeCode', where="officeCode = 1"}</p>

<?php

  // The relation is followed by the outer row's customerNumber, and the outer select's
  // fields= leaves that field out of its rows.

  $padSelect ['custRow']  = [ 'db' => 'customers', 'key' => 'customerNumber' ];
  $padSelect ['orderRow'] = [ 'db' => 'orders',    'key' => 'orderNumber' ];

  $padRelations ['orderRow'] ['custRow'] = [ 'customerNumber' => 'customerNumber' ];

?>

{custRow rows=1, fields='customerName'}{orderRow}x{/orderRow}{/custRow}

<?php

  // A NULL column of the enclosing row is NULL in the SQL it goes into - a $name of a
  // database tag, the relation a nested select follows - where it was bound as the empty
  // text '': a boss of NULL found the row whose key is '', on MySQL an integer key 0.

  $padSelect ['staffBoss']   = [ 'db' => 'staff' ];
  $padSelect ['staffByBoss'] = [ 'db' => 'staff' ];

  $padRelations ['staffByBoss'] ['staffBoss'] = [ 'name' => 'boss' ];

?>

bound:    [{array "name, NULL as boss from staff where name = 'jim'"}{field "$boss is null"}{/array}]
relation: [{staffBoss fields="name, NULL as boss", where="name = 'jim'"}{$name}:{staffByBoss}{$name}{/staffByBoss}{/staffBoss}]

<?php

  // A pager counts the rows the select answers also when its fields= decides what a row is:
  // distinct countries, or one row of totals. The count over the table alone was taken
  // whenever distinct=, group=, having= and union= were not given, so 27 countries had the
  // 13 pages of the 122 customers, and the one row of totals a pager of 28 pages.

  $padSelect ['countriesPaged'] = [ 'db' => 'customers', 'key' => 'customerNumber' ];
  $padSelect ['paymentTotals']  = [ 'db' => 'payments',  'key' => 'checkNumber' ];

?>

[{countriesPaged fields='distinct country', order='country', page=1, rows=10}{$country}; {/countriesPaged}]
({pager 'countriesPaged', window=0}{$kind}:{$page} {/pager})
[{paymentTotals fields='count(*) as n', page=1, rows=10}{$n}{/paymentTotals}]
({pager 'paymentTotals', window=0}{$kind}:{$page} {/pager})

<?php

  // The options a declared select table takes on the tag itself: each section of the .pad
  // half names one, over the four-row staff table of the demo database.

  $padSelect ['staffSel'] = [ 'db' => 'staff', 'key' => 'name' ];
  $padSelect ['elsewhere'] = [ 'key' => 'name' ];
  $padSelect ['staffJim'] = [ 'db' => 'staff', 'where' => "name = 'jim'" ];
  $padSelect ['staffRaw'] = [ 'db' => 'staff' ];

?>

where: [{staffSel where="salary >= 2000", order="salary"}{$name} {/staffSel}]
order: [{staffSel order="salary desc", fields="name"}{$name} {/staffSel}]
fields: [{staffSel fields="name, bonus", where="name = 'bob'"}{$name}/{$bonus}{/staffSel}]
group: [{staffSel fields="count(*) as heads", group="floor(salary / 2000)", order="heads"}{$heads} {/staffSel}]
having: [{staffSel fields="salary + bonus as total", group="total", having="total > 2500", order="total"}{$total} {/staffSel}]
rollup: [{staffRaw fields="sum(salary) as pay", group="name", rollup}{$pay} {/staffRaw}]
distinctrow: [{staffSel fields="floor(salary / 2000) as band", distinctrow, order="band"}{$band} {/staffSel}]
db: [{elsewhere db='staff', where="name = 'jim'"}{$name}/{$phone}{/elsewhere}]
union: [{staffSel where="name = 'bob'", union='staffJim', order="name"}{$name} {/staffSel}]

<?php

  // Paging a declared select table: the page is cut once, by the SQL limit, so page 2 holds
  // the third and fourth rows; a row count in the declaration is a row count, not the
  // query's command word; and a joined table declared without fields joins, adding none.
  // Over the four-row staff table and the join tables of the demo database.

  $padSelect ['staffSel'] = [ 'db' => 'staff', 'key' => 'name', 'order' => 'name' ];
  $padSelect ['staffTwo'] = [ 'db' => 'staff', 'key' => 'name', 'order' => 'name', 'rows' => 2 ];

  $padSelect ['bare']     = [ 'db' => 'table2', 'key' => 'key' ];
  $padSelect ['joined']   = [ 'db' => 'table1', 'fields' => 'table1.text', 'order' => 'table1.key',
                              'join' => [ 'inner' => 'bare', 'key' => 'table1.key' ] ];

?>

page 1: [{staffSel page=1, rows=2}{$name} {/staffSel}]
page 2: [{staffSel page=2, rows=2}{$name} {/staffSel}]
declared rows: [{staffTwo}{$name} {/staffTwo}]
join without fields: [{joined}{$text} {/joined}]

<?php

  // The Select subsystem over the same staff table: the statement it builds - backquoted
  // names, a quoted key, limit offset,count - is one SQLite reads as MySQL does.

  $padSelect ['staffSel'] = [ 'db' => 'staff', 'key' => 'name' ];

  // A value bound into where= is escaped as SQLite wants it: the quote stays in the string.

  $who = "x' or name > '";

  // A negative number bound after a minus keeps its distance, so "salary > 2000-$adj" is
  // 2000 - (-1000) = 3000 and not a -- comment that leaves SQLite with half a clause.

  $adj = -1000;

?>

where: [{staffSel where="salary >= 2000", order="salary"}{$name} {/staffSel}]
order: [{staffSel order="salary desc", fields="name"}{$name} {/staffSel}]
first: [{staffSel order="name", rows=2}{$name} {/staffSel}]
bound: [{staffSel where="name = $who"}{$name} @else@none{/staffSel}]
minus: [{staffSel where="salary > 2000-$adj", order="name"}{$name} @else@none{/staffSel}]