PHP and mysql hours count

I will divide the questions to better answer, then later solve the problem (or suggest a solution) to the case.

  

MySQL just stores and who "does the work" is PHP in case?

Yes and no. MySQL is much more than just SELECT coluna FROM , you can add, subtract, concatenate (...) and several and many other operations. Many people, myself included, always recommend using the functions of MySQL in relation to processing them in PHP when possible.

Example:

Calculate total "Gains" from the "Financial" table where "1 = 1" (ie all).

Using MySQL:

$query = mysqli_query($sql, SELECT sum(Ganhos) FROM financeiro WHERE 1 = 1);
list($total) = mysqli_fetch_row($query);

echo $total;

Answer:

100

You do not have to loop each value to get the sum of all, the sum can be done by SUM() . :)

  

What kind of data do I use in MySQL? The DATE or string type and in php do the casting?

It is always better to use what has been assigned to it. In the example above, if the field "Gains" was a VARCHAR it would be "impossible" to use sum() , ie it would be impossible to add the data via MySQL. To use the% w / w in this condition would have to use sum() and could still have errors in cases of some null values, let's agree that choosing INT would be better than a VARCHAR, in this instance of the example.

You can use "VARCHAR for everything" as long as you give up all the features that MySQL (or any other database) can provide, or you need "gambiarras" and more codes to get them to use them. It is logical that in some cases there may be more than one field to be chosen, a date can be chosen by DATETIME or TIMESTAMP and can opt for INT or BIGINT. Overall, choosing "wrong" may also have processing cost and overall performance.

Solving Your Problem

Imagine this table:

CREATE TABLE tempo
    ('id' int, 'usuario' varchar(32), 'dataEntrada' int(11), 'dataSaida' int(11))
;

Imagine this data:

INSERT INTO tempo
    ('id', 'usuario', 'dataEntrada', 'dataSaida')
VALUES
    (1, 'Inkeliz', '1451638800', '1451671200'),
    (2, 'Thiago', '1451638800', '1451667600'),
    (3, 'Inkeliz', '1451721600', '1451764800')
;

Data of cast() and dataEntrada are in UNIX TIME, this is measured in seconds from 01/01/1970, except for error. So I'm using dataSaida , because the data will be entered as:

mysqli_query($sql, 'UPDATE dataSaida SET dataSaida = "'.time().'" WHERE id = (SELECT * FROM (SELECT id FROM tempo WHERE usuario = "Thiago" ORDER BY id DESC LIMIT 1) as x)');

This will get the last INT where the user is id , then update the exit time. The time of entry would be set by Thiago previously.

For example, you can use the number of hours worked:

$tempoTrabalho = mysqli_query($sql, 'SELECT usuario, TIME_FORMAT(SEC_TO_TIME(SUM(ABS('dataSaida'- 'dataEntrada'))),"%H Horas %i Minutos") FROM tempo WHERE dataSaida != 0 GROUP BY usuario');

foreach(mysqli_fetch_all($tempoTrabalho) as list($usuario, $tempo)){

    echo $usuario.' trabalhou por '.$tempo;
    echo '<br>';

}

Result:

Inkeliz trabalhou por 21 Horas 00 Minutos
Thiago trabalhou por 08 Horas 00 Minutos

  

This example can be complex for those starting in MySQL / PHP!

The big difference of this code is that all the work is done by MySQL, PHP only has the job of displaying the data, "as is."

As the function is concentrated in the MySQL query, here it is separated:

SELECT 
usuario, 
TIME_FORMAT(
  SEC_TO_TIME(
    SUM(
      ABS('dataSaida'- 'dataEntrada')
    )
  ),'%H Horas %i Minutos') 

[...]

GROUP BY 
usuario

  

See in SQL Fiddle!

Explanations, from the inside out:

No SELECT:

INSERT = Returns the gross value (1 - 100 = 99).

ABS = The result of all will be added ( SUM + A ) based on B .

GROUP BY = Convert the seconds (returned from SUM) to HH: MM: SS.

SEC_TO_TIME = Will deliver the data (HH: MM: SS) in the format "0 Hours 0 Minutes".

In summary MySQL is much more than just storing data, as suggested. In this case PHP just had the job of displaying the data, no calculation was done by it.