PHP and mysql count of hours

Question:

Goodnight,

I'm new here.

A question I think is basic.

I will create an "application" with PHP and mysql where I control the working hours of each employee with daily inputs and outputs. The doubt is that SQL only stores the data who does the "counting work is PHP in this case? to work with these types of dates where the objective is to count the weekly and monthly hours of work which type of data I use in mysql type DATE or string and in php do the casting?

I thank the attention

Answer:

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

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

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

Example:

Calculate total "Earnings" 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;

Reply:

100

You don't need to loop through each value to get the sum of everything, the sum can be done by SUM() . 🙂

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

It's always better to use what's been assigned to him. Precisely so that you can make use of MySQL's native functions. In the previous example, if the "Gains" field were a VARCHAR it would be "impossible" to use sum() , that is, it would be impossible to add the data via MySQL. To use sum() this condition would have to use cast() and could still have errors in cases of some null values, let's agree that choosing the INT would be better than a VARCHAR, in this case 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 offer, or you need "hacks" and more code to be able to use them. Of course, in some cases there can be more than one field to be chosen, a date can be chosen by DATETIME or TIMESTAMP and can be chosen by INT or BIGINT. In general, choosing "wrongly" can also have overall processing cost and 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')
;

dataEntrada and dataSaida are in UNIX TIME, this is measured in seconds since 01/01/1970, if I'm not mistaken. So I'm using INT , 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 id where the user is Thiago , then update the checkout time. The entry time would be defined by INSERT earlier.

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

$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 someone new to MySQL/PHP!

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

As the function is focused on the MySQL query, here it is separate:

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:

ABS = Returns the raw value (1 – 100 = 99).

SUM = Sum the result of all ( A + B ) based on GROUP BY .

SEC_TO_TIME = SEC_TO_TIME convert seconds (returned from SUM) to HH:MM:SS.

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

In summary, MySQL is much more than storing data, as you suggested. In this case PHP only had the work to display the data, no calculation was done by it.

Scroll to Top