我需要一些帮助,从表格中提取数据到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;更多推荐
发布评论