Reference - Select options - where

<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

  // negative over a select table's where: the SQL applied it, so nothing was left to turn
  // round and the list came out empty, in silence.

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

?>

{staffSel where="salary >= 2000", negative}{$name}{/staffSel}

<?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

  // On SQLite numeric text goes into a bare placeholder, or a select's $name, as a number
  // only when SQLite gives the same text back: '007' became the integer 7, so the code
  // '007' found the row of code '7', the zip '01234' was stored as '1234' and '1.10' as
  // '1.1'. A numeral like '3' stays a number, so a count still compares with it.

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

  db ( "create table codes (code text primary key, zip varchar(10), label text)" );
  db ( "insert into codes values ('007', '01234', 'bond'), ('7', '1234', 'seven')" );

  $byCode = db ( "field label from codes where code = {0}",                   [ '007' ] );
  $byZip  = db ( "field zip from codes where zip = {0}",                      [ '01234' ] );
  $inList = db ( "array code from codes where code in ({0}) order by code",   [ [ '007', '7' ] ] );
  $inList = implode ( ' ', array_column ( $inList, 'code' ) );

  db ( "insert into codes values ({0}, {1}, 'kept')", [ '0042', '1.10' ] );

  $stored = db ( "record code, zip from codes where label = 'kept'" );
  $count  = db ( "field 'three' where (select count(*) from codes) = {0}",    [ '3' ] );

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

  $c = '007';

?>

code:   {$byCode}
zip:    {$byZip}
list:   {$inList}
stored: {$stored.code} {$stored.zip}
count:  {$count}
select: {codes where="code = $c"}{$label}{/codes}

<?php

  // An array bound into where= as $name is a list, as an array in a bare db() placeholder
  // is: name in ($names) is name in ('jim','bob'), where the array became the one literal
  // 'jim,bob' and matched nothing - and on SQLite an "Array to string conversion".

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

  $names = [ 'jim', 'bob' ];

?>

[{staffList where="name in ($names)", order="name"}{$name} {/staffList}]

<?php

  // A pager after a select that joins two tables sharing a column name - orders and
  // customers, both with customerNumber - counts its rows: the count ran the select list
  // as a derived table, which MySQL refuses with two columns of one name ("1060: Duplicate
  // column name 'customerNumber'"), and the page ended at the pager.

  $padSelect ['ordersPaged'] = [ 'db' => 'orders', 'key' => 'orderNumber' ];

?>

[{ordersPaged join='customers on customers.customerNumber = orders.customerNumber', where="customers.country = 'Norway'", order='orderNumber', page=1, rows=3}{$orderNumber} {$customerName}; {/ordersPaged}]
{pager 'ordersPaged', window=0}{$kind}:{$page} {/pager}

<?php

  // A value written into where= as $name is bound by the select as a quoted literal: a
  // quote in it stays inside the string and matches nothing, a comma is still a comma. In
  // order= a $name is spliced as column names. Over the customers table of the demo database.

  $padSelect ['bound'] = [ 'db' => 'customers' ];

  $who   = "x' or customerNumber > '0";
  $comma = 'Australian Collectors, Co.';
  $sort  = 'customerNumber desc';

?>

a quote in a bound value: [{bound where="customerName = $who"}{$customerNumber},{/bound}]
a comma in a bound value: [{bound where="customerName = $comma"}{$customerNumber}{/bound}]
a bound order: [{bound where="customerNumber < 120", order="$sort"}{$customerNumber},{/bound}]

<?php

  // A $name bound into where= that holds numeric text - a request value - is text on
  // MySQL: name = $zero with '0' compares the names with '0', where it was the number 0 and
  // every name that does not start with a digit matched. A number stays a number.

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

  $zero  = '0';
  $floor = '2500';

?>

zero: [{staffNum where="name = $zero"}{$name} @else@none{/staffNum}]
floor: [{staffNum where="salary > $floor", order="name"}{$name} {/staffNum}]

<?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

  // A comment with an apostrophe in where= no longer stops the $name after it from binding:
  // the apostrophe used to open a quoted literal, so $who was spliced raw and the query
  // failed on "Unknown column '$who'".

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

  $who = 'jim';

?>

[{staffWc where="/* the staff's pick */ name = $who"}{$name} @else@none{/staffWc}]

<?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}]