CS 564 Project Stage 7: Building a Web Interface

In this project part, you will build a simple Web interface to the database that you have created in stage 2 (recall that this database resides on postgres.cs.wisc.edu). This Web interface will allow you to search, modify, insert, delete, and browse items in your database. You will write PhP code embedded in HTML to build this Web interface.

Here are the steps that you should take.

1. Examine the sample application that we have provided

We created a table book(title,author,num) (where title is a primary key) in "stage7_book_schema.book" (on postgres.cs.wisc.edu). We then created the file https://cs564.cs.wisc.edu/halit/index.html (try to access this file; provide your name and password if the server cs564.cs.wisc.edu asks for those).

You can see that this file represents a Web interface. This interface allows you to execute basic queries searching for books by title (e.g., try to search for the book with title "Introduction to Algorithms"; note that the search is case sensitive).

The interface also links to (1) an advanced search capability (where you can search by, e.g., a combination of title and author), (2) a page where you can update books (e.g., modify or delete an existing book, or insert a new book), (3) a page that displays all books in the database.

Out of all these capabilities, we have implemented the basic search capability and the modify-book capability, so that you can get a feel for how to write PhP code embedded in HTML (more below). Browse around and try out various capabilities, to get a feel for the application.

Next, you should do two things: (1) start reading up on PhP if you haven't done so (more detailed suggestions below), and (2) go to ~cs564-1/php_proj/halit, and examine the various files there, to better understand how PhP gets embedded in HTML files, and how you can use PhP code to connect to and query databases.

2. Now work on your application.

First, select a main entity set on which you will search, modify, insert, delete, and browse. For example, in the sample book application, the main entity set is book. Let X be this main entity set that you have selected.

Next, write a Web interface to do basic search on X, advanced search on X, update X (that is, modify, insert, and delete), and browse X, as shown in the sample book application. Feel free to add other capabilities and extend the interfaces in any way you like. But at the minimum you have to implement the above capabilities.

Specifically, you will have to implement the following pages:

index.html: the main page, which provides a form to do basic search on X (e.g., by asking for a title, name, or ID). This page also links to advanced_search.html, update.html, and browse.php.
basic_search.php: if you fill in the query interface for basic search on index.html, then click "submit", then this page will be invoked. It will actually connect to the database, execute the appropriate SQL query, create an appropriate html page, then return it to the Web browser.
advanced_search.html: this page provides a form interface that allows users to search on a combination of conditions on the attribute values of X. For example, one can search for books whose titles contain "database" and whose prices are below $20. Note that to actually do the search, you will have to implement a page called advance_search.php (in a fashion similar to basic_search.php).
update.html: this page provides three query interfaces that allow users to modify, insert, or delete X items, respectively (see the sample application for the interface on modifying books). To implement these capabilities, you will have to implement three pages: modify.php, insert.php, and delete.php.
browse.php: this page queries the database for all X items (e.g., all books), then display all of these items in a table.

You should implement these pages inside ~cs564-1/php_proj/your-login-name. That directory has been created for you. If you have created the page index.html inside that directory, then you can access it via the URL https://cs564.cs.wisc.edu/your-login-name/index.html . Note that the server may ask for your login name and password. It may also ask you to accept its security certificate (which you should accept).

3. How to submit

If you work in a team, then you only need to develop the code within the directory of a designated team member Y. Once done, please email Halit (halit@cs.wisc.edu) the URL https://cs564.cs.wisc.edu/Y/index.html, so that he can examine your Web interface. Note that we will also check the time stamps of all pages in the directory. So you should NOT modify the code after the submission deadline. Please add "CS 564 Stage 7" in the subject line, and include the team name, full names and login names of all the team members along with the URL in the email.

4. More on PhP

You can examine ~cs564-1/php_proj/halit to see how PhP code is embedded in HTML pages for the sample application (by opening them up in a text editor). The pages that you will examine will show you how to

connect to the PostgreSQL database using PhP. Note that you should connect to your database (not the sample database).
submit queries to the database.
collect form data from the page and submit it to PostgreSQL.

Your PhP codes run as a specific user called "www-user". When you try to read/update your tables in your schema through your PhP code, the database will throw a permission denied error. This is because "www-user" does not have permissions to access your tables. You have to grant permissions to "www-user" in order to manipulate your tables through your PhP code. If your schema's name is schema_X, and you have 3 tables table_X, table_Y, table_Z in this schema, you should run:

grant all on schema schema_X to "www-user";
grant all on table table_X to "www-user";
grant all on table table_Y to "www-user";
grant all on table table_Z to "www-user";

Make sure your search path is set to schema_X before you run this commands. You have to do this for each table in your database. This is similar to how you grant permissions to other group members.

This PhP tutorial will give a good high-level overview of how PhP works. Reading the first few sections will give you a general idea on how to write PhP pages and manipulate a database. The PhP Manual is an excellent reference on PhP and is the place to look for help on some particular aspect of PhP that you might want to look up. PHP provides a bunch of functions for querying PostgreSQL that you can take a look. Finally, a Web search will bring up dozens more PHP tutorials and lots of pertinent information.