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

[Chad B. <ch...@be...>]

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.

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
>