### Run Dibi Tests with Docker and Tester

Source: https://github.com/dg/dibi/blob/master/readme.md

To test against databases like MySQL or PostgreSQL, start Docker containers, copy the configuration, and then run the Tester tool. This requires Docker to be installed and running.

```bash
docker compose up -d
cp tests/databases.docker.ini tests/databases.ini
vendor/bin/tester tests -s -c tests/php-win.ini
```

--------------------------------

### Install Dependencies

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Install project dependencies using Composer.

```bash
composer install
```

--------------------------------

### Install Dibi via Composer

Source: https://github.com/dg/dibi/blob/master/readme.md

Use Composer to install the Dibi library. This command requires Composer to be installed and configured on your system.

```bash
composer require dibi/dibi
```

--------------------------------

### PHP Test File with Nette Tester

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Example of a test file using Nette Tester for Dibi. It demonstrates connection setup, loading SQL data, asserting query results, and testing for exceptions.

```php
<?php

declare(strict_types=1);

use Tester\Assert;
use Dibi\Connection;

require __DIR__ . '/bootstrap.php';

$conn = new Connection($config);
$conn->loadFile(__DIR__ . "/data/$config[system].sql");

// Test description as first parameter
Assert::same(3, $conn->dataSource('SELECT * FROM products')->count());

Assert::exception(
	fn() => $conn->query('INVALID SQL'),
	Dibi\Exception::class,
	'SQL error message',
);
```

--------------------------------

### Executing Queries and Fetching Results

Source: https://github.com/dg/dibi/blob/master/readme.md

Demonstrates how to execute SQL queries using the `query()` method and process the `Dibi\Result` object. It covers fetching all rows, associative arrays, key-value pairs, and getting the row count.

```APIDOC
## POST /api/query

### Description
Executes a SQL query against the database and returns the result set.

### Method
POST

### Endpoint
/api/query

### Request Body
- **sql** (string) - Required - The SQL query to execute.
- **params** (array) - Optional - An array of parameters to bind to the query.

### Request Example
```json
{
  "sql": "SELECT * FROM users WHERE name = ?",
  "params": ["John Doe"]
}
```

### Response
#### Success Response (200)
- **result** (object) - The query result set, which can be processed further.

#### Response Example
```json
{
  "result": {
    "rows": [
      {"id": 1, "name": "John Doe"},
      {"id": 2, "name": "Jane Smith"}
    ],
    "rowCount": 2
  }
}
```
```

--------------------------------

### PHP Method Signature Example

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Example of a PHP method signature adhering to strict types, type declarations for parameters and return types, and specific brace placement.

```php
public function example(
	string $param,
	array $options,
): void
{
	// method body
}
```

--------------------------------

### Dibi Integration with Nette Framework Services

Source: https://context7.com/dg/dibi/llms.txt

Use Dibi\Connection as an injected service within your Nette Framework application classes. This example demonstrates common repository methods like findById, findActive, create, update, and delete.

```php
<?php
// Using Dibi\Connection as an injected service
class UserRepository
{
    private Dibi\Connection $database;

    public function __construct(Dibi\Connection $database)
    {
        $this->database = $database;
    }

    public function findById(int $id): ?Dibi\Row
    {
        return $this->database->fetch('SELECT * FROM users WHERE id = ?', $id);
    }

    public function findActive(): array
    {
        return $this->database->fetchAll('SELECT * FROM users WHERE active = ?', true);
    }

    public function create(array $data): int
    {
        $this->database->query('INSERT INTO users', $data);
        return $this->database->getInsertId();
    }

    public function update(int $id, array $data): int
    {
        $this->database->query('UPDATE users SET', $data, 'WHERE id = ?', $id);
        return $this->database->getAffectedRows();
    }

    public function delete(int $id): int
    {
        $this->database->query('DELETE FROM users WHERE id = ?', $id);
        return $this->database->getAffectedRows();
    }
}

```

--------------------------------

### SQL Injection Warning Example

Source: https://github.com/dg/dibi/blob/master/readme.md

Demonstrates a dangerous way to concatenate variables directly into SQL queries, which is highly susceptible to SQL injection. This method should never be used.

```php
$result = $database->query('SELECT * FROM users WHERE id = ' . $id); // BAD!!!
```

--------------------------------

### Testing with Docker Compose

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Set up database environments using Docker Compose, copy configuration, run tests, and then tear down the containers.

```bash
docker compose up -d
cp tests/databases.docker.ini tests/databases.ini
vendor/bin/tester tests -s -c tests/php-win.ini
docker compose down
```

--------------------------------

### Connect to Database using Dibi\Connection

Source: https://github.com/dg/dibi/blob/master/readme.md

Instantiate a Dibi\Connection object with an array of connection parameters to establish a database connection. Ensure all required parameters like driver, host, username, password, and database are correctly provided.

```php
$database = new Dibi\Connection([
	'driver'   => 'mysqli',
	'host'     => 'localhost',
	'username' => 'root',
	'password' => '***',
	'database' => 'table',
]);

$result = $database->query('SELECT * FROM users');
```

--------------------------------

### Configure Query Logging

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Set up query logging by configuring the 'profiler' section in the Dibi connection parameters. Specify a 'file' to log queries.

```php
$database = new Dibi\Connection([
	'driver' => 'mysqli',
	// ... connection params
	'profiler' => [
		'file' => 'queries.log',
	],
]);
```

--------------------------------

### Get Row Count from Result

Source: https://context7.com/dg/dibi/llms.txt

Retrieves the number of rows affected by the last query or the number of rows present in a Dibi\Result object.

```php
// Get row count
$count = $result->getRowCount();
```

