Modify PHP/Mysql table

Asked

Viewed 81 times

0

I have a monthly billing chart that displays earnings from January to December. It sums up all table values pagseguro (spine ProdValor_1).

Below is the code of the table:

<?php    

$mes = array("0", "Janeiro", "Fevereiro", "Março", "Abril", "Maio", "Junho", "Julho", "Agosto", "Setembro", "Outubro", "Novembro", "Dezembro");

for($i = 1; $i < count($mes); $i++) {

    $sql = "select sum(ProdValor_1) as total from pagseguro where DtCad between '".$ano."-".$i."-01 00:00:00' and '".$ano."-".$i."-31 23:59:59' and TipoVenda='cl' and situacao='3'";

    $query = mysqli_query($con, $sql);
    $result = mysqli_fetch_array($query, MYSQLI_ASSOC);
    $clconf = $result['total'];

    $sql = "select sum(ProdValor_1) as total from pagseguro where DtCad between '".$ano."-".$i."-01 00:00:00' and '".$ano."-".$i."-31 23:59:59' and TipoVenda='cl' and situacao='1'";

    $query = mysqli_query($con, $sql);
    $result = mysqli_fetch_array($query, MYSQLI_ASSOC);
    $clnconf = $result['total'];

    $sql = "select sum(ProdValor_1) as total from pagseguro where DtCad between '".$ano."-".$i."-01 00:00:00' and '".$ano."-".$i."-31 23:59:59' and TipoVenda='cl' and situacao='2'";

    $query = mysqli_query($con, $sql);
    $result = mysqli_fetch_array($query, MYSQLI_ASSOC);
    $clcanc = $result['total'];

    $sql = "select sum(ProdValor_1) as total from pagseguro where DtCad between '".$ano."-".$i."-01 00:00:00' and '".$ano."-".$i."-31 23:59:59' and TipoVenda='pl' and situacao='3'";

    $query = mysqli_query($con, $sql);
    $result = mysqli_fetch_array($query, MYSQLI_ASSOC);
    $cpconf = $result['total'];

    $sql = "select sum(ProdValor_1) as total from pagseguro where DtCad between '".$ano."-".$i."-01 00:00:00' and '".$ano."-".$i."-31 23:59:59' and TipoVenda='pl' and situacao='1'";

    $query = mysqli_query($con, $sql);
    $result = mysqli_fetch_array($query, MYSQLI_ASSOC);
    $cpnconf = $result['total'];

    $sql = "select sum(ProdValor_1) as total from pagseguro where DtCad between '".$ano."-".$i."-01 00:00:00' and '".$ano."-".$i."-31 23:59:59' and TipoVenda='pl' and situacao='2'";

    $query = mysqli_query($con, $sql);
    $result = mysqli_fetch_array($query, MYSQLI_ASSOC);
    $cpcanc = $result['total'];

    echo "
    <tr>
        <td align='center' style='background-color:#FCFBC5'><strong><center>$mes[$i]</center></strong></td>
        <td align='center' style='color:#36B851'><strong>".moeda($clconf)."</strong></td>
        <td align='center' style='color:#B17A7B'><strong>".moeda($clnconf)."</strong></td>
        <td align='center' style='color:#B17A7B; border-right:2px solid #B3B3B3'><strong>".moeda($clcanc)."</strong></td>
        <td align='center' style='color:#36B851'><strong>".moeda($cpconf)."</strong></td>
        <td align='center' style='color:#B17A7B'><strong>".moeda($cpnconf)."</strong></td>
        <td align='center' style='color:#B17A7B'><strong>".moeda($cpcanc)."</strong></td>
    </tr>
    ";
}

?>

There is another column in the table pagseguro that is recorded the fees for each payment. I need to modify it to display the results by discounting these fees.

For example for January had a total of 500,00 reais in sales and, in the same period, of these 500,00 real 50,00 reais is rate/discount. Then he must display the 450,00 real.

php mysql

  • Can you put the table structure in the question? The result should be sum(ProdValor_1) - sum(taxas)? Have you tried that?

  • I put the structure there. I didn’t try this, I’ll try

  • That’s exactly what it was, Valew :)

1 answer

1


See if that solves your problem

SELECT ( SUM(ProdValor_1) - SUM(taxas_ps) ) AS total FROM pagseguro

This code is just for example, you just need to make this small change in your SQL query. Subtracting between the sum of the column values Prodvalor_1 and the column taxas_ps.

The rest of your consultation continues as it is.

  • It worked perfectly, I thought it wouldn’t be that simple. rsrs Thank you very much ;)

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.