PHP - PDO Invalid parameter number

Question

There are a lot of questions about this, but still I can't find a question which answers my question.

I got some code (obviously), which does a query to the database, and for some reason it is returning an error.

// ^ somewhere in the top session_start();
require("rw_conx.php");
try {
    // Get answers from database 
    $db = new PDO ("mysql:host=$host;dbname=$dbname", $username, $pass);
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $stmt = $db->prepare("SELECT antwoorden, commentaar
                        FROM antwoorden
                        WHERE vragenlijst = :vragenlijst
                        AND meting = :meting
                        AND behandeltraject = :behandeltraject
                        AND onderzoeksNR = :onderzoeksnummer");
    $vragenlijst = "1";
    $meting = "1";
    $onderzoeksNR = trim(preg_replace('#[^0-9]#', '', $_SESSION["onderzoeksnummer"]));
    $behandeltraject = trim(preg_replace('#[^0-9]#', '', $_SESSION["behandeltraject"]));        
    $stmt->execute(array('vragenlijst'=>$vragenlijst,'meting'=>$meting,'behandeltraject'=>$behandeltraject,'onderzoeksnummer'=>$onderzoeksNR));
    // Render all questions (check if 5's should be hidden), input disabled, no answer categories.          
    $num_rows = $stmt->rowCount();
    $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT);

    // What this does is create a string like: 1=2,2=4,3=5|||COMMENTS|||1=blabla,2=blabla
    if ($num_rows > 0) {
        echo $row[0];
        echo "|||COMMENTS|||";
        echo $row[1];
    } else {
        echo "no data";
    }
} catch(PDOException $e) {
    echo "I'm sorry, I'm afraid I can't do that. (1)";
    file_put_contents('../debug.log', $e->getMessage(), FILE_APPEND);
    exit();
}   

$stmt->execute(array('vragenlijst'=>$vragenlijst,'meting'=>$meting,'behandeltraject'=>$behandeltraject,'onderzoeksnummer'=>$onderzoeksNR));

--- EDIT ---

Just to make this clear, I will explain.

1. I mentioned in my question above, that the sessions were no problem.

What I meant by this was the fact, that the error that occured (SQLSTATE[HY093]) was not thrown because the sessions were empty.

2. The problem still occurs.

Even though the session_start() fixed the query (It now returns the row it should), I am still getting the same error, which is (of course) not supposed to happen.

I hope this clears things up a bit, because everybody is getting mad at me. I did indeed mention the sessions were no problem, and they are no problem for the error that occurs. For some reason the catch function still gives me an error, even though the query succesfully runs.

So, if there are still people who would like to help me out, I would really appreciate it.

--- END EDIT ---

Does anyone see the (probably obvious) flaw?

Answer 1

Seeing that the mystery has been solved by GuyT <=(edit), am posting the following as per a comment I left under OP's question:

You mention sessions. Did you start the session?

Is session_start(); included?

You mentioned in a comment in another answer about var_dump($_SESSION);

session_start(); is required to be inside all files using sessions.

---

Original answer, added one above:

You forgot the $ sign for onderzoeksNR in

'onderzoeksnummer'=> onderzoeksNR
                    ^ right there

in regards to

$onderzoeksNR = "1";

change it to

'onderzoeksnummer'=>$onderzoeksNR

Answer 2

The problem you are facing is due to a zero width space(​ in unicode). This might be automatically added in the editor that you are using. jQuery will also insert zero width space characters as you can read here.

But why is it giving me the SQLSTATE[HY093] error?

This is because $behandeltraject is not defined(there is a zero width space in front of it) so the parameters will not match.

Answer 3

My guess is, that either the error is coming from a different statement, or one of the values is an array or null in your code using the session variables

// Normally this are sessions, I checked them, and they contain a value.

You should be using the bindParam member,

$sth->bindParam(':calories', $calories, PDO::PARAM_INT);

...because

1. it doesn't throw strange errors
2. using execute(), all params are converted to string.

http://php.net/manual/en/pdostatement.execute.php