db_query group by to array

Question

I have the following query:

$query = "SELECT *
      FROM mytable
      {$typeClause}
      HAVING distance <= {$radius}
      ORDER BY distance ASC"
      GROUP BY type;

    $result = db_query($query);
    $points = array();

    while(($point = $result->fetchAssoc()) != null){
    }

In my db there is field 'type' which is a number
How can I get all records as an array without using the while statement above (something like fetchByGroup):

array(
        '1' => array( [all records of type=1] ),
        '2' => array( [all records of type=2] ),
        ......................................
    );

Answer 1

You cannot do this using native Drupal 7 functionality. You need to do that grouping in PHP. You should be using a foreach, not a while loop :)

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

Source: https://drupal.org/node/1251174

Answer 2

fetchAllAssoc($field) will do the magic for you.

$query = "SELECT *
      FROM mytable
      {$typeClause}
      HAVING distance <= {$radius}
      ORDER BY distance ASC"
      GROUP BY type;

    $result = db_query($query)->fetchAllAssoc('fieldname');