Dynamic form with data from mysql/json

Just want to see if I am really moving too far away from where Grav shines and make sure if I am not that I am about to go about this the right way.

I am building a site to basically be the front end to mapping some data between a few tables in MySQL. To do this the simplest way I can think of is to pull the data out of the “static” table into JSON which will allow me to create some cascading drop downs (3) for each row in the table that needs to be updated, mapping the values within to the ones from the static table.
With just PHP scripts I would just loop through the one table from MySQL and build it that way. But with Grav I am just not 100% sure.

From what I have found in the docs, I cant really use the forms plugin, and the forms-database plugin would be fine if I didnt want to get complicated with it and just submitting into a single table (altho I have made it capable of inserting and updating but still limited to 1 entry).

Would I be looking at creating a plugin that would build the table and output the HTML for this onto the Pages? Or is this a combination of plugin + Twig?

There will be multiple similar pages for each “product” that needs to be mapped. The more I think about it, the more I can see that a plugin is the only way this can be possible.

@Chris_W, I’m afraid the goal you are trying to achieve is not very clear to me and I can therefor only give some general pointers.

  • Form fields can be initialised by calling a static php function: See Using function calls
  • A submitted form can be handled in a custom plugin by adding an event handler for event ‘onFormProcessed’. See Custom Action.
  • Forms are not required to use. You can create your own plugin, fetch data, pass the data to Twig and create you own form. The submitted form can then be caught in the plugin.

Thanks @pamtbaau it appears discourse ate my reply, I will try articulate this again in the hopes of making it clearer, but I do think you have answered my question.

The site I need to make will have a number of pages which will in effect be forms. These are going to be a table that is built from data in a MySQL database. The table that is displayed will have 3 fields that are cascading dropdowns, the values of which will come from a pre-generated JSON file.

So in effect, each page will display a table with 3 drop downs that, when selected and saved, will store the selection back into the MySQL database.

+------+-----------+-----------+-----------+
| name | Dropdown1 | Dropdown2 | Dropdown3 |
+------+-----------+-----------+-----------+
| Dog  | Value1    | Value     | Value     |
+------+-----------+-----------+-----------+
| Cat  | Value2    | Value     | Value     |
+------+-----------+-----------+-----------+
| Fox  | Value2    | Value     | Value     |
+------+-----------+-----------+-----------+

So I think ultimately, the answer to my question is “create a plugin”. What I was more concerned about was that the answer would be look at a different CMS as Grav isn’t ideal for this. Which would have been fine, as I would rather know that now before spending days trying to make it work when it just cant be / shouldn’t be done.

It is just a case of now finding some example plugins to look through to get an idea of what I need to do in order to get it to display as I need to and build up the page. From looking at the forms and forms-database plugins, it looks like I can create the plugin such that I only need to enter some Frontmatter to select the table and columns to display and it should build out the page which is perfect.

For some reason editing a post makes it completely disapear…

@Chris_W, Based on the limited information, here is some extra info.

I don’t think the Form plugin is the way to go. Calls to static methods to populate dropdowns are executed when Form is loaded. They are not (re-)executed in response to changes in other fields.

My initial thoughts are:

  • Create a Twig template that can be used by multiple pages
    • Generate form in Twig based on:
      • parameters in frontmatter of page,
      • and/or based on data from plugin that passes data to Twig.
    • Fill cascading dropdowns dynamically using async javascript fetch.
      Handle the fetch using plugin.
    • Submit the form to the backend either sync or async.
      Plugin will handle submitted data.
  • Create a plugin which can:
    • Fetch data from MySQL and:
      • pass data to Twig,
      • and/or return json to async calls.
    • Respond to submited data (sync or async) and store data in MySQL.

To get started with plugins, install plugin Devtools and run $ bin/plugin devtools new-plugin. This will setup a plugin framework for you.
Also, read the chapters on Plugins.

1 Like

Thats perfect, thank you for the guidance. I read through the Plugins chapter last night to try wrap my head around it all. Will do some more research, and start playing with a plugin.

Glad to see that Grav will work for my use case, as everything else about the CMS has been a perfect fit.

Once again, thank you very much for your help.