How to get value from a SQL in php?

0

I have the following code

<?php
$buscaValorTotalSaldoInicial=$pdo->prepare('SELECT SUM(valor) AS vTotal FROM saldo
                                                                                    WHERE data=:data
                                                                                    AND empresa_idempresa=:id_empresa');

$buscaValorTotalSaldoInicial->bindValue('id_empresa', $id_empresa);
$buscaValorTotalSaldoInicial->bindValue('data', $data);
$buscaValorTotalSaldoInicial->execute();

while ($linha=$buscaValorTotalSaldoInicial->fetch(PDO::FETCH_ASSOC)) {

    $valorTotalSaldoInicial = $linha['vTotal'];

}
echo $valorTotalSaldoInicial." - ";
?>

How do I get the vTotal of SQl and insert into a PHP variable?

    
asked by anonymous 16.01.2018 / 01:46

1 answer

1

Gustavo, I'm taking into consideration that your connection to the database is correct.

There would be no need for this While loop.

<?php
     $buscaValorTotalSaldoInicial=$pdo->prepare('SELECT SUM(valor) AS vTotal 
     FROM saldo WHERE data=:data  AND  empresa_idempresa=:id_empresa');
?>

In the above SQL you use the sql SUM command that will sum all the results found in your querie, so this query will only return a row. If you had used the GROUP BY clause, you would have to use a loop repetition because depending on the field used in the cluster could respond more than one line, and in that case we use the loop to traverse the N rows answered.

In the code below we can then debug the bank return, better visualizing what may be happening, use print_r () as below:

<?php
    $buscaValorTotalSaldoInicial=$pdo->prepare('SELECT SUM(valor) AS vTotal 
    FROM saldo WHERE data=:data AND empresa_idempresa=:id_empresa');

    $buscaValorTotalSaldoInicial->bindValue('id_empresa', $id_empresa);
    $buscaValorTotalSaldoInicial->bindValue('data', $data);
    $buscaValorTotalSaldoInicial->execute();

    $linha = $buscaValorTotalSaldoInicial->fetch(PDO::FETCH_ASSOC);

    //Utilize a tag PRE do HTML para manter a formatação no navegador.
    echo "<pre>"; print_r($linha); echo "</pre>"; exit;

    $valorTotalSaldoInicial = $linha['vTotal'];
    echo $valorTotalSaldoInicial." - ";
?>

If the impression of debugging continues to come 300 instead of 600 as commented out, then there is some error in the records saved in your database. In this case remove SUM from querie and have all results printed on the page as follows:

<?php
    $buscaValorTotalSaldoInicial=$pdo->prepare('SELECT valor AS vTotal 
    FROM saldo WHERE data=:data AND empresa_idempresa=:id_empresa');

    $buscaValorTotalSaldoInicial->bindValue('id_empresa', $id_empresa);
    $buscaValorTotalSaldoInicial->bindValue('data', $data);
    $buscaValorTotalSaldoInicial->execute();

    while ($linha=$buscaValorTotalSaldoInicial->fetch(PDO::FETCH_ASSOC)){

      //Utilize a tag PRE do HTML para mantes a formatação no navegador.
      echo "<pre>"; print_r($linha); echo "</pre>"; exit;
    }
?>

Analyze the printed values manually to find the error, compare with the records in the database itself, and verify that the filters defined in the WHERE clause are correct! And if you need to change your question with the results and post here.

    
16.01.2018 / 05:13