breq breq - 14 days ago 6
PHP Question

Doctrine - How to bind array to the SQL?

My SQL looks something like this:

$sql = "select * from user where id in (:userId) and status = :status";

$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue(':userId', $accounts, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY);
$stmt->bindValue(':status', 'declined');
$stmt->execute();

$result = $stmt->fetchAll();


But it returns:


An exception occurred while executing (...)

with params
[[1,2,3,4,5,6,7,8,11,12,13,14], "declined"]

Notice: Array to string conversion


I cannot user
queryBuilder
because my real SQL is more complicated (ex. contains joined select, unions and so on)

Answer

You can't use prepared statements with arrays simply because sql itself does not support arrays. Which is a real shame. Somewhere along the line you actually need to determine if your data contains say three items and emit a IN (?,?,?). The Doctrine ORM entity manager does this for you automatically.

Fortunately, the DBAL has you covered. You just don't use bind or prepare. The manual has an example: http://doctrine-orm.readthedocs.io/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

In your case it would look something like:

$sql = "select * from user where id in (?) and status = ?";
$values = [$accounts,'declined'];
$types = [Connection::PARAM_INT_ARRAY, \PDO::PARAM_STRING];
$stmt = $conn->executeQuery($sql,$values,$types);
$result = $stmt->fetchAll();

The above code is untested but you should get the idea.

Comments