### Initialize Database Driver in Custom Application

Source: https://github.com/joomla-framework/database/blob/4.x-dev/README.md

Example of initializing a database driver within a custom application class using the DatabaseFactory. Ensure your application configuration provides the necessary database connection details.

```php
use Joomla\Application\AbstractApplication;
use Joomla\Database;

class MyApplication extends AbstractApplication
{
	/**
	 * Database driver.
	 *
	 * @var    Database\DatabaseDriver
	 * @since  1.0
	 */
	protected $db;

	protected function doExecute()
	{
		// Do stuff
	}

	protected function initialise()
	{
		// Make the database driver.
		$dbFactory = new Database\DatabaseFactory;

		$this->db = $dbFactory->getDriver(
			$this->get('database.driver'),
			array(
				'host' => $this->get('database.host'),
				'user' => $this->get('database.user'),
				'password' => $this->get('database.password'),
				'port' => $this->get('database.port'),
				'socket' => $this->get('database.socket'),
				'database' => $this->get('database.name'),
			)
		);
	}
}
```

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

### Install Joomla Database with Test Sources via Composer CLI

Source: https://github.com/joomla-framework/database/blob/4.x-dev/README.md

Use the Composer command-line interface to require the joomla/database package, including its test sources.

```sh
composer require --prefer-source joomla/database "~4.0"
```

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

### Install Joomla Database via Composer

Source: https://github.com/joomla-framework/database/blob/4.x-dev/README.md

Add the joomla/database package to your project's composer.json file to install the library.

```json
{
	"require": {
		"joomla/database": "~4.0"
	}
}
```

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

### Install Joomla Database via Composer CLI

Source: https://github.com/joomla-framework/database/blob/4.x-dev/README.md

Use the Composer command-line interface to require the joomla/database package.

```sh
composer require joomla/database "~4.0"
```

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

### Build and Execute Queries with DatabaseDriver::createQuery()

Source: https://context7.com/joomla-framework/database/llms.txt

Utilize createQuery() to get a fluent query builder for constructing SQL statements. Use methods like select(), from(), where(), order(), and setLimit() to build the query. Execute the query using setQuery() and fetch results with loadObjectList() or loadAssocList().

```php
use Joomla\Database\DatabaseFactory;

$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// Basic SELECT
$query = $db->createQuery()
    ->select(['id', 'title', 'published'])
    ->from('#__content')
    ->where('published = 1')
    ->order('title ASC')
    ->setLimit(10, 0);

// Produces: SELECT id, title, published FROM app_content WHERE published = 1 ORDER BY title ASC LIMIT 10

$db->setQuery($query);
$rows = $db->loadObjectList();
// Returns: array of stdClass objects, one per row

// Using quoteName and quote safely
$query2 = $db->createQuery()
    ->select($db->quoteName(['a.id', 'a.title', 'b.name'], ['id', 'title', 'cat_name']))
    ->from($db->quoteName('#__content', 'a'))
    ->innerJoin($db->quoteName('#__categories', 'b'), $db->quoteName('b.id') . ' = ' . $db->quoteName('a.catid'))
    ->where($db->quoteName('a.state') . ' = ' . $db->quote(1))
    ->order($db->quoteName('a.ordering') . ' ASC');

$db->setQuery($query2);
$results = $db->loadAssocList('id'); // array keyed by 'id' field
```

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

### Create Database Driver Instance with DatabaseFactory

Source: https://context7.com/joomla-framework/database/llms.txt

Use DatabaseFactory::getDriver() to obtain a configured database connection instance. Specify the driver name (e.g., 'mysqli', 'pgsql') and an options array for connection details.

```php
use Joomla\Database\DatabaseFactory;

$factory = new DatabaseFactory();

// Connect to MySQL via MySQLi
$db = $factory->getDriver('mysqli', [
    'host'     => 'localhost',
    'user'     => 'dbuser',
    'password' => 'secret',
    'database' => 'myapp',
    'prefix'   => 'app_',
    'port'     => 3306,
    'select'   => true,
]);

// Connect to PostgreSQL via PDO
$dbPg = $factory->getDriver('pgsql', [
    'host'     => '127.0.0.1',
    'user'     => 'pguser',
    'password' => 'pgpass',
    'database' => 'myapp',
    'prefix'   => 'app_',
]);

// Connect to SQLite
$dbSqlite = $factory->getDriver('sqlite', [
    'database' => '/path/to/database.sqlite',
    'prefix'   => '',
]);

echo $db->getName();        // 'mysqli'
echo $db->getServerType(); // 'mysql'
echo $db->getVersion();    // e.g. '8.0.32'
```

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

### Manage Database Transactions with Savepoints

Source: https://context7.com/joomla-framework/database/llms.txt

Use transactionStart(), transactionCommit(), and transactionRollback() for basic and nested transactions. Nested transactions require passing 'true' to these methods to manage savepoints.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// Basic transaction
$db->transactionStart();

