Web Experiment Tutorial: Chapter 8, Additional MySQL

Several years ago, I wrote a tutorial for my previous lab on how to create Web-based experiments in Flash. I am currently posting that tutorial chapter by chapter.

There are a few other useful things you can do in MySQL.

1. Selecting certain rows from a table.

Maybe you want to know how many people have completed your experiment. Your experiment has 6 trials, the last of which is called trial “5”. The easiest way to find out, then, is to use the following command:

mysql> select * from VSTM where trial = 5;

+-------------+-------------+----------------+----------+-------+---------+----------+---------+-------+------------+----------+---------------+------------+

| subject_age | subject_sex | subject_vision | initials | trial | correct | stimulus | matches | probe | date       | time     | ip            | subject_id |

+-------------+-------------+----------------+----------+-------+---------+----------+---------+-------+------------+----------+---------------+------------+

|           2 | male        | no             | jkh      |     5 |       1 |        3 |       1 |     3 | 2007-06-05 | 14:57:33 | NULL          |       NULL |

|          26 | male        | yes            | jkh      |     5 |       1 |        3 |       0 |     2 | 2007-06-05 | 16:01:40 | 140.247.95.39 |          4 |

+-------------+-------------+----------------+----------+-------+---------+----------+---------+-------+------------+----------+---------------+------------+

2 rows in set (0.01 sec)

Two rows were found, so two subjects completed trial #5.

You can use more complicated where statements:

mysql> select * from VSTM where trial = 5 and correct=1;

2. Deleting rows from a table.

To delete all rows from the table VSTM , type:

mysql.> delete from VSTM;

To delete only certain rows, try:

mysql> delete from VSTM where subject_id = “NULL”;

3. Copying a table.

I often run multiple version of an experiment under the same name. Subjects do not know that the experiment has changed. I do this when I don’t want people to participate in the different versions of the same experiment.

I want the data from each version to go into separate tables. For instance, suppose I’ve finished collecting data from the first version of the VSTM experiment and I want to start a second version.

mysql> create table VSTMver1 like VSTM;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into VSTMver1 select * from VSTM;

Query OK, 12 rows affected (0.00 sec)

Records: 12  Duplicates: 0  Warnings: 0

mysql> delete from VSTM;

Query OK, 12 rows affected (0.00 sec)

I now have a table called VSTMver1 with all the data that has been collected so far. The table VSTM is now empty. (Note that the subject numbers will not start over. To do that, you would have to reset VSTM_id_incrementor. I’m not sure of any easy way of doing that other than deleting VSTM_id_incrementor and creating it again. Just deleting all its rows doesn’t work.)

As usual, please leave comments if you have any questions.