Query data and return as a JSON string

Question

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.

Please try adding "Y" before the "M" in your date("M") conversion (the strtotime line), let's see what we got from MySQL as FeedBackDate. I suspect it's something that can't be parsed by strtotime (so it returns zero and you get the month of Epoch) so let's see if you get 1970 Jan or what... — dkellner
– dkellner, Commented Jul 9, 2017 at 11:33
@dkellner this is the JSON by removing (date("M",strtotime ....... )), [{"name":"Promoters","Month":["2017-01-29","2017-02-27","2017-05-30","2017-06-04"],"data":[3,10,17,1]}] — DCJones
– DCJones, Commented Jul 9, 2017 at 11:46
Would you give date("Y-M") a try anyway? Just...... can't imagine how it's all parsed and still all January. — dkellner
– dkellner, Commented Jul 9, 2017 at 12:15
@dkellner I have been messing around with the code that much I am not sure what I have done but now I have the result I was looking for. [{"name":"Promoters","Month":["Apr","May","Jun","Jul"],"data":[3,10,17,1]}] — DCJones
– DCJones, Commented Jul 9, 2017 at 12:16

Sloan Thrasher · Accepted Answer · 2017-07-09 11:46:52Z

1

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;

edited Jul 9, 2017 at 11:46

answered Jul 9, 2017 at 11:35

Sloan Thrasher

5,0383 gold badges26 silver badges44 bronze badges

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

5 Comments

DCJones Over a year ago

thanks for your help. Your query errors with 'Invalid use of group function', can you see why?

Sloan Thrasher Over a year ago

On review, I can see why. Also the group by shouldn't be on the months since we want to collect them all in one month. Try removing the group by entirely so it returns one aggregated row.

Sloan Thrasher Over a year ago

Oops! typo in comment above. "all in one month" should be "all in one row"

DCJones Over a year ago

I removed 'GROUP_CONCAT(COUNT(RecordID)) AS data' and the result is [{"name":"Promoters","Month":["Apr","Apr","Apr"],"data":[3]}], all the wrong months.

DCJones Over a year ago

thanks for all your input, but for whatever reason using my original query I now have the result I was looking for. [{"name":"Promoters","Month":["Apr","May","Jun","Jul"],"data":[3,10,17,1]}]. But again many thanks for all yor time.

Collectives™ on Stack Overflow

Query data and return as a JSON string

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related