try {
    $db->setQuery(
        $db->createQuery()
            ->update('#__orders')
            ->set('status = ' . $db->quote('processing'))
            ->where('id = 42')
    )->execute();

    $db->setQuery(
        $db->createQuery()
            ->insert('#__order_log')
            ->columns(['order_id', 'action', 'created'])
            ->values(implode(',', [42, $db->quote('status_change'), $db->quote(date('Y-m-d H:i:s'))]))
    )->execute();

    $db->transactionCommit();
} catch (	tamente
    $db->transactionRollback();
    throw $e;
}

// Nested transactions using savepoints
$db->transactionStart();
$db->setQuery('UPDATE #__inventory SET qty = qty - 1 WHERE sku = ' . $db->quote('ITEM001'))->execute();

$db->transactionStart(true); // creates a savepoint
try {
    $db->setQuery('INSERT INTO #__shipments (sku, qty) VALUES (' . $db->quote('ITEM001') . ', 1)')->execute();
    $db->transactionCommit(true); // commit to savepoint
} catch (	tamente
    $db->transactionRollback(true); // rollback to savepoint only
}

$db->transactionCommit(); // commit outer transaction
```

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

### DatabaseQuery Parameter Binding

Source: https://context7.com/joomla-framework/database/llms.txt

Demonstrates how to use `bind()`, `bindArray()`, `whereIn()`, and `whereNotIn()` for secure prepared statement parameter binding.

```APIDOC
## DatabaseQuery bound parameters — bind() and whereIn()

The query builder supports prepared statement parameter binding via `bind()`, `bindArray()`, `whereIn()`, and `whereNotIn()`. This is the safe way to pass untrusted values into queries.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// Named parameter binding with bind()
$userId = 42;
$status = 'active';

$query = $db->createQuery()
    ->select('*')
    ->from('#__users')
    ->where('id = :id')
    ->where('status = :status')
    ->bind(':id', $userId, \Joomla\Database\ParameterType::INTEGER)
    ->bind(':status', $status, \Joomla\Database\ParameterType::STRING);

$db->setQuery($query);
$user = $db->loadObject(); // stdClass { id: 42, status: 'active', ... }

// whereIn() with automatic binding (prevents IN-clause injection)
$allowedIds = [1, 2, 3, 4, 5];

$query2 = $db->createQuery()
    ->select(['id', 'title'])
    ->from('#__content')
    ->whereIn('id', $allowedIds); // Binds each value as a prepared parameter

$db->setQuery($query2);
$items = $db->loadObjectList();

// whereNotIn()
$excludedStates = [0, -2];
$query3 = $db->createQuery()
    ->select('*')
    ->from('#__content')
    ->whereNotIn('state', $excludedStates);

// bindArray() for manual IN clause construction
$tags = ['php', 'joomla', 'database'];
$params = $query->bindArray($tags, \Joomla\Database\ParameterType::STRING);
$query->where('tag IN (' . implode(',', $params) . ')');
```
```

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

### DatabaseQuery JOIN Clauses

Source: https://context7.com/joomla-framework/database/llms.txt

Illustrates how to construct multi-table queries using `join()`, `innerJoin()`, `leftJoin()`, `rightJoin()`, and `outerJoin()` methods.

```APIDOC
## DatabaseQuery JOIN clauses — join(), innerJoin(), leftJoin()

The query builder provides `join()`, `innerJoin()`, `leftJoin()`, `rightJoin()`, and `outerJoin()` for constructing multi-table queries.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

$query = $db->createQuery()
    ->select([
        $db->quoteName('a.id', 'article_id'),
        $db->quoteName('a.title', 'article_title'),
        $db->quoteName('u.name', 'author_name'),
        $db->quoteName('c.title', 'category'),
    ])
    ->from($db->quoteName('#__content', 'a'))
    ->innerJoin(
        $db->quoteName('#__users', 'u'),
        $db->quoteName('u.id') . ' = ' . $db->quoteName('a.created_by')
    )
    ->leftJoin(
        $db->quoteName('#__categories', 'c'),
        $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid')
    )
    ->where($db->quoteName('a.state') . ' = 1')
    ->where($db->quoteName('a.publish_up') . ' <= ' . $db->quote(date('Y-m-d H:i:s')))
    ->order($db->quoteName('a.ordering') . ' ASC')
    ->setLimit(20, 0);

$db->setQuery($query);
$results = $db->loadObjectList();
// Each object: { article_id, article_title, author_name, category }
```
```

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

### Escape and Quote Strings for SQL Queries

Source: https://github.com/joomla-framework/database/blob/4.x-dev/README.md

Demonstrates how to properly escape and quote strings for use in SQL queries to prevent SQL injection. Shows handling of exact matches, LIKE clauses, and arrays of values.

```php
function search($title)
{
	// Get the database driver from the factory, or by some other suitable means.
	$db = DatabaseDriver::getInstance($options);

	// Search for an exact match of the title, correctly sanitising the untrusted input.
	$sql1 = 'SELECT * FROM #__content WHERE title = ' . $db->quote($title);

	// Special treatment for a LIKE clause.
	$search = $db->quote($db->escape($title, true) . '%', false);
	$sql2 = 'SELECT * FROM #__content WHERE title LIKE ' . $search;

	if (is_array($title))
	{
		$sql3 = 'SELECT * FROM #__content WHERE title IN ('
			. implode(',', $db->quote($title)) . ')';
	}

	// Do the database calls.
}
```

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

### DatabaseAwareTrait for Database Dependency Injection

Source: https://context7.com/joomla-framework/database/llms.txt

Provides `setDatabase()` and `getDatabase()` methods for classes that need a database connection. Ensure the trait is used and the database is set before calling `getDatabase()`.

```php
use Joomla\Database\DatabaseAwareTrait;
use Joomla\Database\DatabaseAwareInterface;
use Joomla\Database\DatabaseInterface;

