result

Working with a query result

If a query executed by any of the available methods returns rows then that method will return an instance of \sad_spirit\pg_wrapper\ResultSet.

The class implements Iterator, Countable, and ArrayAccess SPL interfaces which allows easy iteration over the query result and access to specific rows:

echo "The query returned " . count($result) . " rows\r\n";
if (count($result) > 0) {
    echo "Id of the first row is " . $result[0]['id'] . "\r\n"; 
}
foreach ($result as $row) {
    // Do some stuff
}

Note that ArrayAccess is implemented read-only for obvious reasons, so trying to do something like

$result[0] = ['foo', 'bar'];
unset($result[1]);

will cause a BadMethodCallException.

Row format

The following methods are available to configure the returned rows: setMode() and setType(). The former accepts either PGSQL_ASSOC or PGSQL_NUM constants and controls whether rows are returned as associative arrays or arrays with numeric keys:

$result = $connection->executeParams(
    'select article_id, article_title from articles where article_id = $1',
    [13]
);

$result->setMode(PGSQL_ASSOC);
var_dump($result[0]);

$result->setMode(PGSQL_NUM);
var_dump($result[0]);

with the following output

array(2) {
  'article_id' =>
  int(13)
  'article_title' =>
  string(37) "Abusing sad-spirit/pg-wrapper package"
}
array(2) {
  [0] =>
  int(13)
  [1] =>
  string(37) "Abusing sad-spirit/pg-wrapper package"
}

The latter accepts a type specification so that a field would be properly converted:

$composite = $conn->execute("select ROW('fuzzy dice', 42, 1.99) as needstype");
$composite->setType('needstype', ['text', 'int4', 'float8']);
var_dump($composite[0]['needstype']);

which will output

array(3) {
  [0] =>
  string(10) "fuzzy dice" 
  [1] =>
  int(42)
  [2] =>
  double(1.99)
}

Note, though, that it is not usually needed to specify the field type as it will be deduced from database metadata and converted automatically.

Returning the whole result

Helper methods fetchAll() and fetchColumn() allow getting the whole result or one of its columns, respectively, as an array.

$result = $connection->execute('select article_id, article_title from articles order by article_id');
var_dump($result->fetchAll());
var_dump($result->fetchColumn('article_title'));

will output

array(2) {
  [0] =>
  array(2) {
    'article_id' =>
    int(12)
    'article_title' =>
    string(35) "Using sad-spirit/pg-wrapper package"
  }
  [1] =>
  array(2) {
    'article_id' =>
    int(13)
    'article_title' =>
    string(37) "Abusing sad-spirit/pg-wrapper package"
  }
}
array(2) {
  [0] =>
  string(35) "Using sad-spirit/pg-wrapper package"
  [1] =>
  string(37) "Abusing sad-spirit/pg-wrapper package"
}

fetchAll() also can return an array keyed with the values of some result column

$result->fetchAll(PGSQL_ASSOC, 'article_id');

will output

array(2) {
  [12] =>
  string(35) "Using sad-spirit/pg-wrapper package"
  [13] =>
  string(37) "Abusing sad-spirit/pg-wrapper package"
}