phpmyadmin Question posted in
The official documentation can be found here.

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

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.

Tags:

4

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    you can use PDO::PARAM_BOOL in PDO

    $stmt->bindValue(':myColumn', false, PDO::PARAM_BOOL);
    Login or Signup to reply.
  4. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search