Is there a way to group a "key-value store data" by ID in a multidimensional array?

Question

SQL Tables:

Table 1: App
ID | Name      | Release
1  | TinyWings | 2014
2  | WarCraft  | 2012
3  | StarCraft | 1998

Table 2: AppInfo
ID | Key       | Value
1  | Genre     | Casual
1  | Rating    | 0+
1  | System    | iOS
...
2  | Genre     | RTS
2  | Rating    | 6+
2  | System    | Win
...
3  | Genre     | RTS
3  | Rating    | 12+
3  | System    | Win
...

For the profil php page of the App i use

$app = $statement->fetchAll(PDO::FETCH_KEY_PAIR);

but on the index.php page, i need an array something like this for pagination:

Array (
    [0]=> Array (
              [Genre]=>[Casual]
              [Rating]=>[0+]
              [System]=>[iOS]
              ...)
    [1]=> Array (
              [Genre]=>[RTS]
              [Rating]=>[6+]
              [System]=>[Win]
              ...)
    [2]=> Array (
              [Genre]=>[RTS]
              [Rating]=>[12+]
              [System]=>[Win]
              ...)
)

the only way i have found is to use

MAX(CASE WHEN ... THEN .. END) AS ...

for EVERY KEY in the prepare statement to write the rows into columns, is there a way to use it with PDO::FETCH_KEY_PAIR or an better and easier way?

score 0 · Answer 1 · edited Dec 12 '17 at 00:57

You can do it, but really you should restructure your table into one which uses your keys as column names as it would make your life easier.

You can get the data the way you want with the following query, but it's a bit clumsy:

select a.id, a.value as 'genre', b.value as 'rating', c.value as 'system'
from Table1 a, Table1 b, Table1 c
where (a.key = 'genre' AND b.key = 'rating' and c.key = 'system') AND (a.id = b.id AND b.id = c.id)
group by a.id

Heres a demo

Is there a way to group a "key-value store data" by ID in a multidimensional array?

1 Answers1