CODEWITHSUNDEEP
phpmysqljson
DCJones
DCJones

Reputation: 3461

Query data and return as a JSON string

I need some help in extracting data from a tabel into Json. I need to query the data and return all the records for the current year that meets the WHERE statement and at the same time group the results by MONTH.

What I have as the query is:

$query_Promoter = "
SELECT COUNT(RecordID) AS Score4, FeedBackDate  
FROM ".$FeedBack." 
WHERE FeedBackDate >= DATE_SUB(NOW(),INTERVAL 1 YEAR)
    AND A = 4
    OR B = 4 
    OR C = 4 
    OR D = 4 
    OR E = 4 
    OR F = 4 
    OR G = 4 
    OR H = 4 
    OR L = 4
    OR M = 4 
    OR N = 4 
GROUP BY MONTH(FeedBackDate)";
$Promoter =$conn->query($query_Promoter);
$totalRows_Promoter  = mysqli_num_rows($Promoter);

I then loop through the result like:

if($totalRows_Promoter > 0) {
    $rows_Promoter  = array();
    $rows_Promoter ['name'] = 'Promoters';
    while($row_Promoter  = mysqli_fetch_array($Promoter )) {
        $rows_Promoter['Month'][] = date("M", strtotime($row_Promoter['FeedBackDate']));
        $rows_Promoter['data'][] = $row_Promoter['Score4'];
    }
}

$result = array();

if($totalRows_Promoter > 0) {
    array_push($result,$rows_Promoter);
}


print json_encode($result, JSON_NUMERIC_CHECK);

// The resulting JSON is:

[{"name":"Promoters","Month":["Jan","Jan","Jan","Jan"],"data":[3,10,17,1]}]

I am trying to get the result as :

[{"name":"Promoters","Month":["Jan","Feb","May","Jun"],"data":[3,10,17,1]}]

Can anyone see what I am doing wrong or am I approaching this the wrong way.

Many thanks in advance for your time.

Upvotes: 0

Views: 40

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

You can do most of the work in the SQL statement. By using the GROUP_CONCAT function and the DATE_FORMAT function, you will end up with one row that can easily be turned into you array for JSON easily. Here's the SQL Statement:

SELECT 
    'Promoters' as `name`,
    GROUP_CONCAT(DATE_FORMAT(`FeedBackDate`,'%b')) as `Month`,
    GROUP_CONCAT(COUNT(`RecordID`)) AS `data`
FROM $FeedBack
WHERE `FeedBackDate` >= DATE_SUB(NOW(),INTERVAL 1 YEAR)
    AND (A = 4
        OR B = 4 
        OR C = 4 
        OR D = 4 
        OR E = 4 
        OR F = 4 
        OR G = 4 
        OR H = 4 
        OR L = 4
        OR M = 4 
        OR N = 4)
ORDER BY DATE_FORMAT(`FeedBackDate`,'%b')

To create the array from the row, you'll need to split up the two columns with the GROUP_CONCAT since they'll be comma separated strings.

if($totalRows_Promoter > 0) {
    $rows_Promoter  = array();
    $rows_Promoter['name'] = 'Promoters';
    //  Should only have one row in results
    $row_Promoter  = mysqli_fetch_array($Promoter );
    $rows_Promoter['Month'] = explode(',',$row_Promoter['Month']);
    $rows_Promoter['data'] = explode(',',$row_Promoter['data']);
}

$result = json_encode($result, JSON_NUMERIC_CHECK);

echo $result;

Upvotes: 1

Related Questions