Symfony2 DBAL array parametersI previously wrote about switching from the mysql extension hgh pen cost PDO. PDO introduces a number of convenient features beyond the mysql extension such as transactions, prepared statements, and more fetching options. However there are still a few things dbzl are a bit painful. It adds doctrine dbal named parameters few conveniences beyond straight PDO as well as a query builder. There are a number of projects under the Doctrine umbrella including a full ORM.
php - Doctrine DBAL - WHERE IN array with additional parameter - Stack Overflow
I previously wrote about switching from the mysql extension to PDO. PDO introduces a number of convenient features beyond the mysql extension such as transactions, prepared statements, and more fetching options. However there are still a few things that are a bit painful. It adds a few conveniences beyond straight PDO as well as a query builder. There are a number of projects under the Doctrine umbrella including a full ORM. This article will only cover the DBAL project. Alternatively you can download a zip archive from the project page.
For this article I am going to assume you are connecting to MySQL but the api is the same regardless of the database you are connecting to. This means you can create the connection in the bootstrap of your application and if no queries are run for a particular request it wont need to actually connect to the database server. Where I find the query builder to be the most helpful is when you have parts of the query that are dependent on user provided data.
For example imagine a page with a search form with multiple fields that filter results by different columns. You might have a text field to search the names of users and another text field to search by email address. There is also a select box to search by account type with the values Any, Editors, and Members. If Any is selected you do not want to filter by account type but if Editors or Members is selected you do. With the other fields you only want to filter them if the user entered anything in the fields.
You always only want to return active accounts. Not only is that code shorter but if you read it out loud it even sounds more like English. This greatly helps at understanding what the code does at a glance 6 months later. The query builder does result in some extra overhead as it needs to compile the query so raw SQL is generally a better idea if the query is simple.
Doctrine DBAL adds a lot of functionality for a very lightweight overhead. Even if you don't use the query builder it is worth it for the shorter syntax on inserts, updates, and deletes as well as the ability to prepare, execute, and fetch data in one statement. There is a lot more functionality I didn't cover so I recommend taking a look at the documentation for other pieces of functionality such as events, a schema manager, caching, sharding, and more. Add to the composer.