Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Question

After reading all others questions about the HY093 i cant figure out what is causing the error

I am uploading a csv file and inserting its values to mysql using PDO.

Here is what i get:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Here is the code that i am using:

<?php

if (in_array($extension, $allowed_extensions)) {

if (!empty($_FILES['file'])) {

    if ($_FILES['file']['error'] == 0) {

        // check extension
        $file = explode(".", $_FILES['file']['name']);
        $extension = array_pop($file);

        if (in_array($extension, $allowed_extensions)) {

            if (move_uploaded_file($_FILES['file']['tmp_name'], $upload_path . '/' . $_FILES['file']['name'])) {
                if (($handle = fopen($upload_path . '/' . $_FILES['file']['name'], "r")) !== false) {

                    $keys = array();
                    $out = array();

                    $insert = array();

                    $line = 1;

                    while (($row = fgetcsv($handle, 0, ',', '"')) !== FALSE) {

                        foreach ($row as $key => $value) {
                            if ($line === 1) {
                                $keys[$key] = $value;
                            } else {
                                $out[$line][$key] = $value;
                            }
                        }

                        $line++;
                    }

                    fclose($handle);

                    if (!empty($keys) && !empty($out)) {

                        $db = new PDO('mysql:host=localhost;dbname=pitstop', 'root', '');
                        $db->exec("SET CHARACTER SET utf8");

                        foreach ($out as $key => $value) {
                            $sql = "INSERT INTO `csat_dump` (`";
                            $sql .= implode("`, `", $keys);
                            $sql .= "`) VALUES (";
                            $sql .= implode(", ", array_fill(0, count($keys), "?"));
                            $sql .= ")";
                            $statement = $db->prepare($sql);
                            if ($value === null) {
                                $value = 'null';
                            }
                            $statement->execute($value);
                        }

                        $message = '<span class="green">File has been uploaded successfully</span>';
                    }
                }
            }
        } else {
            $message = '<span class="red">Only .csv file format is allowed</span>';
        }
    } else {
        $message = '<span class="red">There was a problem with your file</span>';
    }
}

here is how my keys array looks like

Array ( 
    [0] => SBT Interview End Date 
    [1] => Week End Date 
    [2] => YYYY Mm 
    [3] => SBT Case ID 
    [4] => SBT Response ID 
    [5] => SBT Agent 
    [6] => SBT Channel Name 
    [7] => SBT Msg Created 
    [8] => SBT Close Date 
    [9] => SBT Contact Count 
    [10] => SBT Queue Nm 
    [11] => SBT Vendor Name
    [12] => SBT Location Name 
    [13] => SBT Message Age 
    [14] => SBT Q1 (Email overall score) 
    [15] => SBT Q2 (Was your issue resovled?) 
    [16] => SBT Q201(NPS) 
    [17] => SBT Q200(Ease of contact Customer Care) 
    [18] => SBT Q186 (Verbatim) 
    [19] => FCR 
    [20] => FCR Count 
) 

and here is my values aray:

Array ( 
    [0] => 2014-06-29 
    [1] => 2014-07-05 
    [2] => 2014-06 
    [3] => 140625-057952 
    [4] => 1151071 
    [5] => agentname 
    [6] => Email 
    [7] => 2014-06-25 
    [8] => 2014-06-29 
    [9] => 0 
    [10] => AMR-EN-tech 
    [11] => tech- Agents 
    [12] => SMB - Agents 
    [13] => 4720 
    [14] => 8 
    [15] => 1 
    [16] => 8 
    [17] => 10 
    [18] => 
    [19] => 1 
    [20] => 1 
) 

any help

Sample CSV data:

SBT Interview End Date,Week End Date,YYYY Mm,SBT Case ID,SBT Response ID,SBT Agent,SBT Channel Name,SBT Msg Created,SBT Close Date,SBT Contact Count,SBT Queue Nm,SBT Vendor Name,SBT Location Name,SBT Message Age,SBT Q1 (Email overall score),SBT Q2 (Was your issue resovled?),SBT Q201(NPS),SBT Q200(Ease of contact Customer Care),SBT Q186 (Verbatim),FCR,FCR Count
6/29/2014,7/5/2014,2014-06,140625-057952,1151071,agent1,Email,6/25/2014,6/29/2014,0,AMR-EN-xxxxxx-T1,SMB - Agents,SMB - Agents,4720,8,1,8,10,some text1,1,1
6/29/2014,7/5/2014,2014-06,140625-064834,1151074,agent2,Email,6/25/2014,6/29/2014,0,AMR-EN-xxxxxx-T2,SMB - Agents,SMB - Agents,4587,9,1,9,9,some text2,1,1
6/29/2014,7/5/2014,2014-06,140625-054127,1151083,agent3,Email,6/25/2014,6/29/2014,0,AMR-EN-xxxxxx-T3,SMB - Agents,SMB - Agents,4799,8,1,10,3,some text3,1,1 

Answer 1

The problem is your data, more specifically your first line:

SBT Interview End Date,Week End Date,YYYY Mm,SBT Case ID,SBT Response ID,SBT Agent,SBT Channel Name,SBT Msg Created,SBT Close Date,SBT Contact Count,SBT Queue Nm,SBT Vendor Name,SBT Location Name,SBT Message Age,SBT Q1 (Email overall score),SBT Q2 (Was your issue resovled?),SBT Q201(NPS),SBT Q200(Ease of contact Customer Care),SBT Q186 (Verbatim),FCR,FCR Count

One field contains a question mark:

SBT Q2 (Was your issue resovled?)

This is interpreted as another placeholder in the query, hence your array doesn't match.

You can solve this by removing question marks from the keys:

$keys[$key] = str_replace("?", "", $value);