Sum values returned from while

Asked

Viewed 1,248 times

0

I have a table that has the column value, I query these values as follows

$sql2 = 'SELECT * FROM comisao_trabalho WHERE idJob="10" && pagoA="Dalton" ORDER BY id ASC';
$buscar = $conexao->prepare($sql2); 
$buscar->execute();

$retorno = array();
$retorno['dados'] = '';
$retorno['qtd'] = $buscar->rowCount();

if($retorno['qtd'] > 0):

    while($conteudo = $buscar->fetchObject()){
        $retorno['dados'] = $conteudo->valor;
    }                                            

    echo json_encode($retorno);
endif;

The values are in this format: U$ 2,500,00

php mysql

  • You want the full amount?

  • Yes, the query returns me for example: 'U$ 2,500,00', 'U$ 750,00', U$ 1,000,00' . I need to add all the values that my while bring me

  • I made an answer, if you have restrictions regarding the SQL query I ask you to inform, so I will edit the same. If this happens you will need to use a "counter" variable that receives itself and the value of the current line within WHILE.

  • I noticed another detail, that your column valorhas the values saved in String format, it is not interesting that you save them this way, usually the currency symbol (in your case dollar, U$) is added via HTML or PHP when the data will be shown to the user. Use Double (Float) format to save values.

  • The response of our friend Leandro Silva gives an overview of what would be the auxiliary variable (cont) I spoke.

  • Intendi, but similarly I would need to save the value without a dollar sign or I could use preg_replace in $content->value to leave only the numbers?

  • If you use the form that our friend Leandro suggested, you can use the preg_replace() to leave only the value and you can already make the sum. Via SQL command the sum will only be returned if you save the values without a dollar sign.

Show 2 more comments

2 answers

4

You can do VALOR_TOTAL directly via SQL and nicknamed a field, in which case your query would look like this: 

SELECT id, idJob, pagoA, SUM(valor) AS valorTotal 
FROM comisao_trabalho 
WHERE idJob="10" && pagoA="Dalton" 
ORDER BY id ASC

It is not advisable to use SELECT * FROM for testing only. The field AS assigns a nickname to table, you can put any name, that is to say the sum of column values valorwill be placed in an imaginary column called valorTotal.

In PHP you would have a column called Total value that returns the sum of the values.

Ex.: $retorno['dados'] = $conteudo->valorTotal;

It is important to remember that in this example you will have the return of a line, that is, a record with the value of the others who fell in the clause WHERE.

Learn more

  • Intendi Tiago, really that would be the best solution. But not to mess with everything that is already ready, I could add a "copy" column, in which I put the same values that are in the "original" but without cipher or correct points?

  • 1

    Of course, after this warning, I’d better get used to saving values without a dollar sign and adding them later. Thank you

  • @daltongonzaloFuents you can create another column that will store the currency type. Ex.: currency, currency, etc. Which will have values like "R$, U$". In this case in the view you would concatenate the two fields (in PHP if I remember correctly . [endpoint] for this. By saving only the values in the respective column it is possible to ensure that we can manipulate them by performing arithmetic operations within the database itself, without relying on programming language functions or arrays, such as str_replace(), split() [in java], implode() and explodes(), etc

  • @daltongonzaloFuents you can yes make a "copy" column without any problem. You can even do a query that will extract the data, separate the dollar and play each to your column in the database, so it would automate and organize your records, then you could remove the value column. There are several ways, including via SQL you can remove the dollar from all records with one command.

2


You can add with an auxiliary variable inside the while:

$aux = 0;
while($conteudo = $buscar->fetchObject()){
    $aux += $conteudo->valor;
}
$retorno['dados'] = $aux;

And at the end of the while iteration, you would have the total value stored in $aux

  • But then I’d have the dollar problem?

  • Yes, in this case it is not possible to add Strings, Leandro try to give an edited answer with an implode() separating the dollar and adding the values, I believe can help our friend.

  • 1

    Thanks @Tiagoboeing and Leandro, I used your same Leandro, more for ease because I already had a lot ready. But both worked out!

Browser other questions tagged php mysql

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