--------------------------------

### Connect to Database using static dibi::connect

Source: https://github.com/dg/dibi/blob/master/readme.md

Use the static `dibi::connect` method to establish a database connection and store it in a globally accessible storage. This allows subsequent calls to `dibi::query` to use this connection. Connection errors will throw a Dibi\Exception.

```php
dibi::connect([
	'driver'   => 'mysqli',
	'host'     => 'localhost',
	'username' => 'root',
	'password' => '***',
	'database' => 'test',
	'charset'  => 'utf8',
]);

$result = dibi::query('SELECT * FROM users');
```

--------------------------------

### Preview SQL with test() and Dump Results with dump()

Source: https://context7.com/dg/dibi/llms.txt

Use the test() method to preview generated SQL without execution. Use dump() to display result sets as HTML tables. This is useful for debugging and understanding query behavior.

```php
<?php
$database = new Dibi\Connection([
    'driver' => 'sqlite',
    'database' => 'data/app.s3db',
]);

// Preview SQL without executing
$database->test('SELECT * FROM users WHERE name = ? AND active = ?', "O'Brien", true);
// Output: SELECT * FROM users WHERE name = 'O\'Brien' AND active = 1

// Test with complex query
$database->test('INSERT INTO products',
    [
    'title' => 'New Product',
    'price' => 199.99,
    'created' => new DateTime,
    ]
);
// Output: INSERT INTO products (`title`, `price`, `created`) VALUES ('New Product', 199.99, '2024-01-15 14:30:00')

// Dump result set as HTML table
$result = $database->query('SELECT * FROM products LIMIT 5');
$result->dump();

// Access query statistics (via static facade)
dibi::connect(['driver' => 'sqlite', 'database' => 'data/app.s3db']);
dibi::query('SELECT * FROM products');

echo "Last SQL: " . dibi::$sql . "\n";
echo "Query time: " . dibi::$elapsedTime . " seconds\n";
echo "Total queries: " . dibi::$numOfQueries . "\n";
echo "Total time: " . dibi::$totalTime . " seconds\n";

```

--------------------------------

### Connect to SQLite Database

Source: https://context7.com/dg/dibi/llms.txt

Establishes a connection to an SQLite database by specifying the driver and the path to the database file.

```php
// Connect to SQLite
$sqliteDatabase = new Dibi\Connection([
    'driver'   => 'sqlite',
    'database' => 'data/app.s3db',
]);
```

--------------------------------

### Run All Tests

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Execute all tests in the project using Composer and the Tester tool. Use the -s flag to show test output.

```bash
composer run tester
```

--------------------------------

### Database Connection with Logging

Source: https://context7.com/dg/dibi/llms.txt

Configures a database connection with a profiler to log all executed queries to a specified file. This is helpful for debugging and performance analysis.

```php
// With logging enabled
$loggedDatabase = new Dibi\Connection([
    'driver'   => 'mysqli',
    'host'     => 'localhost',
    'username' => 'root',
    'password' => 'secret',
    'database' => 'myapp',
    'profiler' => [
        'file' => 'queries.log',
    ],
]);
```

--------------------------------

### Inject Dibi Connection Service in Nette

Source: https://github.com/dg/dibi/blob/master/readme.md

Demonstrates how to inject the Dibi connection object as a service into a Nette application class using dependency injection.

```php
class Model
{
	private $database;

	public function __construct(Dibi\Connection $database)
	{
		$this->database = $database;
	}
}
```

--------------------------------

### Connect to MySQL Database

Source: https://context7.com/dg/dibi/llms.txt

Establishes a connection to a MySQL database using the mysqli driver with specified host, username, password, database name, charset, and connection timeout options.

```php
<?php
// Connect to MySQL
$database = new Dibi\Connection([
    'driver'   => 'mysqli',
    'host'     => 'localhost',
    'username' => 'root',
    'password' => 'secret',
    'database' => 'myapp',
    'charset'  => 'utf8',
    'options'  => [
        MYSQLI_OPT_CONNECT_TIMEOUT => 30,
    ],
]);
```

--------------------------------

### Connect via PDO

Source: https://context7.com/dg/dibi/llms.txt

Connects to a database using the PDO driver, which supports any PDO-compatible database system. Requires a DSN, username, and password.

```php
// Connect via PDO (works with any PDO-supported database)
$pdoDatabase = new Dibi\Connection([
    'driver' => 'pdo',
    'dsn'    => 'mysql:host=localhost;dbname=myapp',
    'username' => 'root',
    'password' => 'secret',
]);
```

--------------------------------

### Static Analysis with PHPStan

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Perform static analysis on the codebase using PHPStan at Level 5.

```bash
composer run phpstan
```

--------------------------------

### Build SQL Queries with Fluent Interface

Source: https://context7.com/dg/dibi/llms.txt

Use the fluent interface to programmatically build and execute SQL queries. Methods are chainable and generate SQL upon execution. Supports SELECT, INSERT, UPDATE, DELETE, and custom commands.

