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
– Morts81, Commented May 22, 2019 at 12:13

Sarfaraz · Accepted Answer · 2019-05-22 12:01:15Z

1

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


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

answered May 22, 2019 at 12:01

Sarfaraz

1468 bronze badges

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

Comments

stefo91 · Accepted Answer · 2019-05-22 13:39:38Z

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, 2019 at 13:39

answered May 22, 2019 at 13:32

stefo91

6166 silver badges17 bronze badges

Comments

Amitabh Das · Accepted Answer · 2019-05-22 13:25:25Z

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

Collectives™ on Stack Overflow

MySQL to JSON via PHP

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related