Loop through params and dynamically create a table

Hero Zzyzzx has asked for the wisdom of the Perl Monks concerning the following question:

Monks,

So I'm building a system to survey a staff of around 500 people spread across 25+ organizations. The survey is about folks perceived ability with various applications (word processors, OSs, etc.), preferences when it comes to applications, and perceived needs around technology. It isn't meant to be scientific or statistically perfect, only instructive.

Here's how I'm thinking of doing it, and some random thoughts:

Use cookies and track the IP of the surveys. Don't restrict multiple submissions on the same terminal, because some folks share computers.
use CGI.pm and put the list of submitted params into an array, like so:
@names=$q->param();
Then, create (if not created already) a mySQL table by looping through each of the params submitted above creating it as a field. Then store the values in the appropriate field, whether or not a table was created.
I realize that this is potentially inefficient. However, it will allow me to create new surveys by pretty much just editing an HTML file, and since I'll be using CGI::Application, editing the "instance script" to pass along a new table name when I create a new survey.

My questions are:
Besides the obvious (CGI, DBI) and not-so-obvious (HTML::Template, CGI::Application) modules, what other modules can you suggest to make this easier?

How would you do it differently? I've looked at other open-source survey packages and thought they were lacking.

Comment on Loop through params and dynamically create a table Download Code

Replies are listed 'Best First'.
Re: Loop through params and dynamically create a table by bikeNomad (Priest) on Jul 02, 2001 at 21:53 UTC
Of course, you don't necessarily have to make a new table per survey; your data could be expressed in a single table: survey, respondentID, question#, answer	[reply]
Re: Loop through params and dynamically create a table by toma (Vicar) on Jul 02, 2001 at 23:13 UTC
For analysis of the data you should consider using the code from the The R Project for Statistical Computing. R is `GNU S', a freely available language and environment for statistical computing and graphics which provides a wide variety of statistical and graphical techniques: linear and nonlinear modelling, statistical tests, time series analysis, classification, clustering, etc. For the analysis of your survey you may want to use a classification and regression tree. This allows you to put your users into different categories and still do statistics. There is a contributed R module called Pinktoe, which is a piece of S code that will automatically turn a classification and regression tree into a set of HTML and perl files. There is even a site called CRAN, The Comprehensive R Archive Network that is like a CPAN for R. Your database-update idea sounds like it could save you lots of work. However, if you are going to allow the a CGI script to create new database tables, you should use some sort of authentication such as a password field to access this capability. Otherwise, people can play with the values that they submit and mess up your database. It should work perfectly the first time! - toma	[reply]
Re: Loop through params and dynamically create a table by scratch (Sexton) on Jul 03, 2001 at 03:14 UTC
I've done something very similar to this, using the CGI and DBI modules, but no HTML:Template or CGI::Application, so I can't speak to those. A couple of things to think about: <menu> If you want to increase the odd that each person is only completing the survey once you should probably assign a password to each person and require him to login to complete the survey. Mail::Sender makes sending a bunch of email with username/password info fairly painless. If you're carrying over values from page to page you'll need to do something to enable this. I used hidden fields. </menu> I'd suggest reading Lincoln Stein's Official Guide to Programming with CGI.pm. It covers nearly everything you need to know to do this.	[reply]
Re: Loop through params and dynamically create a table by Poetic Justice (Monk) on Jul 02, 2001 at 22:56 UTC
An admirable project, and interesting. The only suggestion that I could make is one I'm tackling right now... use XML::Parser. You may find that it helps you sort your results in a logical fashion. I was skeptical of XML early on, but I've become a believer over the past weekend. I had a young VBScripter show me some tricks with XML and MetaData design that I had not considered. Just something on which you may meditate.... ;-)	[reply]
Re: Loop through params and dynamically create a table by mattr (Curate) on Jul 03, 2001 at 10:21 UTC
I've built some surveys like what you are talking about. You might want to consider that it may be edited often, and save the version number so that people who took an older version can later take it again instead of being penalized. Also you could have fields/choices with the same name, what we did is start out with the concept of a hierarchical survey. You could concatenate section, subsection, question, and choice to get field names but they would be very long. Some kind of editor (XML?) or just an INI style file might be helpful. Incidentally I looked at a module in CPAN called INIFiles which I didn't like and rolled my own on another project. But DBD::RAM seems to read/write INI files so maybe you could use that. Maybe you'd rather make your own file format. Making the HTML was a major pain, and I was constantly editing it. If you can have CGI.pm or something else build the form for you programmatically you will find this much more enjoyable. If that plus maybe a design template is satisfactory, then abstracting it out (using a text file or inline code to describe the system, instead of using html to do so) would be I think, for me anyway, easier in the long run. Finally if it was me, I would make a separate db initialization script which like the main enquete script uses a config.pm module. You would just edit that module and run the init script once. Also I'd recommend a telnetable account for your mysql box, so limiting the permissions of that init script to the account owner will increase security and not cost much time. You can use the command line client mysql to check the db quickly. I think the biggest things to worry about in this project are 1) editing, which you've been thinking about; and 2) corrupting data, or wrongly attributing data to another user or to the wrong skill of the same user. Since it is going to be hard to get people to take the same exact test over again, you should keep a log. Mysql itself has one which you can enable, but something which looks like a filled out form might be more useful. To ensure that everything is correct though, you might want to show a confirmation page and allow a person to go back (just use javascript button to go -1 in the history). Lastly, 3) actually getting people to respond. You need to keep it as short as possible and if possible get someone at each location to adminster the quiz or remind people to take it. Maybe you have a personnel list so you can send email to each person, but this is key. No data, no joy! Have fun!	[reply]
Re: Loop through params and dynamically create a table by scain (Curate) on Jul 02, 2001 at 22:18 UTC
Well, doing a survey is not that different from conducting a poll. So you could use the "poll" portions of code from Everything (see link below) as a foundation. Disclaimer: I've never actually looked at the code, but with a name like that, it has to be good :-) Scott	[reply]
Re: Loop through params and dynamically create a table by zakzebrowski (Curate) on Jul 03, 2001 at 01:09 UTC
An exisiting open source survey package that is worthwhile is: http://sourceforge.net/projects/phpesp/ It's php not perl, but otherwise very good. ---- Zak	[reply]