MySQL and mSQL

An Example DBI Application

DBI allows for the full range of SQL queries supported by MySQL and mSQL. As an example, consider a database used by a school to keep track of student records, class schedules, test scores, and so on. The database would contain several tables, one for class information, one for student information, one containing a list of tests, and a table for each test. MySQL and mSQL’s ability to access data across tables—such as the table-joining feature—enables all of these tables to be used together as a coherent whole to form a teacher’s aide application.

To begin with we are interested in creating tests for the various subjects. To do this we need a table that contains names and ID numbers for the tests. We also need a separate table for each test. This table will contain the scores for all of the students as well as a perfect score for comparison. The test table has the following structure:

CREATE TABLE test (
  id INT NOT NULL AUTO_INCREMENT,
  name CHAR(100),
  subject INT,
  num INT
)

The individual tests have table structures like this:

CREATE TABLE t7 (
  id INT NOT NULL,
  q1 INT,
  q2 INT,
  q3 INT,
  q4 INT,
  total INT
)

The table name is t followed by the test ID number from the test table. The user determines the number of questions when he or she creates the table. The total field is the sum of all of the questions.

The program that accesses and manipulates the test information is test.cgi. This program, which follows, allows only for adding new tests. Viewing tests and changing ...