CS1102 Lab 14

Inside your public_html/cs1102 folder, create a subdirectory called lab14 (all lowercase) to hold this week's work.

If you do not finish the work during the lab, remember that we are in a university. We expect you to engage in private study. In other words, finish the work in your own time. And, do so independently. Let's have no more cogging. If you need help, let me know.

All kinds of everything (1970)

This lab is about Eurovision winners. First you must create a database. Find the email that the Department sent you on Friday 30th November whose subject line was 'Project database'. This email gives you: a user name, a password, and a database name. If you've deleted it, speak to me.

Save a copy of the following file in your lab14 folder: eurovision.sql.
Use ssh to login to cosmos (e.g. using the SSH Secure Shell Client, as we have done before).
On cosmos, type the following:
```
mysql -u userid -p
```
replacing userid with the user id given in the email mentioned earlier.
You will be asked for a password. Use the one from the email.
Then, when the mysql prompt appears, enter the following:
```
use dbname;
```
replacing dbname with the database name given in the email. (Note: you need the semi-colon.)

At the mysql prompt, enter the following:

source public_html/cs1102/lab14/eurovision.sql;

If all of that worked OK, then you now have a database table called winners, which has the following columns: year, country, song, performer, and points. Why not check by typing:
```
select * from winners;
```
You should see a big table of Eurovision winners.
You can now close down the SSH Secure Shell Client.

What's another year? (1980)

Create a file called euroform.html (do I need to tell you to base it on template.html?) to display a form. The form should contain a label, a text-field and a submit button. The text-field allows the user to type in a country. Note that we are not writing a self-processing page today...

Hold me now (1987)

Create a file called euroscript.php (do I need to tell you to base it on template.php?), which will: get the country that the user typed in; check that it is non-empty; and retrieve from the database all winners from that country.

Assuming you have stored the user's country in a variable called $country, then the SQL query that you need is:

	"SELECT * FROM winners WHERE country = '{$country}';"

Remember that to connect to the database you need to supply a host (cosmos.ucc.ie). You also need to supply a user id and password: these will come from the email mentioned earlier. And to select the database, you use the database name also supplied in the email.

Your script should output a table containing data about all of that country's Eurovision winners.

Why me (1992)

Add another label and text-field to your form to allow the user to enter a number, so that s/he can search for winners who attained more than that number of points.

Modify your script so that it now works as follows:

If the user only enters a country (and no points), use the SQL query I gave you earlier.
If s/he enters only a number of points (and no country), assuming you've stored his/her number in a variable called $points, then use the following SQL query:
```
	"SELECT * FROM winners WHERE points > {$points};"
```
If the user has entered both a country and a number of points, use the following SQL query:
```
	"SELECT * FROM winners WHERE country = '{$country}' AND points > {$points};"
```
And if the user has entered neither a country nor points, use the following SQL query:
```
	"SELECT * FROM winners;"
```

In your eyes (1993)

Write a stylesheet to prettify the form.

Rock 'n' Roll Kids (1994)

Write a stylesheet to prettify the results table. E.g. if you include class attributes, you can make odd-numbered rows have one background colour, and even-numbered rows have a different backround colour.

The voice (1996)

Optional: Typing in a country is not very satisfactory. Better would be a menu, where the menu items are actually obtained from the database (using "SELECT DISTINCT country FROM winners;"). I haven't shown you how to create menus. So this optional activity comes with the additional challenge of finding out how to do this from textbooks or the Web. If you want to try this optional activity, leave euroform.html untouched. Do it in a file called euroform.php. (Since this activity is optional, it is not for credit.)

Zip your lab14 folder and submit lab14.zip in the usual way, selecting LAB14 when asked.

Deadline: 1pm, Tuesday 19th February.

In preparation for next week

The final lab will be a 'project' that will last until the end of term. The project will be done in pairs.

In time for next week, find your partner.

In time for next week, think of a scenario where you would want to maintain a database and to update and query the database from your web browser. For example, a club or society may wish to keep membership records; a lepidopterist may wish to catalogue his/her collection of butterflies; a bookshop may wish to keep track of its stock;...

With your partner, discuss your chosen scenario with me next week. Make sure your chosen scenario is ambitious enough to warrant a 'project' that will last for several weeks and will be done in pairs. (E.g. a database with just one table, a form for insertion and a form for retrieval is almost certainly no good.)

Bear in mind that I will help you to design the database and to write the SQL.