use the result from multiple choice in form in a queries

[ZevZ81]

Hi,

I'm new to SQLpage and been testing it out it, now I have faced a problem that I can't work pass.
I've tried to search for answers, but with no luck.

I have a form as component with multiple choice, this work fine.
But my problem is that I want to use the result of my choices on my next page in a SQL queries.
This work fine if I don't use multiple choice, but when I set the parameter to multiple = true and use [] after the name I cant use it in my queries.

This is how I've set up the form:

select 
    'form'                                  as component,
    'Velg ny middag å legge til'                     as title,
    'shopping_list_submit.sql'   as action ;

select 
    'Middag'          as label,
    'SetDinner[]'        as name,                        
    'select'                as type,  
    TRUE                    as multiple,
    FALSE                  as create_new,
    'Legg til middag'     as placeholder,
    TRUE                    as searchable,
    
       
    json_group_array(json_object(
        "label", name,
        "value", id
        )) as options    FROM Dinner;

I try to present the result for my SQL queries in a list:

Select 'list' as component;

        SELECT DISTINCT
            Ingredient.Name AS title
            FROM Ingredient
            INNER JOIN Xref ON Ingredient.Id=Xref.Ingredient
            WHERE Xref.Dinner IN (:SetDinner);

But I'm not able to make this work.

If i print the result as text it look something like this: ["1","5","7"]
If I use the debug tool it look like this: {"component":"debug","contents":"["1","5","7"]"}

My queries work if the form result print it like this: "1"

Could anyone point me in the right direction for solving my issue.

BR
Tom-Erik

[lovasoa]

Hello and welcome to SQLPage ! Which database are you using ? ["1","5","7"] is a json array, and unfortunately, different databases have different ways to deal with json. This page of the documentation evokes the theme: https://sql.datapage.app/functions.sql?function=variables

For instance, this is how you would do it in SQLite (the embedded database that is embedded in SQLPage):

SELECT DISTINCT Ingredient.Name AS title FROM Ingredient INNER JOIN Xref ON Ingredient.Id=Xref.Ingredient
WHERE Xref.Dinner IN (select value from json_each(:SetDinner));

[ZevZ81]

Thank you for your reply.
I'm using SQLite, so I'll try that when I'm back home in a couple of days. Again thanks!

[lovasoa]

You are welcome ! What are you building ? A norwegian meal planning app ? 😋

[ZevZ81]

Yeah, my wife love to plan dinners, so she asked for an app to do this. And to get her the shopping list for selected dinners. I needed to learn something new, so that's a win-win situation 😊 So far I really like SQLpage 👍

[lovasoa]

looks cool, feel free to share your app here ! Others would be happy to learn from it :)

[alexisrc1]

hello @ZevZ81 and thanks for using SQLPage! when your app will be ready you might be interested in hosting it with us via our service datapage.app, make sure to check it out :)

[lovasoa]

I implemented it in a replit as a demo:

You can play with it directly from your browser here: https://replit.com/@pimaj62145/SQLPage-multi-select#index.sql