Store decimal number into MySQL database

Question

Here is the return of AJAX, which display what value I am trying to store into database:

Here is what is really stored into database (58.00 is what I stored):

Here is the structure of table:

Here is the PHP:

$req = $bdd->prepare("INSERT INTO salon_histo(reference, designation, colour, size, type, price, qty, payment, date) VALUES(:reference, :designation, :colour, :size, :type, :price, :qty, :payment, NOW())");
    $req->execute(array(
        'reference' => $_POST['reference'],
        'designation' => $_POST['designation'],
        'colour' => $_POST['colour'],
        'size' => $_POST['size'],
        'type' => $_POST['type'],
        'price' => floatval($price[0]),
        'qty' => $_POST['soldQty'],
        'payment' => $_POST['payment']
    ));
    $req->closeCursor();
    echo json_encode($price[0]);

How can MySQL store a data with 2 decimals at 0 when I am trying to store 58,33? I tried in PHP to use floatval and indeed the number becomes 58.

Please do NOT use screen shots. Copy paste textual data and markup as code or quotation. — marekful, Jun 11 '18 at 15:35
Possible duplicate of [How to store decimal in MySQL?](https://stackoverflow.com/questions/16629759/how-to-store-decimal-in-mysql) — Mickaël Leger, Jun 11 '18 at 15:35
Numbers are usually formatted as `XX.XX`, not `XX,XX`, for MySQL. Convert your number from commas to periods before you insert it into MySQL. — aynber, Jun 11 '18 at 15:36
I'm guessing your price is in EU format `x,xx` rather than PHP/MySQL/whatever decimal format `x.xx` so when you `floatval()` it, it drops everything after (and including) the comma; you probably want to look at either the [NumberFormatter](http://php.net/manual/en/class.numberformatter.php) class or [number_format()](http://php.net/manual/en/function.number-format.php) — CD001, Jun 11 '18 at 15:36
Your issue is the 'comma'. Mysql uses 'period' as a decimal separator. — Forbs, Jun 11 '18 at 15:36
Almost all computer languages use dot as decimal separator. There're tricks to change that in PHP but I'd advise against it. You'll have to convert `58,33` to `58.33` and vice-versa. — Álvaro González, Jun 11 '18 at 15:37
For example see https://stackoverflow.com/questions/4325363/converting-a-number-with-comma-as-decimal-point-to-float — Bill Karwin, Jun 11 '18 at 15:40
You don't need to use `floatval()` at all. Because you use a query parameter, your price value will be forced into the data type for your price column, and any non-numeric characters will be ignored. — Bill Karwin, Jun 11 '18 at 15:42

score 2 · Accepted Answer · answered Jun 11 '18 at 15:36

2

Price appears to be an array:

'price' => floatval($price[0]),

Possibly the decimal part has key 1?

'price' => (float) ($price[0] . '.' . $price[1]),

If that doesn't work, please var_dump($price) and paste me the result.

The other thing to check is wether PHP is formatting those numbers with a comma or not.

answered Jun 11 '18 at 15:36

delboy1978uk

12,118
2
21
39

1

The problem was indeed the coma. I used str_replace to replace "," with ".". The data is correcly stored. Thanks for your help. Screenshots because I couldn't show how the data was stored into db. – Kuartz Jun 11 '18 at 15:44
no problems, could you mark this as the correct answer please? – delboy1978uk Jun 12 '18 at 14:49

Store decimal number into MySQL database

1 Answers1