Calculate duration in php

Question

Hi, I have another little problem and I need someone’s help. The system I’m doing is for a school and I need my system to calculate the duration that each student had within the classes, for that, I know I need to use the command (SUM) within my select, more and then? The final result that I need, is to show in an echo"" all the hours that this student had studying and so do a package control for example, where each student can have only one 8-hour package, each time that the student participates in the class, will add these values and decrease within my package table, as I do this working with PHP time duration?

All I have is this code:

<?php
      $pesquisa_horas_dadas = mysql_query("SELECT SUM(horas_dadas) FROM pacotes");
    while($sum = mysql_fetch_array($pesquisa_horas_dadas)){
        $soma_horas_dadas = $sum['horas_dadas'];
            }

    echo $soma_horas_dadas; echo"</br>";    
    $seleciona_duracao = mysql_query("SELECT SUM(Duracao) FROM Assiste"); 
    $resultset = mysql_query('SELECT SUM(Duracao) as Duracao FROM Assiste'); 
    $linha= mysql_fetch_assoc($resultset); 
    $soma = $linha['Duracao'];

    echo $soma; echo"</br>";

    $soma01 = "01:23";

    $totalHora = $soma / 3600; 

    function converterHora($soma){
        $hora = sprintf("%02s",floor($soma / (60*60)));
        $minuto = ($soma01 % (60*60));
        $soma = sprintf("%02s",floor ($soma / 60 ));
        $soma = ($soma % 60);
        $hora_minuto = $hora.":".$minuto;
        return $hora_minuto;
                        }
       $hora = converterHora($segundosTotal);

      echo $hora;   

                        ?>

MYSQL example:

In this table, the duration of each class I managed to do normally, is returning everything right in the database, My problem is to take the data of this field (Duration) put in a variable and subtract within the field (times_rest) of the number id 22 in the table packages, since each package will only have one student

Answer 1

From what I understand

(take the data of this field "Duration" put in a variable and subtract inside the field "horas_rest" of the id of number 22 in the table packages)

this can be done as follows:

$Horas_rest = '8';  //select de Horas_rest
$soma = '1:07';  //select da soma do campo Duracao da tabela assiste

if (strpos($Horas_rest, ":")){
    $hora1 = explode(":",$Horas_rest);
    $min_hora1 = ($hora1[0] * 3600) + ($hora1[1] * 60);
}else{
    $min_hora1 = ($Horas_rest * 3600);
}

if (strpos($soma, ":")){
    $hora2 = explode(":",$soma);
    $min_hora2 = ($hora2[0] * 3600) + ($hora2[1] * 60);
}else{
    $min_hora2 = ($soma * 3600);
}


$resultado = $min_hora1 - $min_hora2;

$hora = floor($resultado / 3600);

$resultado = $resultado - ($hora * 3600);

$min = floor($resultado / 60)/10;

$min=str_replace(".","",$min);

if(strlen($min)==1){
    $min=$min."0";
}

$result=$hora.":".$min;

  //UPDATE Pacotes SET Horas_rest='$result' Where ....

And to add up the values that are within (duration) in the table assists:

while($row = mysql_fetch_array($query))

    {
        $hora= $row['Duracao'];
        $hora1 = explode(":",$hora);
        $min_hora1 = ($hora1[0] * 3600) + ($hora1[1] * 60);
        $total=$total+$min_hora1;
    }   

    $hora = floor($total / 3600);

    $total = $total - ($hora * 3600);

    $min = floor($resultado / 60)/10;

    $min=str_replace(".","",$min);

    if(strlen($min)==1){
      $min=$min."0";
    }

    $result=$hora.":".$min;