Add records from a field and display in PHP (values have R$ in front)

Question

Add records from a field and display in PHP (values have R$ in front)

Asked 4 years, 7 months ago

Viewed 325 times

-1

Hello, I’m trying to sum up some values using the SUM and is returning 0.

The fields I will use:

category
month
value (want to add this)

I want to add the valor of all records of each category and display next to the same category.

Code:

  // SELECT NOS CAMPOS A SEREM EXIBIDOS
  $result_transacoes = "SELECT categoria, mes, sum(valor) FROM contas WHERE mes = 'JANEIRO' GROUP BY categoria ORDER BY categoria ASC";
  $resultado_trasacoes = mysqli_query($con, $result_transacoes);
  while($row_transacoes = mysqli_fetch_assoc($resultado_trasacoes)){

    $html .= '<tr><td>'.$row_transacoes['categoria'] . "</td>";
    $html .= '<td>'.$row_transacoes['mes'] . "</td>";
    $html .= '<td>'.$row_transacoes['sum(valor)'] . "</td>";
  }

I started trying to add up the total value of each category, however, it did not work, is returning "0", perhaps because of the R$? What can I do in this case? I tried to use the SUM.

Hello @Sam, values are being saved with R$ yes, I removed all the R$ of the column to make a test, but the sum of them did not hit, some were like: 6041.0294810302, and another concern is, how will I put a mask of monetary value on an input without saving the "R$" ?

– Samuel

2020/04/02 at 13:25
@Sam, I removed the "R$" "." "," and put as INT, apparently I think it worked out the sum, but I’m not able to divide the value by 100, I tried to put in the own select, so: SELECT categoria, mes, sum(valor)/100 FROM contas

– Samuel

2020/04/02 at 14:04
Man, it’s a simple thing and I’m not getting... I did it like this: $valor_dividido = 'sum(valor) / 100';

 $html .= '<td>'.$row_transacoes['$valor_dividido']. "</td>";

– Samuel

2020/04/02 at 14:17
It would be right: $valor_dividido = $row_transacoes['sum(valor)'] / 100; and here would be: $html .= '<td>'.$valor_dividido. "</td>";

– Sam

2020/04/02 at 14:25

2 answers

0

I suggest working with monetary values in a column type INT and save the values in cents. For example, R$10 (or R$10,00) you save with the value 1000 (thousand cents).

By pulling the column value, you divide by 100 and have the real value, with the cents separated by point (ex.: 1000 will stay 10.00). For example, if the value is 150000 (one hundred and fifty thousand cents) which is equivalent to R$1,500.00 (one thousand and five hundred Reais) you will divide by 100 and use the function money_format() PHP to display the value with the Brazilian Real formatting, including adding the symbol R$. An example:

<?
setlocale(LC_MONETARY,"pt_BR");
$valor = "150000";
echo money_format("%n", $valor/100); // R$ 1.500,00
?>

You have to use the setlocale to set the format. The line setlocale(LC_MONETARY,"pt_BR"); should be defined only once. You can put it in a general site include because it will already be active in the entire site environment, without having to declare again.

I don’t understand......

– Sam

2020/04/02 at 17:45
Use the normal mask. When vc send the value to PHP, before saving to the bank, vc must convert to cents. For example, if you send 12,00 (twelve real), you choose the comma before putting in the bank.

– Sam

2020/04/02 at 18:29
Before placing in the bank you make a replace by removing the comma and the dot. Use str_replace. I mean, 1,200 will turn into 120,000.

– Sam

2020/04/02 at 18:51
I tried that way: <script>
 // NAO IR PARA O BANCO
 function formatar($valor){
 $pontos = array(",", ".", "R$");
 $result = str_replace($pontos, "", $valor);
 return $result;
 }
 </script> , but the value is reset to the bank...

– Samuel

2020/04/02 at 19:26
Man, I couldn’t use the replace until now, when he removes the "." or "," it erases the value that is then... example: 1,500.00 , the bank only saves the 1

– Samuel

2020/04/03 at 13:29

Browser other questions tagged php sql mysqli

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

by Tiago Sabadini • 95 points · Answer 1 · 2020-04-01T18:53:26+00:00

All right, Samuel? Come on!

You can resolve this in your query and your code will get cleaner. Just add group by categoria before the order by that already solves.

This code snippet creates a category-level grouping and the sum will automatically be separated by category.

NOTE: Necessary to remove the R$ with the code snippet REPLACE(valor, "R$", "") and then convert the text to decimal. See the query in the link below.

Take the example: http://sqlfiddle.com/#! 9/4766a1/3