警告：PDOStatement :: execute（）：SQLSTATE [HY093]：无效的参数编号：绑定变量的数量与令牌数量不匹配

Question

读完所有其他有关HY093的问题后，我无法弄清楚是什么导致错误

我正在上传一个csv文件并使用PDO将其值插入到mysql中。

这里是我得到：

警告：PDOStatement对象::执行（）：SQLSTATE [HY093]：无效的参数编号：绑定变量的数量不匹配的令牌数量

下面是我使用的代码：

<?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>'; 
    } 
} 

这里是我的钥匙阵列看起来像

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 
) 

，这里是我的价值观阿雷：

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 
) 

任何帮助

样品CSV数据：

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

的问题是你的数据，更具体的第一行：

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 

一个字段包含一个问号：

SBT Q2 (Was your issue resovled?) 

这被解释为在查询另一个占位符，因此你的数组不匹配。

您可以通过按键移除问号解决这个问题：

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