// Using DatabaseAwareTrait in a service class
class ArticleRepository implements DatabaseAwareInterface
{
    use DatabaseAwareTrait;

    public function findPublished(int $limit = 10): array
    {
        $db    = $this->getDatabase(); // throws DatabaseNotFoundException if not set
        $query = $db->createQuery()
            ->select(['id', 'title', 'introtext'])
            ->from('#__content')
            ->where('state = 1')
            ->order('created DESC')
            ->setLimit($limit);

        return $db->setQuery($query)->loadObjectList();
    }

    public function findById(int $id): ?object
    {
        $db = $this->getDatabase();
        return $db->setQuery(
            $db->createQuery()->select('*')->from('#__content')
                ->where('id = ' . (int) $id)
        )->loadObject();
    }
}

// Inject the driver
$repo = new ArticleRepository();
$repo->setDatabase($db); // Assuming $db is an instantiated DatabaseInterface object
$articles = $repo->findPublished(5);
```

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

### DatabaseDriver::setQuery() / execute()

Source: https://context7.com/joomla-framework/database/llms.txt

Prepares and executes SQL statements, including INSERT, UPDATE, DELETE, and raw SQL.

```APIDOC
## DatabaseDriver::setQuery() / execute()

### Description
Prepares a SQL string or `QueryInterface` object for execution, and `execute()` runs it. This is the foundation for INSERT, UPDATE, DELETE, or any non-SELECT statement.

### Method
```php
$db->setQuery($query)->execute();
```

### Parameters
- `$query` (string | QueryInterface) - The SQL statement or Query object to prepare and execute.

### Request Example
```php
// INSERT via query builder
$query = $db->createQuery()
    ->insert($db->quoteName('#__content'))
    ->columns($db->quoteName(['title', 'alias', 'state', 'created']))
    ->values(implode(',', [
        $db->quote('My Article'),
        $db->quote('my-article'),
        $db->quote(1),
        $db->quote(date('Y-m-d H:i:s')),
    ]));

$db->setQuery($query)->execute();

// Raw SQL string
$db->setQuery('TRUNCATE TABLE ' . $db->quoteName('#__logs'))->execute();
```

### Response
#### Success Response
- `execute()` returns a boolean indicating success or failure.

### Related Methods
- `insertid()`: Get the auto-increment ID of the last inserted row.
- `getAffectedRows()`: Get the number of affected rows by the last query.
```

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

### Prepare and Execute SQL Statements with DatabaseDriver

Source: https://context7.com/joomla-framework/database/llms.txt

Use setQuery() to prepare SQL statements or QueryInterface objects for execution, and execute() to run them. This is fundamental for non-SELECT statements like INSERT, UPDATE, and DELETE. It also supports raw SQL strings, which are internally handled as prepared statements.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// INSERT via query builder
$query = $db->createQuery()
    ->insert($db->quoteName('#__content'))
    ->columns($db->quoteName(['title', 'alias', 'state', 'created']))
    ->values(implode(',', [
        $db->quote('My Article'),
        $db->quote('my-article'),
        $db->quote(1),
        $db->quote(date('Y-m-d H:i:s')),
    ]));

$db->setQuery($query)->execute();
$newId = $db->insertid(); // get the auto-increment ID

// UPDATE via query builder
$update = $db->createQuery()
    ->update($db->quoteName('#__content'))
    ->set($db->quoteName('title') . ' = ' . $db->quote('Updated Title'))
    ->where($db->quoteName('id') . ' = ' . (int) $newId);

$db->setQuery($update)->execute();
$affected = $db->getAffectedRows(); // 1

// DELETE
$delete = $db->createQuery()
    ->delete($db->quoteName('#__content'))
    ->where($db->quoteName('id') . ' = ' . (int) $newId);

$db->setQuery($delete)->execute();

// Raw SQL string (still uses prepared statement internally)
$db->setQuery('TRUNCATE TABLE ' . $db->quoteName('#__logs'))->execute();
```

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

### DatabaseFactory::getDriver()

Source: https://context7.com/joomla-framework/database/llms.txt

Creates a database driver instance based on the specified name and configuration options. Supports multiple database types like MySQLi, PostgreSQL, SQLite, and SQL Server.

```APIDOC
## DatabaseFactory::getDriver()

### Description
`DatabaseFactory::getDriver()` is the primary entry point for obtaining a configured database connection. It instantiates the correct driver class based on the `$name` argument (e.g., `'mysqli'`, `'pgsql'`, `'sqlite'`, `'sqlsrv'`) and returns a `DatabaseInterface` instance.

### Method
`getDriver(string $name, array $options = []) : DatabaseInterface`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```php
use Joomla\Database\DatabaseFactory;

$factory = new DatabaseFactory();

