Calculate the difference between two whiles

Question

I have two whiles in php:

<?php  
    while($row1 = mysqli_fetch_array($result1))  
      {
?> 
<tr>  
    <td><?php echo $row1["codigo"]; ?></td> 
    <td><?php echo $row1["nome"]; ?></td> 
    <td><?php echo $row1["QTD 1º e 2º Hora"]; ?></td>
    <td><?php echo $row1["Valor 1º e 2º Hora"]; ?></td> 
    <td><?php echo $row1["QTD 3º Hora"]; ?></td> 
    <td><?php echo $row1["Valor 3º Hora"]; ?></td> 
    <td><?php echo $row1["QTD 4º ou mais Horas"]; ?></td> 
    <td><?php echo $row1["Valor 4º ou mais Horas"]; ?></td>
    <td><?php echo $row1["Valor Total"]; ?></td> 
</tr> 
<?php  
} 
?>

Where returns these three lines:

Then I have the second while:

<?php  
    while($row3 = mysqli_fetch_array($result3))  
      {
?> 
<tr>  
    <td><?php echo $row3["codigo"]; ?></td> 
    <td><?php echo $row3["nome"]; ?></td> 
    <td><?php echo $row3["QTD 1º e 2º Hora"]; ?></td>
    <td><?php echo $row3["Valor 1º e 2º Hora"]; ?></td> 
    <td><?php echo $row3["QTD 3º Hora"]; ?></td> 
    <td><?php echo $row3["Valor 3º Hora"]; ?></td> 
    <td><?php echo $row3["QTD 4º ou mais Horas"]; ?></td> 
    <td><?php echo $row3["Valor 4º ou mais Horas"]; ?></td>
    <td><?php echo $row3["Valor Total"]; ?></td> 
</tr> 
<?php  
} 
?>

Where returns this line:

The two whiles within the same returns the 4 lines like this:

If you notice these 4 lines you have two lines with the same User Nº. and you can't. What I mean is that lines coming from the second while draw the value and hours of the first while according to the same User Nº.

How can I do this? Basically the idea is to create a credit note system in case of registration mistake.

show code:

$query2 = "SELECT E.DataDia, 
       E.codigo,
       E.nome,
       E.Colaborador,
       E.`Local`,
       E.Datasaida,
       E.DataChegada,
       E.`Horas Consumidas`,
       TIME_FORMAT(SEC_TO_TIME(SUM(E.teste1)),'%i:%s') AS `QTD 1º e 2º Hora`,
       CONVERT(CONCAT(SUM(E.Valor), '€'), char(8)) AS `Valor 1º e 2º Hora`,
       TIME_FORMAT(SEC_TO_TIME(SUM(E.teste)),'%i:%s') AS `QTD 3º Hora`,
       CONVERT(CONCAT(SUM(E.Valor1), '€'), char(8)) AS `Valor 3º Hora`,
       TIME_FORMAT(SEC_TO_TIME(SUM(E.teste2)),'%i:%s') AS `QTD 4º ou mais Horas`,
       CONVERT(CONCAT(SUM(E.Valor2), '€'), char(8)) AS `Valor 4º ou mais Horas`,
       CONVERT(CONCAT(SUM(E.Total), '€'), char(8)) AS `Valor Total`

