SQLSTATE[HY093] parameter was not defined, where there are an equal number of parameters and values

Question

The following code is producing the error: SQLSTATE[HY093]: Invalid parameter number

if (!$errFlag) { 
        //Get variables from POST
        $values = array (
            $_POST['studentLast'],
            $_POST['studentFirst'],
            $_POST['studentGradeID'],
            $_POST['studentHRID'],
            $_POST['studentTeamID'],
            $_POST['studentParents'],
            $_POST['studentAddress'],
            $_POST['studentCity'],
            $_POST['studentST'],
            $_POST['studentZIP'],
            $_POST['studentPhone1'],
            $_POST['studentPhone2'],
            $_POST['studentEmail1'],
            $_POST['studentEmail2'],
        );

        //INSERT as a new row into the students database
        try {
            $userdb = userConnect();
            $stmt = $userdb->prepare('
                INSERT INTO
                    students (
                        studentStatus,
                        studentLast,
                        studentFirst,
                        studentGradeID,
                        studentHRID,
                        studentTeamID,
                        studentParents,
                        studentAddress,
                        studentCity,
                        studentST,
                        studentZIP,
                        studentPhone1,
                        studentPhone2,
                        studentEmail1,
                        studentEmail2
                    )
                VALUES (
                    "active",
                    :studentLast,
                    :studentFirst,
                    :studentGradeID,
                    :studentHRID,
                    :studentTeamID,
                    :studentParents,
                    :studentAddress,
                    :studentCity,
                    :studentST,
                    :studentZIP,
                    :studentPhone1,
                    :studentPhone2,
                    :studentEmail1,
                    :studentEmail2
                )
            ');
            $stmt->execute($values);
        } catch(PDOException $e) {
            echo $e->getMessage();
            die();
        }
    }

I found several users on Stack Overflow had the same issue, and the answers to their questions led me to believe that this error is caused when the number of parameters does not match the number of values. However, in the case of the code above, I have 14 parameters and 14 values. I am not sure, based on what I have read, why this is failing.

I am very new to PHP, and EXTREMELY new to PDO (I decided this week to refactor all of my work on this project, as I had been writing everything using mysqli, and felt that PDO might be a better option).

Any help would be greatly appreciated. Thanks!

You have 14 in your array and 15 in your query. The missing one is your student status. `$_POST['studentStatus'],` perhaps? Add `$userdb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened. — Funk Forty Niner, Jul 02 '14 at 03:45
*"I have 14 parameters and 14 values"* - My editor nor do my eyes lie. You don't have 14 columns/values, you have 15 in both; and 14 in your array. — Funk Forty Niner, Jul 02 '14 at 03:56
I had thought about that, but in the VALUES section of the SQL statement, the first value for studentStatus indicated is "active", the rest are the 14 bound parameters (:studentLast through :studentEmail2) whose values come from the 14 values in the $values array. I just tried binding all of the VALUES in my INSERT statement, and just added "active" as the first value in my $values array, but I am still getting the same error. — drfinale, Jul 02 '14 at 04:02
See if this method will work better for you http://stackoverflow.com/a/19279388/ - Yours doesn't seem to be binding anything. This one too http://stackoverflow.com/q/22979857/ it uses an array. — Funk Forty Niner, Jul 02 '14 at 04:07
In short, you're not binding your array. I.e. `execute(array(":first"=>$fname,":last"=>$lname,":title"=>$title,":photo"=>$photo))` - All your PDO is seeing is probably something like `execute(array(""=>$fname,""=>$lname,""=>$title,""=>$photo))` because of the missing `:studentLast` etc. with the `:` and column name, in turn throwing that error at you. — Funk Forty Niner, Jul 02 '14 at 04:17
Thanks! Your last comment did it! I had forgotten the keys in the array. I went back and added the correct keys to my $values array and it now works. Thanks again! — drfinale, Jul 02 '14 at 14:44

score 0 · Accepted Answer · answered Jul 02 '14 at 14:49

The problem was that I had not added the parameter names as keys to my array. I made the correction (below), and the code now works as expected.

//Get variables from POST
            $values = array (
                ':studentLast'=>$_POST['studentLast'],
                ':studentFirst'=>$_POST['studentFirst'],
                ':studentGradeID'=>$_POST['studentGradeID'],
                ':studentHRID'=>$_POST['studentHRID'],
                ':studentTeamID'=>$_POST['studentTeamID'],
                ':studentParents'=>$_POST['studentParents'],
                ':studentAddress'=>$_POST['studentAddress'],
                ':studentCity'=>$_POST['studentCity'],
                ':studentST'=>$_POST['studentST'],
                ':studentZIP'=>$_POST['studentZIP'],
                ':studentPhone1'=>$_POST['studentPhone1'],
                ':studentPhone2'=>$_POST['studentPhone2'],
                ':studentEmail1'=>$_POST['studentEmail1'],
                ':studentEmail2'=>$_POST['studentEmail2'],
            );

SQLSTATE[HY093] parameter was not defined, where there are an equal number of parameters and values

1 Answers1