How to input Boolean in MYSQL table field using Wappler

[bradbrd992]

By now most of us that are new to Wappler has ran into this issue and probable spend one day too long testing. I am talking about, false vs ‘false’, true vs ‘true’ in Wappler. In your database insert or update if enter false without the ’ ’ this is saved as a Boolean thus inserting a zero (0) likewise true without the ’ ’ insert a one (1). This is great when working in MYSQL database that doesn’t really reference Boolean but rather TINYINT.

So, to have a default Boolean returns 0 or 1 in your MYSQL database do this

and to save a string as ‘false’ or ‘true’ do this.

This is not groundbreaking discovery it’s just a reminder not to go crazy when your Wappler Condition seems to not be doing the right thing.

[Teodor]

MySQL doesn’t have a built-in Boolean type. It uses TINYINT(1) instead.
Zero is considered as false, and a non-zero value is considered as true.

Wrapping a value with ' ' makes it a string, so you are saving it in the database as a string.

[bradbrd992]

You stand correct @Teodor. I was just giving this useful information to the community.

[Apple]

Nice trick! I’ve always used 0 and 1. Wappler deals with booleans a bit bizarrely (converts to string, right?)

[karh]

I’m confused as to how to use it properly @Teodor , can you help out?

Goal: I want to query all records where the column deleted (tinyINT - made with wappler’s db manager) is not 1.

Screenshot of part of the data:

So several records have deleted=1

This is what I’m trying and what I’m expecting (I’m using paged records to limit the records to see what’s happening easier:

1. Query all records to have a baseline

Query is:

Result is:

data": [
      {
        "id": 12,
        "deleted": 1
      },
      {
        "id": 13,
        "deleted": null
      },
      {
        "id": 14,
        "deleted": null
      },
      {
        "id": 15,
        "deleted": 1
      },
      {
        "id": 16,
        "deleted": 1
      }
    ]

2. Typing 1 in the where clause (without the dynamic data picker)

Query is:

Result is:

    "data": [
      
    ]

I’m getting NO results

Sidenote: it is changed to ‘true’ in the UI which is not consistent with the next try (3):

2022.01.28 13.45 - 3772 - Bluet

3. Typing 1 in the where clause (WITH the dynamic data picker)

Query is:

Result is:

    "data": [
      
    ]

**I’m getting NO results again **

4. Typing '1' in the where clause (without the dynamic data picker)

Query is:

Result is:

"data": [
      {
        "id": 12,
        "deleted": 1
      },
      {
        "id": 15,
        "deleted": 1
      },
      {
        "id": 16,
        "deleted": 1
      },
      {
        "id": 18,
        "deleted": 1
      },
      {
        "id": 19,
        "deleted": 1
      }
    ]

I’m getting all the records that ARE deleted. I was expecting to get the records that have deleted: 0 or deleted: null

5. Typing '1' ( WITH the dynamic data picker)

Query is:

Result is:

    "data": [
      {
        "id": 12,
        "deleted": 1
      },
      {
        "id": 15,
        "deleted": 1
      },
      {
        "id": 16,
        "deleted": 1
      },
      {
        "id": 18,
        "deleted": 1
      },
      {
        "id": 19,
        "deleted": 1
      }
    ]

[Teodor]

Let me test this please.

[Teodor]

@karh i see you are using null as default values for your table. Null values are special values and can only be checked with the IS_NULL condition.
<> 1 will only return values other than 1 and null

So you need to either check for IS_NULL or change the default values for your table to be 0 and use the NOT EQUAL to 1.

@karh

Sidenote: it is changed to ‘true’ in the UI

That is perfectly fine and expected, the database converts the boolean true to 1 and false to 0.

[karh]

Thanks @Teodor

I’ll go with the method of setting default value to 0 and then checking if it’s 1 or 0

So I’m using the NOT EQUAL to 1:

Which is indeed getting ONLY the records where deleted = 0.

This works