How to get value from a SQL in php?

Asked

Viewed 328 times

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 take Sql Vtotal and insert it into a PHP variable?

php sql

  • Does it give any error? always returns more than one record?

  • No, but a wrong value appears. Instead of 600, 300.

  • But the idea is to return only one record or not? pq if you have more records $valorTotalSaldoInicial will always have the last value of the loop. If you execute the direct query in the database the result is correct?

  • The idea is to return the Vtotal value!

  • @rray, see how is the SQL command, I do a sum values and create an alias! So, the sum is in alias, correct?

  • Yes that is correct.

  • Yes, I have tried and it is correct if I give this command in SQL.

  • So I need to get this alias with php.

Show 3 more comments

1 answer

1


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

There would be need of that loop While. 

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

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

In the code below we can then debug the return of the bank, better viewing 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 in debugging print you still see 300 instead of 600 as commented, then there is some error in the records saved in your database. In that case remove the SUM of the querie and have all the results printed on the page this way: 

<?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 manually printed values to find the error, compare with the records in the database itself and check whether the filters defined in the clause WHERE are correct! And if you need to change your question with the results and post here.

Browser other questions tagged php sql

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