db_select() returns an array

Question

I have just started using Drupal 7 and db_select().

if I have a result from db_select() (e.g. $result = $query->execute(), why can I only loop through this result once?

It appears as if there is a cursor that is not reset following a foreach loop.

So I tried to find a function that would convert this result into a multi dimensional array (e.g. $result = $result->fetchAllAssoc()) but I have found nothing.

Instead, at the moment I have to loop through the result to create an array of row objects to use later through the code (if i need to loop through it more than once)

/* this returns an array of row objects that you can use like any other array but seems as though there should be an API function to do this for me as part of the database class ? */

$array = array();
foreach($result as $row) {
  $array[] = $row;
}

Surely there must be an API function to do this already.

Answer 1

I don't have a Drupal 7 dev environment handy to try this on, but I think you are looking for fetchAllAssoc(), as you mentioned. This page has some query examples that are helpful.

When you call fetchAllAssoc(), you need to specify a field name as the primary key of the array, like:

 $result->fetchAllAssoc($field);

It also appears that fetchAll() will work (but apparently it is not the recommended solution).

Answer 2

Yes there is an issue with fetchAll() or fetchAssoc() you havent use either of it just writing execute() will give u only one result that will be something like below

[dbh] => DatabaseConnection_mysql Object
    (
        [shutdownRegistered:protected] => 
        [target:protected] => default
        [key:protected] => default
        [logger:protected] => 
        [transactionLayers:protected] => Array
            (
            )

        [driverClasses:protected] => Array
            (
                [SelectQuery] => SelectQuery
                [UpdateQuery] => UpdateQuery
                [InsertQuery] => InsertQuery_mysql
            )

        [statementClass:protected] => DatabaseStatementBase
        [transactionSupport:protected] => 1
        [transactionalDDLSupport:protected] => 
        [temporaryNameIndex:protected] √ 0
        [connectionOptions:protected] => Array
            (
                [database] => pcl
                [username] => inhouse
                [password] => abcd!234
                [host] => localhost
                [port] => 
                [driver] => mysql
                [prefix] => Array
                    (
                        [default] => 
                    )

            )

        [schema:protected] => 
        [prefixes:protected] => Array
            (
                [default] => 
            )

        [prefixSearch:protected] => Array
            (
                [0] => {
                [1] => }
            )

        [prefixReplace:protected] => Array
            (
                [0] => 
                [1] => 
            )

    )

    [queryString] => SELECT u.uid AS uid, u.name AS name
      FROM profile p
      INNER JOIN users_roles ur ON p.uid = ur.uid
      INNER JOIN field_data_field_client fcl ON p.pid = fcl.entity_id
      INNER JOIN field_data_field_category fca ON p.pid = fca.entity_id
      INNER JOIN users u ON p.uid= u.uid
      WHERE  (ur.rid = :db_condition_placeholder_0) AND (fca.field_category_tid = :db_condition_placeholder_1) AND (fca.bundle = :db_condition_placeholder_2) AND (fcl.field_client_tid = :db_condition_placeholder_3) 
      GROUP BY ur.uid
    )

So the solution is to use either fetchAll(), or fetchAssoc() in case you want a key => value pair array.