2010-07-12
QPL Reference Manual

In the QPL system, you may load user accounts using the on-line Edit User pages, or by writing an SQL script that loads these accounts directly into the data base. Similarly, you may also preload information about individual respondents into their questionnaires. This information is typically used to let respondents verify and update information about them or just to have additional descriptive information about a respondent available later when analyzing the questionnaire results.

This page provides sample SQL scripts that demonstrate how to load this information:

When developing your own scripts, you should review the job.sql file that is created when you click the PHP button on the QPL toolbar to build your web site files. This file defines all the tables and fields that are used to run the questionnaire web site. This is especially important when writing scripts to preload questionnaire data, because the field names and types you will be using in your script are defined by what question names and types the questionnaire author used.

This page presumes you understand how to write and run MySQL scripts. For more information, see the MySQL web site, MySQL 5.1 Documentation. The book by Paul DuBois, "MySQL, Forth Edition" (Addison-Wesley), also is an excellent tutorial and reference for MySQL programming.

user Table

The "user" table holds the respondent account information. When writing scripts to load accounts, only the user name column, called "q_uname," must be loaded (though you will also usually load the password, called "q_pswd," unless you are running a survey on your intranet and can use LDAP services to authenticate your respondents).

User account names must be unique.

One user may have multiple questionnaire records, if called for by the questionnaire author.

The user name in the account list must exactly match the user name used in the questionnaire record.

user Table Structure

Column Type Comment
q_uname VARCHAR(50) NOT NULL PRIMARY KEY User account name
q_pswd VARCHAR(50) NOT NULL Password
q_name VARCHAR(50) User's real name
q_email VARCHAR(120) User's email address
q_phone VARCHAR(20) User's telephone number
q_other TEXT Any other information
q_group VARCHAR(25) NOT NULL DEFAULT 'NONE' Group code name:
NONE=group not used,
other=use group setting
q_user_status CHAR(1) DEFAULT '1' Status of this user:
0=account closed,
1=normal user,
2=super user,
3=manager,
4=administrator,
5=data administrator,
6=system administrator
q_expire DATE NULL Date this account expires
NULL=never expire

Notes: Only the columns that may be preloaded using an SQL script have been included in this table. Other columns are used internally by the system as it is running to manage user accounts.

data Table

The "data" table is used to hold all of the responses to your questionnaire. One respondent may have one or more data table records. The user name column, called "q_uname," links the records in the data table to the user account records in the user table.

Records do not need to be loaded into the data table before the survey is launched. If a record does not exist for a respondent's account, then a new record will be created when the respondent logs in the first time.

You may, however, load respondent records before the questionnaire is launched. This is useful when you want to let respondents correct or update information about themselves that you already have, or when you want to add descriptive data about the respondent that will be on the data record when you later export it for statistical analysis. This information may be put into either visible or hidden questions, but you must take care to load it in the format that will be used by the system when its running.

You must load the user name, "q_uname," when loading data into the data table. The user name should match a user name that is loaded into the user table.

You may also load the group code, "q_group," if you will be letting users share data records.

Other columns are defined in the questionnaire program. You should review the job.sql file that is created you build the web server files to see how they are defined in the data table. Also keep in mind that multiple choice responses are coded as integers in the data base starting at "1." And that each element of a check-all-that-apply question is defined separately, with "1," indicating checked and "0," not checked. Questions you do not load in your SQL script will be automatically set to their default values as defined in the questionnaire program (i.e., the .pg6 file).

data Table Structure

Column Type Comment
q_uname VARCHAR(50) NOT NULL User name
q_group VARCHAR(25) Group code
question name... defined by question type See the .pg6 file for question information

Note: Only the columns that may be preloaded using an SQL script have been included in this table.

User Accounts (Minimal)

This example shows how a simple SQL script may be written that loads the user account table with only user names, passwords, and email accounts. The other columns in the user table will be set to default values. The accounts will all be created as Normal respondent accounts since the q_user_status column is not set and will default to "1." These accounts will never expire since the q_expire column is not set and will default to "NULL."

Note that "job," should be replaced with the name of your data base in this example and the other examples below.

USE job;
INSERT INTO user (q_uname, q_pswd, q_email) valueS
  ('smith', 'apple', 'smith@myhost.com'),
  ('jones', 'orange', 'jones@myhost.com'),
  ('johnson', 'peach', 'johnson@myhost.com');

User Accounts (Fancy)

