Thousand separator in php or mysql


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");
                $('#editUsu').on('', function (event) {
                    var button = $(event.relatedTarget) // Button that triggered the modal
                    var recipient0 ='id_apuesta')
                    var recipient1 ='apostado')
                    var recipient2 ='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);
    <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>
                                                    <input type="text" class="form-control" id="apostado" name="apostado"  value="<?php echo $row['apostado']; ?>" >
                                        <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>
                                                    <input type="text" class="form-control" id="ganado" name="ganado" value="<?php echo $row['ganado']; ?>" >
    asked by Scorpion 10.07.2017 в 04:03

    1 answer


    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

    More info in the documentation:


    Obviously you should avoid formatting the values of the input.

    If in any case you want to keep the format of the input you should delete the characters that are not part of the number when you receive the data, for example with str_replace ()

    $numero = '1,234.56';
    $numero = str_replace(',', '', $número); 
    // 1234.56

    Documentation of str_replace ()


    answered by 21.07.2017 в 16:56