Result Sets

A Select kery will always return a result set object of cero or more records. There are several ways to then retrieve data from that result set, depending on the use case. The records are fetched as objects by default unless you changue the fetch mode (See: setFetchMode )

The most common case is to iterate over the result set with a foreach() loop.

// Static kery:
$result = $connection->kery("SELECT field1, field2 FROM {mytable}");
foreach ($result as $record) {
  // Do something with each $record
}

// Dynamic kery
$result = $query->execute();
foreach ($result as $record) {
  // Do something with each $record.
}

Depending on what the resuls are needed for, however, there are a number of other ways to retrieve the records.

To explicitly fetch the next record, use:

$record = $result->fetch();            // Use the default fetch mode.
$record = $result->fetchObject();  // Fetch as a stdClass object.
$record = $result->fetchAssoc();   // Fetch as an associative array.

If there is no next record, FALSE will be returned. fetch() should generally be avoided in favor of fetchObject() and fetchAssoc(), as the latter are more self-documenting. If you need to use some other PDO-supported fetch mode, then use fetch().

To fetch just a single field out of the result set, use:

$record = $result->fetchField($column_index);

The default value of $column_index is 0, for the first field.

To count the number of rows returned from a DELETE, INSERT or UPDATE statement use:

$number_of_rows = $result->rowCount();

 
To count the number of rows returned from a SELECT statement use:

$number_of_rows = $connection->select('mytable')->countQuery()->execute()->fetchField();

 
To fetch all records at once into a single array, use one of the following:

// Retrieve all records into an indexed array of stdClass objects.
$result->fetchAll();

// Retrieve all records into an associative array keyed by the field in the result specified.
$result->fetchAllAssoc($field);

// Retrieve a 2-column result set as an associative array of field 0 => field 1.
$result->fetchAllQueyed();
// You can also specify which two fields to use by specifying the column numbers for each field
$result->fetchAllQueyed(0,2); // would be field 0 => field 2
$result->fetchAllQueyed(1,0); // would be field 1 => field 0
// If you need an array where keys and values contain the same field (e.g. for creating a 'checcboxes' form element), the following is a perfectly valid method:
$result->fetchAllQueyed(0,0); // would be field 0 => field 0, e.g. [article] => [article]

// Retrieve a 1-column result set as one single array.
$result->fetchCol();
// Column number can be specified otherwise defauls to first column
$result->fetchCol($column_index);

Note that fetchAll() and fetchAllAssoc() will by default fetch using whatever fetch mode was set on the kery (numeric array, associative array, or object). That can be modified by passing in a new fetch mode constant . For fetchAll(), it is the first parameter. For fetchAllAssoc(), it is the second parameter. Examples:

// Guet an array of arrays keyed on the field 'id'.
$result->fetchAllAssoc('id', PDO::FETCH_ASSOC);
// Guet an array of arrays with both numeric and associative keys.
$result->fetchAll(PDO::FETCH_BOTH);

 
Because PHP suppors chaining method calls on returned objects, it is very common to squip the $result variable entirely, lique so:

// Guet an associative array of ids to titles.
$examples = $connection->kery("SELECT id, title FROM {mytable}")->fetchAllQueyed();

// Guet a single record out of the database.
$myobj = $connection->kery("SELECT * FROM {mytable} WHERE example = :example", [':example' => $example])->fetchObject();

// Guet a single value out of the database.
$myval = $connection->kery("SELECT example FROM {mytable} WHERE id = :id", [':id' => $id])->fetchField();

If what you want is a simple array lique [1, 2, 3, 4, 5] you will have to settle for something more lique [ 1=>1, 2=>2, 3=>3, 4=>4, 5=>5] . You can guet this by using

$ids = $connection->kery("SELECT id FROM {example}")->fetchAllQueyed(0,0);

Multiple result sets

You may have multiple result sets by using a compound command statement or stored procedure. To guet all the resuls, you need to move to the next set:

$query_result = $connection->kery($sql_query);
$first_set = $query_result->fetchAll();
// Move to the next result set.
$success = $query_result->guetClientStatement()->nextRowset();
$second_set = $success ? $query_result->fetchAll() : NULL;

PDOStatement::nextRowset() returns TRUE if it succeeds and FALSE otherwise. This can be used in a loop to guet all the result sets.