```php
<?php
$database = new Dibi\Connection([
    'driver' => 'sqlite',
    'database' => 'data/app.s3db',
]);

// SELECT with fluent interface
$result = $database->select('product_id')->as('id')
    ->select('title', 'price')
    ->from('products')
    ->where('price > ?', 100)
    ->where('active = ?', true)
    ->orderBy('price', 'DESC')
    ->limit(10)
    ->fetchAll();
// Generated: SELECT `product_id` AS `id`, `title`, `price` FROM `products`
//            WHERE price > 100 AND active = 1 ORDER BY `price` DESC LIMIT 10

// SELECT with JOINs
$result = $database->select('*')
    ->from('products')
    ->innerJoin('orders')->using('(product_id)')
    ->innerJoin('customers USING (customer_id)')
    ->where('customers.active = ?', true)
    ->orderBy('title')
    ->fetchAll();

// INSERT with fluent interface
$database->insert('products', [
    'title' => 'Super Product',
    'price' => 299,
    'active' => true,
])
->setFlag('IGNORE')  // INSERT IGNORE
->execute();

// UPDATE with fluent interface
$database->update('products', [
    'price' => 350,
    'updated' => new DateTime,
])
->where('product_id = ?', 10)
->execute();

// DELETE with fluent interface
$database->delete('products')
    ->where('product_id = ?', 10)
    ->execute();

// Custom command
$database->command()
    ->truncate('temp_data')
    ->execute();

// Get generated SQL without executing (for debugging)
$database->select('*')
    ->from('users')
    ->where('active = ?', true)
    ->test();  // Prints the generated SQL
```

--------------------------------

### Dibi test() method

Source: https://github.com/dg/dibi/blob/master/readme.md

Use the test() method to simulate query execution and see the generated SQL without actually running it. This is useful for debugging and understanding query compilation.

```php
$database->test('SELECT * FROM users WHERE %and', [
	'name' => $name,
	'year' => $year,
]);
```

--------------------------------

### Run Specific Test File

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Execute a single test file using the Tester tool. The -s flag displays test output.

```bash
vendor/bin/tester tests/dibi/DataSource.phpt -s
```

--------------------------------

### Connect to PostgreSQL Database

Source: https://context7.com/dg/dibi/llms.txt

Connects to a PostgreSQL database using the 'postgre' driver with a connection string and enables persistent connections.

```php
// Connect to PostgreSQL
$pgDatabase = new Dibi\Connection([
    'driver'     => 'postgre',
    'string'     => 'host=localhost port=5432 dbname=myapp',
    'persistent' => true,
]);
```

--------------------------------

### Dibi Shortcuts

Source: https://github.com/dg/dibi/blob/master/readme.md

Introduces Dibi's shortcut methods for common query operations like fetching pairs, all rows, a single row, or a single field.

```APIDOC
## GET /api/shortcut/{method}

### Description
Retrieves data using Dibi's shortcut methods for common query patterns.

### Method
GET

### Endpoint
/api/shortcut/{method}

### Path Parameters
- **method** (string) - Required - The shortcut method to use (e.g., `fetchPairs`, `fetchAll`, `fetch`, `fetchSingle`).

### Query Parameters
- **sql** (string) - Required - The SQL query to execute.
- **params** (array) - Optional - Parameters for the SQL query.

### Request Example
```json
{
  "url": "/api/shortcut/fetchPairs?sql=SELECT id, name FROM users"
}
```

### Response
#### Success Response (200)
- **result** (any) - The data returned by the specified shortcut method.

#### Response Example
```json
{
  "result": {
    "1": "Alice",
    "2": "Bob"
  }
}
```
```

--------------------------------

### Parameterized SQL Queries with Question Marks

Source: https://github.com/dg/dibi/blob/master/readme.md

Safely execute SQL queries with parameters using the '?' placeholder. This prevents SQL injection vulnerabilities. Parameters are passed as additional arguments to the query method.

```php
$result = $database->query('SELECT * FROM users WHERE name = ? AND active = ?', $name, $active);
```

```php
$result = $database->query('SELECT * FROM users WHERE name = ?', $name, 'AND active = ?', $active);
```

```php
$ids = [10, 20, 30];
$result = $database->query('SELECT * FROM users WHERE id IN (?)', $ids);
```

--------------------------------

### Run Tests in Directory

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Execute all tests within a specified directory using the Tester tool. The -s flag shows test output.

```bash
vendor/bin/tester tests/dibi/ -s
```

--------------------------------

### Exception Handling

Source: https://github.com/dg/dibi/blob/master/readme.md

Outlines the exceptions that can be thrown by Dibi's `query()` method, including `ConstraintViolationException`, `ForeignKeyConstraintViolationException`, `NotNullConstraintViolationException`, and `UniqueConstraintViolationException`.

```APIDOC
## POST /api/query/with-error-handling

### Description
Executes a SQL query and handles potential Dibi exceptions.

### Method
POST

### Endpoint
/api/query/with-error-handling

### Request Body
- **sql** (string) - Required - The SQL query to execute.
- **params** (array) - Optional - Parameters for the SQL query.

### Request Example
```json
{
  "sql": "INSERT INTO users (id, name) VALUES (1, 'Test') ON CONFLICT (id) DO NOTHING",
  "params": []
}
```

### Response
#### Success Response (200)
- **message** (string) - A success message.

#### Error Response (4xx/5xx)
- **error** (string) - The type of Dibi exception that occurred (e.g., `UniqueConstraintViolationException`).
- **message** (string) - A detailed error message.

#### Response Example (Success)
```json
{
  "message": "Query executed successfully."
}
```

#### Response Example (Error)
```json
{
  "error": "UniqueConstraintViolationException",
  "message": "Duplicate entry '1' for key 'PRIMARY'"
}
```
```

--------------------------------

### Table and Column Substitutions

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Use prefixes for table and column names to easily manage different database schemas or naming conventions. The substitute() method sets the prefix.

```php
$database->substitute('blog', 'wp_');
$database->query("UPDATE [:blog:items] SET [text]='Hello'");
// UPDATE `wp_items` SET `text`='Hello'
```

--------------------------------

### Parameterized Queries

Source: https://github.com/dg/dibi/blob/master/readme.md

