PDO insert parameter key instead parameter value

Question

Here I have a form:

 <form action="includes/Payment.inc.php" method="get" class="px-4 py-4" >
    <div class="form-group">
        <div class="d-inline py-1"><h5>Payment Type</h5></div>
        <select class="bg-white text-dark" name="payment_type">
            <option value="Type">Type</option>
            <option value="Food">Food</option>
            <option value="House-Rent">House-Rent</option>
            <option value="Other">Other</option>
        </select>
        <h5 class="py-1">Amount of Money</h5>
        <div class="input-group">
            <span class="input-group-addon">$</span>
            <input type="text" class="form-control" name="amount" aria-label="Text input with checkbox">
            <span class="input-group-addon">JPY</span>
        </div>
        <h5 class="py-1">Detail</h5>
        <textarea placeholder="Enter The Detail in here" name="detail"></textarea><br>
        <label><h5 class="py-1">Date: </h5></label>
        <input type="date" name="date"><br>
        <button type="submit" name="submit" class="btn btn-primary m-4 border rounded">Submit</button>
    </div>
</form>

When clicked simply put all the information into database with following PHP code:

<?php
if (isset($_GET['submit'])) {
    include_once  'dbh.inc.php';
    $payment_type = $_GET['payment'];
    $amount_money = filter_input(INPUT_GET,'amount',FILTER_SANITIZE_NUMBER_INT);
    $detail = filter_input(INPUT_GET,'detail',FILTER_SANITIZE_STRING);
    $date = $_GET['date'];
    if (empty($amount_money)) {
        header('Location: ../Data.php?money_empty');
        exit();
    }
    else {
        $sql = "INSERT INTO payment(payment_type,amount,detail,payment_date)
            VALUES (':payment_type',':amount',':detail',':payment_date')";
        $result = $conn->prepare($sql);
        $result->bindParam(':payment_type',$payment_type,PDO::PARAM_STR);
        $result->bindParam(':amount',$amount_money,PDO::PARAM_INT);
        $result->bindParam(':detail',$detail,PDO::PARAM_STR);
        $result->bindParam(':payment_date',$date,PDO::PARAM_STR);
        $result->execute();

        header("Location: ../Data.php?payment_success");
        exit();
    }
}

Then when I test the form, the execution is completed but when I checked the "payment" table, here's what i got:

payment_type(varchar) = ":payment_type"
amount(int) = 0
detail(varchar) = ":detail"
payment_date(date) = "0000-00-00". What's wrong with my code ??

What does this have to do with "lost connection to server"? Your title and question don't seem to match? — Don't Panic, Dec 09 '17 at 03:31
Sorry ,that was title of my previous question , please edit that — Hoàng Việt, Dec 09 '17 at 04:10
After consideration, I chose your's . Thanks for the docs and clear answer — Hoàng Việt, Dec 09 '17 at 04:22

score 2 · Answer 1 · answered Dec 09 '17 at 03:32

In your code, you use '' to eclosed the string part in insert parameters this not need with PDO. Use the following instead...

<?php
if (isset($_GET['submit'])) {
include_once  'dbh.inc.php';
$payment_type = $_GET['payment'];
$amount_money = filter_input(INPUT_GET,'amount',FILTER_SANITIZE_NUMBER_INT);
$detail = filter_input(INPUT_GET,'detail',FILTER_SANITIZE_STRING);
$date = $_GET['date'];
if (empty($amount_money)) {
    header('Location: ../Data.php?money_empty');
    exit();
}
else {
    $sql = "INSERT INTO payment(payment_type,amount,detail,payment_date)
            VALUES (:payment_type,:amount,:detail,:payment_date)";
    $result = $conn->prepare($sql);
    $result->bindParam(':payment_type',$payment_type,PDO::PARAM_STR);
    $result->bindParam(':amount',$amount_money,PDO::PARAM_INT);
    $result->bindParam(':detail',$detail,PDO::PARAM_STR);
    $result->bindParam(':payment_date',$date,PDO::PARAM_STR);
    $result->execute();

    header("Location: ../Data.php?payment_success");
    exit();
}
}

score 1 · Accepted Answer · answered Dec 09 '17 at 03:30

1

You are quoting your parameter markers, eg ':payment_type', which makes them look like plain strings to PDO, so those strings are what show up in the DB. As the docs show, you should not quote them:

$sql = "INSERT INTO payment(payment_type,amount,detail,payment_date)
        VALUES (:payment_type, :amount, :detail, :payment_date)";

answered Dec 09 '17 at 03:30

Don't Panic

13,965
5
32
51

it this required to force some to accept your answer.You also a good reputed user I think this not suits you either. – A.D. Dec 09 '17 at 05:07
@addy I did not force, I did not even ask to accept my answer - though I am grateful he did and I do think it is the better answer. As I said I was curious why he chose yours, that's all. – Don't Panic Dec 09 '17 at 05:17

PDO insert parameter key instead parameter value

2 Answers2