Thousand separator in php or mysql

Question

Thousand separator in php or mysql

Navigation

#1 by (1 votes)

2

I have a query where it shows the numerical values with thousand separator (1,500) and I have two problems:

When I open the modal window to edit the values it shows me the values with the thousand separator, but if I edit the value of one field and the other one not the unedited field, I format it and it only shows me the number which is on the left side of the separator

When I add all the values I only add the numbers that are to the left of the separator. The data in the db is decimal (19.2) and the type in the input is number.

Query that brings the values

  SELECT FORMAT(apostado,'Currency') as apostado, FORMAT(ganado,'Currency') as ganado FROM apuesta

Query that adds the values of betting

SELECT sum(FORMAT(apostado,'Currency')) as apostado FROM apuesta

some images of the problem

some solution .....

Code to update.

$id_apuesta = $_POST['id_apuesta'];
$apostado = $_POST['apostado'];
$ganado = $_POST['ganado'];

$sql = $mysqli->query("UPDATE apuesta SET apostado='$apostado', ganado='$ganado' WHERE id_apuesta=$id_apuesta");


<script>
            $('#editUsu').on('show.bs.modal', function (event) {
                var button = $(event.relatedTarget) // Button that triggered the modal
                var recipient0 = button.data('id_apuesta')
                var recipient1 = button.data('apostado')
                var recipient2 = button.data('ganado');

                var modal = $(this)
                modal.find('.modal-body #id_apuesta').val(recipient0)
                modal.find('.modal-body #valor_apostado').val(recipient1)
                modal.find('.modal-body #valor_ganado').val(recipient2);
            });
        </script>

<div class="modal-body">                      
                            <form action="actualizar_apuesta.php" method="POST">    
                                <input  id="id_apuesta" name="id_apuesta" type="hidden" ></input>  

                                <div class="row">
                                    <div class="col-md-6">
                                        <div class="form-group">				
                                            <label for="apostado" class="form-control-label">Valor Apostado *</label>
                                            <div class="input-group">				
                                                <span class="input-group-addon">
                                                    <i class="glyphicon glyphicon-sound-5-1"></i>
                                                </span> 
                                                <input type="text" class="form-control" id="apostado" name="apostado"  value="<?php echo $row['apostado']; ?>" >
                                            </div>                                                                                     
                                        </div>
                                    </div>                                                               
                                    <div class="col-md-6">
                                        <div class="form-group">
                                            <label for="ganado" class="form-control-label">Valor Ganado *</label>
                                            <div class="input-group">				
                                                <span class="input-group-addon">
                                                    <i class="glyphicon glyphicon-user"></i>
                                                </span> 		
                                                <input type="text" class="form-control" id="ganado" name="ganado" value="<?php echo $row['ganado']; ?>" >
                                            </div>
                                        </div>
                                    </div>
                                </div>
                        </div>

mysql sql php

asked by Scorpion 10.07.2017 в 04:03

source

1 answer

Problem with regexp when using. * " Share ID but it only works in the first

score 1 · Answer 1

Mysql does not have a numeric field that interprets or uses the thousand separator, so it should be avoided when inserting or modifying a field, it should only be used to format the data when displaying it and nothing else.

PHP provides the function n umber_format () for this, my recommendation is that you remove the FORMAT () from your queries.

SELECT apostado, ganado FROM apuesta

SELECT sum(apostado) as apostado FROM apuesta

And format only where necessary with php number_format ()

// Notación  inglesa
echo number_format($numero, 2, '.', ',');
// ejemplo 1,234.56