Explains how to safely pass parameters to SQL queries using placeholders like '?' or specific modifiers to prevent SQL injection vulnerabilities.

```APIDOC
## POST /api/query/parameterized

### Description
Executes a parameterized SQL query, safely binding values to prevent SQL injection.

### Method
POST

### Endpoint
/api/query/parameterized

### Request Body
- **sql** (string) - Required - The SQL query with placeholders (e.g., ?, %s, %i).
- **params** (array) - Required - An array of values to bind to the placeholders in the SQL query.

### Request Example
```json
{
  "sql": "SELECT * FROM users WHERE name = %s AND active = %b",
  "params": ["Alice", true]
}
```

### Response
#### Success Response (200)
- **result** (object) - The query result set.

#### Response Example
```json
{
  "result": {
    "rows": [
      {"id": 3, "name": "Alice", "active": true}
    ],
    "rowCount": 1
  }
}
```
```

--------------------------------

### Iterate Over Query Results

Source: https://context7.com/dg/dibi/llms.txt

Demonstrates how to iterate over the results of a Dibi query. Each row is accessible as an object with properties corresponding to column names.

```php
// Iterate over results
foreach ($result as $row) {
    echo $row->id . ': ' . $row->name . "\n";
}
```

--------------------------------

### Register Dibi Extension in Nette Configuration

Source: https://github.com/dg/dibi/blob/master/readme.md

Configures Dibi within a Nette application by registering the Dibi extension and defining database connection parameters in the NEON configuration file.

```neon
extensions:
	dibi: Dibi\Bridges\Nette\DibiExtension3

dibi:
	host: localhost
	username: root
	password: ***
	database: foo
	lazy: true
```

--------------------------------

### Dibi Query Shortcuts

Source: https://github.com/dg/dibi/blob/master/readme.md

Provides convenient shortcuts for common query operations like fetching pairs, all rows, a single row, or a single field, reducing boilerplate code.

```php
// returns associative pairs id => name, shortcut for query(...)->fetchPairs()
$pairs = $database->fetchPairs('SELECT id, name FROM users');
```

```php
// returns array of all rows, shortcut for query(...)->fetchAll()
$rows = $database->fetchAll('SELECT * FROM users');
```

```php
// returns row, shortcut for query(...)->fetch()
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);
```

```php
// returns field, shortcut for query(...)->fetchSingle()
$name = $database->fetchSingle('SELECT name FROM users WHERE id = ?', $id);
```

--------------------------------

### Nette Framework DI Extension Configuration

Source: https://context7.com/dg/dibi/llms.txt

Configure Dibi integration with Nette Framework using the DI extension in your Neon configuration file. This enables automatic dependency injection and Tracy debugger panel integration.

```neon
# config/common.neon
extensions:
    dibi: Dibi\Bridges\Nette\DibiExtension3

dibi:
    host: localhost
    username: root
    password: secret
    database: myapp
    driver: mysqli
    lazy: true

```

--------------------------------

### Fetch Associative Array with Array Index for Duplicate Keys

Source: https://github.com/dg/dibi/blob/master/readme.md

When multiple entries share the same key (e.g., customer name), this descriptor uses '[]' to create an array index, allowing differentiation between entries with identical keys.

```php
$all = $result->fetchAssoc('name[]order_id');
```

```php
// we get all the Arnolds in the results
foreach ($all['Arnold Rimmer'] as $arnoldOrders) {
   foreach ($arnoldOrders as $orderId => $order) {
	   ...
   }
}
```

--------------------------------

### Execute Simple SELECT Query

Source: https://context7.com/dg/dibi/llms.txt

Executes a simple SQL SELECT query and returns a Dibi\Result object. No parameters are used, so no automatic escaping is applied.

```php
// Simple SELECT query
$result = $database->query('SELECT * FROM users');
```

--------------------------------

### Execute Basic SQL Query and Fetch Results

Source: https://github.com/dg/dibi/blob/master/readme.md

Executes a SELECT query and iterates over the results. Supports fetching all rows, as an associative array keyed by a specific column, or as key-value pairs. Also retrieves the row count.

```php
$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

// array of all rows
$all = $result->fetchAll();

// array of all rows, key is 'id'
$all = $result->fetchAssoc('id');

// associative pairs id => name
$pairs = $result->fetchPairs('id', 'name');

// the number of rows of the result, if known, or number of affected rows
$count = $result->getRowCount();
```

--------------------------------

### Dibi Query WHERE with %and Modifier

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Shows how to use the %and modifier to construct a WHERE clause with multiple conditions combined by AND using Dibi's query method.

```php
$database->query('SELECT * FROM users WHERE %and', ['name' => 'Jim', 'active' => true]);
```

--------------------------------

### LIKE Query with Special Modifiers

Source: https://github.com/dg/dibi/blob/master/readme.md

Illustrates the use of special modifiers (`%like~`, `%~like`, `%~like~`, `%like`) for constructing `LIKE` clauses in SQL queries, allowing for flexible pattern matching.

```php
$result = $database->query('SELECT * FROM table WHERE name LIKE %like~', $query);
```

--------------------------------

### Creating Dibi Expressions with Parameters

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Generate SQL expressions with parameters using the static expression() method. Returns a Dibi\Expression object.

```php
$database::expression('SHA1(?)', 'secret'); // returns Dibi\Expression object
```

--------------------------------

### Defining Table Substitutions in Dibi

Source: https://context7.com/dg/dibi/llms.txt

Configure table name prefixes or substitutions in Dibi to create portable queries. This is useful for multi-tenant applications or different deployment environments.

