I have a problem with a new MariaDB 10.5.8 install. STRICT_TRANS_TABLES
is set, and when I try to use $sql
of:
'INSERT INTO test (flag) VALUES (?)'
(where flag
is defined as tinyint(1)
) with var_dump($params)
showing as:
array(1) { [0]=> bool(false) }
I get this message:
Incorrect integer value: '' for column `mydb`.`test`.`flag` at row 1
If, instead, I do:
'INSERT INTO test (flag) VALUES (false)'
with no parameters, it works as expected.
This is how I connect to the database:
$this->PDO = new PDO('mysql:host=' . DB_SERVER . ';dbname=' . DB_NAME . ';charset=utf8mb4', DB_USER, DB_PASSWORD, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_STRINGIFY_FETCHES => false, ]); $this->PDO->query("SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'");
and this is how I send the query/params to MariaDB:
$stmt = self::$_instance->PDO->prepare($sql); $stmt->execute($params);
If I try to insert true
instead of false
, everything works fine. true
is being converted to 1
somewhere along the lines, and false
to ''
. What am I doing wrong?
Advertisement
Answer
All values are treated as PDO::PARAM_STR.
https://www.php.net/manual/en/pdostatement.execute.php
The execute()
method does not enable you to specify the variable’s type. It always casts to a string, which is why you’re getting strange results:
php > var_dump((string) true); string(1) "1" php > var_dump((string) false); string(0) ""
You can use PDOStatement::bindValue() to specify the value’s type:
$statement->bindValue('some_bool', true, PDO::PARAM_INT);
Note that MySQL lacks a ‘true’ boolean type, which is why I’d recommend PDO::PARAM_INT
over PDO::PARAM_BOOL
.