add an null value as an option in a select

[flosigime]

Hello,

we're looking for a way to ad a null value in a select field in a form.

We succeded in doing this way :

SELECT  
'interpret' AS label,  
'tfait_interpret' AS name,  
'select' as type,  
'[' || group_concat(json_object("label", val_thes, "value", val_thes), ', ') || ', null]' as options FROM ... 

but, it seems that we don't get a null value, but an empty one.
If we're looking for null values in the table, we don't find any...

I red that group_concat is not always trustfull...

Is it possible to add a null value using :

json_group_array(json_object("label", val_thes, "value", val_thes)) as options

thank you

[lovasoa]

Hello !
The easiest way to do what you want is to add the additional null item in the FROM clause, instead of adding it after the fact in the json:

SELECT
  'My dropdown list' as name,
  'select' as type,
  json_group_array(json_object("label", my_title, "value", id)) as options
FROM (
  SELECT id, my_title FROM my_table
  UNION ALL
  SELECT NULL, 'None of the above'
);

Here is an example: https://replit.com/@pimaj62145/SQLPage-dropdown-populated-from-database-example?v=1#index.sql

[lovasoa]

About NULL getting transformed to the empty string (''): standard web forms can only send string values back to the server, and SQLPage is fully transparent, it doesn't try to decode form values to non-string types.
You can easily convert the empty string to a NULL in your sql statement that handles the form results with NULLIF($your_field, '')

[flosigime]

thanks a lot for your answer.
And, yes, it's exactly the point : an empty string and not a null value...