php-sql-query-builder
php-sql-query-builder copied to clipboard
Do not insert null string in sql query if string is '0'
Hello @nilportugues! I run into an issue during using the query builder, I have next table structure.
CREATE TABLE IF NOT EXISTS `Table` (
`UserID` int(1) NOT NULL,
`EnumValue` ENUM("0", "1", "2") NOT NULL
) ENGINE = InnoDB;
If I'll use "0" as the value of enums (it says for status), the result will be "NULL" in the query. Code to reproduce the issue
$genericQueryBuilder = new \NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder();
$query = $genericQueryBuilder->update()
->setTable('table')
->setValues(
[
'EnumValue' => '0'
]
)->where()
->equals('UserId', 1)
->end();
echo $genericQueryBuilder->write($query);
//UPDATE table SET table.EnumValue = :v1 WHERE (table.UserId = :v2)
var_dump($genericQueryBuilder->getValues());
/*
array(2) {
[":v1"]=>
string(1) "NULL"
[":v2"]=>
int(1)
}
*/
As you can see '0' becomes NULL in the end, and it isn't a valid enum value in this case expected values would be
/*
array(2) {
[":v1"]=>
string(1) "0"
[":v2"]=>
int(1)
}
*/