# Statements
A statement is a **template** plus the **content** that fills it. The template is rendered by
`PDOPrinter`, which decides per placeholder whether to emit SQL text or a `?`.
```php
$stmt = $handler->statement('SELECT * FROM {table:id} WHERE name = {name}', [
'table' => 'user',
'name' => 'marc',
]);
$stmt->render(); // SELECT * FROM `user` WHERE name = ?
(string)$stmt; // SELECT * FROM `user` WHERE name = "marc" -- debugging only
$stmt->fetchAll(); // prepares, binds, executes
```
## Placeholders
`{path}` or `{path:type}`, and `{?path}` for an optional one.
> A placeholder's name **must start with a letter**. `{_where}` is not recognised as a placeholder at all: it
> is left in the query as literal text and the driver fails with `unrecognized token: "{"`.
The type picks the exporter, which is what decides between text and a binding:
| type | renders as | bound? |
|---|---|---|
| *(none)* | depends on the value's PHP type — a scalar binds, a `Statement` becomes a sub-query | scalar: yes |
| `id` | `` `name` `` — a quoted identifier | no |
| `bind` | `?`, type inferred from the value | yes |
| `int`, `bool`, `blob` / `lob` | `?`, bound as that PDO type | yes |
| `fields` | `a, b AS \`alias\`` | no |
| `fields-def` | `name TYPE` (CREATE) | no |
| `where`, `order`, `limit`, `on` | the clause, or nothing when unset | values inside: yes |
| `columns`, `values`, `rows` | INSERT/UPDATE column and value lists | values: yes |
| `flags`, `constraints`, `duplicate`, `joins` | SQL fragments | no |
**Never quote a placeholder.** `name = "{name}"` renders the literal text `"?"` while still binding the value,
so the query ends up with one binding and no placeholder: `column index out of range`.
## Binding
Bindings are collected **by the render**, not derived from the content. Only the printer knows which values
actually became a `?` — an identifier does not, a raw fragment does not, and a `$in` array becomes one `?`
per item. `Statement::prepare()` renders once, then binds exactly the list the printer recorded, in order.
If you need to see what a render will bind:
```php
$binds = new MMO\pdo\statements\Binds();
$sql = $stmt->render(['binds' => $binds]);
$binds->toArray(); // [[value, type], …]
```
Rendering with `['bind' => false]` inlines the values instead. That is what `__toString()` does, and it is for
reading — never for executing.
## Sub-queries
A `Statement` used as a value renders as a parenthesised sub-query, and its bindings are collected in place:
```php
$sub = $handler->selectStatement('t2', 'column1');
$handler->statement('SELECT * FROM t1 WHERE column1 = {sub}', ['sub' => $sub]);
// SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2)
```
## The statement classes
All of them come from the handler, which stamps them with the driver.
### SELECT
```php
$handler->selectStatement('user', ['id', 'name'], ['name' => 'marc'])
->order('-id')
->limit(10, 20) //limit, offset
->distinct()
->fetchAll();
```
`fields()`, `from()`, `join()`/`on()` (see [Joins](Joins.md)), `setFilter()` (see [Filters](Filters.md)).
Fetching: `fetchAll()`, `fetch()`, `fetchValue()`, `fetchValues()`, `fetchObject()`, `fetchAllObjects()`,
`rowCount()`.
### INSERT
```php
$handler->insertStatement('user')
->values(['name' => 'marc', 'created:raw' => 'NOW()'])
->values(['name' => 'bob'])
->duplicate(['name' => true, 'email' => false]) //upsert
->execute();
```
A key may carry a bind type — `'age:int'` — and `:raw` renders the value as a SQL expression instead of
binding it.
> One INSERT has one column list, so a multi-row insert uses the **union** of every row's keys, and a row that
> omits one of them inserts an explicit `NULL` — which overrides that column's `DEFAULT`. Give every row the
> same keys, or use one statement per shape. A column *no* row mentions is left out entirely, so its default
> does apply.
`duplicate()` marks which columns form the conflicting key; the rest are updated on conflict. It renders as
`ON DUPLICATE KEY UPDATE` for MySQL and `ON CONFLICT(keys) DO UPDATE SET` for SQLite.
### UPDATE / DELETE / DROP
```php
$handler->updateStatement('user', ['visits:raw' => 'visits+1'], ['id' => 3])->execute();
$handler->deleteStatement('user', ['id' => 3])->limit(1)->execute();
$handler->dropStatement('user', ifExists: true)->execute();
```
UPDATE and DELETE are `FilterStatement`s, so they take `setFilter()`, `order()` and `limit()` too.
### CREATE
```php
$handler->createStatement('t1', ['id' => 'INT AUTO_INCREMENT', 'title' => 'VARCHAR(100) NOT NULL'])
->temporary()
->ifNotExists()
->primary('id')
->index('title')
->unique('title')
->foreign(['author', 'date'], 'author(id, regDate) ON UPDATE CASCADE ON DELETE RESTRICT')
->options(['ENGINE' => 'INNODB'])
->execute();
```
Usually you do not write this by hand — `Table::createStatement()` builds it from the column declarations
(see [Tables](Tables.md)).
## Formatting
`render(['indent' => ' '])` puts each clause on its own line; `['indent' => '']` keeps it to one. Indenting
is on by default (two spaces), inherited from `TextPrinter`'s global options.
## What is deliberately not here
- **A raw-SQL `WHERE`.** `setFilter()` takes a core filter, not a SQL fragment. For genuinely custom SQL, use
`$handler->statement(...)` with your own template and placeholders — you keep full control of what binds.
- **`$handler->select()` / `insert()` / … short aliases.** An earlier API had them with different signatures;
the `…Statement()` names say what they return.