```php
<?php
$database = new Dibi\Connection([
    'driver' => 'sqlite',
    'database' => 'data/app.s3db',
]);

// Define substitutions
$database->getSubstitutes()->blog = 'wp_';
$database->getSubstitutes()->shop = 'store_';

// Use substitutions in queries (colon syntax)
$database->query('SELECT * FROM [:blog:posts] WHERE published = ?', true);
// Generated: SELECT * FROM `wp_posts` WHERE published = 1

$database->query('UPDATE [:shop:products] SET price = ? WHERE id = ?', 99.99, 1);
// Generated: UPDATE `store_products` SET price = 99.99 WHERE id = 1

// Can also use configuration
$database2 = new Dibi\Connection([
    'driver' => 'sqlite',
    'database' => 'data/app.s3db',
    'substitutes' => [
        'blog' => 'wp_',
        'shop' => 'store_',
    ],
]);
```

--------------------------------

### Conditional Query with %if, %else, and %end

Source: https://github.com/dg/dibi/blob/master/readme.md

Use %if, %else, and %end to conditionally include different SQL table names or clauses based on a condition.

```php
$result = $database->query('
	SELECT *
	FROM %if', $cond, 'one_table %else second_table
');
```

--------------------------------

### Fetch All Rows as Array

Source: https://context7.com/dg/dibi/llms.txt

Fetches all rows from the query result and returns them as an array of Dibi\Row objects. If no rows are found, an empty array is returned.

```php
// Fetch all rows as array
$users = $database->fetchAll('SELECT * FROM users');
foreach ($users as $user) {
    echo $user->name . "\n";
}
```

--------------------------------

### Conditional SQL with %if, %else, %end

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Conditionally include SQL parts based on PHP variables using %if, %else, and %end directives. Ensures only relevant SQL is generated.

```php
$database->query('
	SELECT *
	FROM table
	%if', isset($user), 'WHERE user=%s', $user, '%end
	ORDER BY name
');
```

--------------------------------

### Fetch Associative Array with Sequential Duplicates

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Handles duplicate keys by creating sequential arrays for values associated with the same key. Uses '[]' in the descriptor.

```php
$result->fetchAssoc('name[]order_id');
// returns [$name => [[$orderId => $row], [$orderId2 => $row2]], ...]
```

--------------------------------

### Introspecting Database Schema with Dibi

Source: https://context7.com/dg/dibi/llms.txt

Use Dibi's getDatabaseInfo() method to introspect the database schema, retrieving information about tables, columns, indexes, and foreign keys. This allows for dynamic schema exploration.

```php
<?php
$database = new Dibi\Connection([
    'driver' => 'sqlite',
    'database' => 'data/app.s3db',
]);

// Get database info
$dbInfo = $database->getDatabaseInfo();
echo "Database: " . $dbInfo->getName() . "\n";

// List all tables
foreach ($dbInfo->getTables() as $table) {
    $type = $table->isView() ? 'VIEW' : 'TABLE';
    echo "  $type: " . $table->getName() . "\n";
}

// Get table info
$table = $dbInfo->getTable('products');
echo "\nTable: " . $table->getName() . "\n";

// List columns
echo "Columns:\n";
foreach ($table->getColumns() as $column) {
    $nullable = $column->isNullable() ? 'NULL' : 'NOT NULL';
    $default = $column->getDefault();
    echo "  - {$column->getName()} {$column->getNativeType()} $nullable";
    if ($default !== null) {
        echo " DEFAULT '$default'";
    }
    echo "\n";
}

// List indexes
echo "Indexes:\n";
foreach ($table->getIndexes() as $index) {
    $type = $index->isPrimary() ? 'PRIMARY' : ($index->isUnique() ? 'UNIQUE' : 'INDEX');
    $columns = array_map(fn($c) => $c->getName(), $index->getColumns());
    echo "  - {$index->getName()} ($type): " . implode(', ', $columns) . "\n";
}
```

--------------------------------

### Access Query Statistics

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Retrieve statistics about the latest executed query and overall Dibi usage via the static facade. Includes SQL, elapsed time, and query counts.

```php
dibi::$sql;          // Latest SQL query executed
dibi::$elapsedTime;  // Duration in seconds
dibi::$numOfQueries; // Total number of queries
dibi::$totalTime;    // Total execution time
```

--------------------------------

### Array Parameter Substitution with Modifiers

Source: https://github.com/dg/dibi/blob/master/readme.md

Demonstrates how to pass an array to a query modifier, such as '%i' for integers. Dibi automatically expands the array into a comma-separated list suitable for SQL `IN` clauses.

```php
$ids = [10, '20', 30];
$result = $database->query('SELECT * FROM users WHERE id IN (%i)', $ids);
// SELECT * FROM users WHERE id IN (10, 20, 30)
```

--------------------------------

### Test SQL Query Execution

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Inspect the SQL query that would be executed without actually running it. Useful for debugging and verifying query construction.

```php
$database->test('SELECT * FROM users WHERE id = ?', $id);
// Echoes the SQL that would be executed
```

--------------------------------

### Lazy Database Connection

Source: https://context7.com/dg/dibi/llms.txt

Initializes a database connection lazily, meaning the connection is only established when the first query is executed. This is useful for optimizing application startup time.

```php
// Lazy connection (connects only when first query is executed)
$lazyDatabase = new Dibi\Connection([
    'driver'   => 'mysqli',
    'host'     => 'localhost',
    'username' => 'root',
    'password' => 'secret',
    'database' => 'myapp',
    'lazy'     => true,
]);
```

--------------------------------

### Fetch All Rows with Custom Key

Source: https://context7.com/dg/dibi/llms.txt