// Connect to MySQL via MySQLi
$db = $factory->getDriver('mysqli', [
    'host'     => 'localhost',
    'user'     => 'dbuser',
    'password' => 'secret',
    'database' => 'myapp',
    'prefix'   => 'app_',
    'port'     => 3306,
    'select'   => true,
]);

// Connect to PostgreSQL via PDO
$dbPg = $factory->getDriver('pgsql', [
    'host'     => '127.0.0.1',
    'user'     => 'pguser',
    'password' => 'pgpass',
    'database' => 'myapp',
    'prefix'   => 'app_',
]);

// Connect to SQLite
$dbSqlite = $factory->getDriver('sqlite', [
    'database' => '/path/to/database.sqlite',
    'prefix'   => '',
]);

echo $db->getName();        // 'mysqli'
echo $db->getServerType(); // 'mysql'
echo $db->getVersion();    // e.g. '8.0.32'
```

### Response
#### Success Response (200)
- **DatabaseInterface** - An instance of the requested database driver.
```

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

### DatabaseQuery::union() / unionAll() / toQuerySet()

Source: https://context7.com/joomla-framework/database/llms.txt

Methods for performing set operations like UNION and UNION ALL, and a helper for combining ordered/limited subqueries.

```APIDOC
## DatabaseQuery::union() / unionAll() / toQuerySet()

### Description
Supports `UNION`, `UNION ALL` operations and the `toQuerySet()` helper for combining ordered/limited subqueries.

### Method
Chained method calls on a DatabaseQuery object.

### Parameters
- `union(DatabaseQuery $query)`: Appends a UNION clause (distinct).
- `unionAll(DatabaseQuery $query)`: Appends a UNION ALL clause.
- `toQuerySet()`: Returns a query set object for combining subqueries.

### Request Example
```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// UNION DISTINCT
$query1 = $db->createQuery()->select('name')->from('#__users');
$query2 = $db->createQuery()->select('name')->from('#__contacts');

$query1->union($query2); // UNION (distinct)
$db->setQuery($query1);
$names = $db->loadColumn();

// UNION ALL
$query3 = $db->createQuery()->select(['id', 'title'])->from('#__content')->where('catid = 1');
$query4 = $db->createQuery()->select(['id', 'title'])->from('#__content')->where('catid = 2');
$query3->unionAll($query4);

// toQuerySet() — wrap ordered/limited subqueries for UNION with outer ORDER BY
$first = $db->createQuery()
    ->select('name')
    ->from('#__content')
    ->order('id DESC')
    ->setLimit(1);

$second = $db->createQuery()
    ->select('name')
    ->from('#__archive')
    ->order('id ASC')
    ->setLimit(1);

$combined = $first->toQuerySet()
    ->unionAll($second)
    ->order('name')
    ->setLimit(2);

$db->setQuery($combined);
$results = $db->loadColumn();
```
```

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

### Using DatabaseQuery Date Helpers

Source: https://context7.com/joomla-framework/database/llms.txt

Demonstrates the use of currentTimestamp(), dateAdd(), year(), month(), and other date part extraction functions within the DatabaseQuery builder. These functions generate database-agnostic SQL.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);
$query = $db->createQuery();

// currentTimestamp()
$q = $db->createQuery()
    ->select('*')
    ->from('#__sessions')
    ->where('last_activity > ' . $query->currentTimestamp());
// WHERE last_activity > CURRENT_TIMESTAMP()
```

```php
// dateAdd() — add an interval to a date
$expiry = $db->createQuery()
    ->select(['id', 'title'])
    ->from('#__content')
    ->where(
        $db->createQuery()->dateAdd(
            $db->quoteName('created'),
            '30',
            'DAY'
        ) . ' > ' . $db->quote(date('Y-m-d'))
    );
// WHERE DATE_ADD(`created`, INTERVAL 30 DAY) > '2025-01-15'
```

```php
// Date part extraction
$report = $db->createQuery()
    ->select([
        $db->createQuery()->year($db->quoteName('created')) . ' AS `year`',
        $db->createQuery()->month($db->quoteName('created')) . ' AS `month`',
        'COUNT(*) AS `total`',
    ])
    ->from('#__content')
    ->where('state = 1')
    ->group($db->createQuery()->year($db->quoteName('created')))
    ->group($db->createQuery()->month($db->quoteName('created')))
    ->order('`year` DESC, `month` DESC');

$db->setQuery($report);
$stats = $db->loadObjectList(); // [{ year: '2024', month: '12', total: '15' }, ...]
```

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

### Using ChainedMonitor to Combine Monitors

Source: https://context7.com/joomla-framework/database/llms.txt

Combines multiple query monitors (e.g., DebugMonitor and LoggingMonitor) into a single monitor. This allows for simultaneous observation and logging of queries.

```php
use Joomla\Database\DatabaseFactory;
use Joomla\Database\Monitor\DebugMonitor;
use Joomla\Database\Monitor\LoggingMonitor;
use Joomla\Database\Monitor\ChainedMonitor;

// ChainedMonitor — combine both monitors
$chain = new ChainedMonitor();
$chain->addMonitor($debugMonitor); // Assuming $debugMonitor is already instantiated
$chain->addMonitor($loggingMonitor); // Assuming $loggingMonitor is already instantiated

