AbsoluteBeginner AbsoluteBeginner - 5 months ago 7
SQL Question

input field: store decimal values with comma instead of a point in mysql database

I have an input field for a price. The entered value will be stored into a database, for example

13,99
.

In the MySQL database this field has the type
decimal(10,2)
.
What happens is that the price will only be stored correctly into the database if the user types
13.99
with a point instead of a comma. If the user types
13,99
it will be stored as
13.00
.

What should I change in my code, so that the user can also type
13,99
and it will be stored correctly into the database?

<?php
...
if ($valid) {
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE products set price =? WHERE id = ?";
$q = $pdo->prepare($sql);
$q->execute(array($price,$id));
Database::disconnect();
header("Location: index.php");
}
} else {
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM products where id = ?";
$q = $pdo->prepare($sql);
$q->execute(array($id));
$data = $q->fetch(PDO::FETCH_ASSOC);
$price = $data['price'];

Database::disconnect();
}
?>
...

<div class="control-group <?php echo !empty($priceError)?'error':'';?>">
<label class="control-label">Preis</label>
<div class="controls">
<input class="form-control" name="price" type="text" placeholder="price Number" value="<?php echo !empty($price)?$price:'';?>">
<?php if (!empty($priceError)): ?>
<span class="help-inline"><?php echo $priceError;?></span>
<?php endif;?>
</div>
</div>

Answer

Try

...

if ($valid) {
        $price = str_replace(",",".",$price);
        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = "UPDATE products  set price =? WHERE id = ?";
        $q = $pdo->prepare($sql);
        $q->execute(array($price,$id));
        Database::disconnect();
        header("Location: index.php");
    }
} else {
    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT * FROM products where id = ?";
    $q = $pdo->prepare($sql);
    $q->execute(array($id));
    $data = $q->fetch(PDO::FETCH_ASSOC);
    $price = $data['price'];

    Database::disconnect();
}
...