MySQL to JSON via PHP

Question

Let me preface this with I know this question has been asked plenty of times on here (ie here among other pages) and I've trawled through each of the answers and tried the solutions but keep getting an empty json array.

I've got a mysql query as per below:

$myQuery = $wpdb->get_results('SELECT player_name, player_team, 
SUM(ruckContests) AS contests FROM afl_master 
WHERE player_team = "COLL" AND year = 2019 AND ruckContests > 0 
GROUP BY player_id ORDER BY contests DESC LIMIT 2');

The output to this query is as follows:

Array ( [0] => stdClass Object ( [player_name] => Brodie Grundy [player_team] => COLL [contests] => 661 ) [1] => stdClass Object ( [player_name] => Mason Cox [player_team] => COLL [contests] => 51 ) )

What I want to do is convert this to the following json object in php:

{
 player_name: "Brodie Grundy", 
 player_team: "COLL", 
 contests: 661
},
{
 player_name: "Mason Cox", 
 player_team: "COLL", 
 contests: 51
}

I've tried mysql_fetch_array() as well as fetch_assoc() using the method here but to no avail. I'd appreciate any help available.

This simple solution recommended in the comments worked:

json_encode($myQuery)

Oh my god, it was that simple. I was trying to pre-process it then use json_encode(). Thankyou so much. — Morts81, May 22 '19 at 12:13

score 1 · Answer 1 · answered May 22 '19 at 12:01

$JsonData=array();
foreach($myQuery as $val){


            $JsonData[]='
               {
                 "player_name": "'.$val->player_name.'", 
                 "player_team": "'.$val->player_team.'", 
                 "contests": $val->contests 
               }
             ';
}
$expectedJson=implode(',',$JsonData);

stefo91 · Answer 2 · 2019-05-22T13:39:38.383

1

Just cast your result to array with fetch_array() or (array) $myQuery then:

json_encode($myQuery);
json_last_error(); // to see the error

edited May 22 '19 at 13:39

answered May 22 '19 at 13:32

stefo91

618
6
16

score 0 · Answer 3 · answered May 22 '19 at 13:25

You can do it in single SQL Statement (I have just formatted it in multiple lines for better understanding). The inner Select statement uses your query as a sub-query, while using JSON_OBJECT to generate JSON object of each row, finally using GROUP_CONCAT to combine each row with a comma, and then add the "[" and "]" to the final result for creating a perfect JSON array. (Just another way, apart from the json_encode($myQuery), the simpler version :) ).

SELECT CONCAT('[', 
    GROUP_CONCAT( 
        JSON_OBJECT( 
            'player_name',   player_name, 
            'player_team' ,  player_team, 
            'contests', contests) 
    ) , ']') as json  
FROM (
        SELECT  player_name,  player_team, SUM(ruckContests) as contests
        FROM afl_master 
        WHERE player_team = "COLL" AND year = 1995 AND ruckContests > 0 
        GROUP BY player_name, player_team 
        ORDER BY SUM(ruckContests) DESC 
        LIMIT 2 
) AS TT

MySQL to JSON via PHP

3 Answers3