编码为 JSON 的 PHP AJAX 数组不会在 HTML 表中按顺序显示，但在仅执行 MySQL 查询时按顺序显示

Question

我有这个MySQL查询，其中行根据month排序，并按ASCyear：

SELECT
CONCAT(MONTHNAME(diabetes_assessment.date_of_assessment), ' - ', YEAR(diabetes_assessment.date_of_assessment)) as assessmentDate,
MONTHNAME(diabetes_assessment.date_of_assessment) as monthNameAssessment,
YEAR(diabetes_assessment.date_of_assessment) as yearAssessment, 
MONTH(diabetes_assessment.date_of_assessment) as monthAssessment, 
AVG(diabetes_assessment.assessment_result) as avgAssessment
FROM 
diabetes_assessment
WHERE
diabetes_assessment.clinic_id = '361'
group by 
MONTH(diabetes_assessment.date_of_assessment), 
YEAR(diabetes_assessment.date_of_assessment)
ORDER BY yearAssessment, monthAssessment ASC

结果就像这个SQL小提琴上显示的那样，行正确显示并ASC排序。

| assessmentDate | monthNameAssessment | yearAssessment | monthAssessment | avgAssessment |
|----------------|---------------------|----------------|-----------------|---------------|
|   March - 2016 |               March |           2016 |               3 |          40.5 |
|   April - 2016 |               April |           2016 |               4 |            45 |
|   April - 2017 |               April |           2017 |               4 |            31 |
|     May - 2017 |                 May |           2017 |               5 |            40 |

现在我正在复制一些列，因为我将在chartJs中使用它们来显示一些图表。无论如何，当我使用 PHP 对行进行编码并通过 AJAX 发送它们并将它们显示在 html 表中时：

$cid = $_SESSION['clinic_id'];
$arrayResult = array();
$getAverage = "
SELECT
CONCAT(MONTHNAME(diabetes_assessment.date_of_assessment), ' - ', YEAR(diabetes_assessment.date_of_assessment)) as assessmentDate,
MONTHNAME(diabetes_assessment.date_of_assessment) as monthNameAssessment,
YEAR(diabetes_assessment.date_of_assessment) as yearAssessment, 
MONTH(diabetes_assessment.date_of_assessment) as monthAssessment, 
AVG(diabetes_assessment.assessment_result) as avgAssessment
FROM 
diabetes_assessment
WHERE
diabetes_assessment.clinic_id = :cid
group by 
MONTH(diabetes_assessment.date_of_assessment), 
YEAR(diabetes_assessment.date_of_assessment)
ORDER BY 
yearAssessment, 
monthAssessment ASC";
$execGetAverage = $conn->prepare($getAverage);
$execGetAverage->bindValue(':cid', $cid);
$execGetAverage->execute();
$result = $execGetAverage->fetchAll();
$i=0;
foreach($result as $res)
{
$arrayResult[$i] = $res;
$i++;
}
echo json_encode($arrayResult);

这里是 Ajax 脚本：

$.ajax({
url: '../php/getAssessmentResultByMonth.php',
dataType: 'JSON',
success:function(resp)
{
$.each(resp, function(key, result)
{
$("#after_tr").after("<tr><td>"+result['assessmentDate']+"</td><td>"+result['avgAssessment']+"</td></tr>")
});
},
error:function(resp)
{
console.log(resp);
}
})

现在，HTML 表中的结果不显示排序的行：

| assessmentDate | monthNameAssessment | yearAssessment | monthAssessment | avgAssessment |
|----------------|---------------------|----------------|-----------------|---------------|
|   March - 2016 |               March |           2016 |               3 |          40.5 |
|   April - 2017 |               April |           2017 |               4 |            31 |
|   April - 2016 |               April |           2016 |               4 |            45 |
|     May - 2017 |                 May |           2017 |               5 |            40 |

Answer 1

保留原始顺序 在 php 中将数组放入数组内

像这样改变

foreach($result as $res)
{
$arrayResult[$i][] = $res;
$i++;
}
echo json_encode($arrayResult);