Reference - Select options - key

<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

  // key= names columns, each quoted as an identifier: a backtick in the value is part of
  // the name. It ended the quoted name, and the rest - desc, and a # that comments out what
  // follows - was SQL: the rows came back ordered by salary, descending.

  $padSelect ['staffKeyed'] = [ 'db' => 'staff' ];

  $k = "salary` desc #";

?>

{staffKeyed key=$k}{$name},{/staffKeyed}

<?php

  $padSelect ['table1'] = [ 'key' => 'key', 'fields' => ['key' => 'key1', 'text' => 'text1'] ];
  $padSelect ['table2'] = [ 'key' => 'key', 'fields' => ['key' => 'key2', 'text' => 'text2'] ];
  $padSelect ['table3'] = [ 'key' => 'key', 'fields' => ['key' => 'key3', 'text' => 'text3'] ];
  $padSelect ['table4'] = [ 'key' => 'key', 'fields' => ['key' => 'key4', 'text' => 'text4'] ];

  $padSelect ['join']     = [ 'base' => 'table1', 'join' => 'table2' ];

  $padSelect ['inner']    = [ 'base' => 'table1', 'join' => [ 'inner'         => 'table2', 'key'=> 'table1.key'] ];
  $padSelect ['natural1'] = [ 'base' => 'table1', 'join' => [ 'natural'       => 'table2'                      ] ];
  $padSelect ['left']     = [ 'base' => 'table1', 'join' => [ 'left'          => 'table2', 'key'=> 'table1.key'] ];
  $padSelect ['natural2'] = [ 'base' => 'table1', 'join' => [ 'natural left'  => 'table2'                      ] ];
  $padSelect ['right']    = [ 'base' => 'table1', 'join' => [ 'right'         => 'table2', 'key'=> 'table1.key'] ];
  $padSelect ['natural3'] = [ 'base' => 'table1', 'join' => [ 'natural right' => 'table2'                      ] ];
  $padSelect ['cross']    = [ 'base' => 'table1', 'join' => [ 'cross'         => 'table2'                      ] ];

  $padSelect ['multi']    = [ 'base' => 'table1', 'join' => [ ['inner' => 'table2', 'key'=> 'table1.key'],
                                                              ['inner' => 'table3', 'key'=> 'table1.key'],
                                                              ['inner' => 'table4', 'key'=> 'table1.key'] ] ];
?>

<h1>Join demo</h1>

<h2>join</h2>

<table border=1>
  {join}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
    </tr>
  {/join}
</table>

<h2>natural</h2>

<table border=1>
  {natural1}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/natural1}
</table>

<h2>inner</h2>

<table border=1>
  {inner}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/inner}
</table>

<h2>left</h2>

<table border=1>
  {left}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/left}
</table>

<h2>natural left</h2>

<table border=1>
  {natural2}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/natural2}
</table>

<h2>right</h2>

<table border=1>
  {right}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/right}
</table>

<h2>natural right</h2>

<table border=1>
  {natural3}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/natural3}
</table>

<h2>cross</h2>

<table border=1>
  {cross}
    <tr>
      <td>{$key1}</td>
      <td>{$text1}</td>
      <td>{$key2}</td>
      <td>{$text2}</td>
    </tr>
  {/cross}
</table>

<h2>multi</h2>

<table border=1>
  {multi}
    <tr>
      <td>{$text3}</td>
      <td>{$text4}</td>
    </tr>
  {/multi}
</table>

<?php

  // On SQLite a key bound on a select tag and the relation to the enclosing select compare
  // a number as a number: the value went in as quoted text, and a column declared without
  // a type has no affinity, so '2' never equalled the integer 2 - {boards $id=2} found no
  // board, and every board's topics came out empty. A key that only looks like a number,
  // '007', stays text.

  $GLOBALS ['padSqlConnect'] = padDbSqlite ( ':memory:' );

  db ( "create table boards (id, name)" );
  db ( "create table topics (id integer primary key, board_id, title text)" );
  db ( "create table codes (code, label)" );
  db ( "insert into boards values (1, 'one'), (2, 'two')" );
  db ( "insert into topics (board_id, title) values (1, 'a'), (1, 'b'), (2, 'c')" );
  db ( "insert into codes values ('007', 'bond'), (7, 'seven')" );

  $padSelect ['boards'] = [ 'db' => 'boards', 'key' => 'id' ];
  $padSelect ['topics'] = [ 'db' => 'topics', 'key' => 'id' ];
  $padSelect ['codes']  = [ 'db' => 'codes',  'key' => 'code' ];

  $padRelations ['topics'] ['boards'] = [ 'board_id' => 'id' ];

  $wanted = '2';
  $code   = '007';

?>

key:      [{boards $id=2}{$name}@else@none{/boards}]
request:  [{boards $id=$wanted}{$name}@else@none{/boards}]
relation: [{boards}{$name}={topics}{$title}{/topics} {/boards}]
text:     [{codes $code=$code}{$label}@else@none{/codes}]

<?php

  $padSelect ['union1']    = [ 'base' => 'table1',                                             ] ;
  $padSelect ['union12']   = [ 'base' => 'table1', 'union' => 'table2'                         ] ;
  $padSelect ['union1234'] = [ 'base' => 'table1', 'union' => [ 'table2', 'table3', 'table4' ] ] ;

?>

<h1>Union demo</h1>

<h2>1</h2>

<table border=1>
  {union1}
    <tr>
      <td>{$key}</td>
      <td>{$text}</td>
    </tr>
  {/union1}
</table>

<h2>1 2</h2>

<table border=1>
  {union12}
    <tr>
      <td>{$key}</td>
      <td>{$text}</td>
    </tr>
  {/union12}
</table>

<h2>1 2 3 4</h2>

<table border=1>
  {union1234}
    <tr>
      <td>{$key}</td>
      <td>{$text}</td>
    </tr>
  {/union1234}
</table>