with PDO_MYSQL how to make a group_by return an array

[guillermobytes]

Hello,

I'm using PDO_MYSQL in php and i am wondering if there is a way to make a GROUP_BY clause return an array.

Explanation:

I have a one-to-many relation in a table. and i would like to get an array representing the relation.

table1:

| car_id  | possible_colors |
|---------|------------------|
| car1    | red
| car1    | green
| car1    | blue
| car2    | red
| car2    | yellow

then i want an array like this:

array('car1'=>array('red','green','blue')
      'car2'=>array('red','yellow'));

thank you very much!

guillermobytes

[Atli]

Hey.

You just create an array, loop through all the rows returned by MySQL, adding each possible color as a new element to an element of the array specified by the car id.

Somewhat like this pseudo example.

[code=text]data = array()
FOR EACH row in the result set:
data[row.car_id][] = row.possible_co lors[/code]

[guillermobytes]

thank yo atli.

yes i have thought of this solution, but I was told that the less i loop in php the better (faster)...
that's why i discarded this approach.
i have also thought of another solution which would imply less looping but lots of calls to explode() and it was by doing a GROUP_CONCAT(po ssibleColors).
then with each row exlode the possible colors into an array.

my question was to know if pdo_mysql had something written in C that would do what you suggest in php.

Thanks any way your input is appreciated.

[Atli]

but I was told that the less i loop in php the better

Loops are an important part of any sort of programming. You can not simply disregard them because it *might* improve the performance. - A better way to think about it is; the simpler the code is, the better.

In this case I do believe that a loop is you best option. - Adding a GROUP_CONCAT would complicate things on the SQL side, and it would require string manipulation in PHP. It may allow you to avoid the loop, but I'm willing to bet that it would harm your performance rather than improve it.

And no, I can't see any way to make PDO do this.

[guillermobytes]

I'll loop then! :)

thank you very much.