error: 'Invalid parameter number: parameter was not defined' when using an array to INSERT in PDO

Question

I'm trying to insert in array that looks like:

$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'));

// trace($prices);

I want to insert the array into mysql using PDO however i always get the message:

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

(The line number given by the

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();
}

You've got to execute the insert statement for every $insert row. Instead of using $stmt->bindParam() I would use $stmt->execute($insertRow);. You've got to fix the typos as mentioned too. — VMai
– VMai, Commented Sep 19, 2014 at 12:50

black_belt · Accepted Answer · 2014-09-19 17:28:56Z

As Phantom said in his answer, You have a typo. There isevent_price_currency key in your array and :event_price_currency_id placeholder in prepare() statement. If fixing that doesn't work, try the following code and check the typo. Let me know if you face any problem.

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();
}

David · Accepted Answer · 2014-09-19 12:40:24Z

1

Your query is expecting this parameter:

:event_price_currency_id

Which you're not supplying. You are, however, supplying this parameter:

'event_price_currency' => 1

One of these things is not like the other.

answered Sep 19, 2014 at 12:40

David

221k43 gold badges254 silver badges343 bronze badges

Comments

Janny Loco · Accepted Answer · 2014-09-19 13:12:57Z

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

i changed the typo, now I don't get an error but when i look at mysql there is only 1 row inserted:

event_id , price_type, price, price_currency, price_info;
0, 0, 0, 0, cccc;

where it has to be:

event_id , price_type, price, price_currency, price_info;
1, 5, 5, 1, aaaa;
1, 8, 7, 1, bbbb;
1, 1, 8, 1, cccc;

Phantom · Accepted Answer · 2014-09-19 12:40:22Z

0

You have a typo. There is event_price_currency key in your array and :event_price_currency_id placeholder in prepare() statement.

answered Sep 19, 2014 at 12:40

Phantom

1,6964 gold badges17 silver badges32 bronze badges

Collectives™ on Stack Overflow

error: 'Invalid parameter number: parameter was not defined' when using an array to INSERT in PDO

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related