$db->setMonitor($chain);
$db->setQuery($db->createQuery()->select('id')->from('#__content'))->loadColumn();
// Both monitors receive startQuery() / stopQuery() events
```

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

### Using DebugMonitor for Query Performance Metrics

Source: https://context7.com/joomla-framework/database/llms.txt

Collects timing, memory, call stacks, and bound parameters for database queries. Use this to analyze query performance.

```php
use Joomla\Database\DatabaseFactory;
use Joomla\Database\Monitor\DebugMonitor;

$factory = new DatabaseFactory();

// DebugMonitor — collect performance metrics
$debugMonitor = new DebugMonitor();

$db = $factory->getDriver('mysqli', array_merge($options, ['monitor' => $debugMonitor]));

$db->setQuery($db->createQuery()->select('*')->from('#__content')->where('state = 1'))->loadObjectList();
$db->setQuery($db->createQuery()->select('COUNT(*)')->from('#__users'))->loadResult();

$logs       = $debugMonitor->getLogs();       // ['SELECT * FROM app_content ...', 'SELECT COUNT(*) ...']
$timings    = $debugMonitor->getTimings();    // [startTime1, stopTime1, startTime2, stopTime2, ...]
$memLogs    = $debugMonitor->getMemoryLogs(); // [memStart1, memStop1, ...]
$params     = $debugMonitor->getBoundParams();
$callStacks = $debugMonitor->getCallStacks();

for ($i = 0; $i < count($logs); $i++) {
    $elapsed = $timings[$i * 2 + 1] - $timings[$i * 2];
    echo sprintf("Query %d: %.4fs — %s\n", $i + 1, $elapsed, $logs[$i]);
}
```

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

### Constructing JOIN clauses with join(), innerJoin(), leftJoin()

Source: https://context7.com/joomla-framework/database/llms.txt

Use join(), innerJoin(), leftJoin(), rightJoin(), and outerJoin() to build multi-table queries. Ensure identifiers are properly quoted using quoteName().

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

$query = $db->createQuery()
    ->select([
        $db->quoteName('a.id', 'article_id'),
        $db->quoteName('a.title', 'article_title'),
        $db->quoteName('u.name', 'author_name'),
        $db->quoteName('c.title', 'category'),
    ])
    ->from($db->quoteName('#__content', 'a'))
    ->innerJoin(
        $db->quoteName('#__users', 'u'),
        $db->quoteName('u.id') . ' = ' . $db->quoteName('a.created_by')
    )
    ->leftJoin(
        $db->quoteName('#__categories', 'c'),
        $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid')
    )
    ->where($db->quoteName('a.state') . ' = 1')
    ->where($db->quoteName('a.publish_up') . ' <= ' . $db->quote(date('Y-m-d H:i:s')))
    ->order($db->quoteName('a.ordering') . ' ASC')
    ->setLimit(20, 0);

$db->setQuery($query);
$results = $db->loadObjectList();
// Each object: { article_id, article_title, author_name, category }
```

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

### Splitting Multi-Statement SQL with DatabaseDriver::splitSql()

Source: https://context7.com/joomla-framework/database/llms.txt

A static utility to parse multi-statement SQL strings into individual queries, correctly handling quoted strings and comments. Useful for processing migration files.

```php
use Joomla\Database\DatabaseDriver;

$sql = <<<SQL
CREATE TABLE `#__articles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL DEFAULT '',
  `body` TEXT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `#__articles` (`title`, `body`) VALUES ('First', 'Hello World');

-- This is a comment; it should not split here
INSERT INTO `#__articles` (`title`, `body`) VALUES ('Second', 'It\'s alive!');
SQL;

$queries = DatabaseDriver::splitSql($sql);
// $queries = [
//   "CREATE TABLE `#__articles` (...);",
```

```php
//   "INSERT INTO `#__articles` ... VALUES ('First', 'Hello World');",
//   "INSERT INTO `#__articles` ... VALUES ('Second', 'It\'s alive!');"
// ]

$db = (new \Joomla\Database\DatabaseFactory())->getDriver('mysqli', $options);

foreach ($queries as $query) {
    $db->setQuery($db->replacePrefix($query))->execute();
}
```

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

### Using DatabaseQuery::format() for Safe Query Formatting

Source: https://context7.com/joomla-framework/database/llms.txt

Illustrates the use of the format() method, which mimics sprintf() but provides database-aware type specifiers (%n, %q, %a, %e) for automatic escaping and quoting. This prevents SQL injection vulnerabilities.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);
$query = $db->createQuery();

// %n — name quote, %q — value quote, %a — numeric cast, %e — escape
$sql = $query->format(
    'SELECT %1$n FROM %2$n WHERE %3$n = %4$q AND year(%5$n) = %6$a',
    'title',      // %1$n -> `title`
    '#__content', // %2$n -> `app_content`
    'state',      // %3$n -> `state`
    'published',  // %4$q -> 'published'
    'created',    // %5$n -> `created`
    '2024'        // %6$a -> 2024 (numeric, no quotes)
);
// SELECT `title` FROM `app_content` WHERE `state` = 'published' AND year(`created`) = 2024

$db->setQuery($db->createQuery()->setQuery($sql));
$titles = $db->loadColumn();
```

