php pdo bind parameter not working

Question

I'm using the following php pdo code to insert data into mysql database, the insertion succeeded, however, the updated database is showing the string literals ':a', ':b' as values in respectively field. what's wrong?

  
$data = array(
  array('a' => 'John', 'b' => 'OK'),

);
    $st=$dbh->prepare("insert into mytable (a, b) values(':a', ':b')");
    $st->execute($data) or print_r($st->errorInfo());

Michael Berkowski · Accepted Answer · 2012-03-05T19:44:56.890

2

Remove the quotes from your placeholders. Otherwise, they are treated as string literals and directly inserted.

$st=$dbh->prepare("insert into mytable (a, b) values(:a, :b)");

And remove the nesting on your array:

// $data is an associative array, it should not contain another array!
$data = array('a' => 'John', 'b' => 'OK');

To be consistent, I prefer to use the : on the placeholder array keys:

$data = array(':a' => 'John', ':b' => 'OK');

edited Mar 05 '12 at 19:44

answered Mar 05 '12 at 19:38

Michael Berkowski

267,341
46
444
390

now I get $st->errorInfo() showing without detailed error msg: Array ( [0] => HY093 [1] => [2] => ) – user121196 Mar 05 '12 at 19:42
@user121196 I see it now - you have a nested array `$data`, when it should be a single array. See addition above. – Michael Berkowski Mar 05 '12 at 19:45
I've put nearly 12 hours into trying to solve this bug. Thank you, @MichaelBerkowski! – IIllIIll Oct 18 '15 at 22:36

Developer · Answer 2 · 2012-03-05T19:53:30.867

1

You need to define your array the same in the sql and the parameters, you're missing the ":". You also don't need two arrays, only one.

$data = array(':a' => 'John', ':b' => 'OK');

The query also does not need quotes, since PDO already knows it's a parameter

$st=$dbh->prepare("insert into mytable (a, b) values(:a, :b)");
$st->execute($data) or print_r($st->errorInfo());

edited Mar 05 '12 at 19:53

answered Mar 05 '12 at 19:38

Developer

2,021
12
11

1

I believe the actually work with or with the `:`, even though it isn't documented. – Michael Berkowski Mar 05 '12 at 19:38
@Developer: i tried with :, it didn't make a difference.. in fact I used $st->bindParam(":a", "test"), it didn't make any difference... – user121196 Mar 05 '12 at 19:41
Remove the quotes from the parameters values(:a, :b) and not values(':a', ':b') – Developer Mar 05 '12 at 19:42
1

It's because your parameter is a nested array, you only need one array – Developer Mar 05 '12 at 19:53

Pradeep Rajput · Answer 3 · 2018-01-11T11:39:08.877

You are executing a prepared statement with named placeholders. So, you need to remove quotes from your placeholders, otherwise they are treated as a values for respective columns and directly updated.

To be consistent, I prefer to use the : on the placeholder array keys:

$data = array(':a' => 'John', ':b' => 'OK'); 

$st=$dbh->prepare("insert into mytable (a, b) values(:a, :b)");

You can also execute a prepared statement with question mark placeholders:

$data = array(
  array('John','OK'),

);
$st=$dbh->prepare("insert into mytable (a, b) values(?, ?)");
$st->execute($data) or print_r($st->errorInfo());

php pdo bind parameter not working

3 Answers3

Linked

Related