Symfony2 DBAL array parameters

Symfony2 DBAL array parameters ©

Few days ago I had to use DBAL statement that will execute SQL IN(somearray) statement.

Considering that I used Doctrine DBAL in this part of code, I had to find the proper way of doing it.
I wrote some basic things about using Doctrine DBAL inside Symfony2 project, but I didn’t ever need this one before until now …

Regularly, when I made statements with parameters, I found easy to use this approach:

 $sql = 'SELECT rel.path,
         FROM items_to_collections rel
         INNER JOIN items on = rel.item_id
         INNER JOIN statuses s on = rel.status_id
         INNER JOIN item_types it on = items.item_type_id
         WHERE s.status_symbol = ?
         AND it.item_type_symbol = ?
         AND items.item_name = ?';
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(1, 'st_live');
        $stmt->bindValue(2, 'it_application');
        $stmt->bindValue(3, $appname);
        $item = $stmt->fetch(\PDO::FETCH_ASSOC);

But, when you try to pass array in this type of statements, it not working this way.

Instead using $conn->prepare;, $stmt->bindValue(….) …, let’s use executeQuery() function of DBAL connection object.

So, when passing array into SQL statements, for example when we are using:

“SELECT FROM something WHERE IN (?)

I found this approach to be best:

"SELECT DISTINCT, tags.tag_name
        FROM tags
        INNER JOIN tags_to_items tti on tti.tag_id =
        WHERE tti.item_id IN (?)",
        array($items_ids), array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY));
        $tags = $stmt->fetchAll(\PDO::FETCH_ASSOC);

Of course using this way to fetch data, you also have to choose right parameter type which can be one of types: \Doctrine\DBAL\Connection::PARAM_some_param_type or can be one of types:

Hoping that this post will be useful …

Cheers :-).

You made it all the way down here so you must have enjoyed this post! You may also like:

Symfony2 DBAL QueryBuilder Darko Goles
Darko Goles, | 6

Symfony2 DBAL QueryBuilder

Symfony2 caching Doctrine2 results Darko Goles
Darko Goles, | 2

Symfony2 caching Doctrine2 results

Symfony2 registering Doctrine Event Listeners Darko Goles
Darko Goles, | 6

Symfony2 registering Doctrine Event Listeners

1 comment

  1. How would this work if you wanted to add more thanr just the IN clause to you where condition :

    WHERE s.status_symbol = ?
    AND it.item_type_symbol = ?
    AND tti.item_id IN (?)”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.

Tell us about your project

Drop us a line. We'd love to know more about your project.