[thelist] CF: Dynamic variables and Insert Statements

[rudy]

joshua, how long have you been working with databases?  i wish we could sit
down and have a beer, man, i would really enjoy talking to you, you are
pretty sharp

all the questions you asked nicole anticipate the consequences of different
database designs (yeah, trust me to notice, eh!   but what can i do? i love
this stuff)

> Are you inserting them into a single record in the other database,
> or one record per checkbox?  If the former, is that in one field as
> a list or does the table have one field for each checkbox?

with one record per checkbox is what you usually find when there is another
key involved (often when each checkbox has a different meaning)

with just one field, containing a string which is a list (which is a great
solution, there being some great string-manipulation tags in cold fusion,
for example), if you want to search on one of the list items, you have to
use LIKE on that field, which can have performance implications

with one field per checkbox, the search is more efficient but it's ugly
(whole bunch o' ORs) and the html is bloated (it's shorter if you can write
loops, which is why sometimes you see people using field name arrays and
stuff)

> Is every check going to be inserted with an identifier saying whether
> it was checked, or are only those records checked going to be inserted?

a column with a null in it is the same as a row that isn't there   =o)

the trick to this part of checkbox design is to know what it means when
something hasn't been checked

not checked == isn't there

> .. get a real good idea of exact what you are trying to do first.

beauty

couldn't agree more

> This problem is a little tough because HTML does not return in
> the form struct those checkboxes that are not checked.

which is exactly the way it had better keep working, or more than a few web
sites will go down, starting with those that work with form structures or
field name arrays...

> So, you'll have to rely on whatever algorithm generated the original
> form to recover the names of those checkboxes the user failed to check

that's a good idea -- look at the SELECT statement that populates the
checkboxes into the form page in the first place, to see where, um, the
missing information comes from, if you know what i mean -- this will tell
you which records or fields have to be updated with any, um, checked
information on the way back to the database...

which is what you said, i guess, except i personally would never have
thought to try to recover stuff that's going to end up being missing in the
database anyway...

=o)


rudy