CS3/MSc Database Systems

Quick notes on web database integration

The term project will require to build a web interface for the database that you have already designed for homework 3. For that you need to be able to work with HTML and PHP. These notes are intended to give you the basics to get the implementation started, but additional reading (see references) will be probably necessary. Your database front-end should be constructed using HTML &PHP  and the back-end should use the PostgreSQL system.
[Here] you have an example of using PHP&HTML to work online with a simple database.
The main steps are: See more details below.

Your web server account on goldfish

The departmental web server will not be used for your database project for security reasons. Instead, you have been issued with a web account on the machine goldfish.dcs.ed.ac.uk with the same username and password as your PostgreSQL account. In order to prevent everyone from accessing each-others code, the only method of logging in to this machine is via the ftp (File Transfer Protocol). Unfortunately this process is a little cumbersome, and therefore detailed instructions are given below. To construct working HTML and PHP programs it is necessary to perform the following three steps for ever file that you wish to create:
  1. You should first create the HTML/PHP file in your own DCS home directory using your favourite text editor (e.g. xemacs). Files containing PHP code must have the extension .phtml (e.g. test.phtml).
  2. Once you have created the file, you must transfer it to the database web server before it can be tested. This is easiest accomplished using the graphical ftp tool gftp.

  3. Important: The first time you run gftp, you must perform the following steps or you will be unable to transfer files correctly. Hit [Control-O] to bring up the options screen, uncheck the boxes `Preserve Permissions' AND `Passive file transfers', and then hit 'Apply'. You do not need to repeat this every time you start gftp as these options are stored. Once you have configured gftp, you should connect to the web server by entering the following values in the fields at the top of the screen. Enter goldfish in the `Host:' field, enter 21 in the `Port:' field, and enter your username and password in the `User:' and 'Pass:' fields respectively. After entering your password, hit Enter and you will be connected to the database web server. The gftp window contains two directory listings, the left-hand listing is your DCS account and the right hand listing is your web server account. You can transfer files to the web server by selecting the relevant file in your DCS account on the left and clicking on the right-arrow between the directory listings to perform the transfer. You will find it helpful to leave the gftp window open while you debug your code.
  4. After transferring your file to the web server it can be loaded into netscape. You should use the following URL, substituting <user> with your username and <file> for the name of the file you transferred in step 2:

Design your database

Review the first lectures and your solution for Homework 3. Keep in mind that a `good' design simplifies your work and makes your implementations more efficient.

Create and query your database

No explanations here, because by now you should have no problems accessing your accounts on goldfish and to work with PostgreSQL (see the notes on getting started with PostgreSQL, PostgreSQL tutorial and user guide).

Getting started with HTML

I want to believe that everybody is able to deal with HTML (HyperText Markup Language) to write a simple web page.
Anyway, here are some links introducing HTML:
    - HTML Home Page
    - HTML tutorials
    - Sample HTML file
(Tip: once you loaded an HTML page in Netscape, you can see its source  selecting `Page Source' from the menu `View' or directly typing [Alt-U])

Web forms

HTML forms enhance a web page offering the possibility to the user to introduce data.
See the documentation page: Forms in HTML documents and an HTML forms tutorial.

A sample form of introducing data for a student

Student data
Name - 
Surname - 
Matriculation number - 

The source for the above form as a text file can be found [here]. (If you want to use the source you should copy&paste it into a HTML file)

Getting started with PHP

Please read the lecture 13 (see course web page) and the very short introductory PHP tutorial.
Comprehensive online reading: PHP Home Page (including PHP Quick Reference Guide).

Sample web page dealing with a database

Here you can find an example of a web integrated database.
All the files that you need are in the directory
Follow the instruction of from the first webpage.
You will see how to use PHP&HTML to introduce data in a simple database and to make queries on it.


Don't forget that there is a `prize' for the most interesting and nice project!
(you can put that in your CV, too...)

Good luck!