What's wrong with my PDO Insert statement?

Question

Here's my SQL statement:

protected static $_SQLInsert = "INSERT INTO location
(host_id, street, suburb, region, post_code, country, phone, email,
timezone, longitude, latitude, is_main)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

I prepare the statement as follows:

static::$_PDOSInsert = self::$_PDO->prepare(static::$_SQLInsert);

After preparing an array with 12 values, I execute the statement with:

static::$_PDOSInsert->execute($array);

I then get the following warning:

PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in...

So, what am I doing wrong???

EDIT: Here's the array:

(
    [host_id] => 15
    [street] => Street 15
    [suburb] => Suburb 15
    [region] => Region 15
    [post_code] => Post Code 15
    [country] => AU
    [phone] => 12341234
    [email] => asfd@email.com
    [timezone] => 1
    [longitude] => 123
    [latitude] => 234
    [is_main] => 1
)

Thanks!

possible duplicate of ["Invalid parameter number: parameter was not defined" Inserting data in Yii](http://stackoverflow.com/questions/5874383/invalid-parameter-number-parameter-was-not-defined-inserting-data-in-yii) — mario, Jun 04 '12 at 00:23
`parameter was not defined`, maybe you forgot to define one of your parameters in the array? I'd use associative placeholders as `:host_id` instead of a dozen of `?`s to better track your parameters. — Fabrício Matté, Jun 04 '12 at 00:33
Aren't there missing quotes around some of your array values? — Sebas, Jun 04 '12 at 00:43
@KimPrince, Fabricio's suggestion is to start using named parameters. — Brad, Jun 04 '12 at 00:43
do `->execute(array_values($array));` verify the order matches the query, though... — goat, Jun 04 '12 at 00:49
@chris, that worked. Why? You should write this up as an answer... — DatsunBing, Jun 04 '12 at 00:52

score 2 · Accepted Answer · answered Jun 04 '12 at 01:11

2

pdo is really picky about the array you give to execute(). If you used unnamed placeholders like ?, it wants a numerically indexed array, and it must start at index 0.

answered Jun 04 '12 at 01:11

goat

31,486
7
73
96

What's wrong with my PDO Insert statement?

1 Answers1