[phpesp-dev] [patch] survey_update.inc adds sql_static question choices feature

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

The patch itself has all of the documentation. Here's a brief synopsis.

//
// This new feature is used when making surveys.  Prior to this feature 
which
// I call sql_static you had to type all answers to a multiple choice
// question.  Like all the clinic names or all the director names or 
...you
// get the idea Now,  you can just put the following in the 1st possible
// answer slot and it will pull the data from an already existing 
database
// table.
//
//  Examples:
//
//    <sql_static>select `code`, `desc` from `ndas` order by 
`code`</sql_static>
//
//    <sql_static>select `name` from `clinic` order by 
`name`</sql_static>
//
// This is SQL!! Oh the horror!  Something else to learn but the payoff 
is
// BIG.  The first example grabs the code and description from the ndas
// table which must exist in the phpESP database.  The code and 
description
// will be concatenated (smushed) together with a space seperating 
them.  With
// the second statement a table named `clinic` in the phpESP database is
// searched for a `name` field and returns all of those names sorted in
// alphabetical order, `order by name`.  Very Cool!!  A word to the 
paranoid,
// I do check that the first word after the tag is 'select'.  Also if 
the
// table you are grabbing choices from is not in the phpESP database do 
not
// fret.  Use the <connect> tag to open that database. Use DB::connect()
// syntax like: (Yes you need a recent php 4.1.x install, I'm testing 
with
// 4.3.2)
//
//    <connect>mysql://user:pass@host/db_name</connect>
//    <sql_static>select field from table</sql_static>
//
//   example:
//
//    <connect>mysql://phpaicm:phpaicm@localhost/phpaicm</connect>
//    <sql_static>select `first_name`, `last_name` from `composer` 
order by `last_name`</sql_static>
//
// see: http://pear.php.net/manual/en/package.database.db.intro-dsn.php
//
// Please note that this creates all choices on the fly and then 
inserts them
// into the phpESP `response` table.  Really no different than typing 
them all
// in yourself, but quicker and more accurate.  This method does 
require that
// the source table exists, namely in this example, `composer`.  It 
also requires
// that a field in `composer` exists, namely, `last_name`.  I don't 
check all of this
// before submitting the sql to the database but it will return somewhat
// helpful error messages if any of the criteria are not met.
//
// I call this sql_static because if the table you based your choices on
// changes it will NOT change your survey.  The choices are created at
// question build time and afterwards the table is no longer consulted.
// The info is duplicated in the phpESP choices table.
//
// I plan on creating a sql_dynamic tag that will consult the db table 
everytime
// the survey is displayed.  This has huge ramifications for rendering 
and
// reporting and is still in the thinking stages.  Here is a glimpse.
//
// When creating your question type the following into the 1st possible 
answer
// box.
//
//    <sql_dynamic>select name from clinic order by name</sql_dynamic>
//
// Now, when you view your survey in preview mode you will see all of 
the data
// pulled from the table in real time.  If the table changes so too 
does the
// survey.  This is good and bad.  The good,  no need to copy surveys 
for
// adding choices to existing questions, just change the table they get 
their
// answers from.  The bad, reporting becomes a nightmare...




chad.