FROM
(SELECT C.Id,
        C.DataDia,
       C.codigo,
       C.nome,
       C.Colaborador,
       C.`Local`,
       C.Datasaida,
       C.DataChegada,
       C.`Horas Consumidas`,
       C.Minutos,
       C.teste1,
       C.Valor,
       C.teste,
       C.Valor1,
       C.teste2,
       C.Valor2,
       C.Valor+C.Valor1+C.Valor2 AS Total       

FROM
(SELECT A.Id,
        A.DataDia,
        A.codigo,
        A.nome,
        A.Colaborador,
        A.`Local`,
        A.Datasaida,
        A.DataChegada,        
        A.`Horas Consumidas`,
        A.Minutos,
        CAST(CASE WHEN A.teste1 IS NULL THEN 0 ELSE A.teste1 END AS DECIMAL (15,2)) AS teste1,
        CAST(CASE WHEN D.teste IS NULL THEN 0 ELSE D.teste END AS DECIMAL (15,2)) AS teste,
        CAST(CASE WHEN A.teste2 IS NULL THEN 0 ELSE A.teste2 END AS DECIMAL (15,2)) AS teste2,
        CAST(CASE WHEN A.Valor IS NULL THEN 0 ELSE A.Valor END AS DECIMAL (15,2)) AS Valor,
        CAST(CASE WHEN D.Valor1 IS NULL THEN 0 ELSE D.Valor1 END AS DECIMAL (15,2)) AS Valor1,
        CAST(CASE WHEN A.Valor2 IS NULL THEN 0 ELSE A.Valor2 END AS DECIMAL (15,2)) AS Valor2


FROM

(SELECT B.Id,
        B.DataDia,
        B.codigo,
        B.nome,
        B.Colaborador,
        B.`Local`,
        B.Datasaida,
        B.DataChegada,        
        B.`Horas Consumidas`,
        B.Minutos,
        (CASE WHEN B.Minutos <= 120 THEN B.Minutos ELSE 120 END) AS teste1,
        (CASE WHEN B.Minutos <= 120 THEN B.Minutos*20/120 ELSE 20 END) AS Valor,
        (CASE WHEN B.Minutos >= 180 THEN B.Minutos-180 ELSE 0 END) AS teste2,
        (CASE WHEN B.Minutos >= 180 THEN (B.Minutos-180)*15/60 ELSE 0 END) AS Valor2



FROM 
(SELECT A.Id,
        A.DataDia,
        A.codigo,
        A.nome,
        A.Colaborador,
        A.`Local`,
        A.Datasaida,
        A.DataChegada,
        A.`Horas Consumidas`,
        CAST(TIME_TO_SEC(A.`Horas Consumidas`)/60 AS DECIMAL(15,0)) AS Minutos


FROM
(SELECT raddb.NotaCreditoAcompa.Id,
        raddb.NotaCreditoAcompa.DataDia,
        codigo,
        raddb.Utente.nome,
        raddb.usuarios.nome AS Colaborador,
        `Local`,
        DataChegada,
        Datasaida,
        SUBTIME(DATE_FORMAT(`DataChegada`,'%H:%i'), DATE_FORMAT(`Datasaida`,'%H:%i')) AS `Horas Consumidas`

FROM raddb.NotaCreditoAcompa LEFT OUTER JOIN raddb.Utente

ON raddb.Utente.codigo = raddb.NotaCreditoAcompa.NomeUtente LEFT OUTER JOIN raddb.usuarios

ON raddb.usuarios.id = raddb.NotaCreditoAcompa.Enfermeiro) AS A) AS B 

) AS A

LEFT OUTER JOIN

(SELECT B.Id,
        B.DataDia,
        B.nome,
        B.Colaborador,
        B.`Local`,
        B.Datasaida,
        B.DataChegada,
        B.`Horas Consumidas`,
        B.Minutos,
        (CASE WHEN B.Minutos <= 180 THEN B.Minutos-120 ELSE 60 END) AS teste,
        (CASE WHEN B.Minutos <= 180 THEN (B.Minutos-120)*12/60 ELSE 12 END) AS Valor1



FROM 
(SELECT A.Id,
        A.DataDia,
        A.nome,
        A.Colaborador,
        A.`Local`,
        A.DataChegada,
        A.Datasaida,
        A.`Horas Consumidas`,
        CAST(TIME_TO_SEC(A.`Horas Consumidas`)/60 AS DECIMAL(15,0)) AS Minutos


FROM
(SELECT raddb.NotaCreditoAcompa.Id,
        DataDia,
        raddb.Utente.nome,
        raddb.usuarios.nome AS Colaborador,
        `Local`,
        Datasaida,
        DataChegada,
        SUBTIME(DATE_FORMAT(`DataChegada`,'%H:%i'), DATE_FORMAT(`Datasaida`,'%H:%i')) AS `Horas Consumidas`

FROM raddb.NotaCreditoAcompa LEFT OUTER JOIN raddb.Utente

ON raddb.Utente.codigo = raddb.NotaCreditoAcompa.NomeUtente LEFT OUTER JOIN raddb.usuarios

ON raddb.usuarios.id = raddb.NotaCreditoAcompa.Enfermeiro) AS A) AS B

WHERE B.Minutos > 120 ) AS D

ON A.Id = D.Id) AS C

WHERE date(C.`Datasaida`) BETWEEN '$inicio' AND '$fim') AS E

