create_new in dropdown -> INSERT into database

[ArchaeoBasti]

Hi!
So I successfully created a database and a SQLPage site that shows all my data. I also created a form that can add new data to the database which is awesome. I also work with dropdown lists that show a list of options (for example cities). With the create_new property, the user can add a city, that is not part of the dropdown.
Now In my database I have a table "cities" with ID and city as rows and I have the table "content" which stores the ID of "cities" that is selected in the dropdown. It works for already present cities of the table:

SELECT CAST(value AS integer) AS ID FROM json_each(:city)

My question now is, how can I create a new city in my "cities" table and also already enter the correct ID into my "content" table?

Thanks a lot for your help!
Best
Sebastian

[lovasoa]

Here is my suggestion (written for sqlite, but adaptable to other databases):

DROP TABLE IF EXISTS content;
DROP TABLE IF EXISTS cities;

CREATE TABLE cities (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    city VARCHAR(255) NOT NULL
);
CREATE TABLE content (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    city_id INTEGER,
    FOREIGN KEY (city_id) REFERENCES cities(ID)
);

insert into cities(city) values ('paris'), ('london');

-- Form to create content, including the ability to add a new city
SELECT 'form' AS component;
SELECT 'city' AS name, 'City' AS label, 'select' AS type, 
       (
           SELECT json_group_array(json_object('label', city, 'value', ID))
           FROM cities
       ) AS options,
       TRUE AS create_new,
       TRUE as dropdown;

-- insert missing cities
set existing_city_id = (select id from cities where id = CAST(:city AS INT));

insert into cities(city)
select :city where $existing_city_id is null and :city is not null;

-- Insert a new content element (either by linking existing or just inserted city)
insert into content (city_id) 
select COALESCE($existing_city_id, last_insert_rowid()) 
where :city is not null;

select 'table' as component;
select * from cities;

select 'table' as component;
select * from content;

Screencast.From.2025-03-05.23-58-32.mp4

[ArchaeoBasti]

Awesome! I made it work, but there is only one thing I can't fix. If the city is entered. The database shows ["New York"] not New York. I am not sure why there are brackets and quotation marks? I guess it has something to do with data types?

[ArchaeoBasti]

Don't mind, I figured it out:

SELECT json_extract(:city, '$[0]') WHERE $existing_city_id IS NULL AND :city IS NOT NULL;

[lovasoa]

If you are using a multi-select dropdown and name your field city[],
then :city contains a json array with all the selected cities.

You can process json arrays using your database's json function