CODEWITHSUNDEEP
phpmysqljson
Morts81
Morts81

Reputation: 439

MySQL to JSON via PHP

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)

Upvotes: 0

Views: 77

Answers (3)

stefo91
stefo91

Reputation: 618

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

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

Upvotes: 1

Amitabh Das
Amitabh Das

Reputation: 403

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

Upvotes: 0

Sarfaraz
Sarfaraz

Reputation: 146

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


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

Upvotes: 1

Related Questions