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


I am trying to do the following:

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.

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,


Community Page
Last updated: