Selecting Data into a Multidimensional array with PDO

Question

Is it possible to make a select of some tables and push them into a Multidimensional array?

(i am using PDO fetch_all)

or do i have to make several selects ?

tables

TABLE xxx
 id | xxx | aaa | bbb | ccc
---------------------
  1 | 666 | 111 | 222 | 333
  2 | 777 | 444 | 555 | 666

TABLE positions
 xxx | yyy | zzz
----------------
 666 | 999 | 000
 666 | 888 | 111
 777 | 999 | 000
 777 | 888 | 222
 777 | 777 | 333
--^ //forenerkey from table xxx

What I want to achieve is

Array
(
    [0] => Array
        (
            [id] => 1
            [aaa] => 111
            [bbb] => 222
            [ccc] => 333
            [xxx] => 666
            [positions] => Array(
                           [0] => Array(
                                    [xxx] => 666
                                    [yyy] => 999
                                    [zzz] => 000
                                )
                           [1] => Array(
                                    [xxx] => 666
                                    [yyy] => 888
                                    [zzz] => 111
                                )
                         )

        )

    [1] => Array
        (
            [id] => 2
            [aaa] => 222
            [bbb] => 333
            [ccc] => 444
            [xxx] => 777
            [positions] => Array(
                           [0] => Array(
                                    [xxx] => 777
                                    [yyy] => 999
                                    [zzz] => 000
                                )
                           [1] => Array(
                                    [xxx] => 777
                                    [yyy] => 888
                                    [zzz] => 222
                                )
                           [2] => Array(
                                    [xxx] => 777
                                    [yyy] => 777
                                    [zzz] => 333
                                )
                         )
         )

)

Atm I select like this

select * from xxx x, position p where x.xxx=p.xxx

SQLFiddle

But the result is not as I like it. BTW, I don't even know how this should look like in mysql result ^^. Have to do a lot of afterwork to make the array like I want it in the end. Actually I have all done but I really would like to shrink my source :D

Answer 1

You can have this alternative

Query

select
  x.*,
  p.xx as xpositions,
  p.yy as ypositions,
  p.zz as zpositions
from xxx x
  LEFT JOIN (SELECT
           xxx,
           GROUP_CONCAT(xxx) as xx,
           GROUP_CONCAT(yyy)    yy,
           GROUP_CONCAT(zzz)    zz
         FROM position
         group by xxx) p
    on p.xxx = x.xxx
GROUP BY x.xxx;

OUTPUT

| ID | XXX | AAA | BBB | CCC |  XPOSITIONS |  YPOSITIONS | ZPOSITIONS |
|----|-----|-----|-----|-----|-------------|-------------|------------|
|  1 | 666 | 111 | 222 | 333 |     666,666 |     999,888 |      0,111 |
|  2 | 777 | 444 | 555 | 666 | 777,777,777 | 999,888,777 |  0,222,333 |

Fiddle Demo

On php end you can handle it like this

foreach($rows as $row) {
    $ID     =   $row['ID'];
    $XXX    =   $row['XXX'];
    $BBB    =   $row['BBB'];
    $CCC    =   $row['CCC'];
    // explode with php
    $XPOSITIONS =   explode(',',$row['XPOSITIONS']);
    $YPOSITIONS =   explode(',',$row['YPOSITIONS']);
    $ZPOSITIONS =   explode(',',$row['ZPOSITIONS']);

    for($i=0;$i<count($XPOSITIONS);$i++){
        $x_position =   $XPOSITIONS[$i];
        $y_position =   $YPOSITIONS[$i];
        $z_position =   $ZPOSITIONS[$i];
    }
}