[Zope] submitting multi-checkbox

[Dieter Maurer]

Norman Lorrain wrote at 2003-5-25 23:18 -0600:
 > I'm trying to submit multi-checkbox (Formulator widget) values to a
 > database using ZSQL.
 > 
 > My  database schema defines the fields as boolean; Formulator converts
 > the values to a list of checked items, e.g. ['checked_item_one',
 > 'checked_item_two'].  Only checked items are added to the list.
 > 
 > How can I map the formulator output to proper SQL? (a list of booleans)
 > I have a couple of ideas, but I'm new to Zope and not sure what would be
 > best, or even possible.

You are not very specific about how your SQL should look like,
e.g. whether it should be a "select" or an "insert/update" sql command.

For a "select", the "multiple" attribute of "dtml-sqltest" may be helpful.

For an "update" (and similarly an "insert"), you would want something like

    ... set ...
         item_x1 = true,
	 item_x2 = true,
	 ...
	 item_y1 = false,
	 ...
	 item_y2 = false,
	 ...

You can do this through iteration.
Assume "checkedList" is the list of checked items,
you can derive the list of unchecked items ("uncheckedList")
by means of "reorder" (documented in your embedded online help
--> DTML Reference --> Functions).
It looks something like:

 <dtml-let uncheckedList="_.reorder(allItems,without=checkedItems)">
   ...
   <dtml-in checkedList>&dtml-sequence-item; = true,</dtml-in>
   <dtml-in uncheckedList>&dtml-sequence-item; = false,</dtml-in>
   ...
 </dtml-let>

The biggest problem is to get the SQL syntax right, i.e. have a ","
at precisely the correct places. Sometimes, it is easy to ensure
that there is an additional "COL = VAL" after the "dtml-in".
If not, you must add tedious logic to suppress the tailing ",".
It may help in this case to build the whole sequence of "COL = VAL"
in Python, where logic is much easier.



Dieter