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?

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

How does this comes? positions[1] => Array([xxx] => 777, [yyy] => 999, [zzz] => 000 ) — xkeshav
– xkeshav, Commented Aug 20, 2014 at 8:38
it doesn't, this is what i want to achieve :) atm i have severel selects and build this array with php. and this is just a sample array — Dwza
– Dwza, Commented Aug 20, 2014 at 8:40
But I think second array would be [666,888,111] instead of [777,999,000] — xkeshav
– xkeshav, Commented Aug 20, 2014 at 8:49
You are right, this was a typo. I have changed it. But as I sayed, its just a sample :) — Dwza
– Dwza, Commented Aug 20, 2014 at 8:52

Muhammad Raheel · Accepted Answer · 2014-08-20 08:56:31Z

3

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];
    }
}

answered Aug 20, 2014 at 8:56

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dwza Over a year ago

this IS one select but i won't get around the php formating with this right ? because my source almost looks the same... actully my php is a lil bit shorter. and in the end i just want to shrink my source

Collectives™ on Stack Overflow

Selecting Data into a Multidimensional array with PDO

1 Answer 1

Fiddle Demo

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related