List of strings in prepared statement using Doctrine DBAL

Question

Following the Doctrine DBAL documentation I should be able to bind a list of string values like this:

$sql = 'SELECT * FROM mytable WHERE myfield IN (?)';

$stmt = $conn->prepare($sql);
$stmt->bindValue('myfield', array('stringa', 'stringb', 'stringc'), \Doctrine\DBAL\Connection::PARAM_STR_ARRAY);

$stmt->execute();

This results in a PHP notice and kills my script.

Notice: Array to string conversion in C:\www\eurocampings\vendor\doctrine\dbal\lib\Doctrine\DBAL\Statement.php on line 142

Did I do something wrong?

score 9 · Answer 1 · answered Jul 28 '14 at 22:41

9

From doctrine documenation:

$stmt = $conn->executeQuery('SELECT * FROM articles WHERE id IN (?)',
    array(array(1, 2, 3, 4, 5, 6)),
    array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY)
);

For those still having problem with binding array of values, Use the PARAM_INT_ARRAY or the PARAM_STR_ARRAY constants with the 'executeQuery' method instead of binding it to the prepared statement. This is the only way it works.

answered Jul 28 '14 at 22:41

Matan Hafuta

605
6
14

1

He asked decision for `$stmt->bindValue` + `$stmt->execute()` but not for `$conn->executeQuery`. I don't upvote and don't downvote. – FlameStorm Mar 30 '17 at 12:45

score 8 · Answer 2 · answered Jul 09 '14 at 10:22

8

From Doctrine DBAL documentation:

The parameter list support only works with Doctrine\DBAL\Connection::executeQuery() and Doctrine\DBAL\Connection::executeUpdate(), NOT with the binding methods of a prepared statement.

It is on the end of this link: http://doctrine-dbal.readthedocs.org/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

answered Jul 09 '14 at 10:22

xyfantis

81
1
5

Might have been nice if they also told us that it only works with positional parameters (i.e., "?" placeholders) rather than named parameters. I just spent many minutes working that out. – David Oct 10 '18 at 13:54

score -2 · Answer 3 · answered May 29 '13 at 07:43

-2

Try with change the following code:

$stmt->bindValue('fcodes', array('stringa', 'stringb', 'stringc'), \Doctrine\DBAL\Connection::PARAM_STR_ARRAY);

With the below code:

$stmt->bindValue('fcodes', "'stringa', 'stringb', 'stringc'", \Doctrine\DBAL\Connection::PARAM_STR_ARRAY)

Hope it will work as the error show to you on the basis of that.

answered May 29 '13 at 07:43

Manual quoting values - for generally unknown DBMS, may be not MySQL, not Postgre but somethin other - and inline joining in string is bad practice. And of course the `, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY` is wrong because you alredy do all the job and specify inline string - your type is wrong. – FlameStorm Mar 30 '17 at 12:43

kolomiets · Answer 4 · 2014-06-05T12:54:09.217

-3

Use Doctrine\DBAL\Types::SIMPLE_ARRAY instead of \Doctrine\DBAL\Connection::PARAM_STR_ARRAY

For example

$values = ['stringa', 'stringb', 'stringc'];
$statement->bindValue ('column_name', $values, Doctrine\DBAL\Types::SIMPLE_ARRAY);

edited Jun 05 '14 at 12:54

answered Jun 05 '14 at 10:31

kolomiets

87
2

provide more details. – Parixit Jun 05 '14 at 10:50
Not here. In your answer. – Parixit Jun 05 '14 at 12:50
Not worked. When you suggest, please try to do what you said. – FlameStorm Mar 30 '17 at 12:38

List of strings in prepared statement using Doctrine DBAL

4 Answers4