CSE3MS Lab 3 - Introducing MySQL

Laboratory three deals with getting started in MySQL. In this laboratory you will use database tables that have been already created, and explore how to create output by issuing SQL queries on that table, and then processing the resulting output using PHP.

Resources

Exercise 1

Download the following outline files for the PHP scripts that accesses MySQL to your working location in the team account that you were using last week. You may need to shift click, or right click on this link.

Rename the files you have downloaded to be lookup.php3 and viewSubject.php3 in the local directory. Then find the include file named "connect.inc" in the htdocs directory and copy that to your working directory, or create a symbolic link to it. This include file contains the user name and password for your MySQL account. Now link the MySQL script to your html page, or just load the appropriate URL to access the script.

Note the error message that indicates that there are no data tables to access.

For reference purposees, you can see the script operating in a full version here.

Exercise 2

After completing exercise 1, you will note that the script detects that no data is present in the database, and that you need to add tables and data to be able to generate information from SQL queries and return it to the web interface implemented by the PHP code.

To create tables containing some sample data for mysql you might find the following scripts useful.

For instance to create new database tables, and populate them with the lab information one might proceed as follows.

mysql -u cs3ms49 -p<passwd> cs3ms49 < createTable.txt
mysql -u cs3ms49 -p<passwd> cs3ms49 < table1.sql.txt
mysql -u cs3ms49 -p<passwd> cs3ms49 < table2.sql.txt

Now check the output of the script, and data should be displayed for a number of students.

Exercise 3

Examine the index.php3 script that you have been given and experiment with it to create the following changes.

Select only the Student ID from the table.
Select only the Given Name and Surname from the table.
Order the results of the two selections of data above by
- ID,
- Given name.
Modify the table to create a display with some selected student information displayed in two columns.
```
Eg.

ID    Surname     Subjects ID    Surname    Subjects
```
An example of a page with the appropriate format can be viewed here.

Exercise 4

The data that you have been extracting and processing is stored in a MySQL table called Students. Another table called Subjects was also created in exercise 2. This table stores subject codes and student ID pairs under the keys

ID and SUBJECT_CODE

A subsidiary form called viewSubjects.php3 has also been provided above. Download this form, and read through it to understand what it does so far. The link to the index.php3 form is already included, and a parameter sid is used to communicate the student ID to the sub-form. Add in additional code that selects all the SUBJECT_CODE entries for a given student ID from the Subjects data table in MySQL and prints them one below the other in a table.

Once again, refer to the sample solution shown above for an indication of what your solution should do. The student Justin Toomey is a good example to select.