String data, right truncated: 1406 Data too long on Bit Column

Question

I just recently added a bit column and for some reason, I cannot edit its value. Everything else is fine. I look for the error online and some people recommended disabling the strict mode, so I did. Thank you for looking at my question.

I tried disabling the strict mode using SET sql_mode = '';, but it still doesn't work.

SHOW CREATE TABLE tbl_users;

| tbl_users | CREATE TABLE `tbl_users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `user_role_id` int DEFAULT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `admin_approved` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

edit.php

<?php
//DB Connection
include 'conn.php';

if ($_POST['action'] == 'edit') {
    $data1 = [
        ':first_name' => $_POST['first_name'],
        ':last_name' => $_POST['last_name'],
        ':email' => $_POST['email'],
        ':admin_approved' => $_POST['admin_approved'],
        ':user_id' => $_POST['user_id'],
    ];

    $query1 = "
 UPDATE tbl_users
 SET first_name = :first_name,
 last_name = :last_name,
 email = :email,
 admin_approved = :admin_approved
 WHERE tbl_users.user_id = :user_id
 ";
    $statement = $connect->prepare($query1);
    $statement->execute($data1);
    //$statement->close();
    //echo json_encode($_POST);

    $result1 = mysqli_query($conn, $query1);
    if ($result) {
        echo "Successfully Edited";
    } else {
        echo "Error: " . $sql . "" . mysqli_error($conn);
    }
}



if ($_POST['action'] == 'delete') {
    $data2 = [
      ':user_id' => $_POST['user_id'],
  ];

    $query2 =
     "
DELETE FROM tbl_users
WHERE user_id = :user_id
";

    $statement = $connect->prepare($query2);
    $statement->execute($data2);
    //$statement->close();
    //echo json_encode($_POST);

    $result1 = mysqli_query($conn, $query2);
    if ($result) {
        echo "Successfully Deleetd";
    } else {
        echo "Error: " . $sql . "" . mysqli_error($conn);
    }
}

//echo json_encode($_POST);
?>

<?php
    foreach ($_POST as $key=>$post_data) {
        echo "You posted:" . $key . " = " . $post_data . "<br>";
    }
?>

Error msg:

<br />
<b>Fatal error</b>:  Uncaught PDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'admin_approved' at row 1 in /var/www/html/wordpress/multi_users/edit.php:23
Stack trace:
#0 /var/www/html/wordpress/multi_users/edit.php(23): PDOStatement-&gt;execute()
#1 {main}
  thrown in <b>/var/www/html/wordpress/multi_users/edit.php</b> on line <b>23</b><br />

Answer 1

The data type BIT only accepts binary values. You specified the length to be 1 bit long but you try to input a string of value '1' which in binary is 00110001 and it obviously overflows.

The recommended practice for storing booleans in database is to use nullable DateTime data type. e.g.

`admin_approved` DATETIME DEFAULT NULL

Then if the value is populated you know the boolean state is on, and if the value is NULL the boolean state is off. Additionally you know when the flag was switched on.

You could also use tinyint and store 1 or 0. However, from experience I can say it is not very useful.