GROUP BY E.codigo, E.nome, E.DataDia, E.Colaborador, E.`Local`, E.Datasaida, E.DataChegada

ORDER BY E.DataDia ASC";


$query3 = "SELECT E.codigo,
       E.nome,
       TIME_FORMAT(SEC_TO_TIME(SUM(E.teste1)),'%i:%s') AS `QTD 1º e 2º Hora`,
       CONVERT(CONCAT(SUM(E.Valor), '€'), char(8)) AS `Valor 1º e 2º Hora`,
       TIME_FORMAT(SEC_TO_TIME(SUM(E.teste)),'%i:%s') AS `QTD 3º Hora`,
       CONVERT(CONCAT(SUM(E.Valor1), '€'), char(8)) AS `Valor 3º Hora`,
       TIME_FORMAT(SEC_TO_TIME(SUM(E.teste2)),'%i:%s') AS `QTD 4º ou mais Horas`,
       CONVERT(CONCAT(SUM(E.Valor2), '€'), char(8)) AS `Valor 4º ou mais Horas`,
       CONVERT(CONCAT(SUM(E.Total), '€'), char(8)) AS `Valor Total`

FROM
(SELECT C.Id,
       C.codigo,
       C.nome,
       C.Datasaida,
       C.DataChegada,
       C.`Horas Consumidas`,
       C.Minutos,
       C.teste1,
       C.Valor,
       C.teste,
       C.Valor1,
       C.teste2,
       C.Valor2,
       C.Valor+C.Valor1+C.Valor2 AS Total       

FROM
(SELECT A.Id,
        A.codigo,
        A.nome,
        A.Datasaida,
        A.DataChegada,        
        A.`Horas Consumidas`,
        A.Minutos,
        CAST(CASE WHEN A.teste1 IS NULL THEN 0 ELSE A.teste1 END AS DECIMAL (15,2)) AS teste1,
        CAST(CASE WHEN D.teste IS NULL THEN 0 ELSE D.teste END AS DECIMAL (15,2)) AS teste,
        CAST(CASE WHEN A.teste2 IS NULL THEN 0 ELSE A.teste2 END AS DECIMAL (15,2)) AS teste2,
        CAST(CASE WHEN A.Valor IS NULL THEN 0 ELSE A.Valor END AS DECIMAL (15,2)) AS Valor,
        CAST(CASE WHEN D.Valor1 IS NULL THEN 0 ELSE D.Valor1 END AS DECIMAL (15,2)) AS Valor1,
        CAST(CASE WHEN A.Valor2 IS NULL THEN 0 ELSE A.Valor2 END AS DECIMAL (15,2)) AS Valor2


FROM

(SELECT B.Id,
        B.codigo,
        B.nome,
        B.Datasaida,
        B.DataChegada,        
        B.`Horas Consumidas`,
        B.Minutos,
        (CASE WHEN B.Minutos <= 120 THEN B.Minutos ELSE 120 END) AS teste1,
        (CASE WHEN B.Minutos <= 120 THEN B.Minutos*20/120 ELSE 20 END) AS Valor,
        (CASE WHEN B.Minutos >= 180 THEN B.Minutos-180 ELSE 0 END) AS teste2,
        (CASE WHEN B.Minutos >= 180 THEN (B.Minutos-180)*15/60 ELSE 0 END) AS Valor2



FROM 
(SELECT A.Id,
        A.codigo,
        A.nome,
        A.Datasaida,
        A.DataChegada,
        A.`Horas Consumidas`,
        CAST(TIME_TO_SEC(A.`Horas Consumidas`)/60 AS DECIMAL(15,0)) AS Minutos


FROM
(SELECT raddb.NotaCreditoAcompa.Id,
        codigo,
        nome,
        DataChegada,
        Datasaida,
        TIMEDIFF(DATE_FORMAT(`DataChegada`,'%H:%i'), DATE_FORMAT(`Datasaida`,'%H:%i')) AS `Horas Consumidas`

FROM raddb.NotaCreditoAcompa LEFT OUTER JOIN raddb.Utente

ON raddb.Utente.codigo = raddb.NotaCreditoAcompa.NomeUtente) AS A) AS B 

) AS A

LEFT OUTER JOIN

(SELECT B.Id,
        B.nome,
        B.Datasaida,
        B.DataChegada,
        B.`Horas Consumidas`,
        B.Minutos,
        (CASE WHEN B.Minutos <= 180 THEN B.Minutos-120 ELSE 60 END) AS teste,
        (CASE WHEN B.Minutos <= 180 THEN (B.Minutos-120)*12/60 ELSE 12 END) AS Valor1



FROM 
(SELECT A.Id,
        A.nome,
        A.DataChegada,
        A.Datasaida,
        A.`Horas Consumidas`,
        CAST(TIME_TO_SEC(A.`Horas Consumidas`)/60 AS DECIMAL(15,0)) AS Minutos


FROM
(SELECT raddb.NotaCreditoAcompa.Id,
        nome,
        Datasaida,
        DataChegada,
        TIMEDIFF(DATE_FORMAT(`DataChegada`,'%H:%i'), DATE_FORMAT(`Datasaida`,'%H:%i')) AS `Horas Consumidas`

FROM raddb.NotaCreditoAcompa LEFT OUTER JOIN raddb.Utente

ON raddb.Utente.codigo = raddb.NotaCreditoAcompa.NomeUtente) AS A) AS B

WHERE B.Minutos > 120 ) AS D

ON A.Id = D.Id) AS C

WHERE date(C.`Datasaida`) BETWEEN '$inicio' AND '$fim') AS E

