olaf olaf - 6 months ago 6
SQL Question

Join Rows from one huge SQL Table

Many Questions like this are already on stackoverflow but with my min knowledge of SQL i can not put it together.

My table looks like this. I have 1000 Player_Id's and 200 Stat_Id's

PlayerID | StatsID | StatValue
==================================
1 | 1 | 99
1 | 2 | 31
1 | 3 | 1
2 | 1 | 102
2 | 2 | 1
2 | 3 | 33
3 | 1 | 0
3 | 2 | 1
3 | 3 | 3,4


And I would like to get a array of users where one User object looks like this:

{playerId:1, stats:{"1":99, "2":31, "3":1, ...}}


I have tried a lot but nothing is close to what I want to get done. With this SQL i got my best result:

SELECT `PlayerID`,
GROUP_CONCAT(`StatsID` SEPARATOR ', ')
FROM `player_stats`
WHERE 1 group by `PlayerID`


Here is the Result:

Array
(
[0] => Array
(
[PlayerID] => 15895
[stats] => 1, 2, 3
)

[1] => Array
(
[PlayerID] => 21307
[stats] => 1, 2, 3
)


How can I bring the StatValue Columns into my Query?
Would it be better to generate the desired object after the SQL Query or with multiple Queries?

Performance is not very crucial but of course it would be nice if generating the "playerId-array" would not take ages.

Thanks!

Answer

You can actually just add the columns to the GROUP_CONCAT():

SELECT `PlayerID`, GROUP_CONCAT(`StatsID`, ':', StatValue SEPARATOR ', ') 
FROM `player_stats` 
GROUP BY `PlayerID`;
Comments