PHPize Online / SQLize Online  /  SQLtest Online

<?php $query = "select * from equipment"; $stmt = $pdo->prepare($query); $stmt->execute(); $equipments = $stmt->fetchAll(PDO::FETCH_ASSOC); /*$source = [ [ "id" => 222, "equipment_id" => 141490, "datetime" => "2021-02-07 23:08:07", "distance" => 5.48, "speed" => 0.02 ], [ "id" => 223, "equipment_id" => 141490, "datetime" => "2021-02-07 23:10:08", "distance" => 6.09, "speed" => 0.02 ], [ "id" => 224, "equipment_id" => 141490, "datetime" => "2021-02-07 23:11:01", "distance" => 6.36, "speed" => 0.02 ], [ "id" => 418, "equipment_id" => 141491, "datetime" => "2021-02-07 22:05:38", "distance" => 341.65, "speed" => 0 ], [ "id" => 419, "equipment_id" => 141491, "datetime" => "2021-02-07 22:06:03", "distance" => 341.65, "speed" => 0 ], [ "id" => 1065, "equipment_id" => 141491, "datetime" => "2021-02-08 04:55:46", "distance" => 172.12, "speed" => 1.24 ], [ "id" => 1066, "equipment_id" => 141491, "datetime" => "2021-02-08 04:56:22", "distance" => 184.51, "speed" => 0.72 ] ]; */ $result = array_reduce( $equipments, function($res, $el) { if(!is_array($res[$el["equipment_id"]])) { $res[$el["equipment_id"]] = [ "equipment_id" => 141491, "speed" => [] ]; } array_push( $res[$el["equipment_id"]]["speed"], [ "datetime" => $el["datetime"], "value" => $el["speed"], "distance" => $el["distance"], ] ); return $res; }, [] ); var_export(array_values($result));