Insert array into PDO (PHP): "SQLSTATE[HY093]: Invalid parameter number"

Question

Inserting an array via PDO into a MySQL-Database with PHP doesn't work for me. It says "invalid parameter number", even though the array has 41 values; the PDO names 41 columns; and the prepared query has 41 question marks.

The array has 41 values (I checked with var_dump), like this:

$data = [
    'countrycode' => $_GET["id"],
    'country' => $_GET["country"],
    'region' => $_GET["region"],
    'law_db_region' => $_GET["law_db_region"],
    [... etc ...]
 ]

My PDO code is the following (yep, these are 41 columns & 41 question marks):

$sql = "INSERT INTO awards_country
    (countrycode, country, region, law_db_region, law_db_national,
    law_db_national2, const_url, const_version, const_date, const_dummy,
    const_article, const_wording, const_article2, const_wording2, const_article3,
    const_wording3, law_dummy, law_act, law_article, law_source,
    law_date, law_act2, law_article2, law_source2, law_date2,
    law_act3, law_article3, law_source3, law_date3, web_dummy,
    web_url, web_organ, web_language, web_date, web_url2,
    web_organ2, web_language2, web_date2, wikipedia_url, wikipedia_date,
    comment)

    VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
    )";
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare($sql);
$stmt->execute($data);

The error I get is nevertheless:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in [...]country-insert.php:99

Stack trace: #0 [...]country-insert.php(99): PDOStatement->execute(Array) #1 {main} thrown in /data/web/e96483/html/awards/country-insert.php on line 99

It's been a long time since I tested, but this might be due to your $data array having associative keys, while you are using positional ? in the statement. This might be easily solvable with $stmt->execute(array_values($data)) to replace the associative array keys with numeric keys. Or solvable by just creating a plain array in the first place $data = [$_GET['id'], $_GET['country'],....] — Michael Berkowski
– Michael Berkowski, Commented May 4, 2019 at 15:38
print_r( $data ); before PDO execute to make sure it contains all fields you need. — tiebob
– tiebob, Commented May 4, 2019 at 15:40
I know this is after you've fixed it, but could I recommend that if your using PDO to stick with an associative array and change the ? in the query to named parameters - :countrycode etc. This means it is easier to ensure that the right value is put into the right field, just an array of 41 fields can be easy to misalign if you add 1 field in the wrong place. — Nigel Ren
– Nigel Ren, Commented May 4, 2019 at 15:56

Wiimm · Accepted Answer · 2019-05-04 17:08:10Z

2

Not the answer, but a recommendation (and to large for a comment).

Extracted from your code and reformatted for clearness:

const_article,  const_wording,
const_article2, const_wording2,
const_article3, const_wording3,
...
law_act,  law_article,  law_source,  law_date,
law_act2, law_article2, law_source2, law_date2,
law_act3, law_article3, law_source3, law_date3,

It seems, that this is a 1:N relation. And you are working with relational database system (mysql). So why not use relations and one or more additional tables?

In fact, not using relations here will make other (future) request much more complicated.

edited May 4, 2019 at 17:08

answered May 4, 2019 at 15:48

Wiimm

3,7531 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

anmipa Over a year ago

Thanks, Wiimm, I did not think about that. Your recommendation seems indeed very useful!

ScaisEdge · Accepted Answer · 2019-05-04 15:41:56Z

1

for ? param you should not use an associative array but pass values as a positional

$data = [
    $_GET["id"],
    $_GET["country"],
    $_GET["region"],
     $_GET["law_db_region"],
    [... etc ...]
 ]

answered May 4, 2019 at 15:41

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Insert array into PDO (PHP): "SQLSTATE[HY093]: Invalid parameter number"

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related