How to make an average value from more mySQL rows in PHP?

Question

I don't know any PHP, but I needed an API. My friend made one, but I need the average time stamps (of course, just if query found more than one row). From my mysql database I get time stamps (00:34:51) as a string.

I think I know what to do, but I am so incapable in PHP that I just can't fix it myself.

This is the web 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();

And this is how one line of my mySQL database looks like

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

As I understand the timestamps are stored in the format hour:min:sec. To find the minimum of the four timestamps (Swim, Bike, Run, Overall), you need to first convert each timestamp to seconds. Following code can be used to get the best time of the four timestamps:

function GetTimeInSec($time) {
    list($hour, $min, $sec) = explode(":", $time);
    $hour                   = (int) ltrim($hour, "0");
    $min                    = (int) ltrim($min, "0");
    $sec                    = (int) ltrim($sec, "0");
    $new_time               = (($hour * 3600) + ($min * 60) + ($sec));

    return $new_time;
}

function GetBestTime($t1, $t2, $t3, $t4) {
    $time_arr   = array(
                      GetTimeInSec($t1) => $t1, 
                      GetTimeInSec($t2) => $t2,
                      GetTimeInSec($t3) => $t3,
                      GetTimeInSec($t4) => $t4
                  );
    ksort($time_arr);
    $time_cols  = array_keys($time_arr);
    $best_time  = $time_cols[0];                

    return $best_time;
}

$time_data = GetBestTime($row['Swim'], $row['Bike'], $row['Run'], $row['Overall']);

$best_time = $time_data["best_time"];
$avg_time  = $time_data["avg_time"];

The average time for bike, run and swim can be calculated by first converting each value from VARCHAR to number of sec in INT format. After that the array_sum function can be used to find sum of swim, bike and run. The sum is then divided by the total number of rows to get the average. The following code can be used:

function GetAverageTimes($data) {
    $data_new        = array("swim" => array(), "bike" => array(), "run" => array());
    for ($count = 0; $count < count($data); $count++) {
        $row       = $data[$count];
        array_push($data_new['swim'], GetTimeInSec($row['swim']));
        array_push($data_new['bike'], GetTimeInSec($row['bike']));
        array_push($data_new['run'], GetTimeInSec($row['run']));                        
    }

    $averages         = array("swim" => 0, "bike" => 0, "run" => 0);
    $averages['swim'] = ceil(array_sum($data_new['swim']) / count($data));
    $averages['bike'] = ceil(array_sum($data_new['bike']) / count($data));        
    $averages['run']  = ceil(array_sum($data_new['run']) / count($data));

    return $averages;
}

$average_times = GetAverageTimes($return);