PDO Insert not accepting "true" or "false", but only 0 and 1

Question

After introducing phinx as a database migration tool, I am no longer able to use true and false through PDO's execute statement. Whenever I do, I get the following error:

PHP Warning: PDOStatement::execute(): SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'my_db'.'my_table'.'my_column' at row 1...

My table has the following schema (shorted):

| Field                         | Type         | Null | Key | Default | Extra          |
+-------------------------------+--------------+------+-----+---------+----------------+
| my_column                     | tinyint(1)   | NO   |     | NULL    |                |
+-------------------------------+--------------+------+-----+---------+----------------+

I am using the following code (shorted):

$stmt = $this->pdo->prepare("INSERT INTO `$table` (`my_column`) VALUES (:mycolumn)");
$stmt->execute([
   'my_column' => false
]);

The column is created by the migration script with:

->addColumn('my_column', 'boolean', [
  'null' => false,
  'after' => 'another_column',
])

The strange thing is, that I have no problems with using true and false in manual sql statements through phpMyAdmin.

Just make sure the `$table` is properly escaped, by the way. — Mike Doe, Jun 11 '19 at 08:51
By the way, define default value when you are creating a column — Michael Krutikov, Jun 11 '19 at 10:41

score 2 · Answer 1 · answered Jun 11 '19 at 08:38

2

you can use PDO::PARAM_BOOL in PDO

$stmt->bindValue(':myColumn', false, PDO::PARAM_BOOL);

answered Jun 11 '19 at 08:38

Ali Ghaini

882
6
13

Yes, most likely I should do that. However, it isn't exactly the fix I need. I have got this problem at many places throughout my application, which means I would have to change it everywhere as far as I understood. The answer by Red Bottle fixed it for me however. I don't want to accept that either however, because I believe it wasn't downvoted without reason. I don't know why it would be practice though, if I include the command in my migration file. If you could adapt your solution to work in my entire application without making changes everywhere, I could accept your answer. – allinonemovie Jun 12 '19 at 06:24

score 0 · Answer 2 · answered Sep 13 '21 at 13:37

0

This happened to me when i use macOS,

i was building my website from Windows 10, and works fine. After using macOS i had to change the variables to 1 or 0

I hope you fixed it.

answered Sep 13 '21 at 13:37

obeid salem

129
2
13

Red Bottle · Answer 3 · 2019-06-11T08:47:08.263

-1

A solution to that problem is to set sql_mode = '' globally.

Run this query in you DB and check if it fixes the issue:

set GLOBAL sql_mode = "";

You can read up on Server SQL Modes here

P.S. you have to run that query everytime you restart your mysql server. To overcome this you have to set it in mysql config which you can look it up if required.

edited Jun 11 '19 at 08:47

answered Jun 11 '19 at 08:35

Red Bottle

2,839
4
22
59

That fixed it for me! Can you elaborate a bit more on what it does exactly? Does it have something to do with how MySQL parses commands? – allinonemovie Jun 11 '19 at 08:43
shared the link to the doc for your reference – Red Bottle Jun 11 '19 at 08:47

score -2 · Answer 4 · answered Jun 11 '19 at 08:34

-2

Your field type is tinyint(1). so it supports only values from 0-9 . Change to to varchar, text, char etc to accept true/false string. But i strongly suggest you to use bool datatype. Then use 0 or 1 as true or false. There is no point in writing string (true/false) values for boolean operations. Using 0 or 1 will save you from lot of troubles in the future.

answered Jun 11 '19 at 08:34

Saroj Pandey

1
1

As far as my research goes, boolean is internally represented as tinyint(1) by MySQL. I am referring to [this question](https://stackoverflow.com/questions/3751853/boolean-vs-tinyint1-for-boolean-values-in-mysql). – allinonemovie Jun 11 '19 at 09:03
This is just wrong. tinyint always supports [-128,127] or [0,255] unsigned. The number in brackets is the display width and is afaik only for ZEROFILL, nothing else. A column of tinyint(1) will display 1 with ZEROFILL. tinyint(2): 01, tinyint(3): 001 ... – aProgger Nov 22 '20 at 10:21

PDO Insert not accepting "true" or "false", but only 0 and 1

4 Answers4