Sawtooth Software: The Survey Software of Choice

Querying a MySQL Database Within Lighthouse Studio

I have a Lighthouse Studio running on a Linux box that uses a MySQL database. How do I use Perl to create a MySQL statement that displays information from a table in my survey's database? For example, I want to display a summary of the data from previous respondents on the last page of a survey.

The following information is outside of the normal functionality of Lighthouse Studio. Assistance implementing this for your specific survey goes beyond our free technical support.

In order to access a MySQL database from within your survey, you'll need to make sure you have the following pieces of information:

  1. host name (i.e. mysurvey.com)
  2. database name
  3. database user name
  4. database password
  5. table name (see below)

The first four items are the same items you have already specified in Lighthouse Studio's Web Server Management dialog box. The table name will come from one of the several tables that are created for each survey. Below is a list of the tables and their fields. Note that the fields of the data1 table will match those you see when you use the Data Management dialog box to view/edit data within Lighthouse Studio.

Table Fields

studyname_admin_log

  1. date : text
  2. action : text
  3. info : text
  4. ip_address : text
  5. user_agent : text
  6. time_stamp : int(11)

studyname_clists

  1. sys_RespNum : int(11)
  2. list_name : text
  3. value : text

studyname_data1

  1. sys_RespNum : int(11)
  2. sys_CheckSum : int(11)
  3. sys_DataSource : int(11)
  4. sys_StartTime : int(11)
  5. sys_EndTime : int(11)
  6. sys_RespStatus : int(11)
  7. sys_DispositionCode : int(11)
  8. sys_LastQuestion : text
  9. sys_UserJavaScript : tinyint(1)
  10. sys_UserAgent : text
  11. sys_OperatingSystem : text
  12. sys_Browser : text
  13. sys_IPAddress : text
  14. ...

studyname_history

  1. sys_RespNum : int(11)
  2. hop : int(11)
  3. ipaddress : varchar(16)
  4. timestamp : int(11)
  5. quest_name : varchar(64)
  6. quest_version : varchar(128)
  7. page_num : int(11)
  8. limbo : tinyint(1)
  9. data : text

studyname_info

  1. data_version : int(11)
  2. layout_build : text
  3. password_build : text
  4. study_path : text
  5. num_data_tables : int(11)
  6. remove_num : int(11)
  7. survey_paused : tinyint(1)
  8. close_survey : tinyint(1)
  9. close_survey_msg : text

studyname_map

  1. table : int(11)
  2. fields : longtext

studyname_quotas (IF QUOTAS ARE PRESENT)

  1. quota_name : text
  2. cell_value : int(11)
  3. cell_limit : int(11)

studyname_passwords (IF PASSWORDS ARE USED)

  1. Password : text
  2. max_respondents : int(11)

Sample Code

Below is the basic code we'll use. In the CONFIG VARIABLES section you'll need to plug in the database information mentioned earlier into the corresponding fields.

[% Begin Unverified Perl
#!/usr/bin/perl

# PERL MODULE
use DBI;

# CONFIG VARIABLES
my $host = "________________";
my $database = "DBI:mysql:________________";
my $tablename = "________________";
my $user = "________________";
my $pw = "________________";
my $sql = "SELECT * FROM $tablename";

# PERL MYSQL CONNECT
my $connect = DBI->connect($database, $user, $pw)
 or die "Connection Error: $DBI::errstr\n";
my $sth = $connect->prepare($sql);
$sth->execute
 or die "SQL Error: $DBI::errstr\n";

# CREATE OUTPUT
my $output .= "\n<ol>\n";
while (my @row = $sth->fetchrow_array) {
 $output .= "\n <li>@row\n</li>";
} 
$output .= "\n</ol>\n";
return $output;

End Unverified %]

Notice the MySQL variable called my $sql. This is where you'll type in the particular MySQL statement that you want to execute. Make sure properly format this MySQL statement to conform with Perl rules, such as adding a \ before certain symbols.

You can find documentation for acceptable MySQL statements on the Internet. A list of handy MySQL commands may be found here: http://www.pantz.org/software/mysql/mysqlcommands.html.

In our example above, the MySQL statement simply selects all of the columns in a particular table. You can make these statements as complex or as simple as you want, including displaying a summary of the data collected from previous respondents. MySQL is very powerful. Just remember that with great power comes great responsibility. You can also purge all of your data with a single MySQL command.

In the CREATE OUTPUT section, the code formats the output in a string and then displays it via the return command.

Sawtooth Software

6:30 AM to 5:30 PM Mountain Time
(GMT-6; GMT-7 Autumn/Winter)
Monday through Friday

Phone: +1 801 477 4700
Fax: +1 801 434 5493
Email: support@sawtoothsoftware.com

SKIM Software

9:00 AM to 5:30 PM Central European
(GMT+2; GMT+1 Autumn/Winter)
Monday through Friday

Phone: +31 10 282 3500
Fax: +31 10 282 3560
Email: software@skimgroup.com
Online: www.skimgroup.com

Lighthouse Studio

Lighthouse Studio is our flagship software for producing and analyzing online and offline surveys. It contains modules for general interviewing, choice-based conjoint, adaptive choice-based conjoint, adaptive choice analysis, choice-value analysis, and maxdiff exercises.

Try Lighthouse Studio

  Buy Lighthouse Studio