Inputting an unknown number of variables in a form and saving them to the DB

[aschoijett]

Hello,

I am trying to do the following:

• create a form (let’s call it FormVars) to capture the values of a number of variables that I don’t know in advance
• create an API (let’s call it SaveVars) to save to a DB table all the values captured using FormVars

I have 3 questions about this:

1. how to create the form FormVars;
2. how to pass the captured data to the SaveVars API; and
3. how to parse in the SaveVars API the data passed to it.

The possible variables that could be captured and the initial values to present to the user are defined in a table --let’s call this table with the definitions of the variables VARDEF.
I want to store the values captured through FormVars in a separate table --let’s call this table with the entered values of those variables VARSAV.

Below is a simplified structure the VARDEF table, the one that contains the definitions of the variables to capture:

VarDefID (PK)
....
Name (a string that defines the parameter)
Label (a string that is user friendly and can be used in a form)
Default (initial value shown to the user in the FormVars)

The VARDEF could have a large number of records, and I have no control over what is kept there. At a given point in time, the table could look like this:

VarDefID   Name       Default    Label             
---------------------------------------------------
103        NumBdrm	  2          Number of Bedrooms
...				
157        NumBath	  2.5        Number of Bathrooms	
...			
197        HasDen	  N          Unit has a den
...			
1123       BYrdPool	  Y          Backyard has pool

Below is a simplified structure the VARSAV table, the one that contains the values of the variables entered by the user

VarSavID (PK)
…
VarDefID (FK)
EnteredValue (the value entered by the user using the form FormVars)

I already wrote a query (GetVars) that fetches from VARDEF the values that need to be entered using FormVars. Assume that for one particular case, GetVars returned the records 103, 157, and 197 from the VARDEF table.
These 3 variables should be presented to the user in FormVars. After the user entered the new values of these three variables, I’d like the SaveVars API to insert the 3 records in VARSAV, which may end up looking like this:

VarSavID	VarDefID	EnteredValue
1154		103	        3
1155		157	        2
1156		197	        Y

The numbers shown for the Primary Keys in both tables are irrelevant, as they are only used for referencing the rows in this question.

Question1:
What is the best approach to creating FormVars, so it can present to the user all the variables for which the user needs to enter values? In this example, FormVars should present 3 input widgets for NumBdrm, NumBath, and HasDen but in other cases, it may have to deal with a completely different set of variables.

Question 2:
What structure should I use to submit the values captured by FormVars, to pass them to the API SaveVars? I thought of something like an array of (name, value) pairs, probably something like an array of (VarDefID, EnteredValue) pairs, but I’m not a programmer and I don’t know how to assemble such array in the page so the submit button can pass it.

Question 3:
Assuming that passing an array of (name, value) pairs to the SaveVars API is indeed the right way to go, how do I parse that array so I can save its contents in the VARSAV table? I think I need to iterate through that array in a repeat loop, which I know how to do if the array is the result of a database query, but I don’t know in this case.

Any guidance on these questions will be greatly appreciated!

Many thanks in advance,

Alex

[mikkime23]

Hi Alex,

I’m trying to get my head around what you are trying to do. I made some assumptions:

1. I assume this is some kind of Real Estate app you’re creating;
2. You have a table for the properties that are referencing your Variables Def and Variables Sav tables.

It would be advantageous for you to add columns to the Property table for the variables/information you need? You can then use Enum data type if you want preset values and boolean/tinyint for Yes/No. For example:

|           | id   | beds                         | baths                 | den        | bYardPool  |
|-----------|------|------------------------------|-----------------------|------------|------------|
| Data Type | AUTO | ENUM(Studio,1,2,3,4,5,6,7,8) | ENUM(0,1,1.5,2,2.5,3) | TINYINT(1) | TINYINT(1) |

ENUM() data type will allow you to predefine the values allowed for the column and it saves a LOT of JOINS on lookup tables.

TINYINT will be either 0 or 1. Thus returning either true or false using if(den), whereas Y or N will always return true unless you expand your code to if(den === 'Y') which you’ll also need to ensure you’re always writing UPPERCASE or lowercase Y or N, depending on the style.

The above would save as:

| id    | beds   | baths | den | bYardPool |
|-------|--------|-------|-----|-----------|
| 12345 | 3      | 2.5   | 1   | 0         |
| 12346 | Studio | 1     | 0   | 0         |
| 12347 | 2      | 1.5   | 0   | 1         |

---

