php - 错误: 'Invalid parameter number: parameter was not defined' when using an array to INSERT in PDO

Question

我正在尝试插入如下所示的数组:

$prices = array(array('event_id' => 1, 'event_price_type' => 5, 'event_price' => 5, 'event_price_currency' => 1, 'event_price_info' => 'aaaa'), array('event_id' => 1, 'event_price_type' => 8, 'event_price' => 7, 'event_price_currency' => 1, 'event_price_info' => 'bbbb'), array('event_id' => 1, 'event_price_type' => 1, 'event_price' => 8, 'event_price_currency' => 1, 'event_price_info' => 'cccc'));

//跟踪($prices);

我想使用 PDO 将数组插入 mysql，但是我总是收到消息:

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

(由

给出的行号

try{
    $this->dbh->beginTransaction();
    $stmt = $this->dbh->prepare("INSERT INTO event_prices(event_id, price_type, price, price_currency_id, price_info) VALUES(:event_id, :event_price_type, :event_price, :event_price_currency_id, :event_price_info)");

    foreach($prices as $insertRow) {
        // trace($insertRow);
        // now loop through each inner array to match binded values
        foreach($insertRow as $column => $value){
            // echo "COMLOM WAARDE: {$column}, VALUE: {$value}<br/>";
            $stmt->bindParam(":{$column}", $value);
        }
    }
    // NOW DO EXECUTE
    $stmt->execute();
    $this->dbh->commit();
} catch (PDOException $e) {
    echo 'Error ! '.$e->getMessage();
    die();
}

Answer 1

正如 Phantom 在回答中所说，你有一个错字。数组中有 event_price_currency 键，prepare() 语句中有 :event_price_currency_id 占位符。如果修复不起作用，请尝试以下代码并检查拼写错误。如果您遇到任何问题，请告诉我。

try
{
   $DBH->beginTransaction();
   $STH = $DBH->prepare("INSERT INTO event_prices(event_id, event_price_type, event_price,  event_price_currency_id, event_price_info ) values (?, ?, ?, ?, ?)");

  foreach($prices as $price)
  {
    foreach($price as $row)
    {
        $data[] = $row;
    }

    $STH->execute($data);
    $data = NULL;
  }

  $DBH->commit();
}

catch(PDOException $e)
{
  echo 'Error ! ' . $e->getMessage();
  die();
}