如何从 PHP 中的更多 mySQL 行中获取平均值？

Question

我不懂任何 PHP，但我需要一个 API。我的朋友做了一个，但我需要平均时间戳（当然，如果查询发现不止一行）。从我的 mysql 数据库中，我以字符串形式获得时间戳 (00:34:51)。

我想我知道该怎么做，但是我在 PHP 方面太无能了，以至于我无法自己修复它。

这是网络 API

<?php

// import connect

require_once '../db_connect.php';

// api input

$name = $_POST['name'];

$country = $_POST['country'];

$race = $_POST['race_type'];

$return = [];

// execute query

$stmt = $conn->prepare("SELECT Swim,Bike,Run,Overall FROM test WHERE Name = ? AND Country = ? AND Race = ?");

$stmt->bind_param("sss", $name, $country, $race);

$stmt->execute();

$result = $stmt->get_result();

// search in data

while ($row = $result->fetch_assoc()) {

    if ($row['Swim'] < $row['Bike'] && $row['Swim'] < $row['Run']) {

        $best = $row['Swim'];

    } else if ($row['Bike'] < $row['Swim'] && $row['Bike'] < $row['Run']) {

        $best = $row['Bike'];

    } else {

        $best = $row['Run'];

    }

    array_push($return, [

        'swim' => $row['Swim'],

        'bike' => $row['Bike'],

        'run' => $row['Run'],

        'overall' => $row['Overall'],

        'best' => $best

    ]);

}

// return data

echo json_encode($return);

$stmt->close();

$conn->close();

die();

这就是 mySQL 数据库中一行的样子

ID  Race    RaceDate    Name    Country Div Rank    Gender Rank Overall Rank    Swim    Bike    Run Finish

1   70.3 Pula   20170917    Stefan Haubner  DEU 1   1   1   0:00:34 2:23:03 1:20:33 3:46:33

Answer 1

据我所知，时间戳以小时：分钟：秒的格式存储。要找到四个时间戳（游泳、自行车、跑步、总体）中的最小值，您需要首先将每个时间戳转换为秒。以下代码可用于获取四个时间戳的最佳时间：function GetTimeInSec($time) {&nbsp; &nbsp; list($hour, $min, $sec) = explode(":", $time);&nbsp; &nbsp; $hour&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= (int) ltrim($hour, "0");&nbsp; &nbsp; $min&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = (int) ltrim($min, "0");&nbsp; &nbsp; $sec&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = (int) ltrim($sec, "0");&nbsp; &nbsp; $new_time&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= (($hour * 3600) + ($min * 60) + ($sec));&nbsp; &nbsp; return $new_time;}function GetBestTime($t1, $t2, $t3, $t4) {&nbsp; &nbsp; $time_arr&nbsp; &nbsp;= array(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GetTimeInSec($t1) => $t1,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GetTimeInSec($t2) => $t2,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GetTimeInSec($t3) => $t3,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GetTimeInSec($t4) => $t4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; );&nbsp; &nbsp; ksort($time_arr);&nbsp; &nbsp; $time_cols&nbsp; = array_keys($time_arr);&nbsp; &nbsp; $best_time&nbsp; = $time_cols[0];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; return $best_time;}$time_data = GetBestTime($row['Swim'], $row['Bike'], $row['Run'], $row['Overall']);$best_time = $time_data["best_time"];$avg_time&nbsp; = $time_data["avg_time"];自行车、跑步和游泳的平均时间可以通过首先将每个值从 VARCHAR 转换为 INT 格式的秒数来计算。之后，array_sum 函数可用于查找游泳、自行车和跑步的总和。然后将总和除以总行数以获得平均值。可以使用以下代码：function GetAverageTimes($data) {&nbsp; &nbsp; $data_new&nbsp; &nbsp; &nbsp; &nbsp; = array("swim" => array(), "bike" => array(), "run" => array());&nbsp; &nbsp; for ($count = 0; $count < count($data); $count++) {&nbsp; &nbsp; &nbsp; &nbsp; $row&nbsp; &nbsp; &nbsp; &nbsp;= $data[$count];&nbsp; &nbsp; &nbsp; &nbsp; array_push($data_new['swim'], GetTimeInSec($row['swim']));&nbsp; &nbsp; &nbsp; &nbsp; array_push($data_new['bike'], GetTimeInSec($row['bike']));&nbsp; &nbsp; &nbsp; &nbsp; array_push($data_new['run'], GetTimeInSec($row['run']));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; }&nbsp; &nbsp; $averages&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= array("swim" => 0, "bike" => 0, "run" => 0);&nbsp; &nbsp; $averages['swim'] = ceil(array_sum($data_new['swim']) / count($data));&nbsp; &nbsp; $averages['bike'] = ceil(array_sum($data_new['bike']) / count($data));&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; $averages['run']&nbsp; = ceil(array_sum($data_new['run']) / count($data));&nbsp; &nbsp; return $averages;}$average_times = GetAverageTimes($return);