Subtract two fields from a table in PHP mysql?

Question

Subtract two fields from a table in PHP mysql?

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I want to calculate the rest of a driver, and for this I have the following table:

What I want to do is to calculate for each driver, the largest rest expressed in Hours.

For this I have to do the subtraction between the departure and the arrival of the previous trip.

For example, Salida (id_viaje=20) - Llegada (id_viaje=19) , but keep in mind that not always the id_viaje will be the previous number, since in the middle there may be a record for another driver.

I do not know if I have explained myself correctly, any doubt I will try to solve it!

PS: do not listen to the data in the table, they are simply tests, I know they do not make sense!

mysql php

asked by Adri2o 20.02.2017 в 12:26

source

3 answers

Display query result differently SQL How can I link a database in Visual Foxpro to a SQL Server 2014 server? [closed]

score 0 · Answer 1

$sql = "TU CONSULTA";
$res = $db->query($sql) OR DIE ("ERROR: ".$sql);
While($row = $res->fetch_assoc()){
    $horas_totales = (strtotime($row['llegada']) - strtotime($row['salida']))/3600;
    $horas_descanso = $horas_totales - $horas_conducidas;
    echo $row['id_conductor']."=>";
    echo $horas_totales."=>";
    echo $horas_descanso."<br>";
}

Try something like that, assuming that the column hours driven is an entire value should work.

score 0 · Answer 2

You would have to do a JOIN of the table with itself and ask for the trips of the same driver, restricting the ones in the second table to finish before the next trip begins.

I do not have mysql installed to test it but it should be something like:

SELECT a.id_conductor, a.salida, max(b.llegada) 
FROM viajes a JOIN viajes b USING (id_conductor)
WHERE b.llegada<a.salida
GROUP BY a.id_conductor, a.salida

or

SELECT a.id_conductor, a.salida, max(b.llegada) 
FROM viajes a JOIN viajes ON b.id_conductor=a.id_conductor AND b.llegada<a.salida
GROUP BY a.id_conductor, a.salida

score 0 · Answer 3

What I would do is add a table that is called "rest" and in the fields: id, driver_id, start_break, end_break and date. At the end of the day I would summarize the total hours worked and the hours of rest and subtract them so that I get the total.

For that you can use inner join, to link the two tables.