Handling checkbox form values

#1

This may be a rookie question, but I need to handle a server connect form submission that contains a series of checkboxes

        <div class="FormRow FormCheckboxGroupVertical"><span id="Form2Field7Label">Type</span><span class="FormRequiredFieldText"></span></label>
    		<div class="FormInput">
    			<ul class="FormCheckboxGroupVertical">
    				<li><input type="checkbox" name="persontype" id="Form2Field7_0" value="1" /><label for="Form2Field7_0">Value 1</label></li>
    				<li><input type="checkbox" name="persontype" id="Form2Field7_1" value="2" /><label for="Form2Field7_1">Value 2</label></li>
    				<li><input type="checkbox" name="persontype" id="Form2Field7_2" value="3" /><label for="Form2Field7_2">Value 3</label></li>
    				<li><input type="checkbox" name="persontype" id="Form2Field7_3" value="4" /><label for="Form2Field7_3">Value 4</label></li>
    				<li><input type="checkbox" name="persontype" id="Form2Field7_4" value="5" /><label for="Form2Field7_4">Value 5</label></li>
    				<li><input type="checkbox" name="persontype" id="Form2Field7_5" value="6" /><label for="Form2Field7_5">Value 6</label></li>
    			</ul>
    	</div>
    </div>

The data will be stored in a MSSQL database and a table which I can modify.
The visitor can select multiple options on the web form.

Can someone recommend the best way and some guidance of how to handle the storage of these in the database.
I can replicate each checkbox with its own field nchar(1) or as a comma separated nvarchar.

Thanks is advance.

#2

The first choice should always be to avoid a text column with a string of comma separated values – I found a pretty good list of gotcha’s in doing that:

  • Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
  • Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
  • Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5
  • Can’t delete a value from the list without fetching the whole list.
  • Can’t store a list longer than what fits in the string column.
  • Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan. May have to resort to regular expressions, for example in MySQL:
    idlist REGEXP '[[:<:]]2[[:>:]]' *
  • Hard to count elements in the list, or do other aggregate queries.
  • Hard to join the values to the lookup table they reference.
  • Hard to fetch the list in sorted order.

That being said, there are times we have to denormalize things.

So I suppose my advice is, if you have a very clear reason for choosing a comma sep list, and you understand the challenges that presents, then by all means, do it. Otherwise, start with storing in separate columns (or better yet, a distinct table altogether).

I’ve not personally come across a need for comma sep list, but that doesn’t mean the use case does not exist.

1 Like
#3

Thanks @mebeingken
Actually my preference would be that and to store the check box as individual fields in the table with a value of ‘Y’ when checked. Thats where I am struggling in the Wappler UI

How do I map the POST values picker to each of the id’s
The persontype is showing as multiple values in the post

persontype: 1
persontype: 2

So a value of 1 update fieldX to ‘Y’ and fieldY to ‘Y’

#4

scope.$_POST.persontype: [“1”, “2”, “3”, “4”, “5”]
0: “1”
1: “2”
2: “3”
3: “4”
4: “4”

#5

Is there a guide on how I can update the separate fields in the DB based on the above form values?

#6

My query is a little like the post below

However my form is submitting a single row and based on the checkbox array set a ‘Y’ value in the corresponding field. Do I still need to add [] to the checkbox names in the form as its not inserting multiple rows per submit?

#8

Hello.

Based on the code that you provided in the first post, the problem that is mentioned in your message is that all your checkboxes have the same name. On the server side, when you import your form into a server action, it is the names that are taken, not the IDs. Therefore, on the server side, all 6 checkboxes are treated as one. Just change the check box names to unique names, import the form back into the server action, and when inserted into the database table, each check box will transmit data correctly. You can write them to different fields in the table.

What I wrote only makes sense if I understood your problem correctly.

#9

Thanks for taking a moment to reply @Mr.Rubi
I managed to sort it in the end without amending the form or adding the []

It took me longer than I had hoped. But sorted thank you