```php
// %t — current timestamp, %Z — quoted null date
$insert = $query->format(
    'INSERT INTO %n (title, created, modified) VALUES (%q, %t, %Z)',
    '#__content',
    'My New Post'
);
// INSERT INTO `app_content` (title, created, modified) VALUES ('My New Post', CURRENT_TIMESTAMP(), '0000-00-00 00:00:00')
```

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

### DatabaseProvider for DI Container Integration

Source: https://context7.com/joomla-framework/database/llms.txt

Registers the database factory and driver as services in a Joomla DI container. Requires a 'config' service to provide database connection details.

```php
use Joomla\Database\Service\DatabaseProvider;
use Joomla\DI\Container;
use Joomla\Database\DatabaseInterface;

// --- DI Container integration (Joomla\DI) ---
$container = new Container();
$container->registerServiceProvider(new DatabaseProvider());

// Requires 'config' service providing database.driver, database.host, etc.
$db = $container->get(DatabaseInterface::class);
```

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

### DatabaseQuery::format()

Source: https://context7.com/joomla-framework/database/llms.txt

Formats SQL queries using sprintf-style syntax with database-aware type specifiers for automatic escaping and quoting.

```APIDOC
## DatabaseQuery::format() — sprintf-style safe query formatting

`format()` works like `sprintf()` but uses database-aware type specifiers to automatically escape, quote, or name-quote substituted values.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);
$query = $db->createQuery();

// %n — name quote, %q — value quote, %a — numeric cast, %e — escape
$sql = $query->format(
    'SELECT %1$n FROM %2$n WHERE %3$n = %4$q AND year(%5$n) = %6$a',
    'title',      // %1$n -> `title`
    '#__content', // %2$n -> `app_content`
    'state',      // %3$n -> `state`
    'published',  // %4$q -> 'published'
    'created',    // %5$n -> `created`
    '2024'        // %6$a -> 2024 (numeric, no quotes)
);
// SELECT `title` FROM `app_content` WHERE `state` = 'published' AND year(`created`) = 2024

$db->setQuery($db->createQuery()->setQuery($sql));
$titles = $db->loadColumn();

// %t — current timestamp, %Z — quoted null date
$insert = $query->format(
    'INSERT INTO %n (title, created, modified) VALUES (%q, %t, %Z)',
    '#__content',
    'My New Post'
);
// INSERT INTO `app_content` (title, created, modified) VALUES ('My New Post', CURRENT_TIMESTAMP(), '0000-00-00 00:00:00')
```
```

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

### Using LoggingMonitor with PSR-3 Logger

Source: https://context7.com/joomla-framework/database/llms.txt

Forwards query events to a PSR-3 compliant logger, such as Monolog. Ensure a logger instance is configured and pushed to the monitor.

```php
use Joomla\Database\DatabaseFactory;
use Joomla\Database\Monitor\LoggingMonitor;
use Monolog\Logger;
use Monolog\Handler\StreamHandler;

// LoggingMonitor — pipe to PSR-3 logger (e.g. Monolog)
$logger = new Logger('database');
$logger->pushHandler(new StreamHandler('/logs/db.log', Logger::DEBUG));

$loggingMonitor = new LoggingMonitor();
$loggingMonitor->setLogger($logger);

$db->setMonitor($loggingMonitor);
```

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

### Iterate Over Database Query Results

Source: https://github.com/joomla-framework/database/blob/4.x-dev/README.md

Shows how to use the `DatabasexDatabaseIterator` to loop through the results of a database query. The iterator also supports counting the number of returned rows.

```php
$db = DatabaseDriver::getInstance($options);
$iterator = $db->setQuery(
	$db->createQuery()->select('*')->from('#__content')
)->getIterator();

foreach ($iterator as $row)
{
    // Deal with $row
}
```

```php
$count = count($iterator);
```

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

### DatabaseDriver::createQuery()

Source: https://context7.com/joomla-framework/database/llms.txt

Creates a new query builder object using a fluent interface for constructing SQL statements. This is the recommended method for building queries.

```APIDOC
## DatabaseDriver::createQuery()

### Description
`createQuery()` returns a new driver-specific `QueryInterface` instance, which is the fluent query builder for constructing SQL statements. This is the preferred way to build queries (replacing the deprecated `getQuery(true)`).

### Method
`createQuery() : QueryInterface`

### Parameters
None

### Request Example
```php
use Joomla\Database\DatabaseFactory;

$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// Basic SELECT
$query = $db->createQuery()
    ->select(['id', 'title', 'published'])
    ->from('#__content')
    ->where('published = 1')
    ->order('title ASC')
    ->setLimit(10, 0);

// Produces: SELECT id, title, published FROM app_content WHERE published = 1 ORDER BY title ASC LIMIT 10

$db->setQuery($query);
$rows = $db->loadObjectList();
// Returns: array of stdClass objects, one per row

// Using quoteName and quote safely
$query2 = $db->createQuery()
    ->select($db->quoteName(['a.id', 'a.title', 'b.name'], ['id', 'title', 'cat_name']))
    ->from($db->quoteName('#__content', 'a'))
    ->innerJoin($db->quoteName('#__categories', 'b'), $db->quoteName('b.id') . ' = ' . $db->quoteName('a.catid'))
    ->where($db->quoteName('a.state') . ' = ' . $db->quote(1))
    ->order($db->quoteName('a.ordering') . ' ASC');

$db->setQuery($query2);
$results = $db->loadAssocList('id'); // array keyed by 'id' field
```