Fetches all rows from a Dibi\Result object and organizes them into an associative array using a specified column as the key. Each value in the resulting array is a Dibi\Row object.

```php
// Using Result object methods
$result = $database->query('SELECT * FROM users');

// Fetch all with custom key
$byId = $result->fetchAssoc('id');
// Result: [1 => Row, 2 => Row, 3 => Row]
```

--------------------------------

### Conditional Query with %if and %end

Source: https://github.com/dg/dibi/blob/master/readme.md

Construct SQL queries dynamically using %if and %end modifiers. The condition is evaluated, and the SQL fragment is included only if the condition is true.

```php
$user = ???

$result = $database->query('
	SELECT *
	FROM table
	%if', isset($user), 'WHERE user=%s', $user, '%end
	ORDER BY name
');
```

--------------------------------

### Fetch Key-Value Pairs

Source: https://context7.com/dg/dibi/llms.txt

Fetches query results as an associative array where the first column is the key and the second column is the value. This is useful for creating dropdown lists or mapping IDs to names.

```php
// Fetch as key => value pairs
$pairs = $database->fetchPairs('SELECT id, name FROM users');
// Result: [1 => 'John', 2 => 'Jane', 3 => 'Bob']
```

--------------------------------

### Activate Dibi Logger

Source: https://github.com/dg/dibi/blob/master/readme.md

Enables Dibi's built-in logger to track executed SQL statements and their durations. The logger can write to a specified file.

```php
$database->connect([
	'driver'   => 'sqlite',
	'database' => 'sample.sdb',
	'profiler' => [
		'file' => 'file.log',
	],
]);
```

--------------------------------

### Dibi Query UPDATE with %a Modifier

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Demonstrates how to use the %a (associative array) modifier for updating multiple columns in a table using Dibi's query method.

```php
$database->query('UPDATE users SET %a', ['name' => 'Jim', 'year' => 1978]);
```

--------------------------------

### Fetch Associative Data with Nested Structures

Source: https://context7.com/dg/dibi/llms.txt

Use fetchAssoc with '|', '->', and '[]' operators to reshape flat JOIN results into nested associative arrays. Useful for organizing hierarchical data from database joins.

```php
<?php
$database = new Dibi\Connection([
    'driver' => 'sqlite',
    'database' => 'data/app.s3db',
]);

// Query joining customers and orders
$result = $database->query(' 
    SELECT customer_id, customers.name, order_id, orders.number
    FROM customers
    INNER JOIN orders USING (customer_id)
');

// Two-level nesting: customer_id => order_id => row
$all = $result->fetchAssoc('customer_id|order_id');
foreach ($all as $customerId => $orders) {
    foreach ($orders as $orderId => $order) {
        echo "Customer $customerId, Order $orderId: {$order->number}\n";
    }
}

// With intermediate row object (customer_id => row with ->order_id array)
$result = $database->query('SELECT * FROM customers INNER JOIN orders USING (customer_id)');
$all = $result->fetchAssoc('customer_id->order_id');
foreach ($all as $customerId => $customerRow) {
    echo "Customer: {$customerRow->name}\n";
    foreach ($customerRow->order_id as $orderId => $order) {
        echo "  Order $orderId: {$order->number}\n";
    }
}

// Sequential array for duplicate keys (name[]order_id)
$result = $database->query('SELECT * FROM customers INNER JOIN orders USING (customer_id)');
$all = $result->fetchAssoc('name[]order_id');
// Groups multiple customers with same name into sequential array
foreach ($all['John Smith'] as $index => $orders) {
    foreach ($orders as $orderId => $order) {
        echo "John Smith #$index, Order: {$order->number}\n";
    }
}

```

--------------------------------

### Manage Database Transactions

Source: https://context7.com/dg/dibi/llms.txt

Handle database transactions using manual control (`begin`, `commit`, `rollback`) or the convenient callback-based `transaction()` method for automatic commit/rollback.

```php
<?php
$database = new Dibi\Connection([
    'driver' => 'sqlite',
    'database' => 'data/app.s3db',
]);

// Manual transaction control
try {
    $database->begin();

    $database->query('INSERT INTO accounts', [
        'user_id' => 1,
        'balance' => 1000,
    ]);

    $database->query('UPDATE accounts SET balance = balance - ? WHERE user_id = ?', 100, 1);
    $database->query('UPDATE accounts SET balance = balance + ? WHERE user_id = ?', 100, 2);

    $database->commit();
    echo "Transaction committed successfully\n";
} catch (Dibi\Exception $e) {
    $database->rollback();
    echo "Transaction rolled back: " . $e->getMessage() . "\n";
}

// Callback-based transaction (auto-commits on success, auto-rolls back on exception)
$result = $database->transaction(function ($db) {
    $db->query('INSERT INTO orders', [
        'customer_id' => 1,
        'total' => 500,
        'created' => new DateTime,
    ]);

    $orderId = $db->getInsertId();

    $db->query('INSERT INTO order_items', [
        'order_id' => $orderId,
        'product_id' => 42,
        'quantity' => 2,
        'price' => 250,
    ]);

    return $orderId;  // Return value is passed through
});

echo "Created order ID: $result\n";
```

--------------------------------

### Query with Multiple Segments

Source: https://context7.com/dg/dibi/llms.txt

Constructs a query by concatenating multiple string segments and parameters. This allows for building more complex queries programmatically.

```php
// Multiple query segments
$result = $database->query(
    'SELECT * FROM users WHERE name = ?',
    $name,
    'AND active = ?',
    $active
);
```

--------------------------------

### Identifier Substitution with Modifiers

