Internal Server Error in PDO Statment?

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()?

To see the _actual_ error message, check your servers error log. You can also change how PHP displays errors and tell it to show all errors directly on the screen (this is not something you want in production though, since it can show sensitive data, but during development, you should). Here's how to show all errors and warnings: https://stackoverflow.com/questions/5438060/showing-all-errors-and-warnings — M. Eriksson, Aug 10 '17 at 17:52
`'{$currParamPlc}'` won't expand the variable, it will just pass that exact string through. Just use `$stmt->bindParam($currParamPlc, $currParamVal, PDO::PARAM_STR);` — aynber, Aug 10 '17 at 17:54
@aynber I changed it and now i `makeInsert()` returns me `Success` but in the table there is no record inserted. At least the 500 is gone but it's really strange that nothing happens in the database. Any idea how I could find the problem? — Fabian, Aug 10 '17 at 18:01
Read up on [PDO error handling](http://php.net/manual/en/pdo.error-handling.php). Just because it didn't give a PHP error doesn't mean that there isn't a MySQL error. — aynber, Aug 10 '17 at 18:07

Rajdeep Paul · Accepted Answer · 2017-08-10T18:53:34.383

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();
}

That's pretty good and also working! Thanks for the hint with the foreach loop. — Fabian, Aug 13 '17 at 11:31

Internal Server Error in PDO Statment?

1 Answers1