### Response
#### Success Response (200)
- **QueryInterface** - A fluent query builder instance.
```

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

### DatabaseQuery Date Helpers

Source: https://context7.com/joomla-framework/database/llms.txt

Provides portable SQL functions for date/time operations, generating correct syntax for different database backends.

```APIDOC
## DatabaseQuery date helpers — dateAdd(), currentTimestamp(), year/month/day/hour/minute/second

The query builder provides portable SQL functions for date/time operations that generate the correct syntax for each database backend.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);
$query = $db->createQuery();

// currentTimestamp()
$q = $db->createQuery()
    ->select('*')
    ->from('#__sessions')
    ->where('last_activity > ' . $query->currentTimestamp());
// WHERE last_activity > CURRENT_TIMESTAMP()

// dateAdd() — add an interval to a date
$expiry = $db->createQuery()
    ->select(['id', 'title'])
    ->from('#__content')
    ->where(
        $db->createQuery()->dateAdd(
            $db->quoteName('created'),
            '30',
            'DAY'
        ) . ' > ' . $db->quote(date('Y-m-d'))
    );
// WHERE DATE_ADD(`created`, INTERVAL 30 DAY) > '2025-01-15'

// Date part extraction
$report = $db->createQuery()
    ->select([
        $db->createQuery()->year($db->quoteName('created')) . ' AS `year`',
        $db->createQuery()->month($db->quoteName('created')) . ' AS `month`',
        'COUNT(*) AS `total`',
    ])
    ->from('#__content')
    ->where('state = 1')
    ->group($db->createQuery()->year($db->quoteName('created')))
    ->group($db->createQuery()->month($db->quoteName('created')))
    ->order('`year` DESC, `month` DESC');

$db->setQuery($report);
$stats = $db->loadObjectList(); // [{ year: '2024', month: '12', total: '15' }, ...]
```
```

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

### Load First Row into Custom Class Instance with loadObject()

Source: https://context7.com/joomla-framework/database/llms.txt

You can hydrate a custom class instance directly from the first row of a query result by passing the class name to loadObject(). This allows for type-safe data retrieval.

```php
// Custom class hydration
class User { public $id; public $name; public $email; }
$user = $db->setQuery(
    $db->createQuery()->select('*')->from('#__users')->where('id = 100')
)->loadObject(User::class); // User instance
```

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

### Import Database Schema and Data from XML

Source: https://context7.com/joomla-framework/database/llms.txt

Use DatabaseImporter to read XML files generated by DatabaseExporter. It can merge table structures and import data, useful for migrations and backups.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// --- IMPORT ---
$xmlData = file_get_contents('/backup/export.xml');

$importer = $db->getImporter()
    ->from($xmlData)
    ->withStructure(true);

// mergeStructure: creates missing tables, alters existing ones to match XML definition
$importer->mergeStructure();

// importData: inserts rows from <table_data> sections
$importer->importData();

// Import from SimpleXMLElement directly
$xml = new SimpleXMLElement($xmlData);
$db->getImporter()->from($xml)->withStructure(true)->mergeStructure();
```

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

### Load All Rows as Array Keyed by Field with loadObjectList()

Source: https://context7.com/joomla-framework/database/llms.txt

When using loadObjectList() with a key argument, the resulting array will be indexed by the values of the specified field, allowing for quick lookups by that field's value.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// loadObjectList with key — array keyed by a field value
$byId = $db->setQuery(
    $db->createQuery()->select('*')->from('#__content')
)->loadObjectList('id'); // ['1' => stdClass, '2' => stdClass, ...]
```

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

### DatabaseDriver String Safety Methods

Source: https://context7.com/joomla-framework/database/llms.txt

Explains how to use `quote()`, `quoteName()`, and `escape()` to protect against SQL injection by properly quoting and escaping values and identifiers.

