Re: [phpesp-dev] choices from table rather than phpesp:question_choice

[Brian W. <br...@uc...>]

At 01:55 PM 5/28/2003 -0400, you wrote:
>Good point.  I initially thought about making this dynamic but then 
>retreated because of data integrity issues.  Example.
>
>What if we create choices based on a table today and a user picks option 2 
>when responding.   Tomorrow option 2 is removed from the table.  What do I 
>do when it comes time for reporting?  I think this would save space in the 
>phpESP tables but at what cost?  Any good ideas?  I'm all ears.

If we wanted to offer the dynamic question capability, it would be best to 
offer it on a survey-by-survey basis (like the way auth_get_option() 
works).  If the designer chooses to enable this option, they'd need to 
realize the reporting consequences.   I can imagine that some long running 
surveys might take advantage of this option...but if _you_ don't really 
need it, then why build it?

If one wanted to report on multiple choice questions where the answer 
options can change, the tables would need to track which answer options 
were available during what time periods and do some different math...

Brian


>chad.
>
>On Wednesday, May 28, 2003, at 13:31 US/Eastern, Brian Wood wrote:
>
>>Hi Chad,
>>
>>I haven't read survey_update(), but here are couple of quick comments:
>>
>>Sounds like your are thinking of having survey_update() (or a 
>>function  that it calls) run the entered sql query and insert the needed 
>>fields into the response_* tables.  An interesting alternative might be 
>>to create a new type of response (which I think would require a new 
>>response_ table): response_dynamic (or something).  The response_dynamic 
>>table would store the _actual_sql_ for a multiple choice question.  When 
>>the survey is displayed the selectmenu/radio/checkbox etc list would be 
>>built on the fly by executing the sql.  The advantage that this offers is 
>>that if you change the data that the multichoice question is built from 
>>(i.e. you add/delete a dental clinic) that change would be reflected the 
>>next time a user fills out a survey.  ...of course changing data will 
>>affect reporting statistics...
>>
>>Also, asking a designer to enter sql in a "create question" form will 
>>work for programmers, but in my system I'm trying to do everything 
>>possible to make the creation of a survey as simple as possible for 
>>non-techie employees.  This is not to say that your function wouldn't be 
>>a nice "power user" option.  Perhaps a future version of this 
>>fuctionality could allow "power users" to define dynamic multiple choice 
>>questions that could then be selected by other designers from a menu of 
>>common question choices (e.g. <option>dental clinics, <option>dental 
>>clinics-New York state, <option>hair cutting schools....)
>>
>>Brian
>>
>>At 12:07 PM 5/28/2003 -0400, you wrote:
>>>I'm looking for any comments you might have on the following:
>>>
>>>I've been toying around with the idea of grabbing choices for questions 
>>>from other db tables rather than having to type them into the create 
>>>survey interface.  I believe someone else came up with the idea but 
>>>after searching the message archive I can't quite track who...  Anyway, 
>>>the idea goes something like this...
>>>
>>>When creating a new question rather than listing choices that may just 
>>>be duplication of another table in your database enter a sql query and 
>>>have phpESP do the work for you.
>>>
>>>example:  let's say you want to list all of the dental clinics for a 
>>>respondent to choose from.  The question text would be:
>>>
>>>Choose the clinic where you performed the procedures.
>>>
>>>Question Type could be any type with multiple choices: [Radio, Check 
>>>Boxes, Drop Down, Rate]
>>>
>>>Now instead of typing all of the clinics and possibly creating errors 
>>>along the way.  Enter the following special tag and sql.
>>>
>>>On line 1:
>>><sql>select name from clinic order by name</sql>
>>>
>>>( if the clinic table wasn't in the phpESP db then enter an optional 
>>>argument telling the db name, DB::connect() syntax )  like this:
>>>
>>>On line 1:
>>><connect> mysql://user:password@machine_address/db_name</connect>
>>>On line 2:
>>><sql>select clinic_name from clinic order by clinic_name</sql>
>>>
>>>Now, when you clicked [New Field] or [Continue] then the choices would 
>>>be created for you!
>>>
>>>Implementation:
>>>
>>>It looks like survey_update() is the only function involved.  So, my 
>>>idea is to trap multiple choice questions that have the <sql> tags and 
>>>create the choices automagically.
>>>
>>>Any comments?
>>>
>>>chad.
>>>
>>>
>>>
>>>-------------------------------------------------------
>>>This SF.net email is sponsored by: ObjectStore.
>>>If flattening out C++ or Java code to make your application fit in a
>>>relational database is painful, don't do it! Check out ObjectStore.
>>>Now part of Progress Software. http://www.objectstore.net/sourceforge
>>>_______________________________________________
>>>phpESP-devel mailing list
>>>php...@li...
>>>https://lists.sourceforge.net/lists/listinfo/phpesp-devel
>>
>>Brian Wood
>>Programmer/Analyst III
>>UC Berkeley Human Resources
>>230 University Hall
>>Berkeley, CA 94720
>>Voice: (510) 643-4094

Brian Wood
Programmer/Analyst III
UC Berkeley Human Resources
230 University Hall
Berkeley, CA 94720
Voice: (510) 643-4094