php-sql-query-builder icon indicating copy to clipboard operation
php-sql-query-builder copied to clipboard

Do not insert null string in sql query if string is '0'

Open makeey opened this issue 5 years ago • 0 comments

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)
}
*/

makeey avatar Oct 22 '20 20:10 makeey