Creating JSON Array out of PHP loop

Question

in my current project I want to fill a bar chart with summed data for each day of a month. I get the required values from the database, but I can't get the array created in the right format. So far, the individual values are just lined up and I can't read them out in the JS.

I have tried to build the array in the right structure, but then I only get the last day of the month.

How do I get all the values in one output with the right syntax?

If do it like in the code below i got this output. Here I got all the values but i cant use them in my JS code:

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

If I do it with the typed out code (on the end of the code) the output looks like. Here i only got the last date of the month:

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

The output that i want looks like:

[
    {
        "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"
    }
    ,
    .....
]

My PHP code:

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

Answer 1

Add a new associative array to the data list instead of only adding the values to your flat array:

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

Also, as already suggested in the comments, SQL can summarize the distances for you by using GROUP BY and SUM() in your query:

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

Note that i also changed the training_date condition to a 1 month range, because your desired example output contains more than a single date.

You can also build a SUM over multiple fields:

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

When you query the database like above, the resulting recordset already has the form you want to output, so it could be as easy as:

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