breq breq - 1 year ago 173
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');

$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
because my real SQL is more complicated (ex. contains joined select, unions and so on)

Answer Source

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:

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download