PDO语句中的内部服务器错误？

Question

I have written a function that is in the file insert.php and looks like this:

<?php
function makeInsert($query, $paramArray){
    include 'db.php';

    try {
        $pdo = new PDO("mysql:host=localhost;dbname=$db_name", $db_user, $db_pass);

            $stmt = $pdo->prepare($query);
            foreach($paramArray as $k => $v){
                $currParamPlc = $paramArray[$k];
                $currParamVal = $v;
                //bind it
                $stmt->bindParam('{$currParamPlc}', '{$currParamVal}', PDO::PARAM_STR);
            }

            $stmt->execute();

            return "Success";
    } catch (PDOException $e) {
        $error = "Error!: " . $e->getMessage() . "<br/>";

        return $error;
        die();
    }
}
?>

I call the function from another php file (run.php) with this code:

<?php 
    include "./insert.php";

    function run(){
        $registration = "success";
        $ammountInput= "12.34";
        $kanalInput= "2";
        $datumInput = "08.2017";
        //[0] => Monat, [1] => Jahr
        $datumArray = explode(".", $datumInput);

        if ($registration == "success"){
            $response_array['status'] = 'success';
            $indexnameYear      = ":year";
            $indexnameMonth     = ":month";
            $indexnameAmount    = ":amount";
            $indexnameChannel   = ":channel";

            $parameter = array($indexnameYear => $datumArray[1], $indexnameMonth => $datumArray[0], $indexnameAmount => $ammountInput, $indexnameChannel => $kanalInput);

            $insertIncomeQuery = "INSERT INTO `income`(`id`, `year`, `month`, `amount`, `channel`) VALUES (NULL, " . $indexnameYear . ", ". $indexnameMonth .", ". $indexnameAmount .", ". $indexnameChannel .")";
            $returnValue = makeInsert($insertIncomeQuery, $parameter);

            if($returnValue === "Success"){
                echo "All done.";
            }else if (strpos($returnValue, "Error!") !== false){
                echo "Sorry!:" . $returnValue;
            }

        }else{
            echo "something happened";

        }
    }

?>

When I call the run() function in my index.php file nothing happens. I just see the error in the developer tools in google chrome:

/insert.php Failed to load resource: the server responded with a status of 500 (Internal Server Error)

Is there an error in my makeInsert() function that I do not see? Or why do I get this error when I try to run the function run()?

Answer 1

There are three issues with your code.

• As @aynber said, '{$currParamPlc}' won't expand the variable, it will just pass that exact string through. Just use $stmt->bindParam($currParamPlc, $currParamVal, PDO::PARAM_STR);

• In ->bindParam(...); statement, you're trying to bind the value, not the reference, to the statement object. bindParam requires a reference.

• See this statement here inside foreach loop,

  $currParamPlc = $paramArray[$k];
                  ^^^^^^^^^^^^^^^
  

  You're taking the value from the array, instead you should take the reference of the key to appropriately bind the parameters.

So if you could combine all three points above, you don't require those extra $currParamPlc and $currParamVal variables at all, simply change your try{...}catch{...} block in the following way:

try {
    $pdo = new PDO("mysql:host=localhost;dbname=$db_name", $db_user, $db_pass);
    $stmt = $pdo->prepare($query);
    foreach($paramArray as $k => &$v){
        $stmt->bindParam($k, $v, PDO::PARAM_STR);
    }
    $stmt->execute();

    return "Success";
} catch (PDOException $e) {
    $error = "Error!: " . $e->getMessage() . "<br/>";

    return $error;
    die();
}

Alternative solution:

Since you're already sending an array of insert values (named parameters) to makeInsert function, you can directly pass this $paramArray array to ->execute() method. So your try{...}catch{...} block would be like this:

try {
    $pdo = new PDO("mysql:host=localhost;dbname=$db_name", $db_user, $db_pass);
    $stmt = $pdo->prepare($query);
    $stmt->execute($paramArray);

    return "Success";
} catch (PDOException $e) {
    $error = "Error!: " . $e->getMessage() . "<br/>";

    return $error;
    die();
}