Clarification for use of null and conditions in INSERT and UPDATE Queries

Wappler General How To
#1

I am looking for clarification on the use of conditions and .default(null) in both the Value and Condition in Insert and Update Queries.

Below I have set out a table with examples of 'text', 'number' and 'date' fields. Each of these has six combination examples of how they could be used. I suppose I am looking for clarification on which should be used and which should not. And in the case of which should be used, then why would you use it.

Also, the answer may well depend on the database, whether it has any form of Strict applied, and whether NULL values are allowed. If your answers include any of this further information I am sure people would find it helpful.

I know this could be expanded with formatting in the Value column or, say max() in the Condition column but I am trying to get the basics right.

┌────┬──────────────┬──────┬───────────────────────────────────────┬───────────────────────────────────────┐
│ #  │    Column    │ Type │                 Value                 │               Condition               │
├────┼──────────────┼──────┼───────────────────────────────────────┼───────────────────────────────────────┤
│  1 │ trading_name │ text │ {{$_POST.trading_name}}               │                                       │
│  2 │ trading_name │ text │ {{$_POST.trading_name.default(null)}} │                                       │
│  3 │ trading_name │ text │ {{$_POST.trading_name}}               │ {{$_POST.trading_name}}               │
│  4 │ trading_name │ text │ {{$_POST.trading_name.default(null)}} │ {{$_POST.trading_name}}               │
│  5 │ trading_name │ text │ {{$_POST.trading_name}}               │ {{$_POST.trading_name.default(null)}} │
│  6 │ trading_name │ text │ {{$_POST.trading_name.default(null)}} │ {{$_POST.trading_name.default(null)}} │
├────┼──────────────┼──────┼───────────────────────────────────────┼───────────────────────────────────────┤
│  7 │ ref_num      │ num  │ {{$_POST.ref_num}}                    │                                       │
│  8 │ ref_num      │ num  │ {{$_POST.ref_num.default(null)}}      │                                       │
│  9 │ ref_num      │ num  │ {{$_POST.ref_num}}                    │ {{$_POST.ref_num}}                    │
│ 10 │ ref_num      │ num  │ {{$_POST.ref_num.default(null)}}      │ {{$_POST.ref_num}}                    │
│ 11 │ ref_num      │ num  │ {{$_POST.ref_num}}                    │ {{$_POST.ref_num.default(null)}}      │
│ 12 │ ref_num      │ num  │ {{$_POST.ref_num.default(null)}}      │ {{$_POST.ref_num.default(null)}}      │
├────┼──────────────┼──────┼───────────────────────────────────────┼───────────────────────────────────────┤
│ 13 │ post_date    │ date │ {{$_POST.post_date}}                  │                                       │
│ 14 │ post_date    │ date │ {{$_POST.post_date.default(null)}}    │                                       │
│ 15 │ post_date    │ date │ {{$_POST.post_date}}                  │ {{$_POST.post_date}}                  │
│ 16 │ post_date    │ date │ {{$_POST.post_date.default(null)}}    │ {{$_POST.post_date}}                  │
│ 17 │ post_date    │ date │ {{$_POST.post_date}}                  │ {{$_POST.post_date.default(null)}}    │
│ 18 │ post_date    │ date │ {{$_POST.post_date.default(null)}}    │ {{$_POST.post_date.default(null)}}    │
└────┴──────────────┴──────┴───────────────────────────────────────┴───────────────────────────────────────┘

I seem to have got myself into a muddle with various combinations and would like it sorting once and for all. Cheers

#2

The .default() in the insert/update should be used in the value field, as it is the one storing values in the database.
The condition option is there to check for a condition which tells insert/update step when to insert/update the value in the database. You don’t need .default() there as it makes no sense at all.

In short - if the condition is met, the value will be inserted. If the value is empty and you are using .default('something') then something will be inserted in the database.
If the condition is not met no value will be inserted.

#3

Hi Teodor,

thanks for your reply, these are my findings. Until I did this it wasn’t really clear to me.

┌───┬───────────────────────────────────────┬─────────────────────────┐
│ # │                 Value                 │        Condition        │
├───┼───────────────────────────────────────┼─────────────────────────┤
│ 1 │ {{$_POST.trading_name}}               │                         │
│ 2 │ {{$_POST.trading_name.default(null)}} │                         │
│ 3 │ {{$_POST.trading_name}}               │ {{$_POST.trading_name}} │
│ 4 │ {{$_POST.trading_name.default(null)}} │ {{$_POST.trading_name}} │
└───┴───────────────────────────────────────┴─────────────────────────┘

  1. will insert/update the value in the database even if it’s blank
    So BLANK in the Value results in BLANK in the Database, here are further results

    • BLANK > BLANK
    • '0' > 0
    • 'space' > 'space'
    • Text > Text (this includes characters such as '-')

  2. will insert/update the value in the database
    If the value is blank or zero then it will insert/update 'NULL' in the database

    • BLANK > NULL or puts the database default if there is one
    • '0' > NULL or puts the database default if there is one
    • 'space' > 'space'
    • Text > Text (this includes characters such as '-')

  3. and 4. the condition means that if there is a value (ie not blank)
    then it will insert/update the value in the database
    and will ignore altogether if the value is blank or zero

    • BLANK > no insert/update
    • '0' > no insert/update
    • 'space' > 'space'
    • Text > Text (this includes characters such as '-')
      Also, the 'null' will have no affect

Blank in the above means nothing in the Input Field, ie it has been cleared.
So a 'space', a '0' or a '-' are all a value and do not count as blank.

1 Like
#4

For the condition:

There is also the option of putting this in the condition
{{$_POST.trading_name || $_POST.trading_name == '0'}}
note 0 is quoted as a string

Which prevents the field updating at all if blank or not present but will update if the value 0 is sent.

I find this useful for checkboxes where I have 1 or 0 being stored in the DB for on/off. I set a hidden field with the a dynamic value to dmx-bind:value="checkboxid.checked ? '1' : '0'"

Because I use the condition, I can use the same update SC action for records in different places where the value may or may not be submitted with the form (maybe it’s not relevant in that area). Using .default(0) would always update with 0 if the value wasn’t submitted with the form.

2 Likes
#5

Obviously I was just trying to set out the basics but it’s really good that you have come in with some great tips which can build on those basics. This is how many of us learn. Cheers Ben.

1 Like