时间戳

首页 > 解决方案 > 从 PHP 循环中创建 JSON 数组

php - 从 PHP 循环中创建 JSON 数组

问题描述

在我当前的项目中，我想用一个月中每一天的汇总数据填充条形图。我从数据库中获取了所需的值，但无法以正确的格式创建数组。到目前为止，各个值只是排列在一起，我无法在 JS 中读出它们。

我试图以正确的结构构建数组，但是我只得到了本月的最后一天。

如何使用正确的语法在一个输出中获取所有值？

如果像下面的代码那样做，我得到了这个输出。在这里我得到了所有的值，但我不能在我的 JS 代码中使用它们：

[0,0,0,0,0,5,10,7,0,0,10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]

如果我使用输入的代码（在代码的末尾）执行此操作，则输出看起来像。在这里，我只得到了本月的最后一个日期：

{"training_date":"2021-08-31","distance":10}

我想要的输出看起来像：

[
    {
        "training_date":"01.08.2021",
        "distance": "15"
    },
    {
        "training_date":"02.08.2021",
        "distance": "0"
    }
    ,
    {
        "training_date":"03.08.2021",
        "distance": "25"
    }
    ,
    {
        "training_date":"04.08.2021",
        "distance": "15"
    }
    ,
    .....
]

我的PHP代码：

<?php 
    header('Content-Type: application/json');

    include "../../../includes/db.php";

    if(isset($_GET['user_id'])) {
        $user_id = $_GET['user_id'];
    }

    $month = date("m");
    $year = date("Y");

    $daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, $year);

    $free = 6; $injury = 7;

    $data = array();

    for($i = 1; $i < $daysInMonth + 1; $i++) {
        
        $training_date = $year.'-'.$month.'-'.$i; 
        
        $get_user_trainings = $connection->prepare("SELECT * FROM training 
                                                    INNER JOIN training_content ON training.training_id = training_content.training_id
                                                    WHERE training.user_id = ?
                                                    AND training.training_date = ?
                                                    AND training.training_art_id != ?
                                                    AND training.training_art_id != ?
                                                   ");
        $get_user_trainings->bind_param("ssss", $user_id, $training_date, $free, $injury);
        $get_user_trainings->execute();
        $trainings = $get_user_trainings->get_result();
        $total_trainings = $trainings->num_rows;

        if($total_trainings >= 1) {

            $total_distance = 0;
            foreach($trainings as $row) {
                $training_art_id = $row['training_art_id'];
                $training_content_id = $row['training_content_id'];

                if($training_art_id == 1) {
                    ....

                    $total_distance += $training_intervall_length;
                    $total_distance += $training_intervall_pause_length;
                    $total_distance += $training_warmup_length;
                    $total_distance += $training_cooldown_length;                    
                } else if($training_art_id == 2) {
                    ....
                } else if($training_art_id == 3) {
                    ....

                    $total_distance += $training_speedwork_length;
                    $total_distance += $training_warmup_length; 
                    $total_distance += $training_cooldown_length;
                } else if($training_art_id == 5) {
                    ....

                    $total_distance += $training_competition_length;
                    $total_distance += $training_warmup_length; 
                    $total_distance += $training_cooldown_length;
                }
            }

            $total_distance = $total_distance / 1000;

            // $data["training_date"] = $training_date;
            // $data["distance"] = $total_distance;

            $data[] = $total_distance;
        } else {
            
            // $data["training_date"] = $training_date;
            // $data["distance"] = $total_distance;

            $data[] = $total_trainings;
        }
    }

    echo json_encode($data);
?>

标签： phpmysqlarrays

解决方案

将新的关联数组添加到数据列表中，而不是仅将值添加到平面数组中：

$data = [];
for (..) {
    // minimal example - more code to populate $date and $distance here
    $data[] = [
        'training_date' => $date,
        'distance' => $total_distance
    ];
}
echo json_encode($data);

此外，正如评论中已经建议的那样，SQL 可以通过在查询中使用GROUP BY和为您总结距离：SUM()

SELECT user_id, training_date, training_art_id, SUM(distance) AS total_distance
FROM training 
    INNER JOIN training_content 
        ON training.training_id = training_content.training_id
WHERE training.user_id = ?
    AND training.training_art_id != ?
    AND training.training_date BETWEEN ? AND (? - INTERVAL 1 MONTH)
GROUP BY user_id, training_date, training_art_id

请注意，我还将training_date条件更改为 1 个月范围，因为您所需的示例输出包含多个日期。

您还可以在多个字段上构建 SUM：

SELECT ..., (SUM(warmup) + SUM(cooldown) + SUM(..)) AS total_distance

当你像上面那样查询数据库时，结果记录集已经有了你想要输出的形式，所以它可以很简单：

$trainings = $get_user_trainings->get_result();
echo json_encode($trainings);

推荐阅读