GROUP BY E.codigo, E.nome

ORDER BY E.nome";

What i tried

<?php  
    while($row1 = mysqli_fetch_array($result1))  
      {
       while($row3 = mysqli_fetch_array($result3))  
      {
?> 
 <tr>  
    <td><?php echo $row1["codigo"]; ?></td> 
    <td><?php echo $row1["nome"]; ?></td> 
    <td><?php echo $row1["QTD 1º e 2º Hora"]; ?> - <?php echo $row3["QTD 1º e 2º Hora"]; ?></td>
    <td><?php echo $row1["Valor 1º e 2º Hora"]; ?> - <?php echo $row3["Valor 1º e 2º Hora"]; ?></td> 
    <td><?php echo $row1["QTD 3º Hora"]; ?> - <?php echo $row3["QTD 3º Hora"]; ?></td> 
    <td><?php echo $row1["Valor 3º Hora"]; ?> - <?php echo $row3["Valor 3º Hora"]; ?></td> 
    <td><?php echo $row1["QTD 4º ou mais Horas"]; ?> - <?php echo $row3["QTD 4º ou mais Horas"]; ?></td> 
    <td><?php echo $row1["Valor 4º ou mais Horas"]; ?> - <?php echo $row3["Valor 4º ou mais Horas"]; ?></td>
    <td><?php echo $row1["Valor Total"]; ?> - <?php echo $row3["Valor Total"]; ?></td> 
</tr> 
<?php  
} }
?>

