Querying a MySQL Database Within Lighthouse Studio

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&gt:\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.