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 :-).

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>.