Dbal

5.0 4.0 3.1 3.0 main
Edit

Parameter Modifiers

Dbal allows you easy way to escape and build your SQL query. You can use powerful modifiers:

%s, %?s, %s[] string not nullable, nullable, array of
%i, %?i, %i[] integer not nullable, nullable, array of
%f, %?f, %f[] float not nullable, nullable, array of
%b, %?b, %b[] boolean not nullable, nullable, array of
%dt, %?dt, %dt[] datetime not nullable, nullable, array of
%dts, %?dts, %dts[] datetime datetime with conversion to simple storage time zone
%blob, %?blob, %blob[] binary string not nullable, nullable, array of
%any, %any[]   any value scalary value, array of scalar values
%_like, %like_, %_like_ string like left, like right, like both sides
%json, %?json, %json[] any not nullabe, nullable, array of

All modifiers require receiving an argument of the specific modifier data type – eg. %f accepts only floats and integers.

$connection->query('id = %i AND name IN (%?s, %?s)', 1, NULL, 'foo');
// `id` = 1 AND name IN (NULL, 'foo')

$connection->query('name LIKE %_like_', $query);
// escapes query and adds % to both sides
// name LIKE '%escaped query expression%'

Other available modifiers:

%table, %table[] escapes string as table name, surrounding parentheses are not added to %table[] modifier
%column, %column[] escapes string as column name, surrounding parentheses are not added to %column[] modifier
%ex expands array as processor arguments
%raw inserts string argument as is
%% escapes to single % (useful in date_format(), etc.)

%ex modifier expands passed array as arguments of new query() method call.

$connection->query('%ex', ['id = %i', 1]);
// equals to
$connection->query('id = %i', 1);

Last modifiers are connected with array processing:

%and AND condition
%or OR condition
%multiOr OR condition with multiple conditions in pairs
%values, %values[] expands array for INSERT clause, multi insert
%set expands array for SET clause

Let's examine %and and %or behavior. If array key is numeral and value is array, value is expanded with %ex modifier.

$connection->query('%and', [
    'city' => 'Winterfell',
    'age'  => 23,
]);
// `city` = 'Winterfell' AND `age` = 23


$connection->query('%or', [
    'city' => 'Winterfell',
    'age'  => [23, 25],
]);
// `city` = 'Winterfell' OR `age` IN (23, 25)


$connection->query('%or', [
    'city' => 'Winterfell',
    ['[age] IN %i[]', [23, 25]],
]);
// `city` = 'Winterfell' OR `age` IN (23, 25)

If you want select multiple rows with combined condition for each row, you may use multi column IN expression. However, some database does not support this feature, therefore Dbal provides universal %multiOr modifier that will handle this for you and will alternatively use expanded verbose syntax; let's see an example:

$connection->query('%multiOr', [
    ['tag_id' => 1, 'book_id' => 23],
    ['tag_id' => 4, 'book_id' => 12],
    ['tag_id' => 9, 'book_id' => 83],
]);
// MySQL or PostgreSQL
// (tag_id, book_id) IN ((1, 23), (4, 12), (9, 83))

// SQL Server
// (tag_id = 1 AND book_id = 23) OR (tag_id = 4 AND book_id = 12) OR (tag_id = 9 AND book_id = 83)

Examples for inserting and updating:

$connection->query('INSERT INTO [users] %values', [
    'name' => 'Jon Snow'
]);
// INSERT INTO `users` (`name`) VALUES ('Jon Snow')


$connection->query('INSERT INTO [users] %values[]', [
    ['name' => 'Jon Snow'],
    ['name' => 'The Imp'],
]);
// INSERT INTO `users` (`name`) VALUES ('Jon Snow'), ('The Imp')


$connection->query('UPDATE [users] SET %set WHERE [id] = %i', [
    'name' => 'Jon Snow'
], 1);
// UPDATE `users` SET `name` = 'Jon Snow' WHERE `id` = 1

Custom Modifiers#

You may add support for own modifier. To do that, create new factory class for SqlProcessor and use setCustomModifier() method:

class SqlProcessorFactory implements Nextras\Dbal\ISqlProcessorFactory
{
    public function create(Nextras\Dbal\IDriver $driver, array $config)
        : Nextras\Dbal\SqlProcessor
    {
        $processor = new Nextras\Dbal\SqlProcessor($driver);
        $processor->setCustomModifier('mybool', function ($bool, $modifier) use ($driver) {
            return $driver->convertStringToSql($bool ? 'yes' : 'no');
        });
        return $processor;
    }
}