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

Question

我有一个MySQL查询，其中行根据month排序，year根据ASC排序

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

结果类似于this SQL Fiddle中显示的结果，行被正确地显示并排序为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);