Return valid JSON from MySQL
Grabbing nested data from a MySQL
server is a bitch. You will have to use the GROUP_CONCAT
function - and there's no way to get around looping through and parsing your results. Usually when I need to this I will GROUP_CONCAT
the row IDs, and then run a query for each ID to get the properties I need.
This sucks, because more queries means longer execution time. This nifty solution will format your GROUP_CONCAT
field as a valid JSON array. Note: You'll still have to loop through your results and json_decode
that shiat.
SELECT
c.id testicle_id ,
c.`name` testicle_nickname ,
concat( '[' ,
GROUP_CONCAT(
'{ "id":' ,
u.id ,
', "first_name":"' ,
u.first_name ,
'", "last_name":"' ,
u.last_name ,
'", "gender":"' ,
u.gender ,
'", "likes_balls":' ,
u.likes_balls ,
', "likes_dicks":' ,
u.likes_dicks ,
', "teabagged_cnt":' ,
u.teabagged_cnt ,
', "teabag_start":"' ,
ca.teabag_start ,
'", "teabag_end":"' ,
ca.teabag_end ,
'"}'
),
']' ) teabagging_targets
FROM
teabag_tuesday c
INNER JOIN
teabagging_targets u ON u.id = c.user_id
WHERE
NOW() BETWEEN teabag_start AND teabag_end
AND ( likes_balls = 1 OR likes_dicks = 1 )
GROUP BY c.id
···