The site used to register CS186 groups was designed using PHP and PostgreSQL. Although for security reasons, the site is more complex in some areas than the one you will need to design, it will serve as a good starting example. While designing the site we tried to comment and code 'cleanly' to make the example as useful as possible for you.
You can download the source files used to run the registration site here. With the exception of removing the password, the files are the exact SQL and PHP files used in the actual site. You can report any bugs to huebsch@cs.berkeley.edu.
There are three main parts to the site:
Users begin by going to the web page, group-reg.php. This script displays the web form and receives user submissions. All data submissions are checked to make sure they exist and are not duplicate information. If the data is all in working order, it is passed to the back-end website using a HTTP request.
The back-end sites repeats all the data verification and also checks to make sure only valid characters and numbers are in the input. This final check prevents a user from issuing their own SQL commands by 'ending' the previous command and providing their own. Any errors are immediately reported to the requested through a simple one word response code and processing stops.
The script then connects to the database, any errors here are blamed on the system and reported as such. A log of the request is then recorded in the database.
Our scripts use transactions to help make the registration process atomic. This means (as you will later learn in more detail) that either the entire registration occurs, or none of it. All commands between the BEGIN and COMMIT (or ROLLBACK) are considered part of the transaction. COMMIT will make the effects of all commands permanent, while ROLLBACK will undo any changes made. You are not required to use transactions, although they you may find it can make your code simpler.
Each student will be checked to make sure they exist and have not already registered. If there is a problem, a nondescript error message is sent to the requester and the exact error condition is logged to the database. If there are no problems a new group is created, students are assigned to that group and the changes are committed. A final log entry is made and an email is sent to the students.
The bulk of the actual database interaction is contained in a included file, db.php. This file shows how the actual connection to the database is made, how to execute SQL commands, and how to retrieve information from an executed SQL statement.
We also provided the schema files we used to create the database. The first, schema.sql contains the primary DDL commands that creates the tables and associated structures. You will notice a structure called a sequence. Sequences are basically counters that maintained by the database and are commonly used to generate unique numbers. We use the counters to generate log ids and group ids. A function called NEXTVAL retrieves the current value for a sequence and increments its value.
The file, access.sql, resets the permissions on all the tables, limiting what the access the website has in case of a problem.
The file, views.sql, contains a number of views we use to see/collate the data. You may find it useful to use views to simplify the SQL in your scripts, although that is not reason we did in this case since the data is not accessible via the web.
One final note regarding our schema. We used a table called students_in which is where the raw data was loaded. The schema of this table is different from the actual students table due to the format of the data from TeleBears. We used a SQL statement to convert the data from one format to the other, which is in the file dataconv.sql. In the real world, it is common that the data needs to be cleaned/converted before being loaded into a database. Most major database products contain extensive utilities that make this process easy and extensible. Unfortunately, PostgreSQL does not support easy loading techniques, requiring us to process the data after loading it into the system. For your project you will not need to worry about this.