As for the VARDEF table, would you not be best to do all of this client side? Create the label on the form and predefine the default value in the input or select. Use the name property to define the parameter sent to your ServerConnect API.

<form>
    <label for="sel_bedrooms">Bedrooms</label>
    <select name="bedrooms" id="sel_bedrooms">
        <option value="1" selected>1 *selected by default</option>
        <option value="2">2</option>
        <option value="3">3</option>
        <option value="4">4</option>
        <option value="5">5</option>
        <option value="6">6</option>
        <option value="7">7</option>
        <option value="8">8</option>
    </select>
</form>

You can then pass all of this form data to your ServerConnect API and submit the form simply.

---

I’ll be honest, I think you may have tried to overcomplicate things in your database. Reducing JOINS to lookup tables, when you could easily have columns in your Property table using the ENUM() datatype to control your values will not only make your code and database easier to read and query, it will reduce the work the database has to do to find all of your information.

That’s my two cents on assumptions however, let me know if I got anything wrong with those.

Cheers
Michael

[aschoijett]

Thank you Michael, but unfortunately your assumption is wrong. As I stated, the variables that will be required in each case is an unknown subset of a large number that can and will change over time, so I cannot model the data as you suggest. Trust you me, the data model is not the problem here.

(Also, the data contents I used looked as if they were for a real estate application, but that was just for an example, so I could speak about this issue in less abstract terms.)

As a result of the above, I cannot create a form in the client with what you’re suggesting, because everything in this form by definition has to be dynamic: the number of variables that will have to be input, the name of the variables, the labels for each, and their initial values – and all that comes from the VARDEF table.

So far, I created a form, within which there is a table with a variable number of rows, driven by the result of the query to my VARDEF. In each row, I put an <INPUT…> widget with the labels and the values from VARDEF. That is working well, but now I am stuck on how to send that info back to the API that will save it in the database.

I was looking at this post from @ben that may help me address part of my problem, but in the client, I don’t know how to populate a data store with all the records that come as the results of a query.

I feel like a Viktor Frankenstein, finding some useful parts but unable to put them together yet

Thank you,

Alex

[bpj]

When you say send it to the API to save to the DB do you mean an API file in Wappler or external API?

[aschoijett]

a Wappler API

[bpj]

Assuming that you get the VARDEF from a SC call/API call you could create all this with a form and some repeats. Then process it in a Wappler API file - no data store required unless you want resilience to page refresh/navigation

Add a SC form to your page
In the form add a row and set to repeat
In a column add the label & input with dynamic bindings

something like (just showing the input code - assume you know how to call SC and add an SC form):

<div class="row" dmx-repeat:repvardefs="serverconnect_vardefs.data.vardefquery">
<div class="col">
<label dmx-text="Label"></label>
<input dmx-bind:name="'records['+$index+'][enteredval]'" dmx-bind:value="Default" type="text">
<input dmx-bind:name="'records['+$index+'][vardef]'" dmx-bind:value="VarDefID" type="hidden">
</div>
</div>

on the server you would repeat through $_POST.records and insert the vardef and enteredval values into your DB

[aschoijett]

I think this works! Thank you so much.

I used the notation shown by @ben in the example I quoted before, only because it looked a bit more intuitive to me. I tried both with equal results.

Btw, I noticed that

1. I can only create the dmx-bind:name=“blah” using the code editor. The Wappler UI does not see anything “record” and thus I could not edit it.
2. After I enter the code in the editor, it appears rather weird in the Properties UI, extending it to the right of the page and not letting me see the control. See pics below.

What in the editor appears thus:

In the Properties UI appears like this:

Is that a Wappler bug?

Many thanks again!

[scalaris]

Why not use a JSONB data column and push the entire $POST in there. Instant, one-line of no-code, scalable and works in all conditions.

[aschoijett]

Hi scalaris,
Thank you for the suggestion. I actually tried that first, but found that I didn’t know enough about formatting and extracting values out of a JSON object, so I got really stuck.

Working with JSON objects seemed to require a level of know-how that is currently above me. I really want to learn more about how to use JSON objects, stored or not in a DB field, and in particular, how to “massage” them in wappler, both in the client side and in the server side.

Any pointer on where to learn that will be appreciated.

Many thanks,

Alex

[scalaris]

JSON is as a fundamental in Wappler data management. Learn the basic concepts first is my advice.

Create a table with a jsonb column and a primary key and start inserting records (use the entire $POST object as an example), and the select variant. Shouldn’t take you long to get it … and when you do, you’ll realise the flexibility and performance it can offer you …

[aschoijett]

thank you