PHP and mysql time count

Question

Good night,

I’m new here.

A doubt I think is basic.

I will create an "application" with PHP and mysql where I control the working hours of each collaborator with daily inputs and outputs. The question is does SQL only store the data who does the "count job is PHP in case ? to work with this type of dates where the goal is to count the weekly and monthly working hours what type of data I use in mysql type DATE or string and in php I do the casting ?

Thanks for your attention

Answer 1

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

Mysql only stores and who "does the job" is PHP in the case?

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

Example:

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

Answer:

100

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

What kind of data do I use in Mysql? Type DATE or string and in php I cast?

It is always better to use what has been assigned to it. Precisely so that you can make use of the native functions of Mysql. In the previous example, if the "Winnings" field were a VARCHAR it would be "impossible" to use the sum(), or it would be impossible to add the data via Mysql. To use the sum() in that condition would have to use the 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 the example.

You can use "VARCHAR for everything" as long as you give up all the resources that Mysql (or any other database) can offer, or need "gambiarras" and more code to be able to use them. It is logical that some cases may have more than one field to be chosen, a date may choose DATETIME or TIMESTAMP and may choose INT or BIGINT. Overall, choosing "wrongly" can 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')
;

The data of dataEntrada and dataSaida are on UNIX TIME, this is measured in seconds since 01/01/1970, unless mistaken. That’s why I’m using the INT, because the data will be inserted 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 departure time. The arrival time would be set by INSERT formerly.

For example, the number of hours worked may 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>';

}

Upshot:

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

This example can be complex for anyone starting out 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".

Since 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:

In the SELECT:

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

SUM = It will add up the result of all (A + B) based on the GROUP BY.

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

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

In short, Mysql is much more than storing data, as you suggested. In this case PHP just took the trouble to display the data, no calculation was done by it.