Addinng decimals in phpMyAdmin database

Question

I'm having a little code, where the user can select a number with or without decimal places. I added the step attribute to my input type="number", So the user can insert decimals, as shown:

    <form method="post" >
    <input type='number' name='price' min='1' max='30' step=".01" required> 
    <input type='submit' name='submit'> 
    </form>
if(isset($_POST['submit']))
{
    try{
            require("conecction.php");
            $db->beginTransaction();
            $ins = $db->prepare("INSERT INTO items (price) VALUES (?)");
            $ins->bindParam(1,$_POST['price']);
            $ins->execute();
    }
    catch(PDOException $ex)
    {
      $db->rollBack();
      echo "<p style=color:red;> There was an connection error.<p>";
      die($ex->getMessage());
    }
}

and my SQL table has the following format:

CREATE TABLE `items` (
  `ID` int(11) NOT NULL,
  `price` decimal(6,2) NOT NULL,
)

This code is functional and inserts the data into the DB as expected to be, but only it is inserting the closest number to the decimal point, for example the user inserts 21.4 I get 21 in my DB.

Is it correct in `$_POST` but being lost in the INSERT, or incorrect in `$_POST`? — droopsnoot, Dec 11 '20 at 12:41
Have you tried specifying the parameter type via the third parameter of bindparam? https://stackoverflow.com/questions/2718628/pdoparam-for-type-decimal — CBroe, Dec 11 '20 at 12:41
Please show us `var_dump($_POST['price']);` to verify your input. — Ro Achterberg, Dec 11 '20 at 12:45
@droopsnoot I have used `var_dump`, to check the value of it in `$_POST` , it's correct, but when it reaches the DB it comes without any decimals — Ahmed Kena, Dec 11 '20 at 12:46
@CBroe , I have just tried it, still I'm not getting the decimal points — Ahmed Kena, Dec 11 '20 at 12:47
@CBroe, the docs specify `PARAM_STR` as the default, which is the required constant for decimals/floats as you correctly pointed out via the answer you referred to. — Ro Achterberg, Dec 11 '20 at 12:48
What happens, when you do `$ins->bindValue(1, '21.4');` for testing purposes? — CBroe, Dec 11 '20 at 12:53
And if you try `$ins->bindValue(1, '21,4');` instead? (Maybe there’s some sort of locale setting that influences what the database considers to be a decimal point?) — CBroe, Dec 11 '20 at 13:00
Please share `DESCRIBE items;` just to verify your assumption of the table definition. — Ro Achterberg, Dec 11 '20 at 13:00
@CBroe, [locale support](https://dev.mysql.com/doc/refman/8.0/en/locale-support.html) seems to be limited to date/time values. I think they force ANSI on input, and allow for more freedom in output. — Ro Achterberg, Dec 11 '20 at 13:04
@RoAchterberg thank you, I fixed it, I thought that the price column was defined as decimal(6,2) , but it was decimal(6,0). — Ahmed Kena, Dec 11 '20 at 13:07

Atmiya Kolsawala · Answer 1 · 2021-10-05T12:00:08.210

-2

Change the data type of the field to FLOAT As you assign the float datatype in DB then you will be able to save integer as well as float decimal values.

http://prntscr.com/1uvulon

edited Oct 05 '21 at 12:00

answered Dec 11 '20 at 12:45

Atmiya Kolsawala

475
4
12

3

Your answer would be better if you explained _why_ this solves the problem. – Ro Achterberg Dec 11 '20 at 12:46
1

@RoAchterberg _can_ an answer suggesting to use a float type for a price field, that is supposed to contain a currency amount, _ever_ be good? I don’t think it can. – CBroe Dec 11 '20 at 12:49
@CBroe, I'm inclined to agree with you on that. – Ro Achterberg Dec 11 '20 at 12:50
I have tried FLOAT, DOUBLE, and DECIMAL, all are not inserting the decimal point – Ahmed Kena Dec 11 '20 at 12:50

Addinng decimals in phpMyAdmin database

1 Answers1