Possible duplicate of [PHP compare array](https://stackoverflow.com/questions/901815/php-compare-array) — Jan Myszkier, Nov 27 '19 at 15:04
@Patrick Q I know because they come from different tables of the query, the one I want to keep is always the one that comes in the first while — Bruno, Nov 27 '19 at 15:07
@janmyszkier It is not duplicated, because I do not intend to compare values, but to make the difference between the first while and the second while, for example the value of the first while minus the value of the second while. — Bruno, Nov 27 '19 at 15:09
@BrunoPinto Please show the code that is producing the bottom result (the one with 4 entries). — Patrick Q, Nov 27 '19 at 15:11
@Patrick Q I already added to the question the two queries that return the table with the 4 rows — Bruno, Nov 27 '19 at 15:14
You do not seriously expect us to analyze what is going on in those monster queries now? Start by giving a proper _verbal_ description of what problem you are actually trying to solve here. — 04FS, Nov 27 '19 at 15:15
@04FS I'll add what I was trying to do but it doesn't work to show what I really want — Bruno, Nov 27 '19 at 15:17
_“to make the difference between the first while and the second while, for example the value of the first while minus the value of the second while”_ - that phrasing is so off, it hardly makes sense. “whiles” don’t have “values”. You will likely have to make an extra step for processing here, before you output the data, I doubt what you want (which is still massively unclear though) can be achieved at the same time you are creating the output. — 04FS, Nov 27 '19 at 15:18
@BrunoPinto You showed the code that creates the top two results, but not the third. The third is obviously not created by running the first, followed by the second. It is some intersection of the two. We need to see the code that does that (and not just the SQL, but the PHP and HTML too) — Patrick Q, Nov 27 '19 at 15:19
If you create an array in your first while with the id as key, when you use the second while check if the id exists : if yes, update the row as you wish, if not add the new data in your array. — Mickaël Leger, Nov 27 '19 at 15:20
@PatrickQ I don't think there is a third step. In the image the table is sorted by usernum (up arrow is white in the header). — seantunwin, Nov 27 '19 at 15:23
@Patrick Q Then I have all the code that produces the table with 4 rows, simply missing the table — Bruno, Nov 27 '19 at 15:25
@BrunoPinto One approach to solving this is to store the two result sets as separate Arrays and compare them while stripping out duplicate userNums of the second. Combine the Arrays and then with the resulting Array write your `tr`'s from that. — seantunwin, Nov 27 '19 at 15:28
@seantunwin If that's correct (and it very well may be), then that image is incredibly misleading. Mickael Leger probably has a good suggestion for this situation. — Patrick Q, Nov 27 '19 at 15:38
@PatrickQ I agree on both counts. A little less convoluted than the suggestion I offered as well. — seantunwin, Nov 27 '19 at 15:41

Mickaël Leger · Accepted Answer · 2019-11-28T08:04:37.093

You can try something like this :

<?php  

// You create an empty array
$result = [];

while($row1 = mysqli_fetch_array($result1))  
{
    // I assume $row1["codigo"] is your id. If not, replace by your id.
    // The idea is to fill the array with all the data you will display after
    $result[$row1["codigo"]] = array(
        "nome" => $row1["nome"],
        "qtd1_2hora" => $row1["QTD 1º e 2º Hora"],
        ...
        "total" => $row1["Valor Total"]
    );
}

// Now the second while
while($row3 = mysqli_fetch_array($result3))  
{
    // If you don't have a row with the same id : create it
    if (empty($result[$row3["codigo"]])) {
        $result[$row3["codigo"]] = array(
            "nome" => $row3["nome"],
            "qtd1_2hora" => $row3["QTD 1º e 2º Hora"],
            ...
            "total" => $row3["Valor Total"]
        );
    } 
    // Now if you already have a row with this id : update your data
    else {
        // Eg: if you want to add the total of first while with total of second while
        $result[$row3["codigo"]]["total"] += $row3["Valor Total"];

        /* You have to do what you want here, can't help you more ! */   

    }
}

// Now you can display you data :
foreach ($result as $id => $data) {
?>
<tr>  
    <td><?php echo $id; ?></td> 
    <td><?php echo $data["nome"]; ?></td> 
    <td><?php echo $data["qtd1_2hora"]; ?></td>
    ...
    <td><?php echo $data["total"]; ?></td> 
</tr> 

<?php
}
?>

Is it what you are looking for ?

Calculate the difference between two whiles

1 Answers1