This example fills in more of the user table fields creates several types of accounts.

Smith and Jones are created as Normal accounts since the q_user_status field is set to "1." Their passwords are randomly generated 5-digit PIN numbers using MySQL functions (the RAND numeric function generates a random number between 0 and 1, and the RIGHT string function keeps only the five right-most digits of the number -- preserving any leading zeros). Both accounts have been set to expire on October 10, 2003, and both respondents have been put in the same group, "A."

Johnson has been given a Super User account since his q_user_status field is set to "2." Since he is a Super User, and has access to all of the questionnaire records, he does not need to be any group so his q_group code has been set to "NONE."

Baker has been made an Administrator since his q_user_status field is set to "3." Administrative accounts never expire so his q_expire field has been set to "NULL." (Note: Any date in this field would be ignored, but entering NULL makes it clear that you know that the account does not expire.) Administrators also do not have access to any questionnaire records so the q_group code has been set to "NULL."

USE job;
INSERT INTO user (q_uname, q_pswd, q_email, q_name, q_phone, 
  q_other, q_expire, q_user_status, q_group) valueS
  ('smith', RIGHT(RAND(),5), 'smith@myhost.com', 'Mike O\'Dell Smith', '202-512-0000', 
    'Former GAOer now retired.', '2003-10-09', 1, 'A'),
  ('jones', RIGHT(RAND(),5), 'jones@myhost.com', 'Jack Jones', '202-512-0000', 
    'Talented amateur.', '2003-10-09', 1, 'A'),
  ('johnson', RIGHT(RAND(),5), 'johnson@myhost.com', 'Sven Johnson', '202-512-0000', 
    'Authority on everything.', '2003-10-09', 2, 'NONE'),
  ('baker', 'big2hands', 'baker@myhost.com', 'Kevin Baker', '202-512-0000', 
    'Project administrator.', NULL, 3, 'NONE');

User Accounts and Questionnaire Records

Loading the data table uses the same type of SQL statements as used when loading the user table. Here, however, you must use the questionnaire author's source program (.pg6 file) and the generated job.sql file as references to learn the names and types of fields that will exist in the data table. You also need to make sure that the field sizes have been defined in the quesitonnaire program to be large enough to hold the data that will be loaded in the SQL script. MySQL will silently truncate data that is larger than the target field.

As mentioned above, you must set the user name field, "q_uname," for each record to match an account that exists in the user table. More than one record in the data table may be assigned to one respondent.

And multiple respondents may share the same record if their user accounts and the data records specify the same group field, "q_group," code.

The following example is based on a questionnaire which includes a question that asks the respondent to name their favorite color.

.QUESTION = Q1, TYPE = MULT
What is your favorite color?
.ANSWER
Red
Orange
Yellow
Green
Blue
Indigo
Violet
Don't know
.NEXT

The SQL script for this project first loads the user table with respondent accounts, and then loads the data table with one record for each respondent and respondent-specific starting values for question Q1.

USE job;
INSERT INTO user (q_uname, q_pswd, q_email) valueS
  ('smith', 'apple', 'smith@myhost.com'),
  ('jones', 'orange', 'jones@myhost.com'),
  ('johnson', 'peach', 'johnson@myhost.com');

INSERT INTO data (q_uname, Q1) valueS
  ('smith', 1),
  ('jones', 2),
  ('johnson', 3);

When Smith logs in, he will see the "Red" radio button checked by default. Jones will see "Orange," and Johnson will see "Yellow." In this case, the respondents may change the response since it was used to initialize a visible question. You also may use this method to initialize hidden questions which can not be altered by the respondent. Values for hidden questions can be displayed within the text of other, visible, questions.

The SQL scripts you write to load the data table may set be as detailed as needed. Any fields not set in your script will be set to the default values that were specified in the questionnaire program.

The following example also sets the group field, "q_group," and a STRING field in the questionnaire called "Q2," to respondent-specific values.

USE job;
INSERT INTO user (q_uname, q_pswd, q_email) valueS
  ('smith', 'apple', 'smith@myhost.com'),
  ('jones', 'orange', 'jones@myhost.com'),
  ('johnson', 'peach', 'johnson@myhost.com');

INSERT INTO data (q_uname, q_group, Q1, Q2) valueS
  ('smith', 'A', 1, 'Pondicherry'),
  ('jones', 'A', 2, 'Toronto'),
  ('johnson', 'B', 3, 'Washington');