Source: https://github.com/dg/dibi/blob/master/readme.md

Shows how to use the '%n' modifier for table and column names. This ensures identifiers are correctly quoted and escaped, but care must be taken to prevent user manipulation of these variables.

```php
$table = 'blog.users';
$column = 'name';
$result = $database->query('SELECT * FROM %n WHERE %n = ?', $table, $column, $value);
// SELECT * FROM `blog`.`users` WHERE `name` = 'Jim'
```

--------------------------------

### Dibi Transaction Handling

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Manage database transactions using explicit begin, commit, and rollback methods, or leverage the transaction() callback for automatic handling.

```php
$database->beginTransaction();
$database->commit();
$database->rollback();

// Or using callback (auto-commits on success, auto-rolls back on exception)
$database->transaction(function () use ($database) {
	$database->query('...');
	$database->query('...');
});
```

--------------------------------

### Dibi Transaction Management

Source: https://github.com/dg/dibi/blob/master/readme.md

Control database transactions using begin(), commit(), and rollback() methods to ensure data integrity.

```php
$database->begin();
```

```php
$database->commit();
```

```php
$database->rollback();
```

--------------------------------

### Dibi Query Multiple INSERT with %m Modifier

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Demonstrates batch insertion of multiple rows into a table using the %m (multi-value) modifier with Dibi's query method. Each element in the array represents a row with associative column data.

```php
$database->query('INSERT INTO users %m', [
	['name' => 'Jim', 'year' => 1978],
	['name' => 'Jack', 'year' => 1987],
]);
```

--------------------------------

### DateTime Object Support

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Insert DateTime objects directly into queries. Dibi automatically converts them to the appropriate database format for date/time columns.

```php
$database->query('INSERT INTO users', [
	'created' => new DateTime,
	'expires' => new DateTime('+1 year'),
]);
```

--------------------------------

### Fetch Associative Array using Name and Order ID

Source: https://github.com/dg/dibi/blob/master/readme.md

Associates results using a customer's name as the primary key and order ID as the secondary key. Handles cases where multiple customers share the same name by creating nested arrays.

```php
$all = $result->fetchAssoc('name|order_id');
```

```php
// the elements then proceeds like this:
$order = $all['Arnold Rimmer'][$orderId];
```

--------------------------------

### Dump Query Result

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Outputs the query result as an HTML table for easy inspection during development. Call the dump() method on the result object.

```php
$result->dump(); // Outputs result as HTML table
```

--------------------------------

### Using Expression for Parameterized SQL Fragments in Dibi

Source: https://context7.com/dg/dibi/llms.txt

Employ Dibi's expression() method to create parameterized SQL fragments, ensuring safe handling of dynamic values within SQL functions or conditions. This prevents SQL injection.

```php
<?php
$database = new Dibi\Connection([
    'driver' => 'sqlite',
    'database' => 'data/app.s3db',
]);

// Expression - parameterized SQL fragment
$database->query('UPDATE users SET', [
    'password' => $database::expression('SHA2(?, 256)', 'secret_password'),
    'slug' => $database::expression('LOWER(REPLACE(?, " ", "-"))', 'John Doe'),
]);
// Generated: UPDATE users SET `password` = SHA2('secret_password', 256), `slug` = LOWER(REPLACE('John Doe', ' ', '-'))

// Expression in WHERE conditions with %ex modifier
$database->query('SELECT * FROM users WHERE %ex', [
    $database::expression('age BETWEEN ? AND ?', 18, 65),
    'AND',
    'active = 1',
]);
// Generated: SELECT * FROM users WHERE age BETWEEN 18 AND 65 AND active = 1
```

--------------------------------

### Fetch Results as Associative Array by ID

Source: https://github.com/dg/dibi/blob/master/readme.md

Returns results as an associative array where the key is the value of the 'id' field. Useful for quick lookups.

```php
$assoc = $result->fetchAssoc('id');
```

--------------------------------

### Query with DateTime Object

Source: https://github.com/dg/dibi/blob/master/readme.md

Use DateTime objects directly in queries for date comparisons or insertions. Ensure the DateTime extension is enabled if not using PHP's built-in DateTime.

```php
$result = $database->query('SELECT * FROM users WHERE created < ?', new DateTime);
```

```php
$database->query('INSERT INTO users',
	['created' => new DateTime,
]);
```

--------------------------------

### Dibi INSERT ON DUPLICATE KEY UPDATE

Source: https://github.com/dg/dibi/blob/master/readme.md

Insert a new entry or update an existing one if a duplicate key is found. The %a modifier is required for the ON DUPLICATE KEY UPDATE clause.

```php
$database->query('INSERT INTO users', [
	'id' => $id,
	'name' => $name,
	'year' => $year,
], 'ON DUPLICATE KEY UPDATE %a', [
	'name' => $name,
	'year' => $year,
]);
```

--------------------------------

### Build Dynamic SQL with Conditional Blocks

Source: https://context7.com/dg/dibi/llms.txt

Use `%if`, `%else`, and `%end` modifiers within query strings to conditionally include SQL parts. This is useful for building dynamic WHERE clauses or other variable query sections.

