Dev.Pi\Db\Adapter\Adapter

How to fetch this instance in Pi

You can use the following codes either to connect database belongs to other system, or database which configuration is in service.database.php for Pi:

// Connect database not belongs to Pi
$connectOptions = [
    'connection' => [
        'driver'    => 'pdo',
        'dsn'       => 'mysql:host=localhost;dbname=test',
        'username'  => 'test',
        'password'  => 'test',
    ],
];
$adapter = Pi::db($connectOptions)->adapter();

// Connect to Pi's database
$adapter = Pi::db()->adapter();

More:

• Zend\Db\Adapter\Profiler\Profiler

APIs

query

query($sql, $parametersOrQueryMode = self::QUERY_MODE_PREPARE, ResultSet\ResultSetInterface $resultPrototype = null)

Execute a sql.

Parameters

• sql

Sql statement.

• parametersOrQueryMode

Sql parameters or query mode. String indicates that this parameter is query mode, only Adapter::QUERY_MODE_PREPARE or Adapter::QUERY_MODE_EXECUTE can be used; array or ParameterContainer indicates it's sql parameters, and sql will execute in mode QUERY_MODE_PREPARE.

Return

Return value is instance of Driver\ResultInterface if sql is not a query sql, or ResultSet\ResultSet if sql is a query sql.

Examples

// Execute query sql directly, result will be ResultSet/ResultSet instance
$sql = 'select id from demo where active = 1';
$resultSet = $adapter->query($sql, 'execute');
$resultSet = $adapter->query($sql, Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);

// Execute sql directly, result will be Result instance
$sql = 'update demo set active = 1 where id = 2';
$result = $adapter->query($sql, 'execute');

// Execute prepare statement, sql is query, result will be Result instance
$sql = 'select id from demo where active = :active';
$parameterContainer = new \Zend\Db\Adapter\ParameterContainer(['active' => 1]);
$statement = $adapter->query($sql);
$statement->setParameterContainer($parameterContainer);
$result    = $statement->execute();
// Or 
$statement = $adapter->query($sql);
$result    = $statement->execute(['active' => 1]);
// Or, result is ResultSet/ResultSet
$resultSet = $adapter->query($sql, ['active' => 1]);

// Execute prepare statement, sql is not query, result will be Result instance
$sql = 'update demo set active = :active where id = :id';
$result = $adapter->query($sql, [
    'active' => 1,
    'id'     => 1,
]);

// Change ResultSet to array
$result = $resultSet->toArray();

// If result is Result, and sql is query, you can treat it as array
foreach ($result as $row) {
}

// If result is Result, and sql is not query, you can fetch affected rows by
$affectRows = $result->getAffectedRows();

Use prepare to execute multi sql

// These code will send prepare sql each execution
$sql = 'update demo set active = :active where id = :id';
$adapter->query($sql, ['active' => 1, 'id' => 1]);
$adapter->query($sql, ['active' => 0, 'id' => 2]);

// These code will send prepare sql only once, and bind parameters will be send each execution
$sql = 'update demo set active = :active where id = :id';
$statement = $adapter->query($sql);
$statement->execute(['active' => 1, 'id' => 1]);
$statement->execute(['active' => 0, 'id' => 2]);