Missing a canonical example on how to list and edit a table

[pedrolvg]

Hey everyone. I recently found out about sqlpage, and it looks like very useful tool that I could use on a myriad of contexts. When I visit the site thought, after reading about what it is and what it does. The first thing I am after is an example of what I think most people would start with:

List a a table with data from a database table or query and editing the rows with a form. The examples that use the table plugin hardcode the values in the query.

It would be nice to have the absolute canonical example to do this... with as few optional aspects as possible. Just the simple case, and then perhaps a more evolved example right below ilustrating commonly use tweaks.

[lovasoa]

Hello! I agree we need more examples and a better way to display them on the website.

We currently have the following examples on the repo:

• https://github.com/lovasoa/SQLpage/tree/main/examples

And the following are non-official complete open source applications based on SQLPage that users have shared:

• https://github.com/DSMejantel/Ecole_inclusive
• https://github.com/lovasoa/SQLpage/discussions/241

[DSMejantel]

Hello, I am thinking about to write some example too. I will try to explain how to edit and update. It's a frequently question for the news users of SQLpage.

First, we have the table with a 'markdown' column for icon and link : this is a part of ruche.sql

SELECT 'table' as component,
    'Actions' as markdown;

SELECT 
    numero as Num,
     nom as Rucher,
     type as Ruche,
     reine as Reine,
....
    caractere as Caractères,
    info as infos,
    '[
    ![](./icons/pencil.svg)
](ruche_edit.sql?id='||colonie.numero||')' as Actions
    FROM colonie JOIN rucher on colonie.rucher_id=rucher.id WHERE colonie.numero=$id;

Then the edit page

    SELECT 
    'form' as component,
    'ruche.sql?tab=1&action=update&id='||$id as action,
    'Mettre à jour' as validate;

It can be on the same page (with tab conditions) or in another file (ruche_edit.sql). So we have different example for different input values :

    SELECT 'Rucher' AS label, 'rucher' AS name, 'select' as type, 4 as width, $rucher_edit::int as value, json_group_array(json_object("label" , nom, "value", id )) as options FROM (select * FROM rucher ORDER BY nom ASC);
    SELECT 'Rangée' AS label, 'rang' AS name, 'number' as type, 3 as width , rang as value from colonie where numero = $id';
    SELECT 'couleur' AS name, 'select' as type, 2 as width, $couleur_edit::int as value, json_group_array(json_object("label", coloris, "value", id)) as options FROM (select * FROM couleur ORDER BY coloris ASC);
    SELECT 'modele' AS name, 'select' as type, 3 as width, $modele_edit::int as value, json_group_array(json_object("label", type, "value", id)) as options FROM (select * FROM modele ORDER BY type ASC), (SELECT modele from colonie where numero = $id) as value;
    SELECT 'Date d''installation' AS label, 'début' AS name, 'date' as type, 4 as width , début as value from colonie where numero = $id;
    SELECT 'Année de la reine' AS label, 'reine' AS name, 'number' as type, '[0-9]{4}' as pattern, 4 as width, reine as value from colonie where numero = $id';
    SELECT 'Mort de la colonie' AS label, 'mort' AS name, CASE WHEN disparition=1 THEN TRUE ELSE '' END as checked, 'checkbox' as type, 1 as value, 4 as width from colonie where numero = $id; 
     SELECT 'Caractères' AS label,'textarea' as type, 'caractere' AS name, 6 as width , caractere as value from colonie where numero = $id;
    SELECT 'Remarques' AS label,'textarea' as type, 'info' AS name, 6 as width , info as value from colonie where numero = $id';

Finally, go back to the first page and update the database :

-- Mettre à jour la colonie dans la base
UPDATE colonie SET rucher_id=$rucher, rang=$rang, couleur=$couleur, modele=$modele, début=$début, reine=$reine, caractere=$caractere, info=$info, disparition=$mort WHERE colonie.numero=$id and $action is not NULL;

You can find the full project on my repository

I have tried something amazing on miellerie.sql where we can edit directly a "false checkbox" in the table and update and redirect very quickly on the same table modified. part of the table in miellerie.sql :

    CASE WHEN vente::int=1
    THEN '[
    ![](./icons/select.svg)
](/catalogue/indisponible.sql?id='||produits.id||')' 
ELSE '[
    ![](./icons/square.svg)
](/catalogue/disponible.sql?id='||produits.id||')' 
END as Dispo,`
Then in indisponible.sql
` UPDATE produits SET vente=0 WHERE id=$id
 RETURNING 
 'redirect' as component,
 '../miellerie.sql?tab=2' as link;

Try and enjoy !