```php
<?php
$database = new Dibi\Connection([
    'driver' => 'sqlite',
    'database' => 'data/app.s3db',
]);

$name = 'John';
$minAge = null;
$sortDesc = true;

// Simple %if condition
$result = $database->query(' 
    SELECT * 
    FROM users 
    %if', isset($name), 'WHERE name LIKE %like~', $name, '%end
    ORDER BY created
');
// With $name set: SELECT * FROM users WHERE name LIKE 'John%' ORDER BY created
// With $name null: SELECT * FROM users ORDER BY created

// %if with %else
$result = $database->query(' 
    SELECT * 
    FROM %if', $sortDesc, 'users_archive %else users %end
    WHERE active = 1
');
// With $sortDesc true: SELECT * FROM users_archive WHERE active = 1
// With $sortDesc false: SELECT * FROM users WHERE active = 1

// Nested conditions
$result = $database->query(' 
    SELECT * 
    FROM users 
    WHERE 1=1
    %if', isset($name), ' 
        AND name = ?', $name, ' 
        %if', isset($minAge), 'AND age >= ?', $minAge, '%end
    %end
    ORDER BY id
');

// Complex dynamic WHERE with %and and nested arrays
$result = $database->query('SELECT * FROM users WHERE %and', [
    'active' => true,
    'number > ?' => 10,
    'number < ?' => 100,
    ['%or', [
        'role' => 'admin',
        'role' => 'moderator',
    ]],
]);
// Generated: SELECT * FROM users WHERE `active` = 1 AND number > 10 AND number < 100 AND (`role` = 'admin' OR `role` = 'moderator')
```

--------------------------------

### Fetch Single Row

Source: https://context7.com/dg/dibi/llms.txt

Retrieves a single row from the database that matches the query criteria. Returns a Dibi\Row object or null if no matching row is found.

```php
// Fetch single row (returns Dibi\Row or null)
$row = $database->fetch('SELECT * FROM users WHERE id = ?', 1);
echo $row->name;  // Output: John
```

--------------------------------

### Use Query Substitution in SQL

Source: https://github.com/dg/dibi/blob/master/readme.md

Applies a defined substitution within an SQL query. Substitutions are denoted by colons and are automatically quoted.

```php
$database->query("UPDATE [:blog:items] SET [text]='Hello World'");
// UPDATE `wp_items` SET `text`='Hello World'
```

--------------------------------

### Fetch Associative Array with Two-Level Nesting

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Creates a two-level nested associative array from JOIN results, using two key columns separated by '|'. Ideal for hierarchical data.

```php
$result->fetchAssoc('customer_id|order_id');
// returns [$customerId => [$orderId => $row, ...], ...]
```

--------------------------------

### Dibi Multiple INSERT statements

Source: https://github.com/dg/dibi/blob/master/readme.md

Perform multiple row insertions into a table by providing multiple associative arrays as arguments to the query.

```php
$database->query('INSERT INTO users', [
	'name' => 'Jim',
	'year' => 1978,
], [
	'name' => 'Jack',
	'year' => 1987,
]);
```

--------------------------------

### Parameterized SQL Queries with Modifiers

Source: https://github.com/dg/dibi/blob/master/readme.md

Uses a string modifier '%s' for safe parameter substitution in SQL queries. This is an alternative to the '?' placeholder and is particularly useful for specific data types.

```php
$result = $database->query('SELECT * FROM users WHERE name = %s', $name);
```

--------------------------------

### Query with %ex Modifier

Source: https://github.com/dg/dibi/blob/master/readme.md

Use the %ex modifier to insert all elements of an array into the SQL query. Useful for combining expressions and raw SQL strings.

```php
$result = $database->query('SELECT * FROM `table` WHERE %ex',
	[
		$database::expression('left = ?', 1),
		'AND',
		'top IS NULL',
	]);
// SELECT * FROM `table` WHERE left = 1 AND top IS NULL
```

--------------------------------

### Fetch Nested Associative Array by Multiple Keys

Source: https://github.com/dg/dibi/blob/master/readme.md

Fetches a nested associative array using a composite key. The keys are concatenated with '|' to form the nested structure.

```php
$result = $database->query('
  SELECT customer_id, customers.name, order_id, orders.number, ...
  FROM customers
  INNER JOIN orders USING (customer_id)
  WHERE ...
');
```

```php
$all = $result->fetchAssoc('customer_id|order_id');
```

```php
// we will iterate like this:
foreach ($all as $customerId => $orders) {
   foreach ($orders as $orderId => $order) {
	   ...
   }
}
```

--------------------------------

### Identifier and String Quoting

Source: https://github.com/dg/dibi/blob/master/readme.md

Dibi automatically handles quoting of identifiers (tables, columns) and strings according to the specific database driver. Use backticks or square brackets for identifiers and single/double quotes for strings.

```php
$database->query("UPDATE `table` SET [status]='I''m fine'");
// MySQL: UPDATE `table` SET `status`='I\'m fine'
// ODBC:  UPDATE [table] SET [status]='I''m fine'
```

--------------------------------

### Manually Specify Result Type

Source: https://github.com/dg/dibi/blob/master/CLAUDE.md

Assign specific Dibi\Type constants to columns to ensure correct data types after fetching. Guarantees type consistency for specified columns.

```php
$result->setType('id', Dibi\Type::INTEGER);
$result->setType('price', Dibi\Type::FLOAT);
$row = $result->fetch();
// Now $row->id is guaranteed to be int
```

--------------------------------

### Dibi INSERT statement

Source: https://github.com/dg/dibi/blob/master/readme.md

Insert data into a table using an associative array. Modifiers and wildcards are not required for basic INSERT statements.

```php
$database->query('INSERT INTO users', [
	'name' => $name,
	'year' => $year,
]);
```

--------------------------------

### Define Query Substitution

Source: https://github.com/dg/dibi/blob/master/readme.md

Defines a substitution pattern that can be used in SQL queries. This is useful for replacing common table prefixes or other dynamic parts of SQL.

```php
// create new substitution :blog:  ==>  wp_
$database->substitute('blog', 'wp_');
```