```APIDOC
## DatabaseDriver::quote() / quoteName() / escape() — String safety

These methods protect against SQL injection. `quote()` wraps a value in single quotes with escaping; `quoteName()` wraps an identifier (table/column name) in the driver's quote character; `escape()` escapes special characters without quoting.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// quote() — for values in WHERE / INSERT / UPDATE clauses
$safe = $db->quote("O'Brien");        // "'O\'Brien'"
$safeArr = $db->quote(['foo', 'bar']); // ["'foo'", "'bar'"]

// LIKE clause: escape wildcards first, then quote without re-escaping
$search = "100% organic";
$likeSafe = $db->quote($db->escape($search, true) . '%', false);
// "'100\% organic%'"  — % is escaped, trailing % wildcard is preserved

// quoteName() — for table and column identifiers
$col  = $db->quoteName('user_name');        // '`user_name`' (MySQL) or '"user_name"' (PG)
$tbl  = $db->quoteName('#__content');       // '`app_content`' after prefix replacement
$expr = $db->quoteName('a.title', 'art');   // '`a`.`title` AS `art`'
$cols = $db->quoteName(['id', 'title'], ['art_id', 'art_title']);
// ['`id` AS `art_id`', '`title` AS `art_title`']

// Shorthand aliases
$db->q('value');          // same as $db->quote('value')
$db->qn('column_name');   // same as $db->quoteName('column_name')
$db->e('raw string');     // same as $db->escape('raw string')

// IN clause with array quoting
$titles = ['Article One', "O'Reilly Guide", 'PHP & You'];
$sql = 'SELECT id FROM #__content WHERE title IN (' . implode(',', $db->quote($titles)) . ')';
$db->setQuery($sql);
$ids = $db->loadColumn();
```
```

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

### Load First Row as Associative Array with loadAssoc()

Source: https://context7.com/joomla-framework/database/llms.txt

Use loadAssoc() to fetch the first row of a query result as an associative array, where keys are column names. This provides a more readable way to access data by column name.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// loadAssoc — first row as associative array
$assoc = $db->setQuery(
    $db->createQuery()->select('*')->from('#__content')->where('id = 1')
)->loadAssoc(); // ['id' => '1', 'title' => 'Hello World', ...]
```

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

### Build Conditional Clauses with where(), orWhere(), and extendWhere()

Source: https://context7.com/joomla-framework/database/llms.txt

Use these methods to append AND or OR conditions to your database queries. Chain `where()` for simple AND conditions. Use `orWhere()` and `extendWhere()` for complex logical groupings with mixed AND/OR operators. `extendWhere()` allows custom outer/inner glue for advanced logic.

```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// Simple AND conditions (chained)
$query = $db->createQuery()
    ->select('*')
    ->from('#__content')
    ->where('state = 1')
    ->where('catid = 5');
// WHERE state = 1 AND catid = 5

// OR grouping: WHERE ((state = 1 AND catid = 5) OR (state = 2 AND catid = 10))
$query2 = $db->createQuery()
    ->select('*')
    ->from('#__content')
    ->where(['state = 1', 'catid = 5'])
    ->orWhere(['state = 2', 'catid = 10']);

// extendWhere with custom outer/inner glue
// WHERE ((a = 1 AND b = 2) XOR (c = 3 OR d = 4))
$query3 = $db->createQuery()
    ->select('*')
    ->from('#__content')
    ->where(['a = 1', 'b = 2'])
    ->extendWhere('XOR', ['c = 3', 'd = 4'], 'OR');

// isNullDatetime — portable null/zero datetime check
$query4 = $db->createQuery()
    ->select('*')
    ->from('#__content')
    ->where($db->createQuery()->isNullDatetime('publish_down') . ' OR publish_down > ' . $db->quote(date('Y-m-d H:i:s')));

$db->setQuery($query2);
$results = $db->loadObjectList();
```

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

### DatabaseQuery::where() / orWhere() / andWhere() / extendWhere()

Source: https://context7.com/joomla-framework/database/llms.txt

Methods for appending conditional clauses to database queries, supporting simple AND conditions, OR groupings, and complex logical structures with custom glue.

```APIDOC
## DatabaseQuery::where() / orWhere() / andWhere() / extendWhere()

### Description
Appends AND conditions by default. Complex logical groupings with mixed AND/OR operators are built with `orWhere()`, `andWhere()`, and `extendWhere()`.

### Method
Chained method calls on a DatabaseQuery object.

### Parameters
- `where(string|array $conditions)`: Appends conditions. If an array, conditions are joined by AND.
- `orWhere(array $conditions)`: Appends conditions grouped by OR.
- `andWhere(array $conditions)`: Appends conditions grouped by AND.
- `extendWhere(string $glue, array $conditions, string $innerGlue = 'AND')`: Extends the query with custom logical grouping.

### Request Example
```php
$db = (new DatabaseFactory())->getDriver('mysqli', $options);

// Simple AND conditions (chained)
$query = $db->createQuery()
    ->select('*')
    ->from('#__content')
    ->where('state = 1')
    ->where('catid = 5');
// WHERE state = 1 AND catid = 5

// OR grouping: WHERE ((state = 1 AND catid = 5) OR (state = 2 AND catid = 10))
$query2 = $db->createQuery()
    ->select('*')
    ->from('#__content')
    ->where(['state = 1', 'catid = 5'])
    ->orWhere(['state = 2', 'catid = 10']);

// extendWhere with custom outer/inner glue
// WHERE ((a = 1 AND b = 2) XOR (c = 3 OR d = 4))
$query3 = $db->createQuery()
    ->select('*')
    ->from('#__content')
    ->where(['a = 1', 'b = 2'])
    ->extendWhere('XOR', ['c = 3', 'd = 4'], 'OR');

// isNullDatetime — portable null/zero datetime check
$query4 = $db->createQuery()
    ->select('*')
    ->from('#__content')
    ->where($db->createQuery()->isNullDatetime('publish_down') . ' OR publish_down > ' . $db->quote(date('Y-m-d H:i:s')));

$db->setQuery($query2);
$results = $db->loadObjectList();
```
```