COEN 178 Intro to Database Systems Lab 7

COEN 178 Intro to Database Systems Lab 7
Objectives: Learn
● Using HTML, PHP and Oracle SQL to build a Web application that reads the user input via
an HTML form, processes the input on the server side using PHP and Oracle SQL.
SUBMIT:
● Edited PHP and HTML files
● Website link
DEMO:
● Website
Ref:
https://www.oracle.com/technetwork/database/database-technologies/php/phpintro-otn-090860.html
#t5
In this assignment, you will work with different pieces in building a web application that uses an
Oracle database. You will do the following:
a) Create the tables and necessary PLSQL procedures/functions.
b) Use an HTML web form to input the data that should be inserted into the tables in a)
Invoke a PHP script that takes the input data from the webform in b) and inserts it into the
tables in a. You will learn how to call a PLSQL procedure from a PHP program.
c) Use a web form with a clickable link to display the data in the tables.
Note: You will learn all the steps necessary to build a Web application with a GUI for your
final project.
1
Create the table and PLSQL procedure below:
a) Create table MovieReviews (movieName varchar(25), comments
varchar(40));
b) Create or Replace procedure addMovieReview (v_movieName IN
varchar, v_comment IN varchar) AS
BEGIN
insert into MovieReviews values (v_movieName,v_comment);
END;
/
Exercise 1 (15pts)
In this exercise, you will use an HTML web form for user input and a PHP script on the server
side that processes the input and inserts the input into the tables, MovieReviews.
You will use the following files:
● movieReviewsForm.html
● addMovieReview.php
● showMovieStats.php
a) Copy all the files into your webpages directory (/webpages/<username>/)
b) Open the movieReviewsForm.html with your text editor and give the correct path to your
addMovieReview.php script, in the action attribute of the form. Save your changes.
c) Now, open the addMovieReview.php file with your text editor. Edit the code to put your
username and password to login to Oracle database . Note: At the end of the lab session, please
feel free to change your password.
d) From your browser window, using its URL, open movieReviewsForm.html.
You should see a form shown below.
2
This is the input form to insert data into the MovieReviews table.
e) Enter the name of a movie you have seen and your comments in the fields in the form. Click
submit button.
f) Do you see any messages whether your submission succeeded? You should see the message
below displayed on the page.
Movie Review Inserted
g) Now, from your SQLPlus prompt, do a select * FROM MovieReviews table
and check if the data you submitted via the form is inserted into the table. If the table does not
show any data, give it a second or so, and do the query again. Sometimes, there is a lag time
before you see the data. You may log out of SQL and log back on and try the query.
h) Enter a few more rows of data (movie names and comments) into the tables, using the HTML
form. You should do several submissions for the same movie with different comments.
Q: What is the URL for movieReviewsForm.html?
A:
3
Exercise 2 (10pts)
In this exercise, you will use showMovieStats.php script, which when run opens a web page
with a clickable link as below.
a) Open the file showMovieStats.php in your text editor. Edit the path of the file in the
href attribute in the <a> tag. Save your changes.
b) Now, open the file, showMovieStats.php in the browser window. Click on the link
displayed and check if the displayed stats are correct. The stats show below are for the
data that I entered.
4
Exercise 3 (25 pts)
Now, you should provide one more stat when you click the link on showMovieStats.php page.
The stat should show the movie (s) with the most no. of reviews.
The output should be as below:
Edit the showMovieStats.php file and add one more query (follow the comment lines in the
file) to find the name(s) of movie(s) with maximum no. of reviews
5