查询数据并作为JSON字符串返回(Query data and return as a JSON string)

查询数据并作为JSON字符串返回(Query data and return as a JSON string)

我需要一些帮助，从表格中提取数据到Json。 我需要查询数据并返回符合WHERE语句的当前年份的所有记录，同时按MONTH对结果进行分组。

我的查询是：

$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);

然后我循环结果如下：

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]}]

我试图得到结果：

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

任何人都可以看到我做错了什么，或者我是以错误的方式接近这个。

非常感谢你的时间。

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.

最满意答案

您可以在SQL语句中完成大部分工作。 通过使用GROUP_CONCAT函数和DATE_FORMAT函数，您将最终得到一行，可以轻松地将JSON轻松转换为数组。 这是SQL语句：

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')

要从行创建数组，您需要使用GROUP_CONCAT拆分两列，因为它们将